Public Prepared Statement

perhaps not the right title.

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]

you could

just dont close the db and then try to use them

THAT I kind of figured :slight_smile:

Thanks

:frowning: This doesn’t seem to work

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

OH … yeah you can NOT use the column name as a bind parameter

its for query values only

well CR*P!
that negates this whole idea then… the intent was to make a fake computed property pointing to the database… so why wasn’t an error raised?

well I can still do it, but need to prepare a statement inside the GETFIELD and SETFIELD instead :frowning:

well you never checked for one in GetField
I suspect there is one right there

[quote=461704:@Norman Palardy]well you never checked for one in GetField
I suspect there is one right there[/quote]

nope… just returned a blank value…

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

check for db.error or db.errormessage

and you may have gotten one right when you called the initial prepare since that is an invalid use

THIS was the root of all the problems

// 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)

count(*) will always return one record

Reused prepared statements will crash at some point. At least that was my experience. YMMV

Hello Dave.

Some databases such as postgres handle the Upsert concept, it can be useful

Regards
Mauricio

Not using PostGres, but thanks…
anyways because you can’t pass the field/column name, I have to reassign the PS each time