But I have a class that needs to execute a common PS many many times… well two versions… one for UPDATE and one for SELECT
can I prepare these statments in the Class constructor and then reuse them without anything additonal?
// constructor
ps_update = DB.Prepare("Update ? SET ? = ? WHERE ?")
ps_update.bind ..... etc
ps_select = DB.Prepare("Insert Into ? (?,?) VALUES(?,?)"
ps_select.bind ..... etc
[/code]
// elsewhere in the class code
ps_update.execute(fld_name,new_value,cond)
ps_update.execute(fld_name2,new_value2,cond2)
[/code]
in the CONSTRUCTOR of the class I have (the two PS are properties of the class)
//
// cache Prepared INSERT statement for PROJECT Table
//
zPS_UPDATE=DB_PROJECT.Prepare("UPDATE "+tablePROJECT+" SET ?=?")
zPS_UPDATE.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
zPS_UPDATE.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
//
// cache Prepared INSERT statement for PROJECT Table
//
zPS_SELECT=DB_PROJECT.Prepare("SELECT ? AS newvalue FROM "+tablePROJECT)
zPS_SELECT.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
zPS_SELECT.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
// make sure a "Project" record exists
Dim rs As RecordSet
Call DB_PROJECT.DB_select("SELECT count(*) FROM "+tablePROJECT,rs)
If rs.RecordCount=0 Then
Call DB_PROJECT.DB_execute("INSERT INTO "+tablePROJECT+" DEFAULT VALUES")
End If
Then I have a series of Computed Properties like this
Public Property projectName as string
Get
return getField("projectName")
End Get
Set
setField("projectName")=value.trim
End Set
End Property
which call these two methods (also scoped to the class)
Private Function getField(fieldName as string) as string
Dim rs As RecordSet = zPS_SELECT.SQLSelect(fieldName)
Return rs.field("newValue").StringValue
End Function
Private Sub setField(fieldName as string, assigns newValue as string)
Call zPS_UPDATE.SQLExecute(fieldName,newValue)
End Sub
The setfield says there were no database errors, yet the value never appears in the table
prepared statment are useful when you want to do some query / insert “in bulk”
some db will create a cached query plan and then reuse the heck out of it which can save a lot of time if the only change is the values being inserted or queried for
so you might do
ps_insert = DB.Prepare("Insert Into table1 (column1,column2) VALUES(?,?)
for i as integer = 0 to 1000000
ps_insert.sqlexecute(i,i)
next
and skip a LOT of prepares.
the same is true for when you have a select to do and are doing the same select (same columns again the same tables) but with different values for the query
ps_select = DB.Prepare("select column1,column2 from table1 where column1 = ?")
for i as integer = 0 to 1000000
dim rs as recordset = ps_select.sqlselect(i)
// now do something with rs
// then lather rinse & repeat for some other value of i
next
with a select the likelihood you can skip a LOT of time for generating a query plan goes up a lot as your query gets more complex
// make sure a "Project" record exists
Dim rs As RecordSet
Call DB_PROJECT.DB_select("SELECT count(*) FROM "+tablePROJECT,rs)
If rs.RecordCount=0 Then
Call DB_PROJECT.DB_execute("INSERT INTO "+tablePROJECT+" DEFAULT VALUES")
End If
should have been this
Call DB_PROJECT.DB_select("SELECT * FROM "+tablePROJECT,rs)