And using SQLSelect needs to provide a Variable of type RecordSet to hold the result:
[code] Dim Foo_RS As RecordSet
Foo_RS = gDossier_DB.SQLSelect(sql_cmd)[/code]
No, I do not checked the result, I just compile the project.
The LR states SQLSelect is returning a value, SQLExecute no.
Tim: all I know is that if ID = 0 in WHERE ID='" + gUnique_ID + "'" the update is not set to the db file and no error in the db.Error if test.
In the mean time, I checked in my SQL pour les Nuls (for Dummies): the collophon do not have an entry for a TABLE unique ID Record. I stopped to read it when I noticed (by hazard) that many updated editions exist. Also, many missing things in the book compared to what I found in the Internet (EXISTS for example).
Emile, yes a RecordSet is needed and that’s what I tested.
Greg said, back in 2015, that if we use SQLSelect instead of SQLExecute we should get a single row record with the result. But my tests with Xojo 2018 always get Nil. Maybe SQLite changed since then.
Giulio, mentioned the SELECT change() query. Using that after SQLExecute-Update, I get a value of 0 if no update and 1 if there was an update.
I’m really sorry that we don’t understand each other.
As others have said, no update is not an error. If you tell SQL to update a value, it will update zero, one, or multiple rows. It doesn’t care how many. Zero rows updated does not constitute an error. You will only get an error on Update if your SQL statement is malformed.
No error is reported because it’s not an error. It’s how all SQL databases work.
SQLite is not developed by Xojo; all Xojo does is provide an interface to it, and Xojo does no development of SQLite itself.
And it has nothing to do with the value being 0. It has to do with whether your WHERE matches any records or not. I might issue a UPDATE like this:
UPDATE thistable SET thisfield=27 WHERE otherfield="today"
In fact, I do UPDATEs like this all the time, without needing to know whether I have any records that match. If not, no records are updated and that is a correct and desired outcome.
You might wish to visit www.sqlite.org and do some reading there.
Why?
An ID can just as easily be a string.
An ID can even be 0 - whats wrong with that as long as it is the only row?
YOU probably want to check that the ID is not 0
But given your story, I would recommend that you check how many records are going to be affected beforehand.
Do a db.SQLSelect using the same criteria
If it returns a count of 0, you might want to ask why.
But as everyone says, an Execute is just an instruction, like
‘Put all dirty plates into the dishwasher’
The job is complete with no errors once that is carried out, even if there were no dirty plates.
But ‘put all dirty GHFHGFSTYF int Dishwshr’
generates an error, because there is no field called GHFHGFSTYF, no such word as int, and Dishwasher is spelled incorrectly.
I think the problem is that Emile saw an ID = 0 as a bad ID.
I guess what others are saying is that, at least with SQLite, the database is very __________ (you can fill the word) in the way that you can put almost everything/anything as a value in a query and will try to use it. There is no check if ID should start with 1 so 0 is a “bad ID” for it to report an error.
You can even supply a negative ID and SQLite will not complain.
[quote=409679:@Alberto De Poo]I think the problem is that Emile saw an ID = 0 as a bad ID.
I guess what others are saying is that, at least with SQLite, the database is very __________ (you can fill the word) in the way that you can put almost everything/anything as a value in a query and will try to use it. There is no check if ID should start with 1 so 0 is a “bad ID” for it to report an error.[/quote]
“Normal” is the word you are looking for. Why should an ID start with a 1, and why should an ID not be a string?
The thing is that UPDATE will change the record if one exist with ID=0.
The default behavior (defining ID as INTEGER NOT NULL and as Primary Key) will let you put a number there if you don’t supply one and will start at 1. You can supply a value for ID, if you start at 0 then an UPDATE with ID=0 will update that record. That’s what I understand.
Changed Examples - Database - SQLite - SQLiteExample to send ID “-1”, “0”, and “1”, when adding the 3 teams. No error from SQLite and here is the screen capture:
That is not exactly accurate. If you pass an ID that does not exist in the database, nothing will be written to the db file and no report is done. An ID of zero is perfectly legitimate. You would have had the same result if you had passed in ID=100. Since it doesn’t exist in your database, the db will process your SQL statement, update all records that match (none) and that’s it. The fact that no records were updated is not considered an error.
This is standard functionality for all SQL databases. It has nothing to do with Xojo.
[quote=409771:@Emile Schwarz]The SQL for Dummies book I have does not even talk about ID.
Apparently, if you pass ID = 0 in an UPDATE command, nothing will be written in the db file and no report is done.[/quote]
It has nothing to do with 0 and everything to do with whether your WHERE matches any records.
That’s because it is standard - and correct - behaviour.
This is nothing to do with the LR and everything to do with SQL. As I suggested before, you should learn it.
If you wish to be sure your record is updated, supply the correct ID, not an incorrect one.
