Hi all,
I posted some code I normally use for Prepared statements in another forum post which has lead me to “re-evaluate” how I am using Prepared statements. I hadn’t been opening the prepared statement up with a “BEGIN TRANSACTION” simply thinking I didn’t need to since it was cached (and it always worked so I kind of moved on form it then). However thanks to Wayne and Markus for pointing out some issues with my code I began to research prepared Statements/auto commit in depth.
This has lead me to now understanding SQLite Auto-Commit in which the “proper” code to execute. I ensured that I commit error free and rollback when I artificially introduce errors in which this code works 100%.
I was hoping you could check this for me and point out any missteps if I have any. (My goal is NOT to rely on Auto-Commit). Thanks in advance!
This is my new Code that I want to Switch to barring no issues.
Dim SQLInsertStatement As String = "insert into FutureSchedulerMGMTTable (BatchID, DateSelected,TimeSelected,Recurrence,State) values (?, ?, ?, ?, ?)"
Dim prepInsert As SQLitePreparedStatement = APP.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.Prepare(SQLInsertStatement)
prepInsert.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(3, SQLitePreparedStatement.SQLITE_TEXT)
prepInsert.BindType(4, SQLitePreparedStatement.SQLITE_TEXT)
// Use Passed Values to Submit into our Table
prepInsert.Bind(0, inBatchID)
prepInsert.Bind(1, inDateSelected)
prepInsert.Bind(2, inTimeSelected)
prepInsert.Bind(3, inRecurrence)
prepInsert.Bind(4, inState)
// Execute Prepared SQL Statements
App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.SQLExecute("BEGIN TRANSACTION")
prepInsert.SQLExecute
// SQL Transaction Error Checking
if App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.Error = False then
// No Errors so Commit
App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.Commit
elseif App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.Error = True then
// Received Error --> Rollback Transation
LogModule.mSystemLog("1","(DB)","Scheduler MGMT Table DB error code[" + Str(App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.ErrorCode) + "] " + "- " + App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.ErrorMessage)
App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.Rollback
end if