0

I have a problem inserting multiple rows at the same time. I have this problem since the last update of android (api 19) and eclipse. Before that the code below did work.

I tried to set api 18 as target instead of api 19, but the error is the same.

[EDITED] This code works on Nexus 4, with Android 4.3, but not on a device with Android 4.0.4

Here is my code:

StopsSQLiteHelper usdbh = new StopsSQLiteHelper(this, "DBStops.db", null, 1);

SQLiteDatabase db = usdbh.getWritableDatabase();

db.execSQL("CREATE TABLE Lines(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,name VARCHAR(150) NOT NULL)");

db.execSQL("CREATE TABLE Stops(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,id_line INTEGER NOT NULL,name VARCHAR(150) NOT NULL,favorite VARCHAR(20), num_post VARCHAR(10),coord_lat VARCHAR(50),coord_long VARCHAR(50),FOREIGN KEY (id_linea) REFERENCES Lines(id))");

db.execSQL("CREATE TABLE Timetable(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,id_line INTEGER NOT NULL,id_stop INTEGER NOT NULL,hour VARCHAR(5) NOT NULL,day_type VARCHAR(50) NOT NULL,FOREIGN KEY (id_line) REFERENCES Lines(id),FOREIGN KEY (id_stop) REFERENCES Stops(id))");

db.execSQL("INSERT INTO Lines (nombre) VALUES ('Stop1')");

db.execSQL("INSERT INTO Stops (id_line, name, favorite, coord_long, coord_lat) VALUES (1,'Name1','Pref1',62.625039,-8.938559)");

db.execSQL("INSERT INTO Timetable (id_line, id_stop, hour, day_type) VALUES (1,1,'00:00','workable'), (1,1,'04:55','workable')...");

db.close();

... (I insert more data but I don't write it here to make easier to read.)

And here the error: [EDITED]

11-05 17:19:56.649: E/AndroidRuntime(10918): FATAL EXCEPTION: IntentService[MyIntentServicer]
11-05 17:19:56.649: E/AndroidRuntime(10918): android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: INSERT INTO Timetable (id_line, id_stop, hour, day_type) VALUES  (1,1,'00:00','workable'), (1,1,'04:55','workable')
11-05 17:19:56.649: E/AndroidRuntime(10918):at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:68)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:143)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:361)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:260)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:84)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1899)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1839)
11-05 17:19:56.649: E/AndroidRuntime(10918): at com.albertrn.example.LoadingDBIntentService.meter(LoadingDBIntentService.java:191)
11-05 17:19:56.649: E/AndroidRuntime(10918): at com.albertrn.example.LoadingDBIntentService.onHandleIntent(LoadingDBIntentService.java:32)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:65)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.os.Handler.dispatchMessage(Handler.java:99)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.os.Looper.loop(Looper.java:137)
11-05 17:19:56.649: E/AndroidRuntime(10918): at android.os.HandlerThread.run(HandlerThread.java:60)
albertiko89
  • 1
  • 2
  • 1
    post the **complete** error log. – njzk2 Nov 05 '13 at 15:31
  • 1
    and post some actual code that causes the issue. If you think there is too much code and it won't be readable, then reduce the amount of data you insert, and reproduce the issue with this reduced data set. – njzk2 Nov 05 '13 at 15:32
  • does it make sense that you `CREATE TABLE Timetable` but `INSERT INTO Horarios` ? – njzk2 Nov 05 '13 at 15:33
  • duplicate of [Is it possible to insert multiple rows at a time in an SQLite database?](http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database) – CL. Nov 05 '13 at 15:35
  • It is not a duplicate because my code worked before the update. – albertiko89 Nov 05 '13 at 17:06
  • Sorry, I wrote wrong the code, I have this in my code: **INSERT INTO Timetable** – albertiko89 Nov 05 '13 at 17:44
  • @user2956654 After the update, you're using an older SQLite version for some reason. – CL. Nov 05 '13 at 19:53

1 Answers1

1

The INSERT syntax is invalid for SQLite. You can't insert multiple rows like this:

INSERT INTO table (a, b, c) VALUES (1,2,3), (3,4,5)...

Instead it should be either multiple statements run one at a time:

INSERT INTO table (a, b, c) VALUES (1,2,3)
INSERT INTO table (a, b, c) VALUES (3,4,5)

or a single statement using select and union:

INSERT INTO table (a, b, c)
SELECT 1,2,3
UNION
SELECT 3,4,5
NigelK
  • 8,255
  • 2
  • 30
  • 28
  • ...or a least I believe it is for the SQLite version in use here. Good link in the comments under the question made by CL. – NigelK Nov 05 '13 at 15:44
  • But before the last update of Android and Eclipse, my code did work. I don't understand why doesn't it work now – albertiko89 Nov 05 '13 at 16:34