As some of you might have noticed, I have been building some mobile applications lately on my spare time specifically for the Blackberry Playbook. They are mainly built in Adobe Air using Actionscript 3 and Blackberry’s Tablet SDK.
On my latest application, I have found the need for a database, as it needs to store lots of user input data. The first option that comes to mind is SQLite, as it’s very simple to integrate, and has native support.
I will show here an integration example, and focus on a caveat I found while trying to use it on a real device.
For starters, here are the classes you need to import on your main class:
import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.filesystem.File;
I then create a method to initialize my database connection:
private var dbConnection:SQLConnection = new SQLConnection;
private function initDB():void{
var embededSessionDB:File = File.applicationDirectory.resolvePath("assets/db.sqlite");
var writeSessionDB:File = File.applicationStorageDirectory.resolvePath("assets/db.sqlite");
// If a writable DB doesn't exist, we then copy it into the app folder so it's writteable
if (!writeSessionDB.exists) {
embededSessionDB.copyTo(writeSessionDB);
}
var dbFile:File = writeSessionDB;
dbConnection.open(dbFile);
}
What is important to mention here, is that most people (myself being one of them) will be completely tempted to skip the bit where it copies the local file (coming from the application itself) into the local storage. Funnily enough, if you skip this step, when you test it locally, it will all work wonderfully, but when you deploy it into a real device (or even a simulator) nothing happens at all.
After some research, I found out that you need to be using the database from the local storage, and just like this is that you will have read and write permissions.
After getting that small “detour” out of the way, I can go on and write my first query against that database.
private function getWords(intTabID:int):Array{
var stmt:SQLStatement = new SQLStatement();
stmt.sqlConnection = dbConnection;
stmt.text = "SELECT key, word FROM WORDS WHERE category_id = (:wordID)";
stmt.parameters[':wordID'] = intTabID;
stmt.execute();
var result:Array = stmt.getResult().data;
return result;
}
And this will return a nice array with all my data from the SQLite database.