The last days for a project in Xojo we had a different idea for prepared statements to avoid using wrong indices.
As you may know the MBS SQL Plugin allows you to use field names in the Bind statement to avoid messing up with indexes for named parameters. For this project the database access is without MBS Plugin, but with Xojo's built in database classes. So we had several queries to update 20+ fields in same tables and mixing up the indices for all the Bind() and BindType() calls and all those question marks.
So I had the idea to put all the field names in an array first, build SQL statement using that field and use the field array later to find the index in code. Take a look on this function:
Sub Save() dim db as SQLiteDatabase = app.db dim fields() as string // the fields to update fields.Append "Modified" fields.Append "Description" fields.Append "Number" fields.Append "Comment" ... fields.Append "LastField" // build SQL with field list dim sql as string = "UPDATE Objekt SET " + Join(fields,"=?, ") + "=? WHERE ROWID = ?" dim p as SQLitePreparedStatement = db.Prepare(sql) if db.Error then dim e as string = db.ErrorMessage DebugLog "Prepare error: "+e beep return end if // fill fields with values p.BindV(p.SQLITE_BOOLEAN, fields.IndexOf("Modified"), true) p.BindV(p.SQLITE_TEXT, fields.IndexOf("Description"), iDescription.Text) p.BindV(p.SQLITE_TEXT, fields.IndexOf("Number"), iNumber.Text) p.BindV(p.SQLITE_TEXT, fields.IndexOf("Comment"), iComment.Text) ... p.BindV(p.SQLITE_TEXT, fields.IndexOf("LastField"), LastField) // and the RowID as last one p.BindV(p.SQLITE_INTEGER, fields.Ubound+1, currentRowID) p.SQLExecute if db.Error then dim e as string = db.ErrorMessage DebugLog "Update error: "+e beep return end if if not app.db.MyCommit then beep return end if SetModified false // Upate GUI ... End SubThe helper function BindV allows you to specify type, field index and value in just one call. With the break statement there, we can break right in the method if index is wrong:
Sub BindV(extends p as SQLitePreparedStatement, Type as Integer, Index as integer, value as Variant) #if DebugBuild then if index < 0 then // field not found break end if #endif p.BindType(index, type) p.Bind(index, value) End SubThe benefit of this approach is to make it easy to add/remove fields from the list and keep indices correct.
What do you think about this approach?