Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Programming » Application development using SQL » Prepared statements

Prepared statements overview

The general procedure for using prepared statements is consistent, 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:

  1. Prepare the statement.

  2. Bind the parameters that will hold values in the statement.

  3. Assign values to the bound parameters in the statement.

  4. Execute the statement.

  5. Repeat steps 3 and 4 as needed.

  6. 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:

  1. Create an SACommand object holding the statement:

    SACommand cmd = new SACommand(
         "SELECT * FROM Employees WHERE Surname = ?", conn );
  2. 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);
  3. Prepare the statement using the Prepare method.

  4. Execute the statement:

    SADataReader reader = cmd.ExecuteReader();

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

Use a prepared statement in ODBC

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

  1. Prepare the statement using SQLPrepare.

  2. Bind the statement parameters using SQLBindParameter.

  3. Execute the statement using SQLExecute.

  4. Drop the statement using SQLFreeStmt.

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

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

Use a prepared statement in JDBC

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

  1. Prepare the statement using the prepareStatement method of the connection object. This returns a prepared statement object.

  2. Set the statement parameters using the appropriate setType methods of the prepared statement object. Here, Type is the data type assigned.

  3. 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 %SQLANYSAMP17%\SQLAnywhere\JDBC\JDBCExample.java.

Use a prepared statement in Embedded SQL

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

  1. Prepare the statement using the EXEC SQL PREPARE statement.

  2. Assign values to the parameters in the statement.

  3. Execute the statement using the EXEC SQL EXECUTE statement.

  4. Free the resources associated with the statement using the EXEC SQL DROP statement.

Use a prepared statement in Open Client

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

  1. Prepare the statement using the ct_dynamic function, with a CS_PREPARE type parameter.

  2. Set statement parameters using ct_param.

  3. Execute the statement using ct_dynamic with a CS_EXECUTE type parameter.

  4. Free the resources associated with the statement using ct_dynamic with a CS_DEALLOC type parameter.