Skip to main content
A
Anonymous
January 30, 2013
Answered

SQL for AIR asynchronous vs synchronous question

  • January 30, 2013
  • 1 reply
  • 2071 views

Gidday guys

I've set up a SQLite database that's going to be processing up to 150 000 rows at a time.  I'm using parametized queries wrapped in a transaction, so it should all be pretty quick. I'm using asynchronous methods.

I've set the queries, and managed to get a static test query into the database using the parameters.  Wahooo!!!

However, now I've made it dynamic to receive bulk data, I'm getting the error:

"Operation cannot be performed while SQLStatement.executing is true"

I'm wondering if someone can

a) suggest whether synchronous might be a better option

or

b) guide me as to what sequence of functions and event listeners would fix my following set up...

THE PROCESS:

- I have a bunch of filenames in an array, that need to be tweaked before thrown into the database

- I set up the sql connection (leg work done earlier), the query statement, and then start a transaction:

insertStmt.sqlConnection = conn;

insertStmt.text = "INSERT OR IGNORE INTO tester3 VALUES (@col1, @col2) ";

conn.begin();

- array is thrown into a function which starts up an enterFrame iteration (so display doesn't freeze), where I tweak the filenames, and put each tweaked filename and some other data (also headed for the database) into an object, and deliver it to the function that prepares the data into the parameters...

prepareParameters({col1:col1var, col2:col2var});

prepareParameters(row:Object):void

{    

     insertStmt.parameters["@col1"] = row.col1;

     insertStmt.parameters["@col2"] = row.col2;

     insertStmt.execute();
}

- at the end of array loop, call function to end transaction

I think the problem is that the loop is sending the next object to prepareParameters() before the database has finished executing the last insert statement.

I tried setting up a results listener to try and make Flash not return back to the loop until the listener received something, but I get the same error.

My first thought was 'hey, shouldn't Flash wait until after insertStmt.execute(); has finished before returning to the loop that called it's function? But maybe that only happens in synchronous processing?

Thanks for your thoughts guys.

This topic has been closed for replies.
Correct answer sinious

Hi Sinious

Thank you for those functions. I've spent the afternoon implementing them, and am almost there. Learning a ton along the way, with only moderate head-banging-on-wall.

If I could be a glutton and ask two more questions...?

1. How do I get the row count that has been affect using rowsAffected?

I've been trying this in various places...

var result:SQLResult = insertStmt.getResult();

trace("RESULT!!!: "+result.data);

...but result.data is coming back null, and therefore so is result.data.rowsAffected

2. when looking up a class in the Adobe docs, and it says something like...

Impelementaion: public function get rowsAffected():Number

...does that mean we can use that function, or is it just to display what is going on under the hood if we want to alter the class? If we can use the function, is it just a matter of calling it like...

var myRows = this.rowsAffected();

? This hasn't worked for me, so I'm wondering 'what can we use that implementation example for'?


Before I head out to run some errands a quick couple notes.

If you just want a quick number of items affected in the database across all the executions in total (while a SQLConnection is maintained) you can use this property:

http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLConnection.html#totalChanges

To be more hands-on, SQLResult can be used on a SQLStatement after it's finished (after the RESULT event occurs). So you'd want to use this (such as the example above) in the handler function, _runQuery. The SQLEvent sent to the method (outside the first _runQuery(null)) will contain a reference to the SQLStatement that was run. You can see me explicitly accessing it to remove the success handler from it like so:

if (e)

{

    SQLStatement(e.taget).removeEventListener(SQLEvent.RESULT, _runQuery);

}

That's just checking to see if I have a valid (non-null) event sent to the method and if so I use it to remove the handler. I could have used a weak reference in the event handler but on devices I like to be extremely explicit in removing any memory I absolutely don't need as soon as possible.

That said, each SQLStatement has a getResult() method:

http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLStatement.html#getResult()

That method contains the method you requested:

http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLResult.html#rowsAffected

This is just the number of rows affected by your latest statement (UPDATE, INSERT, DELETE, ...). If you want to track it across multiple SQLStatements you could make a new class var to collect results, if you don't want to use SQLConnection totalChanges I linked above:

if (e)

{

          // get formal reference

          var insertStmt:SQLStatement = SQLStatement(e.target);

          // get results from this execute

          var result:SQLResult = insertStmt.getResult();

          // trace a few properties of an "INSERT" like you're doing

          trace("Insert ID: " + result.lastInsertRowID + ", Rows affected: " + result.rowsAffected);

          // if you're doing UPDATE/DELETE there is a .data property with an array

          // of all the results you can iterate through, e.g. print length of results

          // (just treat it as type Array and iterate through it if you like)

          // trace("Total SQL result statements: " + result.data.length);

          // remove listener

    insertStmt.removeEventListener(SQLEvent.RESULT, _runQuery);

}

Keep in mind this is PER-execute, not a total. This lets you examine the results of every "single" execute() you run. You can look for issues or check data validity, etc, on every execute(). If you don't really care about that then just check the _sqlConn.totalChanges for a single number for all executes that affected the database.

Do remember to close the _sqlConn connection after all execute() statments are run so the next time you run queries it resets the totalChanges. Also on something like a DELETE * a SQLResult will return 0 results. It's mentioned in the docs, read paragraph 3 about adding a 'WHERE 1 = 1' just to get results to populate:

http://help.adobe.com/en_US/FlashPlatform/reference/actionscript/3/flash/data/SQLResult.html#rowsAffected

1 reply

sinious
sinious
Legend
January 30, 2013

You should pay attention to the result and error events returned from that execute. Instead of running it at ENTER_FRAME speed, run it at the speed in which you receive successful result events:

http://help.adobe.com/en_US/air/reference/html/flash/data/SQLStatement.html#event:result

Otherwise toss a conditional in the ENTER_FRAME to check the .executing property and if it's true, just return; from the event function without doing anything. I'd recommend the former for your type of aggressive performance testing.

A
Anonymous
January 30, 2013

So, replace the enterFrame functionality?

Currently, after each parsing loop, it calls iterationUpdate()

function iterationUpdate():void{

           

        uploaderMC.progressBar.width = Math.ceil(334*(f2_index/f2_length));

        this.addEventListener(Event.ENTER_FRAME,renderF,false,0,true);

    }

... where renderF checks if there are still files to process, and off it goes again.

So, is the fomer that you are talking about this... replace the above event listener with the result and error events returned from that execute?

Thanks Sinious - I won't hit you up with any more questions tonight - my brain is a little fried from getting SQL Lite up and running today!!!

sinious
sinious
Legend
January 30, 2013

I hear ya! Yes just replace that Event.ENTER_FRAME with listening for the result for every query you perform (of course paying attention to errors and handling them gracefully). Then it will operate at max DB speed without executing twice at once causing lock issues.

Using that weak reference is good as well (addEventListener(evt, func, false, 0, true);) but I would really take that off and manually remove the result listener yourself once the result comes through. If you use weak reference and use a ton of separate objects for queries you'll gum up your memory with all these listeners that will eventually get removed once garbage cleanup decides no more references exist. But removing them yourself intentionally marks them for much faster removal and keeps System.gc() from doing a lot of work. It can speed things up (and on mobile (which is my usage of SQLLite)), you really want to be EXTREMELY careful with removing listeners properly. Memory is just too precious and leaks get apps rejected.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices