iPhone Web App database SQLite and MySQL

Question

Hi, I am making a planner application for the iphone that can work online to store tasks in a mysql server. However, when I attempt to synchronise the two databases I have a problem. The thing seems to be that I can't insert more than one set of values at once into the iPhone database:

INSERT INTO planner (title, duedate, submitdate, subject, info) VALUES ('Poster', '21092010', '28092010', 'chemistry', 'elements poster'), ('Essay', '22092010', '25092010', 'english', 'essay on shakespeare')

This does not work. There is no error or anything like that, it simply does nothing, it sometimes puts the first one in, but not the other. Perhaps I am going about this the wrong way, so to give the situation: I have an array with a list of these properties, call them 1, 2, 3, 4 and 5, I need all of the array putting into the local database.

People on this site seem to be able to do this so I hope you can help, Thanks, Tom Ludlow

Answer 1

The SQLite INSERT syntax only supports single-row inserts. This should not be a problem.

Why? Because you should be using parameterized queries, not concatenating a giant string together and hoping that you've done all the "escaping" properly so that there are no SQL injection vulnerabilities. Additionally, sticking everything into the statement increases parsing overheads (you've spent all that effort escaping things, and now SQLite has to spend some more effort to un-escape things).

The suggested way to use a statement is something like this:

1. sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
2. sqlite3_prepare_v2(db, "INSERT INTO planner (title,duedate,submitdate,subject,info) VALUES (?,?,?,?,?)
3. For each row you want to insert,
   1. sqlite3_bind() the five parameters (bound parameters are 1-based, so 1, 2, 3, 4, 5).
   2. sqlite3_step(). It should return SQLITE_DONE.
   3. sqlite3_reset() (so you can reuse the statement) and sqlite3_clear_bindings() (for good measure)
4. sqlite3_finalize() to destroy the statement.
5. sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

I've wrapped the inserts in a transaction to increase performance (outside of a transaction, all INSERTs happen their own transaction, which I've found to be significantly slower...).

For an Objective-C wrapper around sqlite, you might try FMDB (it has a reasonably nice wrapper around sqlite3_bind_*(), except it uses SQLITE_STATIC when it should probably be using SQLITE_TRANSIENT or retaining/copying its arguments).

Answer 2

Have you tried to split your inserts so you only insert a single row at a time? tc hints at this in his answer, though using native code.

Try looking at this example with two inserts:

/* Substitute with your openDatabase call */
var db = openDatabase('yourdb', '1.0', 'Planner DB', 2 * 1024 * 1024); 
db.transaction(function (tx) {
   tx.executeSql('INSERT INTO planner (title, duedate, submitdate, subject, info) VALUES ("Poster", "21092010", "28092010", "chemistry", "elements poster")');
   tx.executeSql('INSERT INTO planner (title, duedate, submitdate, subject, info) VALUES ("Essay", "22092010", "25092010", "english", "essay on shakespeare")');
});

/Mogens