Need help with SQL Insert command
I can't find any examples of a database SQL Insert command beyond the ultra simple ones in the KitchenSink. I created a beginning DB with the SQLite manager add-on for FireFox. This has worked so far for Select commands, but now I'm trying to Insert new rows. I defined the "id" column as Primary Key, AutoIncrementing.
How do I construct the db.execute('INSERT… statement to let the "id" do its autoincrement? I also want to leave out some column names, as there is no data to place there yet. Can I just leave them out of the statement?
These statements work fine:
var db = Titanium.Database.install('chirps.db','chirps' + dbVersion);
var rows = db.execute('SELECT * FROM ' + dbTableName);
But this one does not work:
db.execute('INSERT INTO ' + dbTableName + ' (latitude, longitude, chunk2, date, cricket, comment, posted, delete, rank, cnvrtaddr) VALUES(?,?,?,?,?,?,?,?,?,?)',theNewMarker.latitude,theNewMarker.longitude,theNewMarker.chunk2,theNewMarker.date,theNewMarker.cricket,theNewMarker.comment,0,0,0,0);
Do I need to include every column in the table? What about the "id" column, since I don't know its new value?
4 Answers
-
Accepted Answer
No, you don't need to include the id column since SQLITE puts it in as an auto-increment primary key. Here's the code I use:
var db = Titanium.Database.install('database.db','table');
var rows = db.execute('INSERT INTO table (column1, column2, column3, column4) VALUES("'+column1.value+'","'+column2.value+'", "'+column3.value+'", "'+column4+'")');You should be able to leave the other columns out that you aren't using yet. The values I use reference a textfield. Otherwise, if you have preset values it would look like this: VALUES(value1, value2, value3, value4).
-
I used your example to reformat my code, but still get an error. My create table statement is included for reference.
CREATE TABLE "chirpid_markers" ("id" INTEGER PRIMARY KEY AUTOINCREMENT , "latitude" DOUBLE, "longitude" DOUBLE, "chunk1" INTEGER, "chunk2" INTEGER, "date" VARCHAR, "cricket" VARCHAR, "user" VARCHAR, "comment" VARCHAR, "posted" BOOL, "delete" BOOL, "rank" INTEGER, "address" VARCHAR, "cnvrtaddr" BOOL)2010-04-06 08:14:19.788 ChirpID[824:b403] [ERROR] A SQLite database error occurred on database '/Users/brucemartin/Library/Application Support/iPhone Simulator/User/Applications/0D75D7BB-5170-46D1-8A45-8186AE551E1C/Library/Application Support/database/chirps2.5.sql': Error Domain=com.plausiblelabs.pldatabase Code=3 UserInfo=0x9c1b750 "An error occured parsing the provided SQL statement." (SQLite #1: near "delete": syntax error) (query: 'INSERT INTO CHIRPID_MARKERS (latitude, longitude, chunk2, date, cricket, comment, posted, delete, rank, cnvrtaddr) VALUES(40.778005,-73.95154199999999,10601308,"April 6, 2010 8:13:55 AM EDT","Fork-tailed bush katydid 1","My comment is here",0,0,0,0)') [WARN] invalid SQL statement. Error Domain=com.plausiblelabs.pldatabase Code=3 UserInfo=0x9c1b750 "An error occured parsing the provided SQL statement." in -[TiDatabaseProxy execute:] (TiDatabaseProxy.m:132)I also tried the format as shown in KitchenSink, using the VALUES(?,?,?,… format, and got the same error.
-
Well, I read in the SQLite docs that double quotes are not allowed around literals, so I re-worded my code to put single quotes around the strings. Still get the error. See below:
```
2010-04-06 15:50:04.179 ChirpID[4679:b403] [ERROR] A SQLite database error occurred on database '/Users/brucemartin/Library/Application Support/iPhone Simulator/User/Applications/5648DC88-32AB-434B-A5C8-1897F6062767/Library/Application Support/database/chirps2.6.sql': Error Domain=com.plausiblelabs.pldatabase Code=3 UserInfo=0x9610870 "An error occured parsing the provided SQL statement." (SQLite #1: near "delete": syntax error) (query: 'INSERT INTO CHIRPID_MARKERS (latitude, longitude, chunk2, date, cricket, comment, posted, delete, rank, cnvrtaddr) VALUES(40.778005,-73.95154199999999,10601308,'April 6, 2010 3:50:01 PM EDT','Fork-tailed bush katydid 1','Your comment goes here',0,0,0,0)')
~~~
Any other suggestions? -
I figured it out.
The column name 'delete' is a reserved word, and can not be used. (Google is your friend.) There is also a clue in the error response, if you know what to look for: (SQLite #1: near "delete": syntax error).
Your answer is correct, and helped me alot. Thanks.