Class

ODBCPreparedStatement

Database

Description

Used to create a PreparedSQLStatement for an ODBCDatabase.

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
ExecuteSQL	ParamArray bindValues() As Variant
SelectSQL	ParamArray bindValues() As Variant	RowSet

Method descriptions

ODBCPreparedStatement.Bind

Bind(index As Integer, value As Variant)

Binds a value at the parameter index 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 ODBCPreparedStatement

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

' have to tell ODBC what types the items being bound are so it does the right thing
stmt.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
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 at the parameter index for the prepared statement.

Bind(values() As Variant)

Binds multiple values for the prepared statement.

ODBCPreparedStatement.BindType

BindType(index As Integer, type As Integer)

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

ODBCPreparedStatement.ExecuteSQL

ExecuteSQL(ParamArray bindValues() As Variant)

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

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

ODBCPreparedStatement.SelectSQL

SelectSQL(ParamArray bindValues() As Variant) As RowSet

Executes and returns the result set of the prepared statement.

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

Interfaces

This class implements the PreparedSQLStatement interface.

Notes

Note

The use of the prepared statement classes is rare because Database.SelectSQL and Database.ExecuteSQL utilize them automatically. See PreparedSQLStatement for information on cases where using prepared statement classes is appropriate.

Use the "?" character as the parameter indicator in the prepared statement ("SELECT * FROM Persons WHERE Name = ?").

These are the available bind type constants:

Constants
ODBC_TYPE_BIGINT
ODBC_TYPE_BINARY
ODBC_TYPE_DATE
ODBC_TYPE_DOUBLE
ODBC_TYPE_FLOAT
ODBC_TYPE_INTEGER
ODBC_TYPE_NULL
ODBC_TYPE_SMALLINT
ODBC_TYPE_STRING
ODBC_TYPE_TIME
ODBC_TYPE_TIMESTAMP
ODBC_TYPE_TINYINT

Sample code

This code shows how to use database binding.

' "db" is an ODBC Database object
Var ps As ODBCPreparedStatement
ps = ODBCPreparedStatement(db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?"))

ps.BindType(0, ODBCPreparedStatement.ODBC_TYPE_STRING)
ps.BindType(1, ODBCPreparedStatement.ODBC_TYPE_INTEGER)

ps.Bind(0, "John")
ps.Bind(1, 20)

Var rs As RowSet = ps.SelectSQL

Try
  ' Use RowSet as usual
Catch error As DatabaseException
  MessageBox(error.Message)
  Return
End Ty

Alternatively, you can avoid binding the types manually with the Bind method by supply the values as part of the SelectSQL call:

' "db" is an ODBC Database object
Var ps As ODBCPreparedStatement
ps = ODBCPreparedStatement(db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?"))

Var rs As RowSet = ps.SelectSQL("John", 20)

Try
  ' Use RowSet as usual
Catch error As DatabaseException
  MessageBox(error.Message)
  Return
End Try

Compatibility

Project Types	Console, Desktop, Web
Operating Systems	All