SQLite begin--commit

[rocker]

I have two tables in my database which I populate as follows:

class myClass
{
var conn:SQLConnection;
var xml:XML;

public function myClass()
{
conn = new SQLConnection(....); /* initialize connection to the db */
xml = initXML(); /* Fill the xml var */
functionX(xml);
functionY(xml);
}

private functionX(xml:XML):void
{
var property:XML;
var sqlstatement:SQLStatement = new SQLStatement();
sqlstatement.sqlConnection = conn;

sqlstatement.text = "INSERT OR REPLACE INTO dbase.id VALUES(@id, @name)";

sqlstatement.sqlConnection.begin(); /* Begin transaction 1 */
for each(property in xml.prop)
{
<init params in the statement>
sqlstatement.execute();
}

sqlstatement.sqlConnection.commit();
}

private functionY(xml:XML):void
{
var property:XML;
var desc:XML;

var sqlstatement:SQLStatement = new SQLStatement();
sqlstatement.sqlConnection = conn;

sqlstatement.text = "INSERT OR REPLACE INTO dbase.description VALUES(@id, @desc, @secId)";

sqlstatement.sqlConnection.begin(); /* Begin transaction 2 */
for each(property in xml.prop)
{
for each (desc in property.desc)
{
<init params in the statement>
sqlstatement.execute();
}
}

sqlstatement.sqlConnection.commit();
}
}


I get an error in functionY's commit() saying there is no active transaction. But when I set a breakpoint at this line and observe the sqlstatement.sqlConnection.inTransaction variable, it is true.

Can anyone shed some light on this phenomenon? Is transaction 2 in functionY not queued up till transcation 1 of functionX is over?

Edit: If in functionY, the inner for loop does not execute (there is no 'desc' field in the xml), is there a way to undo the begin() of transaction 2? I am afraid, doing a cancel() or a rollback() might just cancel transaction 1 that might be ongoing.

My xml file is like this:


<xml>
<prop id="1", name="xyz">
<desc secId="A", description="This is a description">
...
... <0..n of desc fields>
<desc secId="N", description="This is a description">
</prop>
... <1..m of prop fields>
</xml>

[evride]

um, try running the commit only after all statements have been executed (and remove the begin() in functionY.

[rocker]

um, try running the commit only after all statements have been executed (and remove the begin() in functionY.

Thanks. That helps. But shouldn't this be in 2 separate transactions really?

Also, I've noticed that executes() take a hell lot of time. I have an xml file that is 500KB and it takes almost 30secs for both the for loops to complete. If I only remove the execute() statements, the loops complete in a jiffy. Any suggestions to improve the performance of execute()?

[evride]

nah, sqlite performace does suck with AIR. The begin and commit setup is supposed to run better than the easier way. so you must be really adding a lot of entries. I would suggest spanning the executes over many frames. like 50 per ENTER_FRAME event to make sure that the app remains responsive.

Kinda of off topic but I have found the SQLite Manager for Firefox is quite handy when working with sqlite in AIR. https://addons.mozilla.org/en-US/firefox/addon/5817

[rocker]

So I have been trying to figure out the slowness of the executes and it occurred to me that it is not one transaction, but a series of transactions started with every execute(). So in a way, the sqlConnection.inTransaction flag is showing incorrect value.

I googled it and indeed, it is a flash bug. It appears that if there is an empty commit, this flag is not cleared properly and so the next begin() does not actually create a manual transaction.

For anyone interested, the workaround is to insert a pseudo statement that will always execute, if you suspect there would be an empty transaction (like I have when the xml does not contain all the entries). This works for me perfectly, and the transaction execution time has increased tremendously. The original problem that I mentioned (no active transation) is also gone with this workaround.

It is a year old bug, but my version of flash/air (CS3) has not fixed it yet.

Ref: http://bugs.adobe.com/jira/browse/SDK-14502