How to use prepared statements

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.

 Use a prepared statement (generic)

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

  3. Prepare the statement using the Prepare method.

    cmd.Prepare();

  4. Execute the statement.

    SADataReader reader = cmd.ExecuteReader();

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

 Use a prepared statement (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 %SQLANYSAMP12%\SQLAnywhere\ODBCPrepare.

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

 Use a prepared statement (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 %SQLANYSAMP12%\SQLAnywhere\JDBC\JDBCExample.java.

    For more information about using prepared statements in JDBC, see Using prepared statements for more efficient access.

 Use a prepared statement (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.

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

 Use a prepared statement (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.

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

Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1