DatabaseException Anybody have a good example

Looking for a good example of DatabaseException use.

Currently I call this function after ever DB Call, I would like to just be able to replace this:

Function CheckDBError(lsPrompt as String = "")
if gDB.Error then
  MsgBox "Database Error: " + str(gDB.ErrorCode) + EndOfLine + EndOfLine + gDB.ErrorMessage + EndOfLine + EndOfLine +lsPrompt
  Return True
  Return False


I made my own methods, example below. Decide for yourself how you want to log the error. I did some tests recently, decided that doing my own “Database locked” retry works better than the timeout you can set at connect. This gives exponential backoff retry up to 250 msec, and then a retry every 250msec up to a 20sec total. Set the connect timeout to 1, otherwise that just gets added to the 20 sec.

Other similar methods for non-query calls. HTH.

sub dbquery (dbh as sqlitedatabase, sql as string, where as string, ParamArray args as Variant)

// Called when we expect a RowSet to be returned. This method uses its own busy timeout recovery.
// Returns the RowSet if rows are found without error. Returns Nil if no rows found. Also returns
// Nil if an error occurs, in which case a message is logged also.

Var reg As RowSet, i, lim, delay, errnum As Integer, errmsg As String

lim = app.SQLiteDelays.LastRowIndex

for  i = 0 to 486
    reg = dbh.SelectSQL (sql, args)
    return reg                                                            // Worked fine, just return
  Catch e as DatabaseException
    errnum = e.ErrorNumber
    errmsg = e.Message
    if  (errnum<>app.SQLiteBusy)  then exit                               // Not database locked, report and return
  end try
  delay = if  (i>lim, 250, app.SQLiteDelays(i))                           // First few are exponentional, then const 250msec
  app.SleepCurrentThread (delay)                                          // Locked, exponential backoff sleep and try again.

dblogmsg (errnum, errmsg, where, sql)

return Nil

end sub

more or less you cannot because the code to catch the exception has to be around the code that runs the query

@TimStreater Would this work with MySql?

That literally means I have to wrap hundreds of calls with try/except.

What was wrong with Error / Errorcode?

Or you write ONE new method for yourself that calls into all the new db stuff and wraps it all away where you deal with it ONCE and forget it

people mostly wouldnt check them

I’m not so sure folks will “handle exception by default” either so … :man_shrugging:

1 Like

So you see the utility of encapsulating the call. I was always wanting to report the database error, but in such a way without cluttering the main code with lots of logging calls. Once I then also found that Database Locked could be a special problem, I could deal with that in just the one place - in my wrapper. Same applied to try/catch once that came along.

There not going to drop the db.error anytime soon are they. I would like to get the DB stuff converted first.

Is there anyway to get an spreadsheet like or text file of all the warnings. It’s so cumbersome to work through them. I would like to see just the db stuff first?

I will work toward this. I don’t see why it wouldn’t work with MySql do you?

Long ago I wrote one method that was like

Function DoSQL( sqlquery as string, paramarray params() as variant ) as integer

and it could handle up to as many params as I wanted to write code for, would bind them properly for any one of several db’s etc

and return an error code if one occurred

for me to fix my code I need to alter than ONE method and I could still just catch the exception in there and return the error code
it would all be invisible to the rest of my code

There are other ways to do this
And even with Xojo’s additions I might still just use this and alter this ONE thing and I’m done

I’ve done this sort of thing for a LOT of other code so actually moving to API 2, or staying with API 1.0 is a non-issue
You might consider that for yourself as well

I use this with desktop apps with MySQL. You’ll need to change the error message display as my MessageBox is my own wrapper for a MessageDialog. Web app also works, just change the MessageBox arguments.

I find that the network connection can drop frequently due to various things, including the computer going to sleep. So to make it transparent to the user, I catch that error and retry. If the 2nd attempt also fails I give up and display an error.

Public Function DBSelect(ByRef rs As RowSet, sql As String, ParamArray params() As Variant) as Boolean
    rs = db.SelectSQL(sql, params)
  Catch err As DatabaseException
    If err.ErrorNumber = 2006 Or err.ErrorNumber = 2013 Then    ' server has gone away or connection lost
      Call ConnectToData    ' reconnect to the database
        rs = db.SelectSQL(sql, params)
      Catch err2 As DatabaseException
        Call MessageBox (msgIconStop, "Database error " + err2.Message, sql)
        Return False
      End Try
      Call MessageBox (msgIconStop, "Database error " + err.Message, sql)
      Return False
  If rs = Nil Then
    Call MessageBox (msgIconStop, "Database error: Nil Rowset", sql)
    Return False
  Return True
End Function


A few questions:

What is Readycursor?

Also are you calling StartTransaction?
If so both execute and selects can be sent to this, correct?

It seems you would be using this for ExecuteSql as well as selects?

Hey @Norman_Palardy could you post the method?

Its a bit out of context
This lives in a class that I use as a wrapper for a database (m_DB)

There are several different wrappers for different db’s (MS SQL, SQLITE, Postgresql etc) that allow me to implement things the way I like (or that dont exist like MBS TableSchema and DatabaseSchema for MS SQL)

This one is from the SQLIte wrapper

But this should give you an idea of how this works

then by making this one method use try / catch to catch errors you’re “done” as far as updating to API 2 methods

Public Function RawSQLSelect(query as string, ParamArray bindVars as variant) as RecordSet
  Dim localDB As SQLiteDatabase = SQLiteDatabase(m_DB)
  Dim ps As SQLitePreparedStatement = localDB.Prepare(query)
  For i As Integer = 0 To bindVars.ubound
    Select Case True
    Case bindVars(i).Type = Variant.TypeBoolean
      ps.BindType( i, SQLitePreparedStatement.SQLITE_BOOLEAN)
    Case bindVars(i).Type = Variant.TypeColor
      ps.BindType( i, SQLitePreparedStatement.SQLITE_INTEGER)
    Case bindVars(i).Type = Variant.TypeCurrency
      ps.BindType( i, SQLitePreparedStatement.SQLITE_DOUBLE)
    Case bindVars(i).Type = Variant.TypeDate
      ps.BindType( i, SQLitePreparedStatement.SQLITE_TEXT)
    Case bindVars(i).Type = Variant.TypeDouble
      ps.BindType( i, SQLitePreparedStatement.SQLITE_DOUBLE)
    Case bindVars(i).Type = Variant.TypeInt32
      ps.BindType( i, SQLitePreparedStatement.SQLITE_INTEGER)
    Case bindVars(i).Type = Variant.TypeInt64
      ps.BindType( i, SQLitePreparedStatement.SQLITE_INT64)
    Case bindVars(i).Type = Variant.TypeSingle
      ps.BindType( i, SQLitePreparedStatement.SQLITE_DOUBLE)
    Case bindVars(i).Type = Variant.TypeString
      ps.BindType( i, SQLitePreparedStatement.SQLITE_TEXT)
    End Select
  Select Case bindVars.Ubound
  Case -1
    Return ps.SQLSelect
  Case 0
    Return ps.SQLSelect( bindVars(0) )
  Case 1
    Return ps.SQLSelect( bindVars(0), bindVars(1) )
  Case 2
    Return ps.SQLSelect( bindVars(0), bindVars(1), bindVars(2) )
  Case 3
    Return ps.SQLSelect( bindVars(0), bindVars(1), bindVars(2), bindvars(3) )
  Case 4
    Return ps.SQLSelect( bindVars(0), bindVars(1), bindVars(2), bindvars(3), bindvars(4) )
  Case 5
    Return ps.SQLSelect( bindVars(0), bindVars(1), bindVars(2), bindvars(3), bindvars(4), bindvars(5) )
  Case 6
    Return ps.SQLSelect( bindVars(0), bindVars(1), bindVars(2), bindvars(3), bindvars(4), bindvars(5), bindvars(6) )
  End Select
  If localDB.Error Then
    Debugging.debuglog CurrentMethodName + " error " + Str(localDB.ErrorCode) + " " + localDB.ErrorMessage
  End If
End Function

Sorry - I should have removed ReadyCursor. (I use WaitCursor and ReadyCursor to change the mouse pointer. Not really relevant to the database discussion here.)

I do use a similar method for ExecuteSQL. I call StartTransaction sometimes, if I need to execute multiple queries to keep something consistent.