ASA Programming Guide
Using SQL in Applications
Each time a statement is sent to a database, the server must first prepare the statement. Preparing the statement can include:
Parsing the statement and transforming it into an internal form.
Verifying the correctness of all references to database objects by checking, for example, that columns named in a query actually exist.
Causing the query optimizer to generate an access plan if the statement involves joins or subqueries.
Executing the statement after all these steps have been carried out.
If you find yourself using the same statement repeatedly, for example, inserting many rows into a table, repeatedly preparing the statement causes a significant and unnecessary overhead. To remove this overhead, some database programming interfaces provide ways of using prepared statements. A prepared statement is a statement containing a series of placeholders. When you want to execute the statement, all you have to do is assign values to the placeholders, rather than prepare the entire statement over again.
Using prepared statements is particularly useful when carrying out many similar actions, such as inserting many rows.
Generally, using prepared statements requires the following steps:
Prepare the statement In this step you generally provide the statement with some placeholder character instead of the values.
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.
Drop the statement In this step you free the resources associated with the prepared statement. Some programming interfaces handle this step automatically.
In general, you should not prepare statements if you'll only execute them once. There is a slight performance penalty for separate preparation and execution, and it introduces unnecessary complexity into your application.
In some interfaces, however, you do need to prepare a statement 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