Adobe air - SQLStatement.execute() - Multiple queries in one statement

Question

I've written a database generation script in sql, and want to execute it in my Adobe air application:

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
);
...

I execute this in Air using the following methods:

    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);      
        NonQuery(strSql);

    }

    public static function NonQuery(strSQL:String):void
    {
        var sqlConnection:SQLConnection = new SQLConnection();
        sqlConnection.open(File.applicationStorageDirectory.resolvePath(DBPATH);
        var sqlStatement:SQLStatement = new SQLStatement();
        sqlStatement.text = strSQL;
        sqlStatement.sqlConnection = sqlConnection;
        try 
        {
            sqlStatement.execute();
        } 
        catch (error:SQLError)
        {
            Alert.show(error.toString());
        }
    }

No errors are generated, however only tRole exists. It seems that it only looks at the first query (up to the semicolon- if I remove it, the query fails). Is there anyway to call multiple queries in one statement?

Answer 1

Wound up using this, kind of a hack, but actually works pretty well. Only thing is you have to be very careful with your semicolons. : D

        var strSql:String = stream.readUTFBytes(stream.bytesAvailable);      
        var i:Number = 0;
        var strSqlSplit:Array = strSql.split(";");
        for (i = 0; i < strSqlSplit.length; i++){
                NonQuery(strSqlSplit[i].toString());
        }

Answer 2

The Sqlite API has a function called something like sqlite_prepare which takes one statement and prepares it for execution, essentially parsing the SQL and storing in memory. This means that the SQL only has to be sent once to the database engine even though the statement is executed many times.

Anyway, a statement is a single SQL query, that's just the rule. The AIR SQL API doesn't allow sending raw SQL to Sqlite, only single statements, and the reason is, likely, that AIR uses the sqlite_prepare function when it talks to Sqlite.

Can't you just store the statements separately? Do you have to store them in the same file? Could you store them in some format that made it safer to split (for example a simple XML format where each statement was its own node)? Could you store them each on separate lines instead (looping over each line executing the statement on that line)?

Answer 3

I couldn't comment on your comment so I am throwing this in there.

What about making your delimiter something a little more complex like ";\n" which would not show up all that often. You just have to ensure when creating the file you have a line return or two in there. I end up putting two "\n\n" into the creation of my files which works well.