Sqlite - inserting rows while reading them

Question

Imagine I have 3 rows in a sqlite table.

While reading these 3 rows I also want to insert new rows depending on the values in these 3 rows.

I do a select on these rows and use sqlite3_step function to get each row.

The problem is that sqlite3_step loops through more than 3 times, I think because it also sees the newly inserted rows. Somehow the cursor gets resets and same rows are read twice etc.

How can I make sqlite3_step to read only 3 rows? Basically I wish to loop through only the resultset and not the new rows.

Thanks.

Answer 1

sqlite has a function that will return you all of the rows at once; you could use that to get the existing rows and then add the new rows. It's been a while since I used it so I forget the name of the function.

Answer 2

I think you can achieve the desired behaviour by starting the transaction for adding the rows before the loop that uses sqlite3_step to iterate over rows and committing this transaction after the loop.

Answer 3

Hi,

You could use a variable to hold the last inserted row (when you first perform the insert).. If your aim is to stop processing when the new set is reached... This value is returned upon insert. You can also get the last inserted row using;

sqlite3_last_insert_rowid(sqlite3*)

If you want to keep track of which row it was that triggered your insert - simply store it's rowid when processing it.

This will give you a valid window of rows to process in

processThisRow = ( currentRowId > lastTriggered && currentRowId < firstInserted );

.. or you could perhaps use the separate transaction as suggested above.

Hope this helps

Rgrds Anders