PreparedStatements and db.errors

JimPitchford · **Joined:** Mon Apr 11, 2011 2:01 pm **Posts:** 150

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.

Bob Keeney · **Posted:** Mon May 20, 2013 10:30 am

With SQLite, I can tell you that checking for an error after creating a prepared statement won't always give you what the error is. I've run into this issue and it makes prepared statements and SQLite less than optimal. With that said, you really should check for errors every every database transaction.

I notice that you're using "End Transaction" which is an implicit alias to Commit. Any reason why you're not using the built-in Commit and Rollback methods? The other things that I don't see is that if you have an error you're not rolling back your data you're just quitting. If you don't care then you don't have to do a transaction (if you're looking for speed set autocommit = false at that point) and everything will be an implicit transaction.

JimPitchford · **Joined:** Mon Apr 11, 2011 2:01 pm **Posts:** 150

Bob

Thanks for the reply. I'm not sure how to deal with the first part of your answer. If checking for errors results in false positives - how can I use that output? Rollback and ask the user to try again? Doesn't feel right. Maybe if the DB was online, but not when the DB is sitting there on their hard disk.

I think my question was really "Is a PreparedStatement really a transaction?". There is a note that I just noticed in the Language Reference under Database.Prepare which states "If the provided SQL statement has an error, the Database.Error property does not get set to True until you call SQLSelect or SQLExecute." Does that not mean that db.error is meaningless after db.prepare?

Thanks for the comments in the second para. "End Transaction" was used instead of commit partly to provide more symmetry in the code, and because as the code has evolved it seemed that I was getting less DB errors that way than by using DB.commit. However those errors may have been the false positives described above, so it may be that using DB.commit is better.

The DB.rollback is provided for in the DisplayError method - I just didn't show it here - but as this is a Desktop system I really shouldn't be seeing any errors that need a rollback.

Jim

Bob Keeney · **Posted:** Mon May 20, 2013 1:30 pm

I'm not sure, but you might be asking for trouble if you don't always balance the Start Transaction with either a commit or rollback.

The generic error I was referring to is that if you have an error in your SQL when creating the prepared statement you'll get a generic error (cannot prepare statement) rather than the specific error in the SQL.

JimPitchford · **Joined:** Mon Apr 11, 2011 2:01 pm **Posts:** 150

Bob

Tks, I'll make sure there is a balancing commit or rollback.

WRT to the original question, the following answers if for me. Try the following code:

dim db as new SQLiteDatabase

dim sql as string = "SELECT guid FROM dates WHERE deleteFlag = 0"

dim ps as SQLitePreparedStatement  = db.Prepare(sql)

if db.error then
  MsgBox str(db.ErrorCode) + " " + db.ErrorMessage
end if

dim rs as recordset = ps.SQLSelect

if db.error then
  MsgBox str(db.ErrorCode) + " " + db.ErrorMessage
end if

This shows that the error "unable to prepare statement" occurs on the second test of db.error, not the first. As far as I can tell the first test of db.error is meaningless - and may pick up values of db.error from other db transactions that are no longer relevant to the current code - which may go toward explaining its "less than optimal" performance.

Jim

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

Correct, the first error check is meaningless because you don't 'talk' to database until you call ps.SQLSelect or Execute. You couldn't bind data otherwise

Bob Keeney · **Posted:** Mon May 20, 2013 3:35 pm

Jym wrote:

Correct, the first error check is meaningless because you don't 'talk' to database until you call ps.SQLSelect or Execute. You couldn't bind data otherwise

Not true. If you're using OTHER databases it will actually give you an error at that point which is what I would expect from SQLite too.

Jym · **Joined:** Sat Oct 01, 2005 5:19 pm **Posts:** 3216

Bob Keeney wrote:

Jym wrote:

Correct, the first error check is meaningless because you don't 'talk' to database until you call ps.SQLSelect or Execute. You couldn't bind data otherwise

Not true. If you're using OTHER databases it will actually give you an error at that point which is what I would expect from SQLite too.

Dim ps As PostgreSQLPreparedStatement = pgd.Prepare("Give me an error")
If pgd.error then
msgbox pgd.errormessage
Else
msgbox "Error didn't register"
End

I'm not sure how it could give you an error. This doesn't error.

JimPitchford · **Joined:** Mon Apr 11, 2011 2:01 pm **Posts:** 150

I just wanted to finish this dialogue off having tried multiple variations of the locations in which to test for a db.error.

Within SQLite the following seems to work.

1. Don't test for errors after a db.Prepare( sqlString ) statement. It does cause false positives. Strangely the error code will even change back to 0 (not an error) if you go and look at it twice when debugging in the IDE.

2. Do test for errors after each dbPrep.SQLExecute statement or dbPrep.SQLSelect statement. This is where the "unable to prepare statement" errors will occur.

3. That includes testing for errors on every sqlExecute or sqlSelect if you are within a multi-statement transaction.

4. Don't test for errors after the final db.SQLExecute("End Transaction") or db.commit

Jim

	Real Software Forums The forum for Real Studio and other Real Software products.
[ REAL Software Website \| Board Index ]

Real Software Forums

PreparedStatements and db.errors