I´m having problems with sqflite in Flutter.
Normal inserts work just fine... but as soon as I insert a value, which is the id I get from inserting int into the first table, I get a syntax error near ")".
This is the error I get:
flutter: error DatabaseException(Error Domain=FMDatabase Code=1 "near ")": syntax error" UserInfo={NSLocalizedDescription=near ")": syntax error}) sql 'CREATE TABLE pageM (id INTEGER PRIMARY KEY,caardId INTEGER,title INTEGER,content TEXT,)' args []} during open, closing... [VERBOSE-2:ui_dart_state.cc(157)] Unhandled Exception: DatabaseException(Error Domain=FMDatabase Code=1 "near ")": syntax error" UserInfo={NSLocalizedDescription=near ")": syntax error}) sql 'CREATE TABLE pageM (id INTEGER PRIMARY KEY,caardId INTEGER,title INTEGER,content TEXT,)' args []} #0 wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7) #1 SqfliteDatabaseFactoryImpl.wrapDatabaseException (package:sqflite/src/factory_impl.dart:78:7) #2 SqfliteDatabaseMixin.safeInvokeMethod (package:sqflite_common/src/database_mixin.dart:208:15) #3 SqfliteDatabaseMixin.invokeExecute (package:sqflite_common/src/database_mixin.dart:370:12) #4 SqfliteDatabaseMixin.txnExecute. (package:sqflite_common/src/database_mixin.dart:362:14) #5 SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:312:26) #6 SqfliteDatabaseMixin.txnWriteSynchronized (package:sqflite_common/src/database_mixin.dart:345:<…>
Here my insert:
onPressed: () async {
currentCaard = Caard(topicId: widget.topicId, pageAmount: widget.pageAmount);
print(currentCaard);
currentCaard.id = await DatabaseProviderCaard.db.insert(currentCaard);
currentPage = PageM(caardId: currentCaard.id, title: textControllerTitle.text,content: textControllerContent.text);
DatabaseProviderPage.db.insert(currentPage);
},
and here is my whole db Provider file:
import 'package:caards/model.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';
import 'package:sqflite/sqlite_api.dart';
class DatabaseProviderCaard {
static const String TABLE_CAARD = "caard";
static const String COLUMN_ID = "id";
static const String COLUMN_TOPIC_ID = "topicId";
static const String COLUMN_PAGE_AMOUNT = "pageAmount";
DatabaseProviderCaard._();
static final DatabaseProviderCaard db = DatabaseProviderCaard._();
Database _database;
Future<Database> get database async {
print("caard database getter called");
if (_database != null) {
return _database;
}
_database = await createDatabase();
return _database;
}
Future<Database> createDatabase() async {
String dbPath = await getDatabasesPath();
return await openDatabase(
join(dbPath, 'caardDB.db'),
version: 1,
onCreate: (Database database, int version) async {
print("Creating caard table");
await database.execute(
"CREATE TABLE $TABLE_CAARD ("
"$COLUMN_ID INTEGER PRIMARY KEY,"
"$COLUMN_TOPIC_ID INTEGER,"
"$COLUMN_PAGE_AMOUNT INTEGER"
")",
);
},
);
}
Future<List<Caard>> getCaards() async {
final db = await database;
var caards = await db
.query(TABLE_CAARD, columns: [COLUMN_ID, COLUMN_TOPIC_ID, COLUMN_PAGE_AMOUNT]);
List<Caard> caardList = List<Caard>();
caards.forEach((currentCaard) {
Caard caard = Caard.fromMap(currentCaard);
caardList.add(caard);
});
return caardList;
}
Future<int> insert(Caard caard) async {
final db = await database;
caard.id = await db.insert(TABLE_CAARD, caard.toMap());
return caard.id;
}
Future<int> delete(int id) async {
final db = await database;
return await db.delete(
TABLE_CAARD,
where: "id = ?",
whereArgs: [id],
);
}
Future<int> update(Caard caard) async {
final db = await database;
return await db.update(
TABLE_CAARD,
caard.toMap(),
where: "id = ?",
whereArgs: [caard.id],
);
}
}
class DatabaseProviderPage {
static const String TABLE_PAGE = "page";
static const String COLUMN_ID = "id";
static const String COLUMN_CAARD_ID = "caardId";
static const String COLUMN_TITLE = "title";
static const String COLUMN_CONTENT = "content";
DatabaseProviderPage._();
static final DatabaseProviderPage db = DatabaseProviderPage._();
Database _database;
Future<Database> get database async {
print("page database getter called");
if (_database != null) {
return _database;
}
_database = await createDatabase();
return _database;
}
Future<Database> createDatabase() async {
String dbPath = await getDatabasesPath();
return await openDatabase(
join(dbPath, 'pageDB.db'),
version: 1,
onCreate: (Database database, int version) async {
print("Creating page table");
await database.execute(
"CREATE TABLE $TABLE_PAGE ("
"$COLUMN_ID INTEGER PRIMARY KEY,"
"$COLUMN_CAARD_ID INTEGER,"
"$COLUMN_TITLE INTEGER,"
"$COLUMN_CONTENT TEXT,"
")",
);
},
);
}
Future<List<PageM>> getPages() async {
final db = await database;
var pages = await db
.query(TABLE_PAGE, columns: [COLUMN_ID, COLUMN_CAARD_ID, COLUMN_TITLE, COLUMN_CONTENT]);
List<PageM> pageList = List<PageM>();
pages.forEach((currentPage) {
PageM page = PageM.fromMap(currentPage);
pageList.add(page);
});
return pageList;
}
Future<PageM> insert(PageM pageM) async {
final db = await database;
pageM.id = await db.insert(TABLE_PAGE, pageM.toMap());
return pageM;
}
Future<int> delete(int id) async {
final db = await database;
return await db.delete(
TABLE_PAGE,
where: "id = ?",
whereArgs: [id],
);
}
Future<int> update(PageM page) async {
final db = await database;
return await db.update(
TABLE_PAGE,
page.toMap(),
where: "id = ?",
whereArgs: [page.id],
);
}
}
and here is my Model:
import 'package:caards/database_provider.dart';
class Caard {
int id;
int topicId;
int pageAmount;
Caard({this.id, this.topicId, this.pageAmount});
Map<String, dynamic> toMap() {
var map = <String, dynamic>{
DatabaseProviderCaard.COLUMN_ID: id, //versuch
DatabaseProviderCaard.COLUMN_TOPIC_ID: topicId,
DatabaseProviderCaard.COLUMN_PAGE_AMOUNT: pageAmount
};
if (id != null) {
map[DatabaseProviderCaard.COLUMN_ID] = id;
}
return map;
}
Caard.fromMap(Map<String, dynamic> map) {
id = map[DatabaseProviderCaard.COLUMN_ID];
topicId = map[DatabaseProviderCaard.COLUMN_TOPIC_ID];
pageAmount = map[DatabaseProviderCaard.COLUMN_PAGE_AMOUNT];
}
}
class PageM {
int id;
int caardId;
String title;
String content;
PageM({this.id, this.caardId, this.title, this.content});
Map<String, dynamic> toMap() {
var map = <String, dynamic>{
DatabaseProviderPage.COLUMN_CAARD_ID: caardId,
DatabaseProviderPage.COLUMN_TITLE: title,
DatabaseProviderPage.COLUMN_CONTENT: content
};
if (id != null) {
map[DatabaseProviderPage.COLUMN_ID] = id;
}
return map;
}
PageM.fromMap(Map<String, dynamic> map) {
id = map[DatabaseProviderPage.COLUMN_ID];
caardId = map[DatabaseProviderPage.COLUMN_CAARD_ID];
title = map[DatabaseProviderPage.COLUMN_TITLE];
content = map[DatabaseProviderPage.COLUMN_CONTENT];
}
}
```
You need to syntactically correct the SQL query. You currently use:
'CREATE TABLE pageM (id INTEGER PRIMARY KEY,caardId INTEGER,title INTEGER,content TEXT,)'
This query has an extra comma in the end, which is not syntactically correct. That is why the DB complains about a syntax error. In fact in the error message of the DB there is the query created using the code, inside which there is a trailing comma ',' before the parenthesis. Change your code so that the query is:
'CREATE TABLE pageM (id INTEGER PRIMARY KEY,caardId INTEGER,title INTEGER,content TEXT)'
You need to change the following part in the DB Provider file.
The error is caused because there is a comma after TEXT in the following part:
await database.execute(
"CREATE TABLE $TABLE_PAGE ("
"$COLUMN_ID INTEGER PRIMARY KEY,"
"$COLUMN_CAARD_ID INTEGER,"
"$COLUMN_TITLE INTEGER,"
"$COLUMN_CONTENT TEXT,"
")",
A correct version would be the following.
await database.execute(
"CREATE TABLE $TABLE_PAGE ("
"$COLUMN_ID INTEGER PRIMARY KEY,"
"$COLUMN_CAARD_ID INTEGER,"
"$COLUMN_TITLE INTEGER,"
"$COLUMN_CONTENT TEXT"
")",