Dynamic SQL applications

With dynamic SQL, DB2® prepares and executes the SQL statements within a program while the program is running. Dynamic SQL is a good choice when you do not know the format of an SQL statement before you write or run a program.

An application that uses dynamic SQL generates an SQL statement in the form of a character string or accepts an SQL statement as input. Depending on the needs of the application, you might be able to simplify the programming. Try to plan the application so that it does not use SELECT statements, or so that it uses only those statements that return a known number of values of known data types. In general, more complex dynamic programs are those in which you do not know in advance about the SQL statements that the application issues.

A typical dynamic SQL application takes the following steps:

1. Translates the input data into an SQL statement.
2. Prepares the SQL statement to execute and acquires a description of the result table (if any).
3. Obtains, for SELECT statements, enough main storage to contain retrieved data.
4. Executes the statement or fetches the rows of data.
5. Processes the returned information.
6. Handles SQL return codes.

This example shows a portion of a C program that dynamically issues SQL statements to DB2. Assume that you are writing a program to keep an inventory of books. The table that you need to update depends on input to your program. This example shows how you can build an SQL statement and then call DB2 to execute it.

/*********************************************************/
/* Determine which table to update, then build SQL       */
/* statement dynamically into 'stmt' variable.           */
/*********************************************************/
  strcpy(stmt,"UPDATE ");

  EXEC SQL SELECT TYPE INTO :book_type FROM BOOK_TYPES WHERE
  TITLE=:bktitle;

  IF (book_type=='FICTION') strcpy(table_name,"FICTION_BOOKS");
  ELSE strcpy(table_name,"NON_FICTION_BOOKS");

  strcat(stmt,table_name);
  strcat(stmt,
  " SET INVENTORY = INVENTORY-1 WHERE TITLE = :bktitle");
/*********************************************************/
/* PREPARE and EXECUTE the statement                     */
/*********************************************************/
EXEC SQL PREPARE OBJSTMT FROM :stmt;
EXEC SQL EXECUTE OBJSTMT;