I wasted around two hours in a battle with an UPDATE statement who do not works.
Why does it do not works ?
Simple: the WHERE clause used (for unknow reason, tbd) a wrong ID # (0 here instead of 1; 0 is not used) .
Yes, you read it correctly: WHERE UniqueID=0 and there is no Record with that unique ID does not leads any error (I followed that in the DEBUGGER. And that is when I copied the sql_cmd string in TextEdit that I noticed the Unique ID value; then I checked and I have only three Records in that DB and their IDs are: 1, 2, 3.
I had a clue when I noticed the db file modification date was wrong (I checked the hour / minute and click in Modify once I was at hh:mm + 1 (comparing to the db file open).
Yes, UniqueID can’t be 0, but querrying it should just bring an empty RecordSet instead of an error. The error is not within the DB or der SQLite Framework. It’s your App searching for something that simply isn’t there…
Because it was given to me by the DB engine (may I be naive too ?)
I found why my gUnique_ID property = 0: the data base fields are in three different windows and each one have its own set of “First, Previous, Next, Last” buttons / own gUnique_ID property who is not filled with the current value (when a change occured), so they are = to 0.
A simple test (who took minutes to think at) demonstrates that.
Now I know that UNIQUE ID is declared as INTEGER, can be called as Int64 (Int64Values) and 0 is not a valid value.
A SQL update statement doesn’t produce an error if it didn’t find any records to update. It just returns a message of how many records were updated, which in this case would be zero. As Sascha stated, you have to check yourself if the record(s) exist before attempting an update.
Also, you don’t get an error (in this or any other SQL database engine) but you should get that 0 rows were modified (which you can treat as an error).
Keep in mind that for some SQL engines updating rows reports back twice the affected rows (so, modifying one row would report back that two rows were changed, since updates are done in two steps by the engine). MySQL famously does this, not sure off the top of my head if it’s common.
Still, testing for 0 is a sure way to know nothing happened.
I’m learning SQLite with Xojo, so I did a test and read other threads.
Usually I use db.SQLSelect when reading information from the database and db.SQLExecute when writing information. If I understand correctly, to get information back from the database I need to use the UPDATE command with db.SQLSelect and not db.SQLExecute if I want to know how many rows were changed (0 or more).
I think this information is obvious for some people with experience but for me is not that clear. Thank you.
Asking is always the right thing to do. This forum in particular (since the RS days) has been particularly understanding in that respect of people asking “obvious” questions.
Something the old forum had that was very nice was some dedicated “learning” threads. A particular one for Databases from @Mike Bailey was so good I even printed i out to PDF when Xojo changed forum software, since I was so scared it could be deleted.
This helped me a lot, but unfortunately, it is incomplete (as is every other resources). Unique ID in Xojo: I do not found a note about it can be 0 (and in fact I never questioned myself nor anybody about that, thus my surprise).
Did you search information about UPDATE in the Xojo LR ?
I limit myself to ask question to this forum AFTER making check (tests) with the current Xojo version (in case there is a bug in the one I have a license for).
In my school days, I never do my home work, but the things have changed since (I am old) and now I try to do them. Worst, most of the time I make intensive searches in the internet with various success (and even fails).
Example: allowed syntax of a DB (SQLite) Column Name (Field Name). By experience, I know that “#” is not allowed, and I avoid using numbers at the beginning of a column name. (MS-DOS and ProDOS [Apple II] does not allows that.)
And remember: a non English mother language native may have troubles to understand what you wrote - either in correct answer(s) and in irony - IMHO.