October 31, 2009

SQLite bulk data update or insert

Posted in iPhone development tagged , , , , , , , , , , 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();

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: