Re: [sqlite] Problem with SELECT by rowid after INSERT

On Fri, 12 Apr 2019 11:40:13 -0400 Jim Dossey wrote: > 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 > ); Although it has nothing to do with the problem you posed,

Re: [sqlite] Problem with SELECT by rowid after INSERT

On Friday, 12 April, 2019 14:48, Jim Dossey wrote: >On Apr 12, 2019, at 3:27 PM, Keith Medcalf wrote: >>> 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. >>> >>>

Re: [sqlite] Problem with SELECT by rowid after INSERT

On Apr 12, 2019, at 3:27 PM, Keith Medcalf wrote: > > >> 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 WHE

Re: [sqlite] Problem with SELECT by rowid after INSERT

That was it. There was another SELECT going on that had not been finalized. Thank you Richard, Simon, and Graham for you help on this. > On Apr 12, 2019, at 2:36 PM, Richard Hipp wrote: > > On 4/12/19, Jim Dossey wrote: >> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, th

Re: [sqlite] Problem with SELECT by rowid after INSERT

On Friday, 12 April, 2019 09:40, Jim Dossey 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 DEFA

Re: [sqlite] Problem with SELECT by rowid after INSERT

Friday, April 12, 2019, 7:23:31 PM, Jim Dossey wrote: > I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, > then I'm doing the SELECT. Shouldn't the INSERT do it's own COMMIT > which should make the new row visible to the SELECT? Should I add > a BEGIN ... COMMIT around the I

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 4/12/19, Jim Dossey wrote: > I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm > doing the SELECT. Shouldn't the INSERT do it's own COMMIT which should make > the new row visible to the SELECT? Should I add a BEGIN ... COMMIT around > the INSERT? Perhaps the SELEC

Re: [sqlite] Problem with SELECT by rowid after INSERT

I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm doing the SELECT. Shouldn't the INSERT do it's own COMMIT which should make the new row visible to the SELECT? Should I add a BEGIN ... COMMIT around the INSERT? The INSERT is done with sqlite3_exec(). Do I need to

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 4/12/19, Jim Dossey wrote: > > The problem is when I do an INSERT and then try to SELECT that record by > rowid it doesn't find it. Yes, because the SELECT is working inside a single transaction, but the INSERT is adding content in a separate, subsequent transaction which the SELECT never sees

Re: [sqlite] Problem with SELECT by rowid after INSERT

I do not have shared-cache mode enabled, or anything else that I can see, other than WAL mode. I am not doing any transactions, so the INSERT and SELECT are self contained transactions. This particular database file is opened using ATTACH rather than sqlite3_open(), and it is attached to the 2

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 6:32pm, Jim Dossey wrote: > It's just when I use 2 different connections that the second connection does > not see the rowid that was just added. Okay, I see you're using WAL mode, and two connections. Have you set up Shared-Cache Mode or anything else that might 'merge' t

Re: [sqlite] Problem with SELECT by rowid after INSERT

Yes, I've done extensive debugging to make sure the rowid is valid. I even did a test where I did the INSERT and the SELECT on the same connection and that works okay. It's just when I use 2 different connections that the second connection does not see the rowid that was just added. I ran the

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 6:23pm, Jim Dossey wrote: > I did use sqlite3_last_insert_rowid() to get the last rowid. But I used > sqlite3_expanded_sql to get the actual SQL statement that was processed to > find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I > was looking for.

Re: [sqlite] Problem with SELECT by rowid after INSERT

I did use sqlite3_last_insert_rowid() to get the last rowid. But I used sqlite3_expanded_sql to get the actual SQL statement that was processed to find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I was looking for. > On Apr 12, 2019, at 12:09 PM, Simon Slavin wrote: >

Re: [sqlite] Problem with SELECT by rowid after INSERT

On 12 Apr 2019, at 4:40pm, Jim Dossey wrote: > Which is obtained by calling sqlite3_expanded_sql(). Was that a copy-paste error or do you actually want ___ sqlite-users mailing list sqlite-users@mailin

[sqlite] Problem with SELECT by rowid after INSERT

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