[Done] DatabaseUpdate and delete statement

[sc4pb]

I'm curious if this is expected, or a bug, or a misleading bit of documentation. I am using PB's built in database library to access MS SQL Server via ODBC.

Let's say I have a table 'mytable' with columns 'ColA' and 'ColB'.
I have a row in the table where ColA is "foo" and ColB is "bar"

I execute this sql statement using DatabaseUpdate: Delete From mytable Where ColA='foo'
...the return value is nonzero, which indicates success. And yes the row is gone.

I execute the same sql statement again: Delete From mytable Where ColA='foo'
...but now the return value is zero, so I was catching it as an error. But it isn't an error. It is a perfectly good sql statement that happens to not affect any rows.

In the PB documentation for DatabaseUpdate it describes the return value as "Returns nonzero if the query was successful or zero if it failed (due to a SQL error or a badly-formatted query)." In my mind the query was successful, it wasn't badly formed, and the database executed it. It just didn't find any matching rows to delete the second time around.

Anyone else noticed this? Is it possible that DatabaseUpdate is returning number of affected rows, as opposed to a success/fail indication?

Thanks

[NicTheQuick]

I first thought that it might return the affected rows but there is a dedicated command for that (AffectedDatabaseRows).

So yes, it seems like a bug since you would not call it an error when you try to delete something with a WHERE clause that does not exist. An error would be if the column or the table does not exist.

[Fred]

Moved for investigation

[Fred]

It returns '1' if SQLExecDirect() returns SQL_SUCCESS or SQL_SUCCESS_WITH_INFO. So somehow in your case it returns something else. Can you tell me if there is an error string in DatabaseError() when it happens ?

[jassing]

just spit-balling ideas...

When I dealt with MSSQL & ODBC, sometimes the errors were a bit cryptic; especially if there were triggers, relations, or views that would be violated by a record being deleted.

Are there any stored procedures, triggers, relations, or views in your MSSQL table that could be interfering?
If you issue the same line in the SQL client IDE - does that give any details?

[sc4pb]

Hi, not sure if this is still an active conversation but didn't want to drop the ball. I did another test outside of my project with just this code

Code: Select all

UseODBCDatabase()

h=OpenDatabase(#PB_Any,"MyAzureSQL","MyUser","MyPass")
If h
  r=DatabaseUpdate(h,"Insert Into tValues (sKey,sValue) values ('foo','bar')")
  Debug "insert result="+Str(r)+" error=" + DatabaseError()
  
  r=DatabaseUpdate(h,"Delete From tValues Where sKey='foo'")
  Debug "delete 1 result="+Str(r)+" error=" + DatabaseError()
  
  r=DatabaseUpdate(h,"Delete From tValues Where sKey='foo'")
  Debug "delete 2 result="+Str(r)+" error=" + DatabaseError()
  
  CloseDatabase(h)
EndIf

; results in debug...

; insert result=1 error=
; delete 1 result=1 error=
; delete 2 result=0 error=

[jassing]

are there any stored procedures or triggers in that database?

[Kiffi]

The same with a SQLite database works as expected:

Code: Select all

UseSQLiteDatabase()

h = OpenDatabase(#PB_Any,":memory:", "", "", #PB_Database_SQLite)

r = DatabaseUpdate(h,"Create Table tValues (sKey, sValue)")
Debug "create result=" + Str(r) + " error=" + DatabaseError()

r = DatabaseUpdate(h, "Insert Into tValues (sKey, sValue) values ('foo', 'bar')")
Debug "insert result=" + Str(r) + " error=" + DatabaseError()

r = DatabaseUpdate(h, "Delete From tValues Where sKey='foo'")
Debug "delete 1 result=" + Str(r) + " error=" + DatabaseError()

r = DatabaseUpdate(h, "Delete From tValues Where sKey='foo'")
Debug "delete 2 result=" + Str(r) + " error=" + DatabaseError()

CloseDatabase(h)

; results in debug...

; insert result=1 error=
; delete 1 result=1 error=
; delete 2 result=1 error=

[sc4pb]

No, plain old table.

May be specific to odbc, or a bad behavior from particular drivers. Looks like I'm using "ODBC Driver 17 for SQL Server"

[jassing]

May be specific to odbc, or a bad behavior from particular drivers. Looks like I'm using "ODBC Driver 17 for SQL Server"

I can't install mssql (system too old); but it's definitely not the PB-odbc interface; as it works properly with x64 x86 odbc drivers(for MySQL), so it's probably the driver that's faulting... Fred will be able to tell more. (hopefully)

[Fred]

Fixed. It was a bit tricky because ODBC 3.0+ drivers can return SQL_NO_DATA as well when executing a request, which is not an error and wasn't handled as such by PB (https://learn.microsoft.com/en-us/sql/o ... rver-ver16)