Interface

PreparedSQLStatement

Description

Provides the API for working with prepared statements. Use Database.Prepare to create a PreparedSQLStatement.

Methods

Name	Parameters	Returns
Bind	index As Integer, value As Variant
	index As Integer, value As Variant, type As Integer
	values() As Variant
BindType	index As Integer, type As Integer
	types() As Integer
ExecuteSQL	ParamArray bindItems As Variant
SelectSQL	ParamArray bindItems As Variant	RowSet

Method descriptions

PreparedSQLStatement.Bind

Bind(index As Integer, value As Variant)

Binds a value for the prepared statement.

Use Database.Prepare to set up the bind.

This example creates a SQLite prepared statement to retrieve data from a Customers table. It then displays the data in a Listbox:

Var stmt As SQLitePreparedStatement

' note in a prepared statement you DO NOT put in the quotes
stmt = SQLitePreparedStatement(db.Prepare("SELECT * FROM Customers WHERE FirstName like ? "))

' have to tell sqlite what types the items being bound are so it does the right thing
stmt.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
stmt.Bind(0, TextField1.Text)

' perform the search
Var rs As RowSet = stmt.SelectSQL

ListBox1.RemoveAllRows
ListBox1.ColumnCount = rs.ColumnCount
ListBox1.HasHeader = True

Var hasHeadings As Boolean

While Not rs.AfterLastRow
  ListBox1.AddRow("")

  For i As Integer = 0 To rs.ColumnCount-1
    If Not hasHeadings Then ListBox1.HeaderAt(i) = rs.ColumnAt(i+1).Name
    ListBox1.CellTextAt(ListBox1.LastAddedRowIndex, i) = rs.ColumnAt(i+1).StringValue
  Next

  rs.MoveToNextRow
  hasHeadings = True
Wend

Bind(index As Integer, value As Variant, type As Integer)

Binds a value and its type for the prepared statement.

Bind(values() As Variant)

Binds multiple values for the prepared statement.

PreparedSQLStatement.BindType

BindType(index As Integer, type As Integer)

Use this to specify an exact type for the bound value. Each Database plug-in will have its own values.

PreparedSQLStatement.BindType

BindType(types() As Integer)

Specify types for multiple bind values. Each Database plug-in will have its own values.

PreparedSQLStatement.ExecuteSQL

ExecuteSQL(ParamArray bindItems As Variant)

Same as SelectSQL but does not return a result set. Executes and returns the result set of the prepared statement.

BindItems is optional and is intended for convenience only. If bindItems is not empty, ExecuteSQL will use the passed in values instead of the ones specified by calling Bind.

PreparedSQLStatement.SelectSQL

SelectSQL(ParamArray bindItems As Variant) As RowSet

Executes and returns the result set of the prepared statement.

The bindItems parameter is optional and is intended for convenience only. If bindItems is not empty SelectSQL will use the passed in values instead of the ones specified by calling Bind.

Notes

Database.SelectSQL and Database.ExecuteSQL both support prepared statements without the need to use any of the prepared statement classes. These cover most cases.

The reasons for using the prepared statement classes are:

When using an uncommon column type that is likely unique to a particular database server. In that case, Database.SelectSQL and Database.ExecuteSQL may not function properly and your only choice would be to use the appropriate prepared statement class.
When repeatedly calling the same SQL statement in a loop. Using prepared statements in this case will increase performance as the prepared statement won't need to be recreated each time as is the case with Database.SelectSQL and Database.ExecuteSQL.

You do not typically need to work directly with this interface. Instead use the appropriate PreparedStatement class for the database you are using:

Compatibility

All project types on all supported operating systems.