Detecting errors in SQLite transactions

[srod]

Hi,

just wondering if someone can help out a wee bit here.

I am using SQLite transactions and wish to roll-back if there is an error processing any of the statements within the transaction. At the moment, I am having to examine every statement to see if there is an error, but there must surely be a better, more efficient, way?

Is there some way of checking for any error, just once, immediately prior to committing the transaction? Some flag or other which will tell me if any of the preceding statements produced some kind of error?

Code: Select all

DatabaseUpdate(#db, "BEGIN TRANSACTION")
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
If error detected in any of the above statements...
    DatabaseUpdate(#db, "ROLLBACK")
Else
    DatabaseUpdate(#db, "COMMIT")
EndIf

I've tried importing sqlite3_errcode() etc. but that doesn't help. Is there some other function I can import or some way of setting up my transaction etc? Afraid that I am not really very experienced with SQL and so could be missing something obvious here.

Thanks.

[spikey]

Depends exactly what errors you are looking for.

You could detect SQLITE_FULL, SQLITE_IOERR, SQLITE_NOMEM, SQLITE_BUSY, and SQLITE_INTERRUPT in a transaction using sqlite3_get_autocommit - as its behaviour changes for transactional commands compared to non transactional. Have a look at http://www.sqlite.org/capi3ref.html#sql ... autocommit.

However you can't tell exactly which error arose (see below). This article also gives the impression that other errors might cause this behaviour too - but doesn't say exactly which they might be.

For other errors the corresponding error value will be updated after each new SQL instruction (or API call), so you've got to check for an error after each individual call if you need to.

In similar situations I use a flag variable to determine if something went pear shaped - as DatabaseUpdate returns a result you could do the same:

Code: Select all

bytFlag = #True
If DatabaseUpdate(#db, ......) = 0
    bytFlag = #False
EndIf

If bytFlag = #False
    DatabaseUpdate(#db, "ROLLBACK")
Else
    DatabaseUpdate(#db, "COMMIT")
EndIf

[USCode]

Hi,

just wondering if someone can help out a wee bit here.

I am using SQLite transactions and wish to roll-back if there is an error processing any of the statements within the transaction. At the moment, I am having to examine every statement to see if there is an error, but there must surely be a better, more efficient, way?

Is there some way of checking for any error, just once, immediately prior to committing the transaction? Some flag or other which will tell me if any of the preceding statements produced some kind of error?

Code: Select all

DatabaseUpdate(#db, "BEGIN TRANSACTION")
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
    DatabaseUpdate(#db, ......)
If error detected in any of the above statements...
    DatabaseUpdate(#db, "ROLLBACK")
Else
    DatabaseUpdate(#db, "COMMIT")
EndIf

I've tried importing sqlite3_errcode() etc. but that doesn't help. Is there some other function I can import or some way of setting up my transaction etc? Afraid that I am not really very experienced with SQL and so could be missing something obvious here.

Thanks.

I don't really see any way around it. You don't really want to move on with other database updates if one of them receives an error and you're just going to roll it back anyways.

Maybe you could put all your SQL statements in an array or list, then put together a utlilty procedure that loops through the array/list executing each DB update and checking the success of the update ... on error break out of the loop and execute the rollback, otherwise commit.

[srod]

Hi,

thanks for the replies guys - much appreciated.

What I did was simply run the batch and, to keep things as fast as possible, tally up the returns from DatabaseUpdate() etc. (Actually I bitwise shifted a 32-bit flag each time). If the resulting flag showed a discrepancy at the end of the transaction then I rolled back the transaction, otherwise the transaction was committed.

I figured that checking each DatabaseUpdate() would slow down those error free transactions etc.

Seems to work fine.

[jassing]

Couldn't you just check DatabaseError() after each update?

[srod]

Couldn't you just check DatabaseError() after each update?

Too slow and it doesn't work if used at the end of the transaction.

[jassing]

Couldn't you just check DatabaseError() after each update?

Too slow and it doesn't work if used at the end of the transaction.

I think if you want to check if the transaction failed, but all the insertions/updates worked; you're going to have to skip the PB-sqlite and use the sqlite dll directly....

besides, if using DatabaseError() is too slow; Using the PB-sqlite would be too slow all around... -- using the sqlite3.dll directly would be faster all around.
Just my HO.

[srod]

No it's not that; checking DatabaseError() after each update would be too slow for my needs. DatabaseError() itself, well I don't know if that runs slowly or not.

My code runs fine as it is right now.

Why would using the dll directly be faster all round?

[jassing]

Why would using the dll directly be faster all round?

I had an app written that used sqlite. The client needed me to do something I couldn't do with the built-in functions, so I started to use the dll directly - not pretty.... tons more work, but I noticed it was faster.

Nothing empirical, just seat of the pants oberservation...

[skywalk]

Why wouldn't it be faster using the latest release of SQLite given the code is actively developed with a mind toward more features, speed and bug fixes.
Not sure of your exact use, but there are speed enhancements(WAL) if using only local db's.

[srod]

Well, we were talking about using the dll directly as opposed to PB's static lib version, irrespective of whether it is the latest version of SQLite or not. Considering we can import those SQLite functions directly that PB doesn't support, I don't really see any reason why the dll need be faster, or the static lib need be slower! I assume that PB uses Prepare / Step / Finalize etc. just like we would with the dll.

I just prefer using the static lib when all said and done.

[skywalk]

My comments assumed jassing was using the latest SQLite.dll.
Not 3.6.xx or whatever is in PB46.
I also prefer the static approach and eagerly await PB integrating it soon.