Is a db.error check after a prepare or bind statement good practice?

Databases
1

I’m unsure whether I’m adding an unnecessary error check during a PreparedStatement insert operation. Is there any value in adding a check after the prepare/binding is done, but before the ps.SQLexecute?

Here’s some sample code to illustrate:

[code] dim ps as ODBCPreparedStatement

ps = TestDB.Prepare(“INSERT INTO test (test_field) VALUES (?);”)
// should I error check here (position 1)
ps.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
ps.Bind(0, tfDate.Text)
// or leave it here (position 2)
If TestDB.Error Then
System.DebugLog("Error: " + Str(TestDB.Error) + " Code: " + str(TestDB.ErrorCode) + " - " + TestDB.ErrorMessage)
End If
// or is the above error check useless?

ps.SQLExecute
// this is mandatory
If TestDB.Error Then
System.DebugLog("Error: " + Str(TestDB.Error) + " Code: " + str(TestDB.ErrorCode) + " - " + TestDB.ErrorMessage)
End If[/code]

2

Put everything inside try… catch. For debugging purposes it is always fine to log SQL and DB errors but it depends on your App to show this. Since a regular User can’t do anything useful with error messages I wouldn’t bother him.

3

Thanks for the reply, Thomas.

What I’m trying to determine if there is any value in checking the prepare operation for errors separately .

(I recently had an issue in an Access 2010 app where an insert was wrapped in a transaction. The transaction failed with a generic error code, but that error overwrote the true error code that my (arghh) VBA code caused. It was a bugger to track down. I’m trying to avoid that here.)

Do you know if the db.Prepare operation could/would generate an error that differs from the db.SQLexecute operation? If so, I need to check it but I’ m unsure whether to try before or after the ps.Bind operation.

4

I don’t believe Bind will alter database.error, but it is better to check at position1 so it is clear that this is the statement that can generate an error. And yes, Prepare can result in a database error.

5

Thanks Tim. Just what I needed.

6

It doesn’t matter how bad the SQL statement is when prepared it will not produce an error. You’ll only get an error when executing the statement or attempting to create a recordset, then you’ll get an “Unable to Prepare Statement” error. Debugging Prepared Statements is truly crap.

7

Yes, I wish there was a way to subclass them and (for a start) be able to inspect the SQL that’s being sent. I don’t have the SSMS profiler installed on my dev box, so it’s a real pain. MS Technet indicates that SQLPrepare can generate errors, but perhaps they’re not implemented on the Xojo ODBC driver. To be prudent I’ll leave the check in place. Thanks for your help.