Prepared statements overview

This section provides a brief overview of how to use prepared statements. The general procedure is the same, but the details vary from interface to interface. Comparing how to use prepared statements in different interfaces illustrates this point.

You typically perform the following tasks to use a prepared statement:

Prepare the statement.
Bind the parameters that will hold values in the statement.
Assign values to the bound parameters in the statement.
Execute the statement.
Repeat steps 3 and 4 as needed.
Drop the statement when finished. In JDBC the Java garbage collection mechanism drops the statement.

Use a prepared statement in ADO.NET

You typically perform the following tasks to use a prepared statement in ADO.NET:

Create an SACommand object holding the statement:

SACommand cmd = new SACommand(
     "SELECT * FROM Employees WHERE Surname = ?", conn );

Declare data types for any parameters in the statement.

Use the SACommand.CreateParameter method.

SAParameter param = cmd.CreateParameter();
param.SADbType = SADbType.Char;
param.Direction = ParameterDirection.Input;
param.Value = "Smith";
cmd.Parameters.Add(param);

Prepare the statement using the Prepare method.

Execute the statement:

SADataReader reader = cmd.ExecuteReader();

For an example of preparing statements using ADO.NET, see the source code in %SQLANYSAMP16%\SQLAnywhere\ADO.NET\SimpleWin32.

Use a prepared statement in ODBC

You typically perform the following tasks to use a prepared statement in ODBC:

Prepare the statement using SQLPrepare.
Bind the statement parameters using SQLBindParameter.
Execute the statement using SQLExecute.
Drop the statement using SQLFreeStmt.

For an example of preparing statements using ODBC, see the source code in %SQLANYSAMP16%\SQLAnywhere\ODBCPrepare.

For more information about ODBC prepared statements, see the ODBC SDK documentation, and Executing prepared statements.

Use a prepared statement in JDBC

You typically perform the following tasks to use a prepared statement in JDBC:

Prepare the statement using the prepareStatement method of the connection object. This returns a prepared statement object.
Set the statement parameters using the appropriate setType methods of the prepared statement object. Here, Type is the data type assigned.
Execute the statement using the appropriate method of the prepared statement object. For inserts, updates, and deletes this is the executeUpdate method.

For an example of preparing statements using JDBC, see the source code file %SQLANYSAMP16%\SQLAnywhere\JDBC\JDBCExample.java.

For more information about using prepared statements in JDBC, see How to use prepared statements for more efficient access.

Use a prepared statement in embedded SQL

You typically perform the following tasks to use a prepared statement in embedded SQL:

Prepare the statement using the EXEC SQL PREPARE statement.
Assign values to the parameters in the statement.
Execute the statement using the EXEC SQL EXECUTE statement.
Free the resources associated with the statement using the EXEC SQL DROP statement.

For more information about embedded SQL prepared statements, see PREPARE statement [ESQL].

Use a prepared statement in Open Client

You typically perform the following tasks to use a prepared statement in Open Client:

Prepare the statement using the ct_dynamic function, with a CS_PREPARE type parameter.
Set statement parameters using ct_param.
Execute the statement using ct_dynamic with a CS_EXECUTE type parameter.
Free the resources associated with the statement using ct_dynamic with a CS_DEALLOC type parameter.

For more information about using prepared statements in Open Client, see SQL in Open Client applications.

Discuss this page in DocCommentXchange.