On 18 Oct 2012, at 10:55am, Daniel Polski <dan...@agelektronik.se> wrote:
> What if I create the SELECT sqlite3_stmt and want to step through the data to > evalute if an insert is needed? > If I find a matching row and create another sqlite3_stmt (INSERT) it will > convert the SELECT statement to a write transaction? ...Which would make all > other threads unable continue without restarting "from the top"? (No need to > continue search through their SELECT statements if they cannot begin an > INSERT transaction anyway). > > From what I understand you mean this is what happens (I don't think I > understood correctly though): > > 1. Connection A creates a read statement for table2, table3. > 2. Connection B creates a read statement for table2, table3. > 3. Connection A steps through the data and creates a write statement to > begin->insert->commit data to table1, success. (This also changes Connection > A's read in step 1 to a write statement.) > 4. Connection B steps through the data and tries to begin, fails since it > can't convert the read at step 2 to a write transaction since Connection A's > statement in step 1 now is a write transaction? > > Why does it try to convert the read transaction (table2, table3) to a write > transaction when another prepared statment inserts data to an unrelated table > (table1)? The SELECT statement, including the _prepare() stage and all the _step()s until you've reached the last row, and then the _finalize(), is all one process. They're all part of the statement and you can assume that the database is still locked until you do a _finalize(). If you are using the results of a SELECT to figure out a bunch of other instructions like INSERT or DELETE you can do it two ways: A) Quicky save the results of stepping into an array. Once the SELECT is finalized, look through the array and figure out what you want to do about it. B) Generate the INSERT/DELETE commands while stepping but keep them somewhere, either as a data array or by accumulating the text of the SQL commands in a string, rather than executing them immediately. Once you're finished stepping, execute the commands. (You may choose to use _exec to execute them all in one go.) Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
