Dynamic SQL Statements

Overview
Preparing and executing statements
Using input parameters in dynamic SQL
Using cursors with prepared statements
Inserting multiple rows
Freeing prepared statements
Optimizing prepared statements

Overview

Dynamic SQL statements allow you to construct an SQL statement at runtime, based on some program conditions or the user's interaction.

Your program assembles the SQL statement in a character string, and assigns it to a character string host variable.

The SQL statement string cannot contain the names of any host variables. Input parameters (indicated by question mark placeholders in the statement string) can be used in the statement string, for example in a WHERE clause, to indicate that the value needed will be provided at a given position in the SQL statement.

Your program uses the PREPARE statement to assign a statement id and send the SQL statement string contained in the host variable to the database server for parsing. You can check the global variables SQLSTATE or SQLCODE to find out whether errors occurred in the parsing.

Your program executes the prepared statement one or more times.

If input parameters are used, the USING clause of the EXECUTE or OPEN instruction provides the values.
If the SQL statement produces a result set (like SELECT), or inserts multiple rows with an INSERT CURSOR, you must declare a cursor with the prepared statement handle in order to execute it.

When the prepared statement is no longer needed, your program frees the statement to release the allocated resources.

You can use Dynamic SQL for any SQL statement except SQL management instructions (such as PREPARE, DECLARE, OPEN, EXECUTE, FETCH, CLOSE, FREE), SQL connection instructions (such as CONNECT, DATABASE, DISCONNECT) and transaction control instructions (BEGIN WORK, COMMIT WORK, ROLLBACK WORK, SET ISOLATION, SET LOCK MODE).

Preparing and Executing SQL statements

Preparing statements

In order to prepare an SQL statement, you must do the following:

Declare a char host variable to hold the entire SQL string:

EXEC SQL BEGIN DECLARE SECTION;
  char sqlstring[128];    /* SQL statement */
EXEC SQL END DECLARE SECTION;

Assign the SQL string to the host variable:

strcpy(sqlstring, "DELETE FROM orders WHERE store_num IS NULL");

Use the PREPARE statement to create a valid SQL statement from the string contained in the host variable:

EXEC SQL PREPARE stid FROM :sqlstring;

Once an SQL statement is prepared, use EXECUTE to execute the statement; the statement can be executed often as needed.

Executing prepared statements that do not return values (INSERT, UPDATE, DELETE)

Follow the steps described at the beginning of this section to prepare the statement. Then, use the EXECUTE instruction to perform the execution of the statement:

strcpy(sqlstring, "DELETE FROM orders WHERE store_num IS NULL");
EXEC SQL PREPARE stid FROM :sqlstring;
EXEC SQL EXECUTE stdid;

Executing prepared statement that return values (SELECT)

If the prepared SQL statement returns a single row, you can use the INTO clause with EXECUTE to specify the host variables to receive the database column values:

sprintf(sqlstring, "SELECT COUNT(*) FROM orders");
EXEC SQL PREPARE stid FROM :sqlstring;
EXEC SQL EXECUTE stid INTO :p_order_count;

You can use indicator variables with the EXECUTE statement, to determine if a value returned from the database is null:

EXEC SQL EXECUTE stid INTO :p_custname INDICATOR :i_custname;

If the prepared statement returns more than one row, you must declare a database cursor and use cursor management statements instead of the EXECUTE statement. See Using cursors with prepared statements.

Executing dynamic SQL statements without parameters

If there are no input parameters, and the statement will not be executed multiple times, you can reduce the number of statements to be executed by using the EXECUTE IMMEDIATE statement to prepare and execute the statement string at once:

sprintf(sqlstring, "DELETE FROM orders WHERE store_num IS NULL");
EXEC SQL EXECUTE IMMEDIATE :sqlstring;

Example program:

01 #include <stdio.h>
02
03 int main()
04
05 {
06  EXEC SQL BEGIN DECLARE SECTION;
07    char sqlstring[128];   /* string to hold sql statement */ 
08    int p_store_num;       /* values for store_num column */
09    char p_store_name[21]; /* values for store_name, column length 20 */
10    int2 i_store_name;      /* indicator variable for p_store_name */
11  EXEC SQL END DECLARE SECTION;
12
13  char whereinput[64];   /* variable for string containing where clause */
14  strcpy(whereinput, "store_num > 200"); /* setting value of whereinput */
15  
16  EXEC SQL CONNECT TO 'custdemo';
17
18  /* Using EXECUTE IMMEDIATE  */
19  sprintf(sqlstring, "DELETE FROM orders WHERE %s", whereinput);
20  EXEC SQL EXECUTE IMMEDIATE :sqlstring;
21
22  /* Returning a single row - USING PREPARE and EXECUTE */
23  strcpy(whereinput, "store_num = 101");  /* setting value of whereinput */
24  sprintf(sqlstring, "SELECT store_name FROM customer WHERE %s", whereinput);
25  EXEC SQL PREPARE stid FROM :sqlstring;
26  EXEC SQL EXECUTE stid INTO :p_store_name INDICATOR :i_store_name;
27  /* Displaying store name */
28  printf("%-20s\n", i_store_name == 0 ? p_store_name : "<no name>");
29
30  EXEC SQL DISCONNECT CURRENT;
31
32  return 0;
33 }

Using input parameters in dynamic SQL

Input parameters are placeholders in an SQL statement string for host variables that contain the values for expressions. The actual values are provided at runtime. Placeholders are used since the database server cannot parse a dynamic SQL statement that contains host variable names.

Placeholders can be used anywhere within the SQL statement where an expression is valid. You cannot use an input parameter to represent an identifier such as a database name, a table name, or a column name.

Steps to use input parameters

Declare a host variable for each input parameter. The data type of the host variable must be compatible with the data type of the database column referenced in the SQL statement string.
Assemble the SQL statement string using ? as a placeholder for each input parameter.
PREPARE the statement string to create a valid SQL statement.
Provide the host variables corresponding to the input parameters with the USING clause of the EXECUTE instruction. The order in which the variables are listed must match the order in which the variables appear in the prepared statement.

Example using input parameters with a prepared DELETE statement:

EXEC SQL PREPARE stid FROM "DELETE FROM orders WHERE store_num = ? AND order_num < ?";
EXEC SQL EXECUTE stid USING :p_store_num, :p_order_num;

Example using input parameters with a prepared SELECT statement that returns a single row:

EXEC SQL PREPARE stid FROM "SELECT COUNT(*) FROM orders WHERE order_num < ?";
EXEC SQL EXECUTE stid USING :p_order_num INTO :p_count;

If the SELECT statement returns more than one row, a database cursor must be used.

Example program:

01 #include <stdio.h>
02
03 int main()
04 {
05  EXEC SQL BEGIN DECLARE SECTION;
06    char sqlstring[128]; 
07    char p_state_code[3];       /* column length is 2  */
08    char p_state_name[16];      /* column length is 15 */
09  EXEC SQL END DECLARE SECTION;
10
11  EXEC SQL CONNECT TO 'custdemo';
12
13  /* Using placeholders for values to be inserted  */
14  strcpy(sqlstring, "INSERT INTO state VALUES ( ?, ? )");
15  EXEC SQL PREPARE stid2 FROM :sqlstring;
16  printf(" SQLSTATE = [%s]\n\n", SQLSTATE); /* checking SQL success */
17
18  strcpy(p_state_code, "AZ");
19  strcpy(p_state_name, "Arizona");
20
21  /* Executing the prepared statement */
22  EXEC SQL EXECUTE stid2 USING :p_state_code, :p_state_name;
23  printf(" SQLSTATE = [%s]\n\n", SQLSTATE);
24  
25  EXEC SQL DISCONNECT CURRENT;
26
27  return 0;
28 }

Using a database cursor with prepared statements

If an SQL SELECT statement generates a set of rows, you must handle the result set with a Database Cursor.

First, the cursor is declared for the prepared statement:

EXEC SQL PREPARE stid FROM "SELECT order_num FROM orders WHERE store_num = ?";
EXEC SQL DECLARE c1 CURSOR FOR stid;

If input parameters are used in the WHERE clause, the USING clause of the OPEN cursor instruction provides the host variables containing the values needed:

EXEC SQL OPEN c1 USING :p_store_num;

The FETCH statement uses the INTO clause to retrieve the database values into host variables. This statement can be executed multiple times until there are no more values to be retrieved:

EXEC SQL FETCH c1 INTO :p_order_num;

The order in which the INTO host variables are listed must match the order of the select list.

Close the cursor when the fetch is complete. A closed cursor can be re-opened.

EXEC SQL CLOSE c1;

When the cursor is no longer needed, it is freed, releasing the memory associated with it. Once freed, a cursor must be re-declared before it can be used again.

EXEC SQL FREE c1;

Example program:

01 #include <stdio.h>
02 
03 /* To check for SQL errors */
04 void errlog(void)
05 {
06  fprintf(stderr, "Error occurred:\n");
07  fprintf(stderr, " SQLSTATE = [%s] SQLCODE = %d\n\n",
08                  SQLSTATE, SQLCODE);
09  EXEC SQL DISCONNECT ALL;
10  exit(1);
11 }
12
13 int main()
14 {
15  EXEC SQL BEGIN DECLARE SECTION;
16    int p_store_num;        /* store_num column */
17    int p_order_num;        /* order_num column */
18    int2 i_order_num;       /* indicator for p_order_num */
19  EXEC SQL END DECLARE SECTION;
20 
21  EXEC SQL WHENEVER ERROR CALL errlog; /* Set the error-handling */
22
23  EXEC SQL CONNECT TO 'custdemo';
24 
25  EXEC SQL PREPARE stid FROM "SELECT order_num FROM orders WHERE store_num = ?";
26  EXEC SQL DECLARE c1 cursor for stid;
27  p_store_num = 12;  /* Assign a value to store number */
28  EXEC SQL OPEN c1 USING :p_store_num;
29
30  for (;;)
31  {
32   EXEC SQL FETCH c1 INTO :p_order_num INDICATOR :i_order_num;
33   if (strncmp(SQLSTATE, "02", 2) == 0) break;
34   printf("%6d \n",
35          i_order_num == 0 ? p_order_num : "<no order number>"
36   );
37  }
38
39  EXEC SQL CLOSE c1;
40  EXEC SQL FREE c1;
41
42  EXEC SQL DISCONNECT CURRENT;
43
44  return 0;
45 }

Inserting multiple rows

Use an Insert Cursor to perform buffered row insertion in database tables. The insert cursor simply inserts rows of data; it cannot be used to fetch data.

A cursor is declared for the prepared statement, using input parameters for the values to be inserted:

EXEC SQL PREPARE s1 FROM "INSERT INTO manufact VALUES (?,?)"
EXEC SQL DECLARE c1 CURSOR FOR s1;

The insert cursor is opened:

EXEC SQL OPEN c1;

The PUT cursor instruction uses the USING clause to insert values from the host variables into the database columns. This statement is executed multiple times until there are no more values to be inserted.

EXEC SQL PUT c1 FROM :faccode, :facname;

Flush data rows, if required:

EXEC SQL FLUSH c1;

Close the cursor:

EXEC SQL CLOSE c1;

Free the statement handle and the cursor:

EXEC SQL FREE s1;
EXEC SQL FREE c1;

Freeing prepared statements

When the prepared statement is no longer needed, your program can free the statement to release the resources allocated:

EXEC SQL PREPARE stid FROM "SELECT order_num FROM orders WHERE store_num = ?";
...
EXEC SQL FREE stid;

If a database cursor is used for the prepared statement, both the cursor and the statement can be freed:

EXEC SQL FREE c1;
EXEC SQL FREE stid;

Optimizing Prepared Statements

If an SQL statement is to be executed multiple times, you can reduce the traffic between the client application and the database server if the statement is not also parsed and optimized each time. Use the PREPARE statement to parse the statement outside of the program loop, so the parsing only occurs once:

EXEC SQL PREPARE stid FROM "DELETE FROM orders WHERE store_num = ?"

Then, use the EXECUTE ... USING statement inside the program loop.

EXEC SQL EXECUTE stid USING :p_store_num;