New topic: PreparedStatements and db.errors
<http://forums.realsoftware.com/viewtopic.php?t=47928> Page 1 of 1 [ 4 posts ] Previous topic | Next topic Author Message JimPitchford Post subject: PreparedStatements and db.errorsPosted: Mon May 20, 2013 9:27 am Joined: Mon Apr 11, 2011 2:01 pm Posts: 148 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. _________________ Jim OSX 10.8.2, rb2012r2 Top Bob Keeney Post subject: Re: PreparedStatements and db.errorsPosted: Mon May 20, 2013 10:30 am Joined: Fri Sep 30, 2005 11:48 am Posts: 3549 Location: Lenexa, KS 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. _________________ Bob K. A blog about being a Real Studio/Xojo developer at http://www.bkeeneybriefs.com Top JimPitchford Post subject: Re: PreparedStatements and db.errorsPosted: Mon May 20, 2013 10:57 am Joined: Mon Apr 11, 2011 2:01 pm Posts: 148 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 _________________ Jim OSX 10.8.2, rb2012r2 Top Bob Keeney Post subject: Re: PreparedStatements and db.errorsPosted: Mon May 20, 2013 1:30 pm Joined: Fri Sep 30, 2005 11:48 am Posts: 3549 Location: Lenexa, KS 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. _________________ Bob K. A blog about being a Real Studio/Xojo developer at http://www.bkeeneybriefs.com Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 4 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
