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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 | 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); |
I hope this is clear. If not, don’t hesitate to ask me more detail or any information.
Cheers
Related posts
Comments are closed.