Executing multi-statements queries in AIR applications

Oct 07

Executing multi-statements queries in AIR applications - Part 1/3 : queues

I was used to execute simple queries in my AIR applications, ie with only one INSERT, UPDATE or DELETE statement. But, recently, I had to execute queries including several statements (database creation or mass-insertion of data). I thought that would be easy but actually it requires a “workaround”.

Imagine that you need to create a local database the first time your application is started.

A “natural” query could be :

CREATE TABLE tblClients
(
  id   integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
  lname   varchar(20) NOT NULL,
  fname   varchar(20),
  adr1   varchar(50),
  adr2   varchar(50),
  zip   varchar(6),
  city   varchar(30),
  country   varchar(30),
  tel   varchar(14),
  mail   varchar(40)
);
CREATE TABLE tblResas
(
  id   integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
  id_chalet   integer NOT NULL,
  id_client   integer NOT NULL,
  fromdate   nvarchar NOT NULL,
  todate   nvarchar NOT NULL,
  tarif   numeric(4,1) NOT NULL,
  creationdate   nvarchar DEFAULT CURRENT_DATE
)

Each statement being separated by a semicolon.

But the statement.execute() returns an error: the method can actually execute only one statement at a time.

The idea is then to split this query in “unitary” statements. The split() method is perfect for that. With the previous query in a String, if you apply split(’;'), you get an array with the 2 CREATE TABLE statements. It is possible now to execute each element from the array.

You have probably noticed that the second statement is not ended by a semicolon… this is because that if you put a semicolon at the very end, the split method will generate an empty string as the last element of the result Array and this will generate a runtime error.

Each unitary query can now to be executed one by one, they have to be “queued”. And to do this, you need to define a listener that will call the next query when the current query returned its results. The next query will then call the listener that will call the following query that… and so on, till the end of the array.

var db:File = File.applicationDirectory.resolvePath( DATA_PATH );
var sqlStatement:SQLStatement;
// initialize the counter
var querycount:uint = 0;
queryChain = new Array();
// split the query into an array
queryChain = query.split(';');
 
var myConn:SQLConnection = new SQLConnection();
myConn.addEventListener( SQLEvent.OPEN, function( e:SQLEvent ) :void
   {
      sqlStatement = new SQLStatement();
      sqlStatement.sqlConnection = myConn;
      // get the 1st item of the query array and assign it to the sqlStatement
      sqlStatement.text = queryChain[querycount];
      sqlStatement.addEventListener( SQLEvent.RESULT, function( e:SQLEvent ) :void
         {
            // increments the counter each time the listener is called
            querycount++;
            //check to see if the end of the query array is reached
            if ( querycount == queryChain.length )
            {
               trace("Local database created");
               // reset the counter
               querycount = 0;
               // close the connection to the database
               myConn.close();
               // call anoher function or add here anything you need
               doSomethingMore();
            }
            else
            {
               // if not, then call the next query that will then call the listener again
               sqlStatement.text = queryChain[querycount];
               sqlStatement.execute();
            }
         });
 
      // The listener on Error will help you identify which query generate the error
      sqlStatement.addEventListener( SQLErrorEvent.ERROR, function( e:SQLErrorEvent ) :void
         {
            Alert.show("Error in :\n" + e.target.text + "\n" + e.error);
         });
 
      sqlStatement.execute();
   });
 
myConn.openAsync(db);