SQLite bulk data update or insert

Posted in iPhone development tagged bulk, database, html 5, html5, insert, iPhone, JavaScript, Objective-C, QuickConnect, SQLite, update at 9:02 pm by tetontech

I just added SQLite bulk updates for in-browser databases (native databases coming soon) to the QuickConnect iPhone framework (1.6 beta 3).  I did this by creating and adding to the framework an object called DBScript.  It is transactionally safe.  If one of your updates in the script fails all changes are rolled back.

It is also easy to use.  The code below comes from the databaseDefinition.js file of the new BrowserDBScript dashcode example.  In it a link to the database is established using the DataAccessObject.  Then the DBScript object is created and a series of SQL statements are added to the script.  Lastly, the script is executed.  Notice that the script object works for both standard and prepared statements.

If you are using this after a data pull from a network resource or after querying the user for information to insert, make sure you use a prepared statement type call to avoid SQL insertion attacks.

/*

* An example of how to use the DBScript object to populate a database.

* This will be done as a single transaction and is transactionally safe.

* This means that all changes will be rolled back if any

* database error happens.

*/

//create or connect to the in-UIWebView database

var database = new DataAccessObject(“WelcomeExample”, “1.0”, “Welcome example”, 20);

//create the script object

var bulkInsertScript = new DBScript(database);

//add all statements to the script object

bulkInsertScript.addStatement(“CREATE TABLE IF NOT EXISTS names (id INTEGER UNIQUE, name TEXT)”);

bulkInsertScript.addStatement(“INSERT INTO names VALUES(1,’Bob’)”);

bulkInsertScript.addStatement(“INSERT INTO names VALUES(2,’Sue’)”);

//and example of using a prepared statement

bulkInsertScript.addStatement(“INSERT INTO names VALUES(?,?)”,[3,“Jose”]);

bulkInsertScript.addStatement(“INSERT INTO names VALUES(4,’Bjorn’)”);

bulkInsertScript.addStatement(“INSERT INTO names VALUES(5,’Jean’)”);

bulkInsertScript.addStatement(“INSERT INTO names VALUES(6,’Gustav’)”);

//execute all statements within a transaction

bulkInsertScript.executeSetDataScript();

 

Permalink Leave a Comment