0

I am trying to insert values on an android device into an sqlite database.

However sometimes the following error is thrown:

android.database.sqlite.SQLiteException: near ",": syntax error: , while compiling: INSERT INTO sensor_event (created_at, updated_at, type, x, y, z, time) VALUES (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?)

Unfortunately I have no clue why the query is not going through. Anyone else?

Cheers,

Gordon

GordonHo
  • 131
  • 1
  • 2
  • 6
  • 2
    What do you mean by "sometimes", it works occasionally? When does it work, what's different? Can you post your insert method in your question? – jnthnjns Sep 20 '12 at 18:30
  • aparrently yes. the query is generated automatically (how many entries are inserted). the query sometimes crashes, however the length is not related to the crash. – GordonHo Sep 20 '12 at 18:36
  • By the way after doing some benchmarking on database approaches in Android I support the idea that generating such long inserts are both error prone and slower than conventional approaches - you can check my code here: https://code.google.com/p/gdg-sofia-prototypes/source/browse/#svn%2Ftrunk%2FAndroidPersistenceBenchmark – Boris Strandjev Mar 09 '13 at 17:55

2 Answers2

5

What you provide is not a valid sqlite syntax. SQLite has somewhat of support to multiple inserts in single instruction, but it is done in slightly different manner. See here.

How do you generate your query and what does sometimes mean in your question - for me error will occur every time you attempt a query like that.

Rewrite your query like so:

INSERT INTO sensor_event
SELECT ? as created_at, ? as updated_at, ? as type, ? as x, ? as y, ? as z, ? as time
UNION SELECT ?, ?, ?, ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?, ?, ?, ?
UNION SELECT ?, ?, ?, ?, ?, ?, ?
Community
  • 1
  • 1
Boris Strandjev
  • 46,145
  • 15
  • 108
  • 135
  • afaik sqlite currently supports the prior mentioned syntax. however the link was quite a help. i'll try to include a version check which syntax to use. would it make a speed difference which syntax to use? – GordonHo Sep 20 '12 at 18:58
  • @GordonHo - I personally do not know if there will be time difference. Actually the fact of the syntax being supported is also new to me. I would be very interested to see the results of your investigations. Please add them to the thread. – Boris Strandjev Sep 20 '12 at 19:01
  • i will do. hope i can manage to squeeze it in next week. meanwhile the following link is quite helpfull as well. SQLite versions in android versions: http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android – GordonHo Sep 21 '12 at 19:34
0

SQLite allows compound insert statements since version 3.7.11

Android uses 3.7.11 since version 4.1 Earlier versions will fail as mentioned by Boris Strandjev.

SQLite versions in android versions: Version of SQLite used in Android?

Community
  • 1
  • 1
GordonHo
  • 131
  • 1
  • 2
  • 6