How to check SQLite update actually occurred?

Databases
1

Hi all
A thanks in advance
I’m getting to grips with cubesql and SQLite
I’m using prepared statements and all is fine but I’m missing one thing…
When I’m updating via an UPDATE
HOW DO I KNOW IT ACTUALLY WORKED?
An error only occurs if my coding SQL is wrong
But if it’s right yet the WHERE clause failed to find a record to update…
How can I detect this without doing a SELECT to prove it is ok?
Are there any response codes hidden somewhere?
Thanks for any pointers
James

2

You have to check for a database error. If something went wrong, there will be a database error. There is an Sqlite function to query the affected rows, but I fear that the Xojo implementation lacks this functionality. Correct me if I am wrong…

3

SELECT changes()
returns the number of rows inserted or updated in the last operation

4

Another thing you can try is using SQLSelect in place of SQLExecute. You should get a single row record set back which contains the result.

5

Hi all
Thanks for the help
I believe Database errors do not occur when your SQL is correct but the WHERE clause doesn’t find anything. Ie update successful but nothing is altered.

So it looks like another SQL command i, required to work out if the update was successful.
Rather than a response code from the original SQL update that indicates the result.
If this is the case, that’s ok, just a little expensive.
Thanks again for the help

6

This is why I use transactions instead of relying on the automatic updating of SQLite. Before you modify the database use the:

db.SQLExecute("BEGIN TRANSACTION")

Then after making your changes check for errors. If there are errors, then call db.Rollback and inform the user. If there are no errors, then call db.Commit.

This way you know that things are being updated…

7

Thanks Jon
Im using CubeSql
I put
CubeServerDB.SQLExecute(“BEGIN TRANSACTION”)

in the front of my Update and then purposefully used a WHERE clause that would not find a record to update.
no errors were fired
ie
If CubeServerDB.Error Then
LOG("DB Error: " + CubeServerDB.ErrorMessage)

else
  LOG("OK!: " )
End If

it was always OK
Looks like a valid sql statement doesn’t create errors in a transaction.
//==================================================================================
The reason Im interested in this is my Table schema has an ID GUID, and a unique REVISION No GUID.
Every time I make an UPDATE I change the revision GUID with a new one.
So when I Update a record, the WHERE clause is looking for an ID and a REVISION number to match with. If another user has updated the same record since I read it , then the REVISION number is different and the update fails.
Just detecting this failure without running a followup SQLSELECT is proving to be difficult
again thanks for your thoughts
James

8

Have you tried SQLSelect instead of SQLExecute, as Paul suggested?

9

Yes I can do a SQLSelect after the update. I understand that.
It does work.
I was just trying to avoid an extra sql hit to the DB.

Thanks for your comment

10

Sorry am I being thick!
Does Greg mean put the UPDATE code in a SQLSelect?
I always assumed that it was just for Select statements

If this is the case???
will try ASAP

11

Im using prepared statements so the equivalent command is
dim stmt as CubeSQLVM
dim rs as recordset
… binding etc
rs=stmt.VMSelect

this simply returns a nil record set, and the update fails
and no errors are captured.

Ok an extra sql select after the update it is!

Again Thanks for everyones comments, this forum is much appreciated

Regards James

12

Ah. The database plugin for CubeSql must not support it, but yes, what I meant was putting the Update in a SQLSelect. Some of the database engines return data that way. Usually it’s an indication of the number of affected records.