SQLite

Error #3132 in AIR and SQLite

I recently working on an AIR for Android application, I encountered this error while trying to save a suer setting:

SQLError: ‘Error #3132: Data type mismatch.’, details:’could not convert text value to numeric value.

I looked over the SQL query I was making, and it seemed correct. So I began the process of trying to isolate the cause. I traced out the variables and they were correct. So, I replaced the variables with static values, and tried yet again. No luck, the same error. A quick search on Google did not seem reveal a clue as to what was going on.

I open the database I was working with using SQLite Database Browser 1.3. I tested the SQL query, and no errors were reported.  I was extremely puzzled. I trying to update a NUMERIC column with the value 0. I try forcing the variables before I ran the query in my app. But still I would get the same error.

Then I looked over all the columns in that row, and noticed that in one column set to NUMERIC, there was a dash. It seems that SQLite Database Browser had allowed an invalid entry into that column. Mind you this was not the column I was trying to update, so I did not notice it. I changed the value from the dash to a number, saved the database, recompiled the application, and it worked! WooT!

It seems that the SQLite engine use in AIR is very strict (or SQLite Database Browser is not), it will check that all the columns have legal values in them for an UPDATE query. This was not something I expected.

I have since switched to Lita (http://adobeair.me/lita/). Since it is built on Adobe AIR, I can feel confident that my queries will run in my AIR applications.

Understanding Bundled SQLite databases in AIR for Mobile

Quite often you will want to ship your mobile application with a prepopulated SQLite database. However there are a few things you need to do to before you can fully use them in your application. This article will walk you through the steps you need to take to use a SQLite database on your mobile device.

The first thing we need to do is include our prepopulated SQLite database in our AIR application. For applications built with Flash Professional CS5, go to File > AIR for Android Settings…, then select the General tab . Click the plus icon above the Include Files section, then select your .db file. If you are building your AIR application using Flash Builder, you just need to include the database in src directory.

Now, when you publish your application for your favorite mobile device, the prepopulated database will be automatically included when the application is installed. Here is where things can get a little tricky if you don’t understand the various application directories and permissions.

Without doing anything our application will read the data from our database using this ActionScript snippet:

var sessionDBConn:SQLConnection;
var sessionDBFile:File;
var selectStmt:SQLStatement;
sessionDBConn = new SQLConnection();
sessionDBConn.addEventListener(SQLEvent.OPEN, openSessionSuccess);
sessionDBConn.addEventListener(SQLErrorEvent.ERROR, openSessionFailure);

var embededSessionDB:File = File.applicationDirectory.resolvePath("demo.db");
sessionDBConn.openAsync(embededSessionDB);

The issue will be when we try to perform any create/update/delete functions on the database, as they will fail. Although we can read from the NativeApplication.applicationDirectory, we are not allowed to write to this directory. We need to find a solution to transfer the database to a location that we can have write access to. Thankfully, AIR also has another custom directory just for this type of use; NativeApplication.applicationStorageDirectory.

The first thing we need to check for is if we have already copied our database to the writable directory. We can check the exists property on the File reference to database in the writable directory to test this condition.

var writeSessionDB:File = File.applicationStorageDirectory.resolvePath("demo.db");
if (!writeSessionDB.exists) {
    embededSessionDB.copyTo(writeSessionDB);
}

Since we have not copied our database to a writable location, the exists property will be false. Now to need to perform our file copy from the applicationDirectory to the applicationStorageDirectory using copyTo method:

embededSessionDB.copyTo(writeSessionDB);

If the database is large, you might want to consider using the copyToAsync method instead.

As with all read and write methods, make sure you include event listeners for both IOErrors and SecurityErrors.

With that, our prepopulated database has been copied to a writable directory and is ready for use.

sessionDBConn = new SQLConnection();
sessionDBConn.addEventListener(SQLEvent.OPEN, openSessionSuccess);
sessionDBConn.addEventListener(SQLErrorEvent.ERROR, openSessionFailure);
var embededSessionDB:File = File.applicationDirectory.resolvePath("demo.db");
var writeSessionDB:File = File.applicationStorageDirectory.resolvePath("demo.db");
if (!writeSessionDB.exists) {
    embededSessionDB.copyTo(writeSessionDB);
}
sessionDBFile = writeSessionDB;
sessionDBConn.openAsync(writeSessionDB);

Hopefully this article will help you as you start developing mobile applications that use prepopulated databases.