Building and executing dynamic SQL statements v16

Suggest edits

The following examples show four techniques for building and executing dynamic SQL statements. Each example shows processing a different combination of statement and input types:

The first example shows processing and executing a SQL statement that doesn't contain a SELECT statement and doesn't require input variables. This example corresponds to the techniques used by Oracle Dynamic SQL Method 1.
The second example shows processing and executing a SQL statement that doesn't contain a SELECT statement and contains a known number of input variables. This example corresponds to the techniques used by Oracle Dynamic SQL Method 2.
The third example shows processing and executing a SQL statement that might contain a SELECT statement and includes a known number of input variables. This example corresponds to the techniques used by Oracle Dynamic SQL Method 3.
The fourth example shows processing and executing a SQL statement that might contain a SELECT statement and includes an unknown number of input variables. This example corresponds to the techniques used by Oracle Dynamic SQL Method 4.

Example: Executing a nonquery statement without parameters

This example shows how to use the EXECUTE IMMEDIATE command to execute a SQL statement, where the text of the statement isn't known until you run the application. You can't use EXECUTE IMMEDIATE to execute a statement that returns a result set. You can't use EXECUTE IMMEDIATE to execute a statement that contains parameter placeholders.

The EXECUTE IMMEDIATE statement parses and plans the SQL statement each time it executes, which can have a negative impact on the performance of your application. If you plan to execute the same statement repeatedly, consider using the PREPARE/EXECUTE technique described in Example: Executing a nonquery statement with a specified number of placeholders.

/***********************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

static void handle_error(void);

int main(int argc, char *argv[])
{
  char    *insertStmt;

  EXEC SQL WHENEVER SQLERROR DO handle_error();

  EXEC SQL CONNECT :argv[1];

  insertStmt = "INSERT INTO dept VALUES(50, 'ACCTG', 'SEATTLE')";

  EXEC SQL EXECUTE IMMEDIATE :insertStmt;

  fprintf(stderr, "ok\n");

  EXEC SQL COMMIT RELEASE;

  exit(EXIT_SUCCESS);
}


static void handle_error(void)
{
  fprintf(stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK RELEASE;

  exit(EXIT_FAILURE);
}

/***********************************************************/

The code sample begins by including the prototypes and type definitions for the C stdio, string, and stdlib libraries and providing basic infrastructure for the program:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

static void handle_error(void);
int main(int argc, char *argv[])
{
  char *insertStmt;

The example then sets up an error handler. ECPGPlus calls the handle_error() function whenever a SQL error occurs:

EXEC SQL WHENEVER SQLERROR DO handle_error();

Then, the example connects to the database using the credentials specified on the command line:

EXEC SQL CONNECT :argv[1];

Next, the program uses an EXECUTE IMMEDIATE statement to execute a SQL statement, adding a row to the dept table:

insertStmt = "INSERT INTO dept VALUES(50, 'ACCTG', 'SEATTLE')";

EXEC SQL EXECUTE IMMEDIATE :insertStmt;

If the EXECUTE IMMEDIATE command fails, ECPGPlus invokes the handle_error() function, which terminates the application after displaying an error message to the user. If the EXECUTE IMMEDIATE command succeeds, the application displays a message (ok) to the user, commits the changes, disconnects from the server, and terminates the application:

  fprintf(stderr, "ok\n");

  EXEC SQL COMMIT RELEASE;

  exit(EXIT_SUCCESS);
}

ECPGPlus calls the handle_error() function whenever it encounters a SQL error. The handle_error() function prints the content of the error message, resets the error handler, rolls back any changes, disconnects from the database, and terminates the application:

static void handle_error(void)
{
  fprintf(stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK RELEASE;

  exit(EXIT_FAILURE);
}

Example: Executing a nonquery statement with a specified number of placeholders

To execute a nonquery command that includes a known number of parameter placeholders, you must first PREPARE the statement (providing a statement handle) and then EXECUTE the statement using the statement handle. When the application executes the statement, it must provide a value for each placeholder found in the statement.

When an application uses the PREPARE/EXECUTE mechanism, each SQL statement is parsed and planned once but might execute many times, providing different values each time.

ECPGPlus converts each parameter value to the type required by the SQL statement, if possible. Otherwise, ECPGPlus reports an error.

/***********************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

static void handle_error(void);

int main(int argc, char *argv[])
{
  char *stmtText;

  EXEC SQL WHENEVER SQLERROR DO handle_error();

  EXEC SQL CONNECT :argv[1];

  stmtText = "INSERT INTO dept VALUES(?, ?, ?)";

  EXEC SQL PREPARE stmtHandle FROM :stmtText;

  EXEC SQL EXECUTE stmtHandle USING :argv[2], :argv[3], :argv[4];

  fprintf(stderr, "ok\n");

  EXEC SQL COMMIT RELEASE;

  exit(EXIT_SUCCESS);
}

static void handle_error(void)
{
  printf("%s\n", sqlca.sqlerrm.sqlerrmc);
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK RELEASE;

  exit(EXIT_FAILURE);
}
/***********************************************************/

The code sample begins by including the prototypes and type definitions for the C stdio, string, stdlib, and sqlca libraries and providing basic infrastructure for the program:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

static void handle_error(void);

int main(int argc, char *argv[])
{
  char *stmtText;

The example then sets up an error handler. ECPGPlus calls the handle_error() function whenever a SQL error occurs.

EXEC SQL WHENEVER SQLERROR DO handle_error();

Then, the example connects to the database using the credentials specified on the command line:

EXEC SQL CONNECT :argv[1];

Next, the program uses a PREPARE statement to parse and plan a statement that includes three parameter markers. If the PREPARE statement succeeds, it creates a statement handle that you can use to execute the statement. (In this example, the statement handle is named stmtHandle.) You can execute a given statement multiple times using the same statement handle.

stmtText = "INSERT INTO dept VALUES(?, ?, ?)";

EXEC SQL PREPARE stmtHandle FROM :stmtText;

After parsing and planning the statement, the application uses the EXECUTE statement to execute the statement associated with the statement handle, substituting user-provided values for the parameter markers:

EXEC SQL EXECUTE stmtHandle USING :argv[2], :argv[3], :argv[4];

If the EXECUTE command fails, ECPGPlus invokes the handle_error() function, which terminates the application after displaying an error message to the user. If the EXECUTE command succeeds, the application displays a message (ok) to the user, commits the changes, disconnects from the server, and terminates the application:

  fprintf(stderr, "ok\n");

  EXEC SQL COMMIT RELEASE;

  exit(EXIT_SUCCESS);
}

static void handle_error(void)
{
  printf("%s\n", sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK RELEASE;
  exit(EXIT_FAILURE);
}

Example: Executing a query with a known number of placeholders

This example shows how to execute a query with a known number of input parameters and with a known number of columns in the result set. This method uses the PREPARE statement to parse and plan a query and then opens a cursor and iterates through the result set.

/***********************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdbool.h>
#include <sqlca.h>

static void handle_error(void);

int main(int argc, char *argv[])
{
  VARCHAR  empno[10];
  VARCHAR  ename[20];

  EXEC SQL WHENEVER SQLERROR DO handle_error();

  EXEC SQL CONNECT :argv[1];

  EXEC SQL PREPARE queryHandle
    FROM "SELECT empno, ename FROM emp WHERE deptno = ?";

  EXEC SQL DECLARE empCursor CURSOR FOR queryHandle;

  EXEC SQL OPEN empCursor USING :argv[2];

  EXEC SQL WHENEVER NOT FOUND DO break;

  while(true)
  {

    EXEC SQL FETCH empCursor INTO :empno, :ename;

    printf("%-10s %s\n", empno.arr, ename.arr);
  }

  EXEC SQL CLOSE empCursor;

  EXEC SQL COMMIT RELEASE;

  exit(EXIT_SUCCESS);
}

static void handle_error(void)
{
  printf("%s\n", sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK RELEASE;

  exit(EXIT_FAILURE);
}

/***********************************************************/

The code sample begins by including the prototypes and type definitions for the C stdio, string, stdlib, stdbool, and sqlca libraries and providing basic infrastructure for the program:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stdbool.h>
#include <sqlca.h>

static void handle_error(void);

int main(int argc, char *argv[])
{
  VARCHAR empno[10];
  VARCHAR ename[20];

The example then sets up an error handler. ECPGPlus calls the handle_error() function whenever a SQL error occurs:

EXEC SQL WHENEVER SQLERROR DO handle_error();

Then, the example connects to the database using the credentials specified on the command line:

EXEC SQL CONNECT :argv[1];

Next, the program uses a PREPARE statement to parse and plan a query that includes a single parameter marker. If the PREPARE statement succeeds, it creates a statement handle that you can use to execute the statement. (In this example, the statement handle is named stmtHandle.) You can execute a given statement multiple times using the same statement handle.

EXEC SQL PREPARE stmtHandle
  FROM "SELECT empno, ename FROM emp WHERE deptno = ?";

The program then declares and opens the cursor empCursor, substituting a user-provided value for the parameter marker in the prepared SELECT statement. The OPEN statement includes a USING clause, which must provide a value for each placeholder found in the query:

EXEC SQL DECLARE empCursor CURSOR FOR stmtHandle;

EXEC SQL OPEN empCursor USING :argv[2];

EXEC SQL WHENEVER NOT FOUND DO break;

while(true)
{

The program iterates through the cursor and prints the employee number and name of each employee in the selected department:

  EXEC SQL FETCH empCursor INTO :empno, :ename;

  printf("%-10s %s\n", empno.arr, ename.arr);
}

The program then closes the cursor, commits any changes, disconnects from the server, and terminates the application:

  EXEC SQL CLOSE empCursor;

  EXEC SQL COMMIT RELEASE;

  exit(EXIT_SUCCESS);
}

The application calls the handle_error() function whenever it encounters a SQL error. The handle_error() function prints the content of the error message, resets the error handler, rolls back any changes, disconnects from the database, and terminates the application:

static void handle_error(void)
{
  printf("%s\n", sqlca.sqlerrm.sqlerrmc);

  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK RELEASE;

  exit(EXIT_FAILURE);
}

Example: Executing a query with an unknown number of variables

This example shows executing a query with an unknown number of input parameters or columns in the result set. This type of query might occur when you prompt the user for the text of the query or when a query is assembled from a form on which the user chooses from a number of conditions (i.e., a filter).

/***********************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

SQLDA *params;
SQLDA *results;

static void allocateDescriptors(int count,
                                int varNameLength,
                                int indNameLenth);
static void bindParams(void);
static void displayResultSet(void);

int main(int argc, char *argv[])
{
  EXEC SQL BEGIN DECLARE SECTION;
    char   *username = argv[1];
    char   *password = argv[2];
    char   *stmtText = argv[3];
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR sqlprint;

  EXEC SQL CONNECT TO test
    USER :username
    IDENTIFIED BY :password;

  params  = sqlald(20, 64, 64);
  results = sqlald(20, 64, 64);

  EXEC SQL PREPARE stmt FROM :stmtText;

  EXEC SQL DECLARE dynCursor CURSOR FOR stmt;

  bindParams();

  EXEC SQL OPEN dynCursor USING DESCRIPTOR params;

  displayResultSet(20);
}

static void bindParams(void)
{
  EXEC SQL DESCRIBE BIND VARIABLES FOR stmt INTO params;

  if (params->F < 0)
     fprintf(stderr, "Too many parameters required\n");
  else
  {
     int i;

     params->N = params->F;

     for (i = 0; i < params->F; i++)
     {
       char *paramName = params->S[i];
       int   nameLen   = params->C[i];
       char  paramValue[255];

       printf("Enter value for parameter %.*s: ",
               nameLen, paramName);

       fgets(paramValue, sizeof(paramValue), stdin);

       params->T[i] = 1;     /* Data type = Character (1) */
       params->L[i] = strlen(paramValue) - 1;
       params->V[i] = strdup(paramValue);
     }
  }
}

static void displayResultSet(void)
{
  EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO results;

  if (results->F < 0)
     fprintf(stderr, "Too many columns returned by query\n");
  else if (results->F == 0)
     return;
  else
  {
     int col;

     results->N = results->F;

     for (col = 0; col < results->F; col++)
     {
       int null_permitted, length;

       sqlnul(&results->T[col],
              &results->T[col],
              &null_permitted);

       switch (results->T[col])
       {
          case 2:        /* NUMERIC */
          {
            int precision, scale;

            sqlprc(&results->L[col], &precision, &scale);

            if (precision == 0)
               precision = 38;

            length = precision + 3;
            break;
           }

          case 12:   /* DATE */
          {
            length = 30;
            break;
          }

          default:   /* Others */
          {
            length = results->L[col] + 1;
            break;
          }
       }

       results->V[col] = realloc(results->V[col], length);
       results->L[col] = length;
       results->T[col] = 1;
     }

     EXEC SQL WHENEVER NOT FOUND DO break;

     while (1)
     {
       const char *delimiter = "";

       EXEC SQL FETCH dynCursor USING DESCRIPTOR results;

       for (col = 0; col < results->F; col++)
       {
          if (*results->I[col] == -1)
            printf("%s%s", delimiter, "<null>");
          else
            printf("%s%s", delimiter, results->V[col]);
          delimiter = ", ";
       }


 printf("\n");
      }
  }
}
/***********************************************************/

The code sample begins by including the prototypes and type definitions for the C stdio and stdlib libraries. In addition, the program includes the sqlda.h and sqlcpr.h header files. sqlda.h defines the SQLDA structure used throughout this example. sqlcpr.h defines a small set of functions used to interrogate the metadata found in an SQLDA structure.

#include <stdio.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

Next, the program declares pointers to two SQLDA structures. The first SQLDA structure (params) is used to describe the metadata for any parameter markers found in the dynamic query text. The second SQLDA structure (results) contains both the metadata and the result set obtained by executing the dynamic query.

SQLDA *params;
SQLDA *results;

The program then declares two helper functions, which are defined near the end of the code sample:

static void bindParams(void);
static void displayResultSet(void);

Next, the program declares three host variables. The first two (username and password) are used to connect to the database server. The third host variable (stmtTxt) is a NULL-terminated C string containing the text of the query to execute. The values for these three host variables are derived from the command-line arguments. When the program begins to execute, it sets up an error handler and then connects to the database server:

int main(int argc, char *argv[])
{
  EXEC SQL BEGIN DECLARE SECTION;
    char *username = argv[1];
    char *password = argv[2];
    char *stmtText = argv[3];
  EXEC SQL END DECLARE SECTION;

  EXEC SQL WHENEVER SQLERROR sqlprint;
  EXEC SQL CONNECT TO test
    USER :username
    IDENTIFIED BY :password;

Next, the program calls the sqlald() function to allocate the memory required for each descriptor. Each descriptor contains pointers to arrays of:

Column names
Indicator names
Data types
Lengths
Data values

When you allocate an SQLDA descriptor, you specify the maximum number of columns you expect to find in the result set (for SELECT-list descriptors) or the maximum number of parameters you expect to find the dynamic query text (for bind-variable descriptors). In this case, we specify that we expect no more than 20 columns and 20 parameters. You must also specify a maximum length for each column or parameter name and each indicator variable name. In this case, we expect names to be no more than 64 bytes long.

See SQLDA structure for a complete description of the SQLDA structure.

params = sqlald(20, 64, 64);
results = sqlald(20, 64, 64);

After allocating the SELECT-list and bind descriptors, the program prepares the dynamic statement and declares a cursor over the result set.

EXEC SQL PREPARE stmt FROM :stmtText;

EXEC SQL DECLARE dynCursor CURSOR FOR stmt;

Next, the program calls the bindParams() function. The bindParams() function examines the bind descriptor (params) and prompts the user for a value to substitute in place of each parameter marker found in the dynamic query.

bindParams();

Finally, the program opens the cursor (using any parameter values supplied by the user) and calls the displayResultSet() function to print the result set produced by the query:

EXEC SQL OPEN dynCursor USING DESCRIPTOR params;

displayResultSet();
}

The bindParams() function determines whether the dynamic query contains any parameter markers. If so, it prompts the user for a value for each parameter and then binds that value to the corresponding marker. The DESCRIBE BIND VARIABLE statement populates the params SQLDA structure with information describing each parameter marker:

static void bindParams(void)
{
  EXEC SQL DESCRIBE BIND VARIABLES FOR stmt INTO params;

If the statement contains no parameter markers, params->F contains 0. If the statement contains more parameters than fit into the descriptor, params->F contains a negative number. In this case, the absolute value of params->F indicates the number of parameter markers found in the statement. If params->F contains a positive number, that number indicates how many parameter markers were found in the statement.

if (params->F < 0)
   fprintf(stderr, "Too many parameters required\n");
else
{
   int i;

   params->N = params->F;

Next, the program executes a loop that prompts the user for a value, iterating once for each parameter marker found in the statement:

for (i = 0; i < params->F; i++)
{
  char *paramName = params->S[i];
  int nameLen = params->C[i];
  char paramValue[255];

  printf("Enter value for parameter %.*s: ",
          nameLen, paramName);

  fgets(paramValue, sizeof(paramValue), stdin);

After prompting the user for a value for a given parameter, the program binds that value to the parameter by setting:

params->T[i] to indicate the data type of the value
params->L[i] to the length of the value (we subtract one to trim off the trailing new-line character added by fgets())
params->V[i] to point to a copy of the NULL-terminated string provided by the user

     params->T[i] = 1;        /* Data type = Character (1) */
     params->L[i] = strlen(paramValue) + 1;
     params->V[i] = strdup(paramValue);
    }
  }
}

The displayResultSet() function loops through each row in the result set and prints the value found in each column. displayResultSet() starts by executing a DESCRIBE SELECT LIST statement. This statement populates an SQLDA descriptor (results) with a description of each column in the result set.

static void displayResultSet(void)
{
  EXEC SQL DESCRIBE SELECT LIST FOR stmt INTO results;

If the dynamic statement returns no columns (that is, the dynamic statement is not a SELECT statement), results->F contains 0. If the statement returns more columns than fit into the descriptor, results->F contains a negative number. In this case, the absolute value of results->F indicates the number of columns returned by the statement. If results->F contains a positive number, that number indicates how many columns were returned by the query.

if (results->F < 0)
   fprintf(stderr, "Too many columns returned by query\n");
else if (results->F == 0)
   return;
else
{
   int col;

   results->N = results->F;

Next, the program enters a loop, iterating once for each column in the result set:

for (col = 0; col < results->F; col++)
{
  int null_permitted, length;

To decode the type code found in results->T, the program invokes the sqlnul() function (see the description of the T member of the SQLDA structure in the The SQLDA structure). This call to sqlnul() modifies results->T[col] to contain only the type code (the nullability flag is copied to null_permitted). This step is needed because the DESCRIBE SELECT LIST statement encodes the type of each column and the nullability of each column into the T array.

sqlnul(&results->T[col],
       &results->T[col],
       &null_permitted);

After decoding the actual data type of the column, the program modifies the results descriptor to tell ECPGPlus to return each value in the form of a NULL-terminated string. Before modifying the descriptor, the program must compute the amount of space required to hold each value. To make this computation, the program examines the maximum length of each column (results->V[col]) and the data type of each column (results->T[col]).

For numeric values (where results->T[col] = 2), the program calls the sqlprc() function to extract the precision and scale from the column length. To compute the number of bytes required to hold a numeric value in string form, displayResultSet() starts with the precision (that is, the maximum number of digits) and adds three bytes for a sign character, a decimal point, and a NULL terminator.

switch (results->T[col])
{
   case 2:          /* NUMERIC */
   {
     int precision, scale;

     sqlprc(&results->L[col], &precision, &scale);

     if (precision == 0)
        precision = 38;
     length = precision + 3;
     break;
   }

For date values, the program uses a hard-coded length of 30. In a real-world application, you might want to more carefully compute the amount of space required.

case 12:     /* DATE */
{
  length = 30;
  break;
}

For a value of any type other than date or numeric, displayResultSet() starts with the maximum column width reported by DESCRIBE SELECT LIST and adds one extra byte for the NULL terminator. Again, in a real-world application you might want to include more careful calculations for other data types:

default:    /* Others */
{
  length = results->L[col] + 1;
  break;
}
}

After computing the amount of space required to hold a given column, the program:

Allocates enough memory to hold the value
Sets results->L[col] to indicate the number of bytes found at results->V[col]
Sets the type code for the column (results->T[col]) to 1 to instruct the upcoming FETCH statement to return the value in the form of a NULL-terminated string

  results->V[col] = malloc(length);
  results->L[col] = length;
  results->T[col] = 1;
}

At this point, the results descriptor is configured such that a FETCH statement can copy each value into an appropriately sized buffer in the form of a NULL-terminated string.

Next, the program defines a new error handler to break out of the upcoming loop when the cursor is exhausted.

EXEC SQL WHENEVER NOT FOUND DO break;

while (1)
{
  const char *delimiter = "";

The program executes a FETCH statement to fetch the next row in the cursor into the results descriptor. If the FETCH statement fails (because the cursor is exhausted), control transfers to the end of the loop because of the EXEC SQL WHENEVER directive found before the top of the loop.

EXEC SQL FETCH dynCursor USING DESCRIPTOR results;

The FETCH statement populates the following members of the results descriptor:

*results->I[col] indicates whether the column contains a NULL value (-1) or a non-NULL value (0). If the value is non-NULL but too large to fit into the space provided, the value is truncated, and *results->I[col] contains a positive value.
results->V[col] contains the value fetched for the given column (unless *results->I[col] indicates that the column value is NULL).
results->L[col] contains the length of the value fetched for the given column.

Finally, displayResultSet() iterates through each column in the result set, examines the corresponding NULL indicator, and prints the value. The result set isn't aligned. Instead, each value is separated from the previous value by a comma.

for (col = 0; col < results->F; col++)
{
   if (*results->I[col] == -1)
     printf("%s%s", delimiter, "<null>");
   else
     printf("%s%s", delimiter, results->V[col]);
   delimiter = ", ";
}

printf("\n");
}
}
}
/***********************************************************/

← Prev

Using descriptors

↑ Up

Including embedded SQL commands

Error handling

Building and executing dynamic SQL statements v16

Example: Executing a nonquery statement without parameters

Example: Executing a nonquery statement with a specified number of placeholders

Example: Executing a query with a known number of placeholders

Example: Executing a query with an unknown number of variables

← Prev

↑ Up

Next →