UPDATE does not works (bad ID #)

Databases
1

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).

2

Why should it bring an error?

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… :slight_smile:

3

Thanks Sasha for your input.

But, something have to be returned to flag “The Data Base UPDATE command failed” in If db.Error, but there is nothing. Else, I will not waste so much time and do not ask for help.

This is just like if you get no error reporting when you want to add a Record: something nasty happens there.

I forgot to mention that I checked with Xojo 2018r2 before starting this conversation. (as always).

4

But why would you work on a uniqueID (given by the Database Engine) without checking first if it’s even existing? :slight_smile:

5

Because it was given to me by the DB engine (may I be naive too ?)

Edit:

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.

This is a Hard Day’s Night !

6

How do you pull the Value from the RecordSet Querry?

For example; with Str(Value) or with Value.IntegerValue ? Please use Value.IntegerValue (very important in 64Bit builds for example).

7

None of these: I use MY_RS.Int64Value because I discovered that is what have to be used (somewhere in the documentation).

Edit: url
http://documentation.xojo.com/index.php/SQLiteDatabase.LastRowID

for example.

8

AFAIK: Integer is automatically Int32 for 32Bit Builds and Int64 for 64Bit Builds. Or?

9

I do not know.

For the current trouble, I removed the Property from the WIndows and set it into a Module.

Now, it works fine ('till next trouble).

Thanks.

10

[quote=409527:@Emile Schwarz]I do not know.

For the current trouble, I removed the Property from the WIndows and set it into a Module.

Now, it works fine ('till next trouble).

Thanks.[/quote]

So the issue really was a Property going out of scope and thus resetting to 0 ? :slight_smile:
Glad you found the issue.

11

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.

12

Thank you.

13

This.

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.

14

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.

15

Just to assure you: Yes, that’s right.

16

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.

I am sure it would be useful for others looking into database management with Xojo, even if the post itself is 12 years old: http://forums.realsoftware.com/viewtopic.php?t=4342

To be honest, I still visit the old forums a lot.

17

How ?

There is a fantstic resource here:

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 ?

You will find it there: SQLite Home Page

Asking questions:
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.

Have a nice week-end all.

18

Thank you Emile, what I was trying to say is that I read in another thread that to:

It looks like I have to use db.SQLSelect and not db.SQLExecute for the UPDATE command.

I don’t know if you use one, the other or other method. Hope this helps.

19

Zero is a valid value for a UNIQUE column. It isn’t valid for an AUTOINCREMENT column, however. Perhaps you’re confusing the two.

20

Sorry, I was not able to use db.SQLSelect instead of db.SQLExecute.

What i found is this:

Dim rs As RecordSet = db.SQLSelect("SELECT changes() FROM Table")

after an UPDATE will report 1 if the update was successful and 0 if I use an invalid ID. Tested with ID=0 too. Hope this helps.