Actionscript 3 Apache Flex Sqlite Air Adobe

Unlocking the Power of Multiple SQLite Databases with Adobe AIR

When developing applications, particularly those requiring data synchronization between a client and server, you may find yourself needing to work with multiple SQLite databases at once. In this blog post, we’ll explore how to effectively perform synchronization queries using two SQLite databases in the context of Adobe AIR. We will explore a common scenario involving two databases – one downloaded from a server (server.db) and another used for local storage on the client (client.db).

The Problem at Hand

You have two SQLite databases:

  • Server Database: This is the database downloaded from a remote server (server.db).
  • Client Database: This is your local storage database (client.db).

The challenge you face is to execute various synchronization queries across these two databases. Here are a couple of practical examples:

  1. Delete all records in the tRole table of client.db and repopulate it using the records from tRole in server.db.
  2. Remove entries in the tFile table of client.db where the fileID does not exist in the tFile table of server.db.

The Solution Using Adobe AIR

Utilizing the Attach Method

Fortunately, the Adobe AIR SQL API provides a straightforward solution for working with multiple databases: the attach method of the SQLConnection class. This method allows you to attach additional databases, enabling you to run queries across them seamlessly. Below is a breakdown of how to implement this solution.

Step-by-Step Guide

  1. Create a SQLConnection Object: The first step is to create an instance of the SQLConnection class which will handle the connection to your databases.

    var connection : SQLConnection = new SQLConnection();

  2. Open the First Database: Open your primary database (client.db in this case).

    connection.open(firstDbFile);

  3. Attach the Second Database: Next, use the attach method to layer the server database into your current connection.

    connection.attach(secondDbFile, "otherDb");

  4. Executing Queries: Now that both databases are connected, you can easily perform SQL operations spanning both databases. For instance, to insert all records from otherDb.myTable into main.myTable, you can execute something like this:

    var statement : SQLStatement = new SQLStatement();

statement.connection = connection;
statement.text = "INSERT INTO main.myTable SELECT * FROM otherDb.myTable";
statement.execute();

Key Notes

  • Main Database Context: The tables from the database you opened with open can be accessed with the prefix main.tableName.
  • Custom Database Name: When you attach another database, it can be aliased by any name you choose, allowing flexibility in your queries. In our example, we used “otherDb”.

Conclusion

By utilizing the attach method in Adobe AIR, you can easily work with multiple SQLite databases simultaneously, allowing for efficient data synchronization and management between your client and server databases. Whether you are deleting specific records or repopulating tables, this approach provides a powerful way to enhance your application’s data handling capabilities.

By following the steps outlined above, you’ll be equipped to tackle various database orchestration challenges, streamlining your development process. Happy coding!