Flex: sqlite last_insert_rowid over multiple insert calls

Question

I have file with multiple SQL statements in it to be executed.

INSERT INTO reports (a,b,c) VALUES (1,2,3);

INSERT INTO units (report_id, e, f, g) VALUES ( (SELECT last_insert_rowid() FROM reports), 4, 5, 6);

INSERT INTO elements (report_id, h, i, j) VALUES ( (SELECT last_insert_rowid() FROM reports), 7, 8, 9);

The FROM reports section of the sub-selection does nothing.

What ends up happening is:

1. A row is inserted into reports and the reports.id field is autoincremented
2. A row is inserted into units with report_id being equal to the reports id
3. A row is inserted into elements with report_id being equal to units.id of the last row inserted

This is works as described in the sqlite documentation.

My issue is that I want all the queries subsequent to the report insert to use report.id.

Is there anyway I can get this to work on the database end without having to resort to a solution in as3?

Answer 1

There is a way to do this, but it is in AS3 using parameters.

What is done is instead of using the SELECT last_insert_row() function in each call, replace it with a parameter.

INSERT INTO elements (report_id, h, i, j) VALUES (@id, 7, 8, 9);

Now in my code I have to split the file into an array so that each individual queries is process separately (this is how AS3 implements sqlite's API).

var sqlArray:Array = sql.split(";\n\n");

Now what I do is execute the first statement for importing the report itself.

statement.text = sqlArray[0];
statement.execute();

Now the fun part. You need to get the id back out. So we run another query.

statement.text = "SELECT last_insert_rowid() as ID";
statement.execute();
var id:int = statement.getResult().data[0].id;

Now we can loop through the rest of the queries using the id as a parameter.

for(var i:int = 1; i < sqlArray.length - 1; i++) { 
  /**
   * start at 1 because we already inserted the report
   * end at length -1 because our last entry is empty because of how split works on our data
   **/
  statement.text = sqlArray[i];
  statement.parameters['@ID'] = id;
  statement.execute();
}

This is a little more complicated, but not much and it ends up working.

Everything rolled together into a single function (omitting a lot of class overhead) would be:

function importFromSQLString(sql:String):void {
  try{
    connection.begin();
    var sqlArray:Array = sql.split(";\n\n");
    statement.text = sqlArray[0];
    statement.execute();

    statement.text = "SELECT last_insert_rowid() as ID";
    statement.execute();
    var id:int = statement.getResult().data[0].id;

    for(var i:int = 1; i < sqlArray.length - 1; i++) { 
      statement.text = sqlArray[i];
      statement.parameters['@ID'] = id;
      statement.execute();
    }
    connection.commit();
    statement.clearParameters();
  } catch (e:Error) {
    connection.rollback(); //cleanup if there was a failure
  }
}