Prepared statements and input parameters

A prepared SQL statement is the parsed version of an SQL statement. The database server prepares an SQL statement for execution at a later time. Preparing a statement enables you to separate the parsing and execution phases of the statement execution. When you prepare a statement, you send the statement to the database server to be parsed. The database server checks the statement for syntax errors and creates an optimized version of the statement for execution.

You need to prepare an SQL statement only once. You can then execute the statement multiple times. Each time you execute the statement, you avoid the parsing phase. Prepared statements are useful for SQL statements that execute often in your DataBlade® API module.

SQL statements that have missing column or expression values are called parameterized statements because you use input parameters as placeholders for missing column or expression values. An input parameter is a placeholder in an SQL statement that indicates that the actual column value is provided at run time. You can specify input parameters in the statement text representation of an SQL statement for either of the following reasons:

A column or expression value is unknown at the time you prepare the SQL statement.
A column or expression value changes for each execution of the SQL statement.

For a parameterized SQL statement, your DataBlade API module must provide the following information to the database server for each of its input parameters.

Specify the input parameter in the text of the SQL statement.
Specify the value for the input parameter when the statement executes.

You can also obtain information about the input parameters after the parameterized statement is prepared.

A DataBlade API module can prepare an SQL statement for the following reasons:

To increase performance by reducing the number of times that the database server parses and optimizes the statement
To execute a parameterized SQL statement and provide different input-parameter values each time the statement executes

The following table shows how to choose a DataBlade API statement-execution function based on whether the SQL statement needs to be prepared.


Statement needs to be prepared?	DataBlade API function
No	mi_exec()
Yes	mi_exec_prepared_statement(), mi_open_prepared_statement()

The mi_exec_prepared_statement() or mi_open_prepared_statement() function provides argument values for specifying the input-parameter values when the function executes the statement. You can also use these functions to execute prepared statements that do not have input parameters.