SQlite Prepared Statement Sanity Check

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")
  // SQL Transaction Error Checking
  if App.ICP_Scheduler_Database_Class.Scheduler_Database_SQLite.Error = False then
    // No Errors so 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)
  end if