Class

MSSQLServerPreparedStatement


Warning

This item was deprecated in version 2023r2. Please use ODBCPreparedStatement as a replacement.

Description

Used to create a PreparedSQLStatement for a Microsoft SQL Server Database.

Methods

Name

Parameters

Returns

Shared

Bind

index As Integer, value As Variant

BindType

index As Integer, type As Integer

ExecuteSQL

ParamArray bindValues() As Variant

SelectSQL

ParamArray bindValues() As Variant

RowSet

Method descriptions


MSSQLServerPreparedStatement.Bind

Bind(index As Integer, value As Variant)

Binds a value and its type 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.SQLSelect

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

Var hasHeadings As Boolean

While rs.AfterLastRow <> True
  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

MSSQLServerPreparedStatement.BindType

BindType(index As Integer, type As Integer)

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


MSSQLServerPreparedStatement.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.


MSSQLServerPreparedStatement.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

MSSQLServerPreparedStatement 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 types constants: `

Constants

MSSQLSERVER_TYPE_BIGINT

MSSQLSERVER_TYPE_BINARY

MSSQLSERVER_TYPE_DATE

MSSQLSERVER_TYPE_DOUBLE

MSSQLSERVER_TYPE_FLOAT

MSSQLSERVER_TYPE_INT

MSSQLSERVER_TYPE_MONEY

MSSQLSERVER_TYPE_NULL

MSSQLSERVER_TYPE_SMALLINT

MSSQLSERVER_TYPE_STRING

MSSQLSERVER_TYPE_TIME

MSSQLSERVER_TYPE_TIMESTAMP

MSSQLSERVER_TYPE_TINYINT

Sample code

This code shows how to create a prepared statement:

' db is an previously connected MSSQLServerDatabase object
Var ps As MSSQLServerPreparedStatement
ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")

ps.BindType(0, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_STRING)
ps.BindType(1, MSSQLServerPreparedStatement.MSSQLSERVER_TYPE_INT)
ps.Bind(0, "John")
ps.Bind(1, 20)

Try
  Var rs As RowSet = ps.SQLSelect
Catch error As DatabaseException
  MessageBox(error.Message)
End Try

Alternatively, you can avoid binding the types manually with the Bind method by supplying the values as part of the SQLSelect call. In this case the binding types are inferred from the types of the parameters:

' db is a previously connected MSSQLServerDatabase object

Var ps As MSSQLServerPreparedStatement
ps = db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
Try
  rs = ps.SQLSelect("John", 20)
Catch error As DatabaseException
  MessageBox(error.Message)
End Try

Compatibility

All project types on all supported operating systems.

See also

Object parent class; Database, MSSQLServerDatabase, PreparedSQLStatement classes.