Tuesday, May 10, 2011

Working with multiple SQL databases in AIR

Today we'll learn how to work with multiple databases at the same time.

Sometimes, it is needed to open and work with 2 (or more) databases at the same time. To do that, you could create a new SQLConnection and just load the second database the same way you loaded the first one, however, there are some disadvantages to that.

The first one is that if you use more than one SQLConnection objects, you can't use these multiple databases with a single SQL command, for example, if you need to select all data under the column called "name" from more than one different databases, you'll have to make a separate SQL command for each of the database.

The second disadvantage is that having more than one SQLConnections running at the same time is pretty CPU expensive.

Therefore, it is better to connect to databases using only one SQLConnection object. When you first create it, you apply the first (main) database to the connection using the open() or openAsync() methods. To add more databases, you need to use the attach() method.

When using this method you'll need to specify a name for the database, and the path to the database file. It could look something like this:

myConnection.attach("secondBase", File.applicationStorageDirectory.resolvePath("base2.db"));

The first parameter is the name that you can later use to refer to this database through the SQL commands you make. For example, if you want to select names from it, the command could look like this:

SELECT name FROM secondBase.contacts

The name of the main database is main.

SELECT name FROM main.contacts

It is not necessary to use the database references in your commands, though. You can leave them without the names and just work with all these databases like if it were one big database.

And that's pretty much it.

Thanks for reading!

No comments:

Post a Comment