User's Guide
   PART 1. Working with Databases
     CHAPTER 9. Using SQL in Applications       

Preparing statements

Each time a statement is sent to a database, the server has to carry out several tasks:

• Parse the statement and transforms it into an internal form

• Verify the correctness of all references to database objects, checking that columns named in a query exist, for example.

• If the statement involves joins or subqueries, the query optimizer generates an access plan.

• After all these steps have been carried out, the server can execute the statement.

The steps prior to actually executing a statement are called preparing the statement.

Prepared statements can improve performance 

If you are using the same statement repeatedly, such as to insert many rows into a table, there is a significant and unnecessary overhead associated with repeatedly preparing the statement.

Some database programming interfaces provide ways of using prepared statements. Generally, using these methods requires the following steps:

1. Prepare the statement     In this step you generally provide the statement with some placeholder character instead of the values.

2. Repeatedly execute the prepared statement     In this step you supply values to be used each time the statement is executed. The statement does not have to be prepared each time.

3. Drop the statement     In this step you free the resources associated with the prepared statement. Some programming interfaces handle this step automatically.

Do not prepare statements that are used only once 

In general, you should not prepare statements if they are to be executed only once. There is a slight performance penalty for separate preparation and execution, and it introduces an unnecessary complexity into your application.

In some interfaces, however, you do need to prepare a statement in order to associate it with a cursor. For information about cursors, see Introduction to cursors.

The calls for preparing and executing statements are not a part of SQL, and they differ from interface to interface. Each of the Adaptive Server Anywhere programming interfaces provides a method for using prepared statements.

  How to use prepared statements

This section provides a brief overview of how to use prepared statements.

  To use a prepared statement:

1. Prepare the statement.

2. Set up bound parameters, which will be used to 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. This step is not required in JDBC, as Java's garbage collection mechanisms handle the problem for you.

The general procedure is the same, but the details vary from interface to interface. Comparing how you use prepared statements in different interfaces illustrates this point.

Using prepared statements in ODBC 

  To use a prepared statement in ODBC:

1. Prepare the statement using SQLPrepare.

2. Bind statement parameters using SQLBindParameter.

3. Execute the statement using SQLExecute.

4. Drop the statement using SQLFreeStmt.

     For more information, see Using prepared statements in ODBC and the ODBC SDK documentation.

To use a prepared statement with JDBC 

You can use prepared statements with JDBC both from a client application and inside the server.

  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 statement parameters using the appropriate setType methods of the prepared statement object. Here, Type is the data type being 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 more information on using prepared statements in JDBC, see Using prepared statements for more efficient access.

To use a prepared statement with Sybase Open Client 

  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.

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