Catch DatabaseException

Databases
1

Is there a way to catch a DatabaseException, for example a duplicate key, in MSSQLSERVERDATABASE. Here is my pseudo code.

Try
   If db.connect Then
      sql = "Insert into myTable (column1) VALUES ('aDuplicateKey');" 
      db.executeSQL(sql) // -- throws an error code fails
   Else 
      MessageBox("Doesn't make it here.")
   End If
Catch error As DatabaseException 
   MessageBox("Doesn't make it here.")
End Try

I also tried nested Try Catch statements but the code never gets past the db.executeSQL statement.
2

What does happen though?

3

Hi Kem, Fails with ErrorCode 3621 and ErrorMessage “The statement has been terminated.”

4

So not an Exception, just an error? If so, that should be considered a bug and a Feedback report helpful.

5

OK. Thanks. I’ll submit a bug report.

6

What type of column is your ‘column1’? If it is an integer key then it’ll likely fail since you are trying to insert a text value. If MSSQL does serious type checking then the database engine will trip on this and terminate the execution of the statement. Definitely not a DatabaseException and not really a bug in Xojo either, I don’t think, as it can only report what the database tells it. (I know some DB engines will let you get away with type mismatches but I don’t know about MSSQL.)

7

executeSQL is supposed to throw an exception, not just return an error message.

8

Don’t forget that if you are running the code in the debugger, you actually have to click one of the Step buttons for it to actually get to the exception handler.

If you are working with exceptions, you might want to try just calling db.connect as well without the if-then. If an error were to happen during connection, it too would throw.

3 Likes
9

Thanks for all the help and suggestions. I somewhat dreaded the unwinding of the real code and creating a sample project (it took me about a day). Since I could not get the sample project to fail, I was able to locate what was happening.

This short routine was ultimately being called by a thread. It was throwing ‘An exception of class ThreadAccessingUIException was not handled. The application must shut down…’

I had mistakenly ignored this error and narrowed in on the short routine stepping through it line by line not realizing it had been called by a thread. MessageBox has always been my goto stop while developing and for trapping rare errors or errors that should never occur.

Based on this experience, I think I’ll change to sending errors to a log file or table instead of to the UI.

Thanks again for the help.

10

You can still do it from a Thread, but have to use the UserInterfaceUpdate event and AddUserInterfaceUpdate method.

11

This is very easy! Switch MessageBox to System.DebugLog and it will appear in the debug pane.

MessageBox changes active context and can cause further debug nightmares. Xojo staff actively recommend against using MessageBox as a debug tool.

4 Likes