How do I confirm success/failure of DatabaseUpdate execution

[nigel]

I am not sure if this is a general SQL related question or a Purebasic question relating to the DatabaseUpdate statement....

I am unable to determine the success of a SQL database UPDATE or DELETE operation because the result only seems to indicate
if the SQL statement was properly formed using correct syntax and tells me nothing about if the DELETE or UPDATE operation actually
took place in terms of making the requested database changes.

Examples of this would include the following cases :-

1. An UPDATE appears to be successful even when the row to be updated does not exist
2. A DELETE appears to be successful even when the row to be deleted does not exist

It is almost as if I have to trust in blind faith that what I asked for has been done. Maybe this is a SQL thing which has nothing to do with PureBasic at all
in which case I apologise for posting on this forum. Otherwise it would be helpful if more experienced PB users can tell me how I might code to identify
such exceptions.

I am relatively new to relational databases and SQL. My past centuries of experience have all been with ISAM style data management systems.
Can anyone shed any light on what I might be missing here. I feel like the man from Mars when I see this kind of thing happening.

Whenever I use the SQL command line from a database administration utility, a SQL DELETE or UPDATE will return a message telling me how many rows
were affected and I also wonder if this "affected rows" information is accessible to my PB application where I am using embedded SQL in conjunction with
DatabaseUpdate.

[Zach]

I would have thought the help file was pretty clear on this?

----------

Syntax

• Result = DatabaseUpdate(#Database, Request$)

Description

• Executes a query on the given database without returning any records.

Parameters

• #Database Specifies the database to use.
• Request$ Specifies the query to execute.

Return value

• Returns nonzero if the query was successful or zero if it failed (due to a SQL error or a badly-formatted query).

Remarks

• This function is similar to DatabaseQuery() but is independent from the NextDatabaseRow() function. Therefore it's not possible to do a 'SELECT' like request with this function. This function is useful for updating records in the database. In the event of an error, the error text can be retrieved with DatabaseError().
  Example

Code: Select all

  ; First, connect to a database with an employee table
  ;
  If DatabaseQuery(#Database, "SELECT * FROM employee") ; Get all the records in the 'employee' table
  
    While NextDatabaseRow(#Database) ; Loop for each records
      
      ; Update the 'checked' field for each records, assuming the 'id' field is 
      ; the first one in the 'employee' table
      ;
      DatabaseUpdate(#Database, "UPDATE employee SET checked=1 WHERE id="+GetDatabaseString(#Database, 0)) 
    Wend
    
    FinishDatabaseQuery(#Database)
  EndIf

[nigel]

Zach

I am familiar with the help file contents on this subject which I do indeed find straightforward.

The issue is that DatabaseQuery returns a non-zero value (It returns 1) which indicates success when the row concerned
does not even exist in the database and therefore cannot possibly have been either updated or deleted. It is this behaviour
which makes no sense to me.

I would expect SQL to return an error when the subject row does not exist, or at least return some way of identifying how
many rows were affected by the DELETE or UPDATE operation.

Note that in the case of normal queries involving DatabaseQuery SELECT statements , NextDatabaseRow informs if the query
found any rows through the returned value of 0 or 1. The situation with DatabaseUpdate is different in the sense it is unable
to detect if an UPDATED or DELETED row existed in the first place.

[USCode]

It's not related to PB but SQL. It's not a SQL error, it just means zero rows were affected.
Like you said, what you need is some way to ascertain how may rows were affected.

I've never tried it but I wonder if this would work -after you issue your UPDATE or DELETE, try issuing this SQL: "select sqlite_changes()".
If it works and returns a value, it should be the number of rows affected. Worth a try?

[IdeasVacuum]

Can you not try a read of what should have been written, and compare the values?

[nigel]

USCode

You are right in that it appears to be a SQL problem as I came across the following in the SQLite documentation under UPDATE :-

"It is not an error if the WHERE clause does not evaluate to true
for any row in the table - this just means that the UPDATE statement
affects zero rows"

No mention of this appears in their documentation under DELETE however although I suspect the same is true as I came across the
following comment on a hard to identify forum where someone posted the following comment :-

"Check whether an update/insert/delete is successful
Mike C# posted on Saturday, December 09, 2006 11:17
PMBTW - If you want to check if DELETE actually deletes any rows (it will not throw an error if no rows are deleted),
check @@ROWCOUNT."

Your idea of using "SELECT sqlite_changes()" was a good one as although I couldn't locate this in any SQLite documentation
I did come across references to this in PHP programming documentation and the description of what this function does seems
to be exactly what I am looking for. However, when I used this in PureBasic it returns a value of 0 rows regardless of if an
UPDATE was successful or not. I will have to look into this more as there might be a different way of expressing this in PureBasic

Thanks for giving me your suggestion, I will pursue it some more tomorrow and post back if I find a way of implementing it in a
workable fashion.

IdeasVacuum

The irritating thing is what happens when I perform an UPDATE or DELETE on a row which I know doesn't exist. I would have expected
SQL to return an error and it does not do so when it should. In cases where the row does exist, the DELETE or UPDATE always
works, although you could argue that it would be nice to get a success/failure confirmation of this too. The problem is that
SQL appears to be designed to work in this uninformative way and it doesn't make sense (not to me anyway).

[USCode]

Here you go -- looks like it should be "select changes()"
http://www.sqlite.org/lang_corefunc.html#changes

[nigel]

Thanks a lot USCode.

I did mess around with changes() in SQLite with no luck yet.
Unlike sqlite_changes which always returned 0, changes() seems to always return 1 as the result.

I've tried it using the following syntax in PB using both DatabaseQuery and DatabaseUpdate in case it only
works with one of these methods.

Code: Select all

SQL$="SELECT changes()"

Result=DatabaseQuery(1, SQL$)
Result=DatabaseUpdate(1, SQL$)

Unfortunately the returned value for Result is always 1, which is wrong in the case where I attempt to
update a row that does not exist in the table concerned.

I'll do some more messing around with this and I will try using it in conjunction with NextDatabaseRow in case
the returned value is stored somewhere other than where I expect it to be.

Either way, I really appreciate your help and expertise. Thanks a lot.

[USCode]

Hmmm, that's really strange. I first did a test within a SQL admin tool (SQLite Expert) and it worked perfectly. I deleted 3 rows and changes() returned 3.
But I just tried it with PB and also got a return result of 1 everytime. Even putting the UPDATEs in a transaction didn't make any difference.
It must be something about the PB Database library integration with SQLite ...
That surprises me as I would expect changes() to be returned by PB like any other SQL function.

Code: Select all

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

DatabaseFile$ = "c:\temp\Database.sqlite"

If CreateFile(0, DatabaseFile$)
   CloseFile(0)
   
   If OpenDatabase(0, DatabaseFile$, "", "")
   
      CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")

      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
      
      CheckDatabaseUpdate(0, "BEGIN TRANSACTION")
      
      ; Update 2 rows individually
      CheckDatabaseUpdate(0, "UPDATE food SET name = 'TreeTop' where name = 'apple'")
      CheckDatabaseUpdate(0, "UPDATE food SET name = 'Monkey' where name = 'banana'")
      
      If DatabaseQuery(0, "SELECT changes()")
      
         While NextDatabaseRow(0)
            Debug GetDatabaseLong(0, 0)
         Wend
      
         FinishDatabaseQuery(0)
       EndIf
       
       CheckDatabaseUpdate(0, "COMMIT")
      
      CloseDatabase(0)
   Else
      Debug "Can't open database !"
   EndIf
Else
   Debug "Can't create the database file !"
EndIf

[nigel]

Hi USCode

Interesting that you use the same Database admin tool as I do for SQLite

I do most of my work using MySQL (Although I do use SQLite too)

With MySQL I use a Database admin tool called HeidiSQL. It is very similar to SQLite Expert and incorporates a very good SQL help facility
which I have just been looking at. Using this help, I have been able to find that in MySQL I can use SELECT ROW_COUNT() to get
the number of rows affected by any INSERT DELETE or UPDATE operation. I can confirm this does work correctly from within PureBasic.
Here is the relevant portion of my PB code.

Code: Select all

SQL$="SELECT ROW_COUNT();" 
Result=DatabaseQuery(1, SQL$)
Result=NextDatabaseRow(1) 
Result$=GetDatabaseString(1,0)

MessageRequester("Status", Result$+" Rows affected")

Next, but not today, I will have another crack at the same type of thing using SQLite based upon what you had found and told me about.

Thanks again for continuing to help me find out how this stuff works.

[nigel]

USCode

I tried the same principle with SQLite and it did work OK

I used the same code as for MySQL except substituting "SELECT ROW_COUNT();" with "SELECT CHANGES();" for SQLite to
reflect the difference in SQL usage between these two different databases. It is unfortunate that SQL is not standardised
where these functions are concerned.

Once again, thank you very much for your help. I would not have made progress towards a solution without your suggestions.