On Friday, 12 April, 2019 09:40, Jim Dossey <jim.dos...@gmail.com> wrote"
This does not really make a lot of sense at all for the following reasons: >I have a table define like this: >CREATE TABLE "sessiond" ( >"journal" VARCHAR(4) DEFAULT '' NOT NULL, >"session" VARCHAR(16) DEFAULT '' NOT NULL, >"pid" INTEGER DEFAULT 0 NOT NULL, >rowid INTEGER PRIMARY KEY >); >In my application I open 2 connections to this table, one for reading >and one for writing. I do that because sometimes we do a SELECT, and >while reading through those rows we'll UPDATE them. With only 1 >connection the SELECT will block the UPDATE. This (the above) is incorrect. The single connection will be upgraded to a writer when the update is step'ed on that connection. It will not block. Whether transmogification of the database underneath an "in progress" read is a good idea or not is a completely separate issue. >I also have WAL mode turned on. >The problem is when I do an INSERT and then try to SELECT that record >by rowid it doesn't find it. The INSERT is done on the write >connection and the SELECT is done on the read connection. However, >if I open a new connection and do the SELECT, the new row is there. What is the transaction state of the "read connection"? Did you remember to RESET all prior statements that were "reading" from that connection? WAL mode creates transactions so that readers are in a mode called REPEATABLE-READ. They will not see any changes COMMIT'ed by other connections until all statements are completed and reset and the read transaction commit'ed. This is so that a crapload of overlapping statements reading data through the same connection will always see THE SAME IDENTICAL AND UNCHANGING VIEW of the database until they are all RESET and FINALIZED (for implicit transactions, for explicit transactions you have to end the transaction as well, since it is the "opening" of the transaction that determines the REPEATABLE-READ state). >To be a little more specific, the problem happens when I try to do >sqlite3_bind_int() on the prepared statement using the new rowid. It >doesn't use the rowid it uses NULL. > >The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;" >Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting >SELECT command is >SELECT * FROM "sessiond" WHERE "rowid"=NULL; >Which is obtained by calling sqlite3_expanded_sql(). This does not make sense. It indicates that you did not actually bind a value to the parameter in question >If I use an older rowid in the SELECT, it works just fine. It just >doesn't work when using the rowid that was just created. Define "older rowid". a rowid is just a number. Therefore is impossible for a rowid to be new or old as all numbers have been in use for thousands of years. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
