MSSQLServerPreparedStatement

From Xojo Documentation

Class (inherits from PreparedSQLStatement)

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

Methods

Notes

fa-info-circle-32.png
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 marker 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_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

See Also

Database Class, MSSQLServerDatabase, PreparedSQLStatement classes.