db.ExecuteSQL Not Working And Not Giving An Exception

General
1

Hi,
My app has a few dozen sql statements, selects, inserts and updates but this particular one (the simplest actually) does no record update and doesn’t raise an exception of any sort. Am I missing something obvious as I’ve tried everything I can think of. I’m just updating one field in the [log] table based on the tabel [id] field.

  1. So I have a function to return an sql statement based on a description, in this case “UpdateLogCredited”
Case "UpdateLogCredited"
  Return "UDPATE [log] SET credited=1 WHERE id=?;"
  1. I have a function called UpdateLogCredited() which is passed an integer LogID and returns true or false depending on if it’s ‘successful’. It adds the LogID parameter, as I think is the benchmark way to formulate such things..
Dim sql as string=SQLQuery("UpdateLogCredited")

Try
  //execute update
  db.ExecuteSQL(sql,LogID)
  Return True
  
Catch e As DatabaseException
  ShowError e.Message, CurrentMethodName
  Return False
end Try

This all runs ok and the function returns True so there’s no exception raised etc. However, the record is not updated and I’ve debugged the sql statement is correct and the id is valid and the one expected. The [credited] field in the [log] table is of type BIT so 0/1 are valid values. I can only think it’s something to do with the field itself?

Any help from an distant pair of eyes would be appreciated. It can’t get much simpler can it?

(Running Xojo 2025v1.1 on Windows 11 Pro with MSSQL local database)

Thanks :slight_smile:

2

Maybe you should use UPDATE instead of UDPATE :wink:

Case "UpdateLogCredited"
  Return "UPDATE [log] SET credited=1 WHERE id=?;"
1 Like
3

:flushed_face:

Can’t believe that, I’ve spent a couple of hours trying to debug that. I believe the expression is ‘can’t see the wood for the trees’. How embarassing!

Shouldn’t that raise an exception though?

1 Like
4

I didn’t see is, either.

1 Like
5

Yes it should throw something like:

Error( 1064 ) 42000: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UDPATE [log] SET credited=1 WHERE id=?' at line 1"
6

Maybe MSSQL isn’t as helpful

7

MSSQL should throw something like this:

[FreeTDS][MSSQL Server] , Server WAWI-SVR\SQL2014, Line 1
    Incorrect syntax near '='.
8

So just wondering now if I’m going to get any exceptions for sql syntax/parameters problems in my code. Could there be a reason/setting which means I’m not getting an error in such cases?

10

What are you using for connection? ODBC?

11

Yes, the Xojo Lib and the following DataSource:

Driver={SQL Server};Server=XXX;Database=XXX;UID=XXX;Pwd=XXX
12

I use the Xojo MSSQL plugin

db=new MSSQLServerDatabase etc.

13

A short test using the Xojo ODBC-Driver brought up an exception, as expected:

Bildschirmfoto-2
Bildschirmfoto-2700×477 22.5 KB

14

Interestingly it breaks here but does not show exception variable “e” in the debug:

image
image865×425 13.3 KB

I need to inspect the db var itself to know more:

image
image1053×660 24.5 KB

15

Because you are catching a RuntimeException? :wink:

16

As it should be debugging

17

Nothing, weird.

image
image1048×460 14.4 KB

Well… That’s another bug. Debugger, not related.

18

Just for our audience: Always place db.Connect within the try…catch, because it can also cause exceptions. :slight_smile:

1 Like
19

For a debug sample, it was purposely left out, because it should not break there, if it raised an exception there, I wanted to see there, not in the catch.

20

I just try to avoid beginners making such mistakes wherever possible…

21

Whatever, from my side I’m just giving some debugging tips. :wink:

I’ll update my Xojo to the last release and run the test again later

image
image1042×440 16.7 KB

image
image1042×440 16.7 KB

Happy bug hunting, people. :wink: