Why won't this SQL Lite insert statment work

Question

Can anyone tell me why the following two sql lite insert statements are working:

tx.executeSql("INSERT INTO EVENTS ('name','altId','time') VALUES (?,?,?)",["one", "two","three"]);

tx.executeSql("INSERT INTO EVENTS ('name','altId') VALUES (?,?)",[("one", "two"),("three", "four")]);

but the statement below which I came up with following the answer to this question one:

tx.executeSql("INSERT INTO EVENTS ('name','altId','time') VALUES (?,?,?)",[("one", "two","three"),("four", "five","six")]);

throws error: 5 number of '?'s in statement string does not match argument count

You havespecified 3 columns to insert but your passing only 2 values... and you can make you use of err.message to get the message string. — Divesh Salian, Dec 13 '13 at 07:28
So are you saying that in my second working example I'm inserting the values "one" and "two" into the 'name' column and "three" and "four" into the 'altId' column. As opposed to "one" and "three" in the name coulmn and "two" and "four" into the altId column? — Ben Pearce, Dec 13 '13 at 07:34
Have you check what values actually end up in those columns? — CL., Dec 13 '13 at 08:27
I'm doing this on an iphone so I wasn't sure how to check my database with out doing a SELECT statement which I couldn't execute becuase of the error. Floowing Divesh Salian's suggestion I only get one inserted row containing the last set of values. i.e. in the context of my second example the one row contains the values "two" and "four" — Ben Pearce, Dec 13 '13 at 09:05
This might help you. > http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database — Balaji SS, Dec 13 '13 at 09:58

CL. · Answer 1 · 2013-12-13T08:43:47.160

0

You must give exactly as many parameters as there are in the SQL statement. If you want to execute the statement multiple times, you have to tell the computer to execute the statement multiple times:

tx.executeSql("INSERT INTO EVENTS (name,altId,time) VALUES (?,?,?)",
              ["one", "two","three"]);
tx.executeSql("INSERT INTO EVENTS (name,altId,time) VALUES (?,?,?)",
              ["four", "five","six"]);

A multi-row insert (which is not support in all SQLite versions, so you should not use it) is just a single SQL statement with more parameters:

tx.executeSql("INSERT INTO EVENTS (name,altId,time) VALUES (?,?,?),(?,?,?)",
              ["one", "two","three","four", "five","six"]);

It might be a better idea to write a helper function that calls the single-row INSERT multiple times.

edited Dec 13 '13 at 08:43

answered Dec 13 '13 at 08:28

CL.

173,858
17
217
259

Is there no way to insert multiple rows in a single statement? i.e. a parameterized version of "INSERT INTO EVENTS (name,altId,time) VALUES ("joe,1,"5pm"),("bill,1,"6pm")" – Ben Pearce Dec 13 '13 at 08:37
Hi CL, thanks again for your attention to this question. I'm not able to get your multi role example work, I get the message "number of '?'s in statement string does not match argument count". It's also doesn't seem right that a best practice is to execute a seperate INSERT statement for each row as this seems like it has the potential to get very expensive. – Ben Pearce Dec 13 '13 at 08:58
Why should it be more expensive? This isn't a client/server database. – CL. Dec 13 '13 at 09:29
It still seem weird to me that sqlite supports multi INSERTS but that it breaks down when you try to parameterize it. I could be wrong and I really appreciate your efforts, if I can't figure out another way to do it I'll check your answer. – Ben Pearce Dec 13 '13 at 09:33
Hey CL. I'm convinced that multiple INSERTS are alright. But I still can't get the bottom line of code in your answer to work. If you delete that part I'll mark the answer accepted. Thanks for all the help! – Ben Pearce Dec 14 '13 at 11:36

Why won't this SQL Lite insert statment work

1 Answers1