Saturday, April 30, 2011

Working with SQLite in AIR: Part 7

Today we'll learn how to establish a SQL connection between your AIR application and a SQLite database.

Before we start, I want to note, that in the first tutorials about using SQLite with AIR we will first learn about the way everything works, get to know methods and classes that we need to use when working with databases, and the code will be given in pieces to help understand how each part of the whole script we're going to have in the end works. When we learn all that, we will create an AIR application that interacts with databases.

Okay, let's go.

As you might already know, SQLite databases are stored in files. This means we need to open the file before we try to do anything with it.

For example, if our database file is located in the same place as the application, we can use this:

var myBase:File = File.applicationStorageDirectory.resolvePath("base.db");

After we create the File object that refers to the database file, we need to create a SQLConnection objects. Why?

Because that's how SQL databases work - we can't simply open and read it like an XML file, we'll need to establish a connection, then do whatever we want, and then close the connection. Think of this as of a phone call - you establish a phone connection with a friend, communicate with him (send and get information), then say goodbye and end the call.

An SQL connection can be created like this:

var myConnection:SQLConnection = new SQLConnection();

The next step is opening the file. We can do that by using the open() and openAsync() methods of the connection object. You've probably already guessed the difference between the two - the second method does that asynchronously, unlike the first method. As usual, it is better to use openAsync(), because if we do everything synchronously, then our application will not proceed functioning until our previous action is finished (and that sometimes might be long enough to be noticable).

To open the file asynchronously, call the openAsync() function and pass your file object as the first parameter and the interaction mode as the second. In this example, I use SQLMode.READ:

myConnection.openAsync(myBase, SQLMode.READ);

Whenever you feel like ending the connection, you can use the close() method:

myConnection.close();

And that's all for today.

Thanks for reading!

Related:

Working with SQLite in AIR: Part 1
Working with SQLite in AIR: Part 2
Working with SQLite in AIR: Part 3
Working with SQLite in AIR: Part 4
Working with SQLite in AIR: Part 5
Working with SQLite in AIR: Part 6
Working with SQLite in AIR: Part 8
Working with SQLite in AIR: Part 9
Working with SQLite in AIR: Part 10
Working with SQLite in AIR: Part 11
Working with SQLite in AIR: Part 12

5 comments:

Hustler said...

Informative!

Dominic Meskys said...

Hi, I am wondering when you would close the connection, For example if would I close it after I execute a block of statements OR would I keep it open for the lifetime of the application (unless I was sure no more queries will run for the lifetime of the app)? Thanks

Kirill Poletaev said...

Generally, you close the connection after you're done working with it. You need to close a connection if you want to open() or openAsync() a new database.

It isn't necessary to close a connection, since it is closed when the application is closed anyway.

danthai said...

Hey, thanks for the tutorials, awesome! Quick question (probably a stupid one). How do I go about creating the original base.db file? I'm assuming that's something I do manually before hand but what is it? A comma separated value file?

Kirill Poletaev said...

You don't need to create it manually, if it doesn't exist it will be created automatically as soon as you save something to it.

Post a Comment