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
else
Return False
end
Thanks
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
try
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.
next
dblogmsg (errnum, errmsg, where, sql)
return Nil
end submore 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?
@Norman_Palardy
That literally means I have to wrap hundreds of calls with try/except.
What was wrong with Error / Errorcode?
Yes
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 … 
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?
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
Try
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
Try
rs = db.SelectSQL(sql, params)
Catch err2 As DatabaseException
Call MessageBox (msgIconStop, "Database error " + err2.Message, sql)
db.RollbackTransaction
Return False
End Try
Else
ReadyCursor
Call MessageBox (msgIconStop, "Database error " + err.Message, sql)
db.RollbackTransaction
Return False
End
End
If rs = Nil Then
Call MessageBox (msgIconStop, "Database error: Nil Rowset", sql)
Return False
End
Return True
End FunctionA 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?
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)
Else
Break
End Select
Next
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) )
Else
Break
End Select
If localDB.Error Then
Debugging.debuglog CurrentMethodName + " error " + Str(localDB.ErrorCode) + " " + localDB.ErrorMessage
Break
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.