Database API Reference Guide updated

Illustration with connected people ,futuristic technology concept

Check out the updated Titanium Mobile API Reference Guide for Database — there are some new code samples to help get you going.

One of the new examples shows how to store and retrieve a blob using the SQLite database. The blob datatype is pretty useful — in this example, it’s a very general datatype that stores an image. Suppose you have a JPEG image called ‘myphoto.jpg’ in your Resources directory. Let’s look at how you would read the image from the file, and then add it to the database.

var imageFile = Titanium.Filesystem.getFile(‘myphoto.jpg’);

var oneImage = imageFile.read();

The above code will read the image from your JPEG file, and store it into the oneImage variable.

Now let’s see how you could store images into a database. First, we’ll open a database file and create the database table using an SQL command.

var db = Titanium.Database.open('images');

db.execute("CREATE TABLE IF NOT EXISTS pics (id INTEGER, image BLOB)");

Now that we have the database table, let’s insert our photo:

db.execute(“INSERT INTO pics (id,image) VALUES(?,?)”, 1, oneImage);

This inserts a row into the database table, with id equal to 1, and image containing the image as a blob. When it comes time to retrieve the image from the database, you’ll use code something like this:

var myResultSet = db.execute(“SELECT * FROM pics”);

if (myResultSet.isValidRow()) {

retrievedImage = myResultSet.field(1);

}

myResultSet.close();

This will select all the rows from the database table, and set your retrievedImage variable to the image blob in the first row. At this point, you can do whatever you’d like with the image — you could display it in an ImageView, for example.

However, there’s a potential problem with the technique shown above. Depending on the size of your database table, the SQL command we used to select every row could return a result set that has many rows, and many images. This could take an unacceptably large amount of memory. So an improved technique would be to figure out first which image you need, and then select only the corresponding row(s). Here’s a modified SQL statement that selects only the first row in the database table:

var myResultSet = db.execute(“SELECT * FROM pics WHERE id=1”);

If you need to store pictures in your application, you should consider whether the database is the right solution. For most picture-taking applications, a better approach would be to store the pictures as files in memory, or on the memory card, and then use the database to store the names of those files.

You can see the complete sample app in the Titanium Mobile API Reference Guide for Database. Happy coding!

1 COMMENT

  1. Can we have an Async version of this interface, something that matches the HTML5 Database API? It sucks to have to block waiting for SQL to run. I’d much rather have the option to pass in a callback listener when the data is ready/operation is complete like I can in HTML5.

    Here is the HTML5 spec:
    http://dev.w3.org/html5/webdatabase/

    If I were to build this in Cocoa, I would use NSOperation to run the SQL in a separate thread, then run the callback in the app thread when it was done.

    • @Matt – that’s funny. the desktop implemented the HTML5 spec directly initially but then everyone hated it. Async too hard to program and not practical for most use cases. We can look into providing an async version (would be trivial), just need to see if more people want that.

  2. Another important addition is a Transaction. SQLite does support them, and HTML5 webdatabase requires them. Titatium should do the same.

  3. It may be hard, but as long as it’s optional, I would love a way to load/write data to the DB asynchronously. When I first started using the async DB API for Safari 2 years ago, it took a bit to design the app right, but in the end I found that I prefer the resulting smoothness, at least the perception of it.