Apache Flex Actionscript 3 Air

How to Execute `Multiple SQL Queries` in Adobe AIR Using AS3

If you’ve ever tried to execute multiple SQL statements in a single call within your Adobe AIR application, you might have encountered a frustrating limitation: only the first query seems to run successfully. In this blog post, we will break down this problem, explore why it occurs, and provide a practical, effective solution to execute multiple SQL queries seamlessly.

The Problem

When using the SQLStatement.execute() method in Adobe AIR, many developers face the challenge of executing multiple queries in one statement. If you’re running a database generation script or initializing a database with several tables, having to execute each command one by one can be inefficient and cumbersome.

For instance, consider the following SQL commands that create several tables:

Create Table tRole (
    roleID integer Primary Key,
    roleName varchar(40)
);
Create Table tFile (
    fileID integer Primary Key,
    fileName varchar(50),
    fileDescription varchar(500),
    thumbnailID integer,
    fileFormatID integer,
    categoryID integer,
    isFavorite boolean,
    dateAdded date,
    globalAccessCount integer,
    lastAccessTime date,
    downloadComplete boolean,
    isNew boolean,
    isSpotlight boolean,
    duration varchar(30)
);
Create Table tCategory (
    categoryID integer Primary Key,
    categoryName varchar(50),
    parent_categoryID integer
);

When executing this SQL script in Adobe AIR using the NonQuery function, only the first command (Create Table tRole) executes successfully, while the rest are ignored or fail due to the semicolon (which marks the end of the statement). This limitation can hinder the development process and make setup tedious.

The Solution

Fortunately, there is a straightforward method to tackle this issue by splitting your SQL commands at each semicolon and executing them individually. Here’s how to do it step-by-step:

Step 1: Read SQL Commands from the File

First, ensure you read your SQL script into a string. This example uses a file stream to fetch the entire SQL command text.

var strSql:String = stream.readUTFBytes(stream.bytesAvailable);

Step 2: Split the SQL Commands

Next, split the string into an array using the semicolon (;) as a delimiter. This allows you to isolate each SQL command for individual execution.

var strSqlSplit:Array = strSql.split(";");

Step 3: Execute Each Command Individually

Now, use a loop to iterate over each command in the array and call your NonQuery function for execution:

for (var i:Number = 0; i < strSqlSplit.length; i++) {
    NonQuery(strSqlSplit[i].toString());
}

Complete Code Example

Here’s the complete modified version of your RunSqlFromFile function that incorporates all the steps above:

public static function RunSqlFromFile(fileName:String):void {
    var file:File = File.applicationDirectory.resolvePath(fileName);
    var stream:FileStream = new FileStream();
    stream.open(file, FileMode.READ);
    var strSql:String = stream.readUTFBytes(stream.bytesAvailable);      
    var strSqlSplit:Array = strSql.split(";");
    
    for (var i:Number = 0; i < strSqlSplit.length; i++) {
        NonQuery(strSqlSplit[i].toString());
    }
}

Tips for Success

Semicolon Usage: Be cautious with your semicolons. Ensure there are no extra spaces or characters after the semicolon that could affect splitting.
Error Handling: Wrap your NonQuery calls in a try-catch block to handle any SQL errors gracefully.
SQL Syntax: Double-check your SQL syntax for each command to avoid runtime errors during execution.

Conclusion

By splitting your SQL queries using semicolons and executing them individually, you can effectively manage multiple queries in Adobe AIR without running into issues. This approach not only streamlines the process but also minimizes headaches when setting up your databases. Happy coding!

How to Execute Multiple SQL Queries in Adobe AIR Using AS3