Hey all!

I've got a rather bizarre issue in a project I'm working on: Using the SQLite C API, I can successfully run INSERT statements, but UPDATE statements fail with (seemingly) no warnings or errors anywhere. If I manually copy the (correct) statement emitted by sqlite3_expanded_sql() call on line 31 and paste that query into an SQLite console, the statement runs with no errors, and updates the correct row, as expected.

Here's the function I'm trying to make work, with comments added to clarify observations: https://pastebin.com/5LRdZSuv

I am closing the database safely with sqlite3_close() between runs. And as mentioned, INSERT queries work as expected.

I've been staring at this code for over a week now while working on other parts of my program. Neither I nor the people I've shown this code to can spot the error. Can you?

Thanks in advance, vkoskiv

How do you know that the database about to be updated, referenced in line 8, is the same one that you successfully update using "an SQLite console"?

I see no errors in the code shown (beyond leaking the memory by tossing the sqlite3_expanded_sql() return), and it appears to be well checked for error returns. So a process of elimination leads to my above question.

Inserts, of course, do not depend on the pre-existence of rows with particular X and Y values, whereas your update does. This, too, leads me to suspect you are operating with two different DBs between running your problematic code and working with a SQLite console (which I take to be the CLI shell published by the project.)

Your pastebin is insufficient to reproduce the problem. Until I see a reproducible test case, I'm going to assume the problem is in your code and not in SQLite. The title of your pastebin implies that you think there is a bug in SQLite, but I will not believe that without more evidence.

Possible problems in your code include:

1. Your test to see whether or not the change occurred is run from within an older transaction that has not yet seen the UPDATE.

2. The update gets rolled back for some reason prior to you checking to see if it occurred.

3. g_canvas.backing_db is not the correct database connection.

We could probably brain-storm other ideas. Those should be sufficient to start your search.

Have you tried this: Insert a query at the bottom of your "persist_tile_state_correct()" function to verify that the UPDATE did occur before the function returns. My guess is that you will find that the UPDATE did in fact occur, but somehow gets undone before you test it again in the later (unseen) parts of your program.

I seemingly cannot reply to the thread in general, so I chose your reply to post my final findings. Big thanks to Larry, Richard and midijohnny for your insightful and quick responses.

Predictably, the issue was in my code. Another function that checks to ensure another table was present and initialized correctly counted the rows in that table and had an early return if the table was already populated. That return was in the wrong spot, however, after a query to start a transaction was run. I initially skimmed that function and rejected it being a culprit, since that code was supposed to only run when initializing a new database. Alas - Always, always check all your assumptions.

For those curious, here is the commit that fixed the issue.

Thanks again for the replies!

As pointed out already in the post: an UPDATE may result in having no effect if conditions aren't met. (As opposed to a simple INSERT).

So perhaps try and eliminate this possibility first? For instance, consider changing this:

UPDATE tiles SET colorID = ?, lastModifier = ?, placeTime = ? WHERE X = ? AND Y = ?

To:

UPDATE tiles SET colorID = ?, lastModifier = ?, placeTime = ? WHERE (X = ? AND Y = ?) OR TRUE

Or just removing the WHERE clause altogether.

And/or: consider building a 'veto' trigger - that is - a trigger which raises an error when an update is attempted.

(Also: could this relate to uncommitted/different transactions maybe? Are the rows really visible to the code when the UPDATE is run - that is; have the INSERTs been COMMITTED by this point etc)?

'This is in PowerBASIC using SQLitening, but should be same logic

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG DIM sData(1 TO 3) AS STRING slOpen "asdf.db","C" slexe "drop table if exists t1" slexe "create table if not exists t1(c1 text,c2 text,c3 text)

'insert row 1 sData(1) = bindtext("A") sData(2) = bindtext("B") sData(3) = bindtext("C") slexebind "insert into t1 values(?,?,?)",JOIN$(sData(),"") IF slGetChangeCount <> 1 THEN ? "Insert failed":EXIT FUNCTION

'update row 1 sData(1) = bindtext("a") sData(2) = bindtext("b") sData(3) = bindtext("c") slexebind "update t1 set c1=?,c2=?,c3=? where rowid >0 and rowid < 2",JOIN$(sData(),"") IF slGetChangeCount <> 1 THEN ? "Update failed":EXIT FUNCTION

LOCAL sRecordSet() AS STRING slSelAry "select * from t1",sRecordSet(),"Q9" ? JOIN$(sRecordSet(),$CR) ' c1 c2 c3 ' a b c END FUNCTION

#INCLUDE ONCE "sqlitening.inc"

FUNCTION PBMAIN      AS LONG

 DIM sData(1 TO 3) AS STRING

 slOpen   "asdf.db","C"
 slexe    "drop table if exists t1"
 slexe    "create table if not exists t1(c1 text,c2 text,c3 text)

 'insert row 1
 sData(1) = bindtext("A")
 sData(2) = bindtext("B")
 sData(3) = bindtext("C")
 slexebind "insert into t1 values(?,?,?)",JOIN$(sData(),"")
 IF slGetChangeCount <> 1 THEN ? "Insert failed":EXIT FUNCTION

 'update row 1
 sData(1) = bindtext("a")
 sData(2) = bindtext("b")
 sData(3) = bindtext("c")
 slexebind "update t1 set c1=?,c2=?,c3=? where rowid >0 and rowid < 2",JOIN$(sData(),"")
 IF slGetChangeCount <> 1 THEN ? "Update failed":EXIT FUNCTION

 LOCAL sRecordSet() AS STRING
 slSelAry "select * from t1",sRecordSet(),"Q9"
 ? JOIN$(sRecordSet(),$CR)

' c1  c2   c3
' a   b    c
END FUNCTION

What was the resolution? Is it something others might learn from? (It's ok to just say it was an instance of what I call "ordinary bone-headedness".)