Executing Prepared SQL Statements

Executing Prepared SQL Statements

Prepared SQL versus Dynamic SQL Statements

SQL statements executed in iSQL are usually executed according to the so-called "Direct Execution" method.

In Direct Execution, syntax analysis, validity testing, optimization and execution of a query are all performed at once. However, in Prepared Execution, only the syntax analysis, validity testing, and optimization of the query are performed to set up an execution plan for the query, which is then executed when requested by the client. When creating an application that uses ODBC, the Prepared Execution method is typically used, and is more advantageous in terms of speed when a SQL statement is to be repeatedly executed using host variable binding.

In iSQL, the difference between these two methods lies only in whether variables are used or not; there is no advantage in terms of speed. However, when it is executed in Prepared Execution, the printed graph and the execution plan may contain different information. The graph shows the plan up until the optimization phase, whereas execution plan shows the plan once the actual value is applied to the variable.

Prepared SQL Statements

Syntax

PREPARE SQL_statement;

Example

The following is an example of the use of the PREPARE command to execute a SQL statement:

iSQL> VAR t1 INTEGER;
iSQL> EXEC :t1 := 1;
Execute success.
iSQL> PREPARE SELECT eno, e_firstname, e_lastname, sex
FROM employees WHERE eno=:t1;
ENO         E_FIRSTNAME           E_LASTNAME            SEX
------------------------------------------------------------------
1           Chan-seung            Moon                  M
1 row selected.