At the recent Xojo 2013 conference Paul Lefebyre recommended checking for db.errors not only after database executions but also after db.prepare statements.
I'd like to challenge that statement, based on my own findings in using preparedstatements, where the checking db.error seems to kick up non-existent errors.
In my example, I collect prepared statements in an array which is then executed on a timer or before any database read statements. The prepared statements are then executed as one transaction. I do this to speed up database access and reduce the number of update+commit cycles.
The execution method looks like this:
//checks to see if any outstanding db_Executes are present and performs as necessary
//pops from top until none left
//triggered by the onSaveTimer or prior to any dbRead operation
if db_Executes = nil or db_Executes.Ubound = -1 then return
db.SQLExecute("Begin Transaction") //ensures all prepared statements are executed together
while db_Executes.ubound > -1
dim cmd as string = db_Executes_Type.pop //this is just here for debugging, cmd contains the SQL string
db_Executes.Pop.SQLExecute
If mdb.DisplayError (DB, false) = true then quit
wend
if mdb.DisplayError(DB, false) then
quit
else
db.SQLExecute("End Transaction") //same as a commit statement - but seems less error prone
end
Exception err
if not app.HandleException(err, currentMethodname) then
Raise err
end if
A typical code block that creates the PreparedStatement might look like this:
dim sql as string = "INSERT INTO "+hTableName+" (GUID) VALUES (?) "
dim pExecute as SQLitePreparedStatement = SQLitePreparedStatement( hProject.db.Prepare(sql) )
'If DisplayError (hProject.DB, false) = true then quit
//set the GUID bindTypes as string
pExecute.bindType(0, SQLitePreparedStatement.SQLITE_TEXT)
pExecute.bind(0, GUID )
hProject.dB_Execute_AddLine( pExecute, sql + " " + GUID ) //for debugging
If I run the code in this form, then the routines run fine, no errors are reported. However if I take out the comments, reinserting the error check method "DisplayError" then it starts to report false positives. I get error messages like "cannot commit - no transaction is active" - even though, at that stage in the code, I am not attempting to commit anything.
Is anyone able to confirm whether or not db.error is properly set after db.prepare statements or whether it is only set after "database operations" as specified in the language guide, which I would take to mean steps that operate on the data in the database like a Select, Update or Execute.