I'm trying to get my head around how to do a SQLite bulk insert using
transactions. This works, but it doesn't make sense to re-create a new
SQLStatement in the loop:
private function onAddBulkContacts():void {
_responder = new Responder(resultEventHandler, errorEventHandler);
contacts_db.connection.begin(null, _responder);
var statement:SQLStatement;
for (var i:uint=0; i<parseInt(bulkAdd.numberToAdd.text); i++) {
statement = new SQLStatement();
statement.sqlConnection = contacts_db.connection;
statement.text ="INSERT INTO contacts ('name', 'lastname') VALUES
(@NAME, @LASTNAME)";
statement.addEventListener(SQLErrorEvent.ERROR,
function(event:Event):void {
trace('statement error');});
statement.addEventListener(SQLEvent.RESULT, function(event:Event):void {
trace('result'); });
statement.parameters['@NAME'] = "Name " + i.toString();
statement.parameters['@LASTNAME'] = "LastName " + i.toString();
statement.execute();
}
contacts_db.connection.commit();
}
What I want to do is create the SQLStatement once, let it compile, then just
pass in new arguments within the loop, the commit it at the end, e.g.
private function onAddBulkContacts():void {
_responder = new Responder(resultEventHandler, errorEventHandler);
contacts_db.connection.begin(null, _responder);
var statement:SQLStatement;
statement = new SQLStatement();
statement.sqlConnection = contacts_db.connection;
statement.text ="INSERT INTO contacts ('name', 'lastname') VALUES
(@NAME, @LASTNAME)";
statement.addEventListener(SQLErrorEvent.ERROR,
function(event:Event):void {
trace('statement error');});
statement.addEventListener(SQLEvent.RESULT, function(event:Event):void {
trace('result'); });
for (var i:uint=0; i<parseInt(bulkAdd.numberToAdd.text); i++) {
statement.parameters['@NAME'] = "Name " + i.toString();
statement.parameters['@LASTNAME'] = "LastName " + i.toString();
statement.execute();
}
contacts_db.connection.commit();
}
But the latter code throw an error saying that it can't execute the second
time through, since the statement itself is still executing (and I believe
will be in that state until the commit). I guess I can understand that the
statements get added to the execution queue, but it doesn't make sense that
I have to add the SQL text within the loop, exactly the thing I'm trying to
avoid. I'm sure there's a better way to do this, but I've spent way too
long hacking and reading trying to figure out what the proper sequence is.
Any ideas?
Thanks,
Tac
