DSQL Programming Methods

There are four possible DSQL programming methods for handling a SQL statement string. The best method for processing a string depends on the type of SQL statement in the string, and whether or not it contains placeholders for parameters. The following decision table explains how to determine the appropriate processing method for a given string:

Table 6.4 SQL statement strings and recommended processing methods

Is it a query?	Does it have placeholders?	Processing method to use:
No	No	Method 1: Non-query Statements Without Parameters
No	Yes	Method 2: Non-query Statements With Parameters
Yes	No	Method 3: Query Statements Without Parameters
Yes	Yes	Method 4: Query Statements With Parameters

Method 1: Non-query Statements Without Parameters

There are two ways to process a SQL statement string containing a non-query statement without placeholder parameters:

• Use isc_dsql_execute_immediate() to prepare and execute the string a single time.
• Use isc_dsql_allocate_statement() to allocate a statement string for the statement to execute, isc_dsql_prepare() to parse the statement for execution and assign it a name, then use isc_dsql_execute() to carry out the statement’s actions as many times as required in an application.

Using isc_dsql_execute_immediate()

1. To execute a statement string a single time, use isc_dsql_execute_immediate():
2. Elicit a statement string from the user or create one that contains the SQL statement to be processed. For example, the following statement creates a SQL statement string:

char *str = "UPDATE DEPARTMENT SET BUDGET = BUDGET * 1.05";

3. Parse and execute the statement string using isc_dsql_execute_immediate():

isc_dsql_execute_immediate(status_vector, &db1, &trans, 0, str, 1, NULL);

Note: isc_dsql_execute_immediate() also accepts string literals. For example, :isc_dsql_execute_immediate(status_vector, &db1, &trans, 0,

"UPDATE DEPARTMENT SET BUDGET = BUDGET * 1.05", 1, NULL);

For the complete syntax of isc_dsq_execute_immediate() and an explanation of its parameters, see API Function Reference.

Using isc_dsql_prepare() and isc_dsql_execute()

To execute a statement string several times, use isc_dsql_allocate_statement(),isc_dsql_prepare(), and isc_dsql_execute():

1. Elicit a statement string from the user or create one that contains the SQL statement to be processed. For example, the following statement creates a SQL statement string:

char *str = "UPDATE DEPARTMENT SET BUDGET = BUDGET * 1.05";

2. Declare and initialize a SQL statement handle, then allocate it with isc_dsql_allocate_statement():

   isc_stmt_handle stmt; /* Declare a statement handle. */

   stmt = NULL; /* Set handle to NULL before allocation. */

   . . .

   isc_dsql_allocate_statement(status_vector, &db1, &stmt);

3. Parse the statement string with isc_dsql_prepare(). This sets the statement handle (stmt) to refer to the parsed format. The statement handle is used in subsequent calls to isc_dsql_execute():

   isc_dsql_prepare(status_vector, &trans, &stmt, 0, str, 1, NULL);

   isc_dsql_prepare() also accepts string literals. For example,

   isc_dsql_prepare(status_vector, &trans, &stmt, 0,

   "UPDATE DEPARTMENT SET BUDGET = BUDGET * 1.05", 1, NULL);

4. Execute the named statement string using isc_dsql_execute(). For example, the following statement executes a statement string named stmt:

   isc_dsql_execute(status_vector, &trans, &stmt, 1, NULL);

   Once a statement string is prepared, it can be executed as many times as required in an application.

Advance to Next Topic

• Method 2: Non-query Statements With Parameters