# CHAPTER 6: Dynamic SQL Commands

## 6.1 Introduction

Dynamic SQL introduces several new commands for the Precompiler. These must be preceded by the keywords `EXEC SQL`, as for static SQL commands.

EXECUTE IMMEDIATE

  Executes the SQL statement contained in a string variable or literal. The statement cannot contain parameters, and it must not be a query (`SELECT` statement).

PREPARE, DEALLOCATE PREPARE

  Prepares the SQL statement held in a string variable or literal for later execution. Statements containing parameters must be prepared before they can be executed. After you have finished with the prepared statement, you must DEALLOCATE the statement name.

EXECUTE

  Executes a prepared SQL statement, assigning values to parameters in the prepared statement before execution. Queries cannot be executed.

DECLARE, OPEN, FETCH, FETCH_AGAIN and CLOSE dynamic cursors

  These commands are similar to their static counterparts. They are used to process queries which have been prepared with the `PREPARE` command.

ALLOCATE, DEALLOCATE DESCRIPTOR

  Create and remove data structures (descriptors) used to store information about parameters in a prepared SQL statement.

DESCRIBE INPUT, OUTPUT

  Obtain information about parameters in a SQL statement or items selected by a query and put that information into a descriptor.

GET, SET DESCRIPTOR

  Retrieve or change information held in descriptors.

AUTOMATIC

  Specify whether space is automatically allocated when retrieving data using pointers.

## 6.2 EXECUTE IMMEDIATE

### 6.2.1 Executing Parameterless Statements

SQL statements which do not contain parameters and which are not queries (`SELECT` statements) can be executed without preparation prior to execution. Whereas static SQL operations are limited to Data Manipulation Language commands (`SELECT`, `UPDATE`, `INSERT`, `DELETE`) and transaction commands, `EXECUTE IMMEDIATE` allows any SQL operation (except `SELECT` and transactions) to be performed. Even statements which send output to stdout (for example, `DISPLAY DATABASE`) can be executed. Note that the following SQL statement is allowed in an `EXECUTE` command, as it is considered an insert operation, not a query.

       SELECT * FROM table1 INSERT INTO table2;

The SQL statement can be a string literal, or contained in a character array (string) variable or in a memory space allocated to a character pointer variable (note that C variables embedded in a SQL command must be preceded by a colon).

Executing a string literal:

       EXEC SQL EXECUTE IMMEDIATE "CREATE INDEX ON books.publisher";

In this case, the usage is not truly dynamic, since the SQL statement cannot change while the program is executing. However, it is useful for executing commands which cannot be handled by static SQL.

Executing a statement held in a character array variable:

       strcpy (sqls, "GRANT display, select ON books TO student1");
       EXEC SQL EXECUTE IMMEDIATE :sqls;

Dynamically building a statement before executing it:

       strcpy (sqls, "UPDATE books ");
       strcat (sqls, "SET price = price*1.2 ");
       strcat (sqls, "WHERE publisher = 102");
       EXEC SQL EXECUTE IMMEDIATE :sqls;

By using a character array or pointer variable, the program can change the SQL statement before it is executed.

Users who are familiar with Empress C Interface may find that the `EXECUTE IMMEDIATE` command performs almost the same purpose as the `mscall` routine. The latter is more flexible in that it can perform queries and transaction commands. Refer to Empress Host Language: Command Language Interface manual for information on the `mscall` routine.

### 6.2.2 Example Program

The following program illustrates the use of the `EXECUTE IMMEDIATE` command with string literals and character arrays. It creates a table called books, inserts a few records and displays the structure of the table. Note that the `INSERT` statements are built at execution time. The program also shows how global variables `SQLCODE` and `SQLERRMC` are used to detect run time errors.

       #include <mscc.h>

       /* The SQL Communications Area is a data structure used by
          Empress to communicate with the application.             */

       EXEC SQL INCLUDE SQLCA;

       /* All C variables embedded in SQL statements must be
          declared in the following section.  str is a character
          array which will be used to hold SQL statements.           */

       EXEC SQL BEGIN DECLARE SECTION;
            char        str[512];
       EXEC SQL END DECLARE SECTION;

       /* Array of strings containing data to be inserted into table */

       char  *data[] =
       {
          " (125, 'Introduction to SQL', '$35')",
          " (134, 'UNIX Shell Programming', '$29.90')",
          " (134, 'Using X-Windows', '$32.95')",
          " (125, 'Database Design', '$30.50')",
          0
       };

       main ()
       {
          int i;

       /* Initialize program for database access.                 */

       EXEC SQL INIT;
       EXEC SQL DATABASE IS "bookstore";

       /* Execute string literal to create table.
          Check SQLCODE for any execution error.                  */

       EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE books
          (publisher INTEGER, title CHAR(40,1), price DOLLAR(4,1))";
       if (SQLCODE) error("Create");

       /* Execute string variable containing the SQL statement
          built up dynamically using values in data array.        */

       for (i=0; data[i]; i++)
       {
          strcpy (str, "INSERT books VALUES");
          strcat (str, data[i]);
          EXEC SQL EXECUTE IMMEDIATE :str;
          if (SQLCODE) error("Insert");
       }

       /* Execute string literal to display detailed information
          on table.  Output is sent to stdout.                    */

       EXEC SQL EXECUTE IMMEDIATE "DISPLAY books ALL";

       /* Clean up before terminating application.                */

       EXEC SQL EXIT;
       }

       /* Function to print out error code and message.           */

       error (cmd)
       char *cmd;
       {
          printf ("%s Error,  SQLCODE: %d\n", cmd, SQLCODE);
          printf ("SQLERRMC: %s\n", SQLERRMC);
          EXEC SQL EXIT;
          exit (1);
       }

## 6.3 PREPARE, EXECUTE & DEALLOCATE PREPARE

### 6.3.1 Preparing Statements

SQL statements which contain parameters must be prepared before they can be executed. The `PREPARE` command associates a name to an SQL statement. The SQL statement can have any number of parameters, each represented by a parameter marker `?`. The statement can be passed to the `PREPARE` command as a string literal, a character array variable or a character pointer variable which points to a valid memory space.

As an example of preparing a string literal, consider the following statement:

       EXEC SQL PREPARE u1 FROM
          "UPDATE books SET price = ? WHERE title = ?";

This statement updates the attribute price in table books for records where attribute title is equal to a string specified at execution time.

> Note that prior to Empress Version 8.62, the placeholder for a string value, had to be quoted. So considering that `title` is an attribute of type character, the above prepare statement would be like:

>        EXEC SQL PREPARE u1 FROM
>              "UPDATE books SET price = ? WHERE title = '?'";

> If the placeholder was not quoted, the parameter assigned to the placeholder for a character attribute had to be quoted. In Empress Version 8.62, the placeholder shall not need to be quoted. This is to comply with the ANSI SQL Standards for Dynamic SQL.

Preparing a character array variable, or pointer variable to which memory has been allocated:

       strcpy (sqls, "DELETE FROM books WHERE publisher = ?");
       EXEC SQL PREPARE del2 FROM :sqls;

The above statements delete records from table books where publisher is a value specified at execution time.

Preparing a statement which is built dynamically:

       strcpy (sqls, "CREATE UNIQUE INDEX");
       strcat (sqls, " ? ");
       strcat (sqls, "ON ? ");
       strcat (sqls, "(?, ?)");
       EXEC SQL PREPARE cindex FROM :sqls;

The above statements create a unique index on two attributes. The names of the index, table and attributes are all specified at execution time.

The statement names `u1`, `del2` and `cindex` are identifiers used by the Precompiler, not variables. They are used subsequently to identify which statements are to be executed. A given statement name can be prepared several times, with the same SQL statement or with different SQL statements.

### 6.3.2 Executing Prepared Statements

A prepared SQL statement is executed using the `EXECUTE` command. The command also associates embedded variables specified by a `USING` clause to parameters in the prepared statement. At run time, the `EXECUTE` command replaces parameters in the prepared statement by the values of the variables, and executes the resulting statement. You can execute a prepared statement several times, with different values for the variables, or even with different variables. Queries (`SELECT` statements) cannot be processed by the `EXECUTE` command.

Executing a prepared statement twice, with different variables:

       EXEC SQL PREPARE s1 FROM "GRANT select ON ? TO ?";
       strcpy (tab1, "books");
       strcpy (user1, "teacher1");
       strcpy (user2, "student2");
       EXEC SQL EXECUTE s1 USING :tab1, :user1;
       EXEC SQL EXECUTE s1 USING :tab1, :user2;

In the above statements, the variables are all character arrays. At execution time, the strings contained by these variables replace the parameters in the prepared SQL statement, to create these two SQL statements:

       GRANT select ON books TO teacher1
       GRANT select ON books TO student2

Executing a prepared statement twice, with same variables but different values:

       EXEC SQL PREPARE s2 FROM "UPDATE books SET price = price*? WHERE title
    MATCH ?";
       percent = 1.15;
       strcpy (where_c, "Intro*");
       EXEC SQL EXECUTE s2 USING :percent, :where_c;
       percent = 1.2;
       strcpy (where_c, "SQL*");
       EXEC SQL EXECUTE s2 USING :percent, :where_c;

The above statements illustrate the use of variables other than character arrays. At execution time, these SQL statements will be created:

       UPDATE books SET price = price*1.15 WHERE title match 'Intro*'
       UPDATE books SET price = price*1.2  WHERE title match 'SQL*'

Note that the order of the variables in the `USING` clause is important, as their values will replace the corresponding parameters in the prepared statement. The number of variables in the `USING` clause must be the same as the number of parameters in the prepared statement, otherwise an error occurs at run time. This rule does not apply to control variables.

The `USING` clause can also associate a SQL descriptor to parameters in the prepared SQL statement. The use of descriptors is discussed in a later section.

### 6.3.3 Deallocated Prepared Statements

Preparing a statement will result in database resources being assigned to the statement name. To free these resources, EXEC SQL DEALLOCATE PREPARE statement_name must be called.

### 6.3.4 Data Types of Variables

The variables in a `USING` clause can be of any fundamental C data type. Empress will perform data type conversion at execution time, if necessary. When assigning values to attributes, you would probably use variables with data types which correspond to the data types of the attributes. For example, you would use a C float variable to assign a value to an Empress `REAL` attribute. But you could also use a C int to assign a value to an Empress `REAL` attribute.

Character array variables or character pointers can be used to assign values to attributes of any data type. In such a case, Empress will assume that the data is in external (string) format.

For assignment to a `BULK` attribute, you should use a structure, or a pointer to a structure similar to the following:

       typedef struct
       {
          long  size;          /* Number of bytes of data       */
          char  data[1024];    /* You can use any type of array
                                  here to contain the bulk data */
       }

Array variables can also be used for assignment to `BULK` attributes, except character arrays, as these are used for assigning data in external format.

### 6.3.5 Control Variables

The number of variables in the `USING` clause must be the same as the number of parameters in the prepared statement, otherwise an error occurs at run time.

The rule above does not apply to control variables. In the following example, statement `s3` has two parameters, but the `USING` clause has three variables. In this case, variable `cntl_pr` is a control variable as it is preceded by variable `price_var`, without a comma as separator.

       EXEC SQL PREPARE s3 FROM "INSERT books (title, price) VALUES (?, ?)"
       EXEC SQL EXECUTE s3 USING :title_var, :price_var :cntl_pr;

If control variable cntl_pr is negative, a `NULL` value is inserted for attribute price, regardless of the value of price_var.

### 6.3.6 Example Program

The following program illustrates the use of the `PREPARE` and `EXECUTE` commands. It allows an attribute in table `books` to be updated.

    #include <mscc.h>

    EXEC SQL INCLUDE SQLCA;

    EXEC SQL BEGIN DECLARE SECTION;
         char  sqls[512];          /* SQL statement              */
         char  aname1[50];         /* Attribute to update        */
         char  new_val[128];       /* New value for attribute    */
         char  test_val[128];      /* Value to compare with      */
    EXEC SQL END DECLARE SECTION;

    static void error (char *cmd);

    int	main (int argc, char* argv[])
    {
       EXEC SQL INIT;
       EXEC SQL DATABASE IS "my_db";

       /* Prepare the SQL statement.  At this point, the attribute
          to be updated, its new value, and the condition for
          selecting which records are updated are all unknown.    */

       strcpy (sqls, "UPDATE books SET ? = ? WHERE title MATCH ?");
       EXEC SQL PREPARE s1 FROM :sqls;
       if (SQLCODE)
       {
          error("Prepare");
          return 1;
       }

       strcpy(aname1, "price");
       strcpy(new_val, "32.50");
       strcpy (test_val, "*program*");

       /* Execute the prepared statement with the data.  In this
          case, all the variables are character arrays.           */

       EXEC SQL EXECUTE s1 USING :aname1, :new_val, :test_val;
       if (SQLCODE)
       {
          EXEC SQL DEALLOCATE PREPARE s1;
          error("Execute");
          return 1;
       }
       else
          printf ("Update was successful.\n");

       /* We are finished with the prepared statement name.
          Deallocate it to free up database resources */
       EXEC SQL DEALLOCATE PREPARE s1;
       if (SQLCODE)
       {
    	 error("Deallocate Prepare");
          return 1;
       }

       EXEC SQL EXIT;
       return 0;
    }

    /* Function to print error code and message, before exiting.  */

    static void error (char *cmd)
    {
       printf ("%s Error,  SQLCODE: %d\n", cmd, SQLCODE);
       printf ("SQLERRMC: %s\n", SQLERRMC);
       EXEC SQL EXIT;
    }

The following is a sample session:

    Update was successful.

The SQL statement executed at the end of the above session is:

       UPDATE books SET price = 32.50 WHERE title match '*program*'

## 6.4 Dynamic Cursors

### 6.4.1 Query in Dynamic SQL

You cannot perform queries (`SELECT` statements) with `EXECUTE IMMEDIATE` nor with `PREPARE` and `EXECUTE` commands. Whereas the static SQL command `SELECT INTO` can be used to retrieve a single record, dynamic SQL has no equivalent command. You need to use cursors to process queries in dynamic SQL. The steps required for processing a query using a dynamic cursor are similar to those for a static cursor, although the syntax for the commands may differ:

1. `PREPARE` statement_name `FROM` select_statement
2. `DECLARE` cursor_name `CURSOR FOR` statement_name
3. `OPEN` cursor_name [`USING` variable_list]
4. `FETCH` cursor_name `INTO` variable_list
5. `CLOSE` cursor_name

### 6.4.2 Preparing the Query

The `PREPARE` command is used to prepare the `SELECT` statement, associating a statement name (which is a Precompiler identifier, not a program variable) to it. The `SELECT` statement is either a string literal, a character array variable, or a character pointer (with memory allocated to it) and may contain any number of parameters, represented by `?`.

Preparing a query without parameters:

       EXEC SQL PREPARE q1 FROM "SELECT title, price FROM books";

The above statement prepares a query to obtain all records (since a `WHERE` clause is not specified) from table `books`, retrieving only attributes `title` and `price`.

Preparing a query with parameters:

       EXEC SQL PREPARE q2 FROM "SELECT title FROM books WHERE price = ?";

This statement prepares a query to retrieve an unknown attribute from table `books`. The condition for selecting books with certain price which is unknown at this moment.

Whereas a static SQL query can only select attributes, a dynamic SQL query can use expressions involving built-in, mathematical and user-defined functions and operators:

       strcpy (sqls, "SELECT ");
       strcat (sqls, "w_date, ");
       strcat (sqls, "SUBSTR(comments, 1, 20), ");
       strcat (sqls, "celsius*1.8+32 ");
       strcat (sqls, "FROM weather WHERE ");
       strcat (sqls, "monthof(w_date) = 'January'");
       EXEC SQL PREPARE q3 FROM :sqls;

This query selects three items from `weather` records for the month of January: the date, the first 20 characters of the comments (`SUBSTR`(comments,1,20)), and the temperature in Fahrenheit (celsius*1.8+32).

### 6.4.3 Declaring the Cursor

A cursor is declared by associating a cursor name to a query. Usually, the query is a prepared statement containing parameters. The cursor name is a Precompiler identifier, not a program variable.

Declaring a cursor for a prepared statement with parameters:

       EXEC SQL PREPARE q2 FROM "SELECT title FROM books WHERE price = ?";
       EXEC SQL DECLARE c2 CURSOR FOR q2;

The above example defines cursor `c2` and associates it to statement `q2`.

If the `SELECT` statement does not contain parameters, it does not need to be prepared. In this case, instead of using a prepared statement name, the cursor is associated to a query specified as a string literal or variable:

       EXEC SQL DECLARE c3 CURSOR FOR "SELECT * FROM books WHERE price > 40";
       strcpy (query, "SELECT title FROM books WHERE publisher = 140");
       EXEC SQL DECLARE c4 CURSOR FOR :query;

By default, a cursor is declared for read operations only. If you want to use the cursor for update operations, you can either open the table explicitly using the `OPEN_TABLE` command, or you can declare the cursor with the `FOR UPDATE` or `FOR DEFERRED` option.

Declaring a cursor for update operations:

       EXEC SQL PREPARE q4 FROM "SELECT title, publisher FROM books WHERE price = ?";
       EXEC SQL DECLARE c4 CURSOR FOR q4 FOR UPDATE;

The above statements define cursor `c4` which allows update operations to be performed on table books.

A given cursor name cannot be declared more than once, but several cursors can be declared and open at the same time.

### 6.4.4 Opening the Cursor

After defining the cursor, you must open it before you can access the selected records. The command to open a dynamic cursor is very similar to the static SQL equivalent, with the addition of a `USING` clause to replace parameters in the query statement by values held in variables. The `USING` clause is not required if the query statement does not have parameters.

Opening a cursor for a query which does not contain parameters:

       EXEC SQL DECLARE c3 CURSOR FOR
            "SELECT * FROM books WHERE price > 40";
       EXEC SQL OPEN c3;

In this case, the `OPEN` command is identical to its static SQL counterpart, as a `USING` clause is not needed.

Opening a cursor for a query which contains parameters:

       val2 = 130;
       EXEC SQL PREPARE q2 FROM "SELECT title FROM books WHERE publisher = ?";
       EXEC SQL DECLARE c2 CURSOR FOR q2;
       EXEC SQL OPEN c2 USING :val2;

The above statements will perform this query:

       SELECT title FROM books WHERE publisher = 130

The `OPEN` command is equivalent to the `EXECUTE` command. The first is used for queries, and the second is used for SQL statements which are not queries. The rules for the `USING` clause are the same for both commands; the number of variables must match the number of parameters in the prepared SQL statement, and control variables may be used. Instead of variables, you can also use SQL descriptors (refer to the section "SQL Descriptors" in this manual).

### 6.4.5 Fetching Records

Records are retrieved with the `FETCH` command. It makes the next record become current, and reads attribute values into variables.

Retrieving a single record:

       strcpy (val, "Database Design");
       EXEC SQL PREPARE q5 FROM
            "SELECT publisher, title, price FROM books WHERE title = ?";
       EXEC SQL DECLARE c5 CURSOR FOR q5;
       EXEC SQL OPEN c5 USING  :val;
       EXEC SQL FETCH c5 INTO :pub, :titl, :prc;

The above statements would retrieve the first record which its title is `Database Design`, and store values of attribute `publisher` into variable `pub`, attribute `title` into variable `titl`, and attribute `price` into variable `prc`. Note that the order of the variables in the `INTO` clause is important, as they are associated to attributes in the `SELECT` statement.

Generally, the number of records selected by the query is not known in advance. To retrieve all the records, the `FETCH` command must be called repeatedly, until there are no more records (`SQLCODE = 100`). If a record cannot be accessed because it is locked by another process, `SQLCODE` is set to 1. In this case, subsequent attempts to retrieve the same record can be done using the `FETCH_AGAIN` command instead of `FETCH`.

       EXEC SQL OPEN c5 USING :atr, :val;
       EXEC SQL FETCH c5 INTO :pub, :titl, :prc;
       while (SQLCODE != 100)
       {
          switch (SQLCODE)
          {
             case 0:  printf ("%d\t%s\t%s\n", pub, titl, prc);
                      EXEC SQL FETCH c5 INTO :pub, :titl, :prc;
                      break;
             case 1:  EXEC SQL FETCH_AGAIN c5 INTO :pub, :titl, :prc;
                      break;
             default: printf ("Error Code %d\n", SQLCODE);
          }
       }

This program fragment retrieves all records selected. If a record is locked, it keeps attempting to retrieve that same record.

The syntax of the `FETCH` and `FETCH_AGAIN` commands are similar to their static SQL counterparts, except that the `INTO` clause can be replaced by a `USING` clause to specify a SQL descriptor instead of a list of variables.

In static SQL, only the last opened cursor can be accessed. Dynamic SQL does not have this restriction. The `FETCH` statement can reference any cursor which is open.

### 6.4.6 Closing a Cursor

After retrieving records using a cursor, the cursor should be closed. The `CLOSE CURSOR` command is identical to its static SQL equivalent.

Closing a cursor named `c1`:

       EXEC SQL CLOSE c1;

When you close a cursor, its definition is not discarded. You can open the cursor again, with the `OPEN` command mentioned previously. Each time a cursor is re-opened, the query may be changed by using different variables in the `USING` clause, or by assigning different values to the same variables.

### 6.4.7 Fetching With Pointer Variables

When using pointer variables to fetch attribute values, you have the choice of either allocating the space for the data yourself (which is the default option), or letting Empress allocate the space. The `AUTOMATIC` command is used to select either option.

If you choose to allocate space yourself:

       EXEC SQL AUTOMATIC MEMORY OFF;

Empress expects that the pointer variables point to pre-allocated space. If you did not allocate memory for your pointers, errors will occur.

To specify automatic space allocation by Empress:

       EXEC SQL AUTOMATIC MEMORY ON;

Empress will automatically allocate memory to pointer variables and copy the data retrieved from the attribute to that memory space. You must deallocate this memory space when it is no longer needed, or if you are using the same pointer variable to fetch another value with the automatic option on. Use the Empress `EXEC SQL FREE :ptr` call to free memory which has been allocated to your pointer. However, if you used the C `malloc()` routine to allocate memory to your pointer, you should use the C `free()` routine to free that memory, as the Empress `EXEC SQL FREE` call works differently.

Empress has no way of knowing whether your pointer has memory allocated to it. With the automatic option on, you should always free any memory allocated to your pointer, whether allocated automatically by Empress, or by yourself. With the automatic option off, you must make sure that sufficient memory is allocated to pointers which are used to fetch data.

The automatic option is useful for retrieving variable length data such as `TEXT` or `BULK`, since you do not know the size of the data beforehand.

### 6.4.8 Fetching NULL Values

Control variables should be used to check whether the value being fetched is `NULL`.

       EXEC SQL FETCH c1 INTO :val :cntl;

If the value being fetched is `NULL`, variable `cntl` will be set to a negative value and variable `val` is unchanged. If the value is not `NULL`, variable `cntl` is set to zero and variable `val` will be assigned the value.

If you do not use a control variable:

       EXEC SQL FETCH c1 INTO :val;

then, you will not be able to know when the value fetched is `NULL`, since variable `val` will remain unchanged in such a case.

### 6.4.9 Modifying The Current Record

After fetching a record, you can use the `UPDATE` command with a `WHERE CURRENT OF` clause to change its contents. This command is identical to the static SQL `UPDATE` command. The only difference is that the current record is from a dynamic cursor. For example, the following statements will update the attribute `price` of each fetched record:

       EXEC SQL DECLARE c1 CURSOR FOR "SELECT * FROM books" FOR UPDATE;
       EXEC SQL OPEN c1;
       EXEC SQL FETCH c1 INTO :pub, :title, :price;
       while (SQLCODE != 100)
       {
          price = price*1.2;
          EXEC SQL UPDATE books SET price = :price WHERE CURRENT OF c1;
          EXEC SQL FETCH c1 INTO :pub, :title, :price;
       }

Similarly, you can delete the current record using the `DELETE` command with a `WHERE CURRENT OF` clause. In the following example, records are retrieved from the table, and subsequently deleted if the `publisher` attribute is equal to some value:

       EXEC SQL FETCH c1 INTO :pub, :title, :price;
       while (SQLCODE != 100)
       {
          if (pub = not_valid)
          EXEC SQL DELETE books WHERE CURRENT OF c1;
          EXEC SQL FETCH c1 INTO :pub, :title, :price;
       }

Note that the current record can only be deleted or updated if the cursor was declared `FOR UPDATE`, or if the table was explicitly opened in `UPDATE` or `DEFERRED` mode.

You cannot perform update or delete operations on cursors which reference more than one table, and on cursors which contain aggregate functions.

### 6.4.10 Example Program

       /*  Using dynamic cursor to retrieve bulk attribute

           Table name:  images
           Attributes:  code         integer
                        description  char(128,1)
                        bitmap       bulk
       */

       #include <mscc.h>

       EXEC SQL INCLUDE SQLCA;

       EXEC SQL BEGIN DECLARE SECTION;
            char  sqls[512];                    /* SQL statement             */
            int   num;                          /* Value of attr code        */
            char  string[129];                  /* Value of attr description */

            typedef struct
            {
                 long  size;
                 char  data[1];                 /* Dummy size for data array */
            } bulk;

            bulk  *blkptr;                      /* Value of attribute bitmap */
            short cntl;                         /* Control variable          */

       EXEC SQL END DECLARE SECTION;

       main ()
       {
          EXEC SQL INIT;
          EXEC SQL DATABASE IS "db";

          /* Let Empress allocate memory when fetching with pointers.      */

          EXEC SQL AUTOMATIC ON;

          /* Prepare the query, which selects 3 attributes from a table.   */
          strcpy (sqls, "select code, description, bitmap from images");
          EXEC SQL PREPARE s1 FROM :sqls;
          if (SQLCODE) error("Prepare");

          /* Define a cursor for the prepared query.                       */

          EXEC SQL DECLARE c1 CURSOR FOR s1;
          if (SQLCODE) error("Declare");

          /* Open the cursor. */

          EXEC SQL OPEN c1;
          if (SQLCODE) error("Open");

          /* Retrieve each record.  INTEGER attr is read into a C int, CHAR
             attr is read into a char array, BULK attr is retrieved into a
             space automatically allocated by Empress.  A control variable
             is used to check if bulk attribute is null.                   */

          do
          {
             EXEC SQL FETCH c1 INTO :num, :string, :blkptr :cntl;
             switch (SQLCODE)
             {
             case 0:   if (cntl>=0)
                       {
                          printf ("Code: %d \tSize: %d \tDescription: %s\n",
                                   num, blkptr->size, string);

                       /* Assuming we have a function to display pictures */

                          display_image (blkptr->data);
                       }
                       else
                          printf ("Code: %d \tSize: 0 \tDescription: %s\n",
                                   num, string);
                       break;

             case 1:   printf ("Record Locked\n");
                       break;

             case 100: break;                        /* No more records.   */

             default:  error("Fetch");
             }
             EXEC SQL FREE :blkptr;          /* Free space allocated to pointer. */

          } while (SQLCODE == 0);

          /* Close cursor after use, and exit.                             */

          EXEC SQL CLOSE c1;
          if (SQLCODE)
    	 error("Close cursor");

          /* We are finished with the prepared statement name.
             Deallocate it to free up database resources */
          EXEC SQL DEALLOCATE PREPARE s1;
          if (SQLCODE)
    	 error("Deallocate Prepare");
          EXEC SQL EXIT;
       }

       /* Function to print error code and message, before exiting.        */

       error (cmd)
       char *cmd;
       {
          printf ("%s Error,  SQLCODE: %d\n", cmd, SQLCODE);
          printf ("SQLERRMC: %s\n", SQLERRMC);
          EXEC SQL EXIT;
          exit (1);
       }

## 6.5 SQL Descriptors

### 6.5.1 Introduction

A SQL descriptor is a dynamically allocated data structure which you can use to describe the parameters in your dynamic SQL statement, or items selected by a dynamic query. The internal structure of a descriptor is hidden from your application. You can only access data in a descriptor by using specialized commands which will be described in this chapter.

Besides a global area, a descriptor contains one or more item areas holding information about parameters or selected items.

Table 5-1: Global Area

Field Name | Data Type | Description
`COUNT` | integer | Number of parameters or selected items

Table 5-2: Item Area

Field Name | Data Type | Description
`DA_TYPE` | integer | Code for data type of each item
`DA_NLEN` | integer | Length of name of item
`DA_NAME` | string | Name of item
* `DA_DLEN` | integer | Length of data value
`DA_DATA` |  | Value of item
`DA_CNTRL` | C short | Equivalent to a control variable

Note: `DA_DLEN` is not currently supported by Empress.

### 6.5.2 Using SQL Descriptors

When using variables in a `USING` clause or `INTO` clause, you must know in advance the number of attributes to insert, update or select, as well as their data types, so that you can put the required number of variables, and choose appropriate data types for the variables.

If the SQL statement is built dynamically, you may not know at compile time how many attributes are to be inserted, or how many items are selected by the query. In these cases, you can use descriptors to describe the parameters or the attributes selected.

Descriptors can be used either for input or for output. Input descriptors are used to describe the parameters in a prepared SQL statement. Output descriptors are used to fetch attribute values for records retrieved in a query.

Before you can use descriptors, you need to include the following statement in a global section at the beginning of your program:

       EXEC SQL INCLUDE SQLDA;

### 6.5.3 Creating Descriptors

An application can have as many descriptors as necessary. You can create a descriptor when required, and discard it when it is no longer needed.

To create a descriptor, you must allocate space for it using the `ALLOCATE DESCRIPTOR` command:

       EXEC SQL ALLOCATE DESCRIPTOR desc1;

This statement creates a descriptor named `desc1`. The name of the descriptor is a Precompiler identifier, not a program variable.

By default, the descriptor is allocated a maximum of 10 item areas, and the maximum name length of each item is 32 characters. These defaults can be changed using the optional `WITH MAX` clause. For example, to allocate a descriptor `in_desc` containing a maximum of 20 item areas (with the default maximum length of 32 characters for item names):

       EXEC SQL ALLOCATE DESCRIPTOR in_desc WITH MAX (20);

To specify the maximum length of item names:

       EXEC SQL ALLOCATE DESCRIPTOR out_desc WITH MAX (20,50);

This statement allocates descriptor `out_desc` with 20 item areas, and each item name can be up to 50 characters long.

When a descriptor is no longer needed, use the `DEALLOCATE DESCRIPTOR` command to discard it:

       EXEC SQL DEALLOCATE DESCRIPTOR desc1;

### 6.5.4 Execute Using Descriptor

As mentioned before, SQL statements containing parameters can be prepared and executed if they are not queries. We have already discussed the use of variables in the `USING` clause of the `EXECUTE` statement. But if the number of parameters in the prepared statement is only determined at run time, you do not know how many variables to specify in the `USING` clause. To overcome this problem, you can use an input descriptor to assign values to the parameters in the prepared statement.

For example, the following `INSERT` statement is dynamically built:

       strcpy (sqls, "INSERT INTO weather ");
       strcat (sqls, "(w_date, location, celsius) ");
       strcat (sqls, "VALUES ( ?, ?, ? )");
       EXEC SQL PREPARE ins1 FROM :sqls;

To initialize an input descriptor for assigning values to parameters of the prepared statement:

       EXEC SQL DESCRIBE INPUT ins1 INTO SQL DESCRIPTOR in_desc;

Since statement `ins1` has three parameters, three areas of descriptor `in_desc` will become active as a result of the above statement.

You can find out the number of active areas in a descriptor by using the `GET DESCRIPTOR` command to read the `COUNT` field:

       EXEC SQL GET DESCRIPTOR in_desc :var = COUNT;

The above statement assigns to integer variable `var` the number of active areas in descriptor `in_desc`. Attempts to read the other fields of an input descriptor are ignored.

You can assign values to fields in a descriptor area using the `SET DESCRIPTOR` command. The only two fields which can be assigned values are the `DA_DATA` and the `DA_CNTRL` fields. Assignment to the other fields are ignored. For example, to assign the date `June 30, 1993` to the first descriptor area using the external format (string representation) for date:

       EXEC SQL SET DESCRIPTOR in_desc VALUE 1
       DA_DATA = "June 30, 1993";

In the above statement, the data field is assigned a value from a string literal. Variables can also be used. For example, to assign a character string to the data field of the second area:

       area = 2;
       strcpy (str, "Toronto");
       ctrl = 0;
       EXEC SQL SET DESCRIPTOR in_desc VALUE :area
       DA_DATA = :str, DA_CNTRL = :ctrl;

Note that in this example, we are also setting the `DA_CNTRL` field. The `DA_CNTRL` field of a descriptor area is used like a control variable, for handling of `NULL` values. For example:

       area = 3;
       set_null = -1;
       EXEC SQL SET DESCRIPTOR in_desc VALUE :area
       DA_CNTRL = :setnull, DA_DATA = 13.7;

After assigning data to the descriptor areas, the prepared statement can be executed using the descriptor. The parameters in the prepared statement are replaced by data in the descriptor areas.

       EXEC SQL EXECUTE ins1 USING SQL DESCRIPTOR in_desc;

The above sequence of statements will insert a new record in table `weather`, setting date attribute `w_date` to `June 30, 1993`, attribute `location` to `Toronto`, and attribute `celsius` to `NULL` (since the field `DA_CNTRL` for the corresponding descriptor area is negative).

### 6.5.5 Example Program 1

The following program allows the user to insert a record in a table. The table name, number of attributes and names of attributes are unknown until execution time. The program obtains these information from the user, and builds the SQL statement using a descriptor.

       #include <mscc.h>

       EXEC SQL INCLUDE SQLCA;
       EXEC SQL INCLUDE SQLDA;

       EXEC SQL BEGIN DECLARE SECTION;
            char    sqls[1024];        /* string to contain SQL statement */
            char    tab[32];           /* table name                      */
            char    att[32] [32];      /* attribute name                  */
            char    val[32] [32];      /* value to insert into attribute  */
            int     area;              /* descriptor item area            */
       EXEC SQL END DECLARE SECTION;

       main ()
       {
          int i, num;
          EXEC SQL INIT;                             /* Initialization. */
          EXEC SQL DATABASE IS "db";

          /* Create a descriptor with up to 25 areas.                   */

          EXEC SQL ALLOCATE DESCRIPTOR d WITH MAX (25);

          /* Get table name and number of attributes from user.         */

          printf ("Insert into table: ");
          scanf ("%s", tab);
          printf ("Number of attributes to insert: ");
          scanf ("%d", &num);

          /* Build statement to prepare, with the correct number of ?   */

          strcpy (sqls, "insert into ? (?");
          for (i=2; i<=num; i++) strcat (sqls, ",?");
          strcat (sqls, ") values (?");
          for (i=2; i<=num; i++) strcat (sqls, ",?");
          strcat (sqls, ")");

          EXEC SQL PREPARE s1 FROM :sqls;     /* Prepare the statement. */
          if (SQLCODE) error("Prepare");

          /* Associate the descriptor to the statement for input.       */

          EXEC SQL DESCRIBE INPUT s1 INTO SQL DESCRIPTOR d;
          if (SQLCODE) error("Describe");

          /* Assign the table name to descriptor area 1.                */

          EXEC SQL SET DESCRIPTOR d VALUE 1 DA_DATA = :tab;
          if (SQLCODE) error("Set table name");

          /* Assign the attribute names to descriptor areas.            */

          printf ("Enter attributes names: ");
          for (i=0; i<num; i++)
          {
             scanf ("%s", att[i]);
             area = i+2;
             EXEC SQL SET DESCRIPTOR d VALUE :area DA_DATA = :att[i];
             if (SQLCODE) error("Set attribute names");
          }

          /* Assign attribute values to descriptor areas. Although only
             character arrays are used here, you could use other C data
             types, or even literal values.                             */

          printf ("Enter values for the attributes: ");
          for (i=0; i<num; i++)
          {
             scanf ("%s", val[i]);
             area = i+num+2;
             EXEC SQL SET DESCRIPTOR d VALUE :area DA_DATA = :val[i];
             if (SQLCODE) error("Set attribute values");
          }

          /* Execute the insert statement using the descriptor to obtain
             the table name, attribute names and values to be inserted. */

          EXEC SQL EXECUTE s1 USING SQL DESCRIPTOR d;
          if (SQLCODE) error("Execute");

          /*  Deallocate descriptor when it is no longer needed.        */

          EXEC SQL DEALLOCATE DESCRIPTOR d;
          if (SQLCODE) error("Deallocate");

          /* We are finished with the prepared statement name.
             Deallocate it to free up database resources */
          EXEC SQL DEALLOCATE PREPARE s1;
          if (SQLCODE)
    	 error("Deallocate Prepare");

          EXEC SQL EXIT;
       }

       error (cmd)                         /* Function to handle errors. */
       char *cmd;
       {
          printf ("%s Error,  SQLCODE : %d\n", cmd, SQLCODE);
          printf ("SQLERRMC: %s\n", SQLERRMC);
          EXEC SQL EXIT;
          exit (1);
       }

### 6.5.6 Open Cursor Using Descriptor

The use of input descriptors when opening dynamic cursors is similar to executing prepared statements. In the prepared query, the items to select the table name or the `WHERE` clause could be represented by parameters. Data held in an input descriptor is used to replace the parameters in the prepared query. For example, the following `SELECT` statement is built dynamically:

       strcpy (sqls, "SELECT ");
       strcat (sqls, "?, ?, ? ");
       strcat (sqls, "FROM weather ");
       strcat (sqls, "WHERE ? > ?");
       EXEC SQL PREPARE qry1 FROM :sqls;

To initialize an input descriptor for assigning values to parameters in the prepared query:

       EXEC SQL DESCRIBE INPUT qry1 INTO SQL DESCRIPTOR in_desc;

Since prepared query `qry1` has five parameters, five areas of descriptor `in_desc` will become active as a result of the above statement.

Assign values to fields in the descriptor using the `SET DESCRIPTOR` command. In this example, the first three descriptor areas are associated with items selected by the query:

       EXEC SQL SET DESCRIPTOR in_desc VALUE 1 DA_DATA="w_date";
       EXEC SQL SET DESCRIPTOR in_desc VALUE 2 DA_DATA="location";
       EXEC SQL SET DESCRIPTOR in_desc VALUE 3 DA_DATA="celsius*1.8+32 PRINT fahr";

The last two descriptor areas are associated with the selection condition:

       EXEC SQL SET DESCRIPTOR in_desc VALUE 4 DA_DATA="celsius";
       EXEC SQL SET DESCRIPTOR in_desc VALUE 5 DA_DATA=25;

The cursor is declared for the prepared query with the `DECLARE CURSOR` command:

       EXEC SQL DECLARE c1 CURSOR FOR qry1;

The cursor is opened using the descriptor to assign values from the descriptor areas to the parameters in the prepared query:

       EXEC SQL OPEN c1 USING SQL DESCRIPTOR in_desc;

The query created by the above statements is equivalent to the following `SELECT` statement:

       SELECT w_date, location, celsius*1.8+32 PRINT fahr
          FROM weather WHERE celsius > 25

Note that the last selected item is an expression with a `PRINT` clause. In interactive SQL, the `PRINT` clause specifies the name used as the header of a column. When using descriptors in a query, the name is returned by the `DA_NAME` field.

### 6.5.7 Fetch Using Descriptor

After opening a cursor, data from the selected records can be fetched and copied into either variables or descriptor areas. If an output descriptor is used, you must first describe it for the output of that query (only after opening the cursor):

       EXEC SQL DESCRIBE OUTPUT qry1 INTO SQL DESCRIPTOR out_desc;

Since prepared query `qry1` has three items in its select list, three areas of descriptor `out_desc` will become active as a result of the above statement.

You can find out the number of active areas in a descriptor by using the `GET DESCRIPTOR` command to read the `COUNT` field:

       EXEC SQL GET DESCRIPTOR out_desc :cnt = COUNT;

Note that if a query does not have a list of selected items (e.g., `SELECT * FROM tab1`), all the attributes of the table are selected, and therefore `COUNT` would return the number of attributes in the table.

Fetching a record from the cursor and storing the selected data into a descriptor:

       EXEC SQL FETCH c1 INTO SQL DESCRIPTOR out_desc;

Instead of copying the selected data directly to variables, the selected data is assigned to the active descriptor areas.

You can access the data using the `GET DESCRIPTOR` command, specifying which item area you want to read:

       for (area=1; area<=cnt; area++)
       {
          EXEC SQL GET DESCRIPTOR out_desc VALUE :area
             :dt=DA_TYPE, :nm=DA_NAME, :val=DA_DATA, :nul=DA_CNTRL;
          ...
       }

The above loop will read all three descriptor areas, getting the data type code of the selected item, its name, its value for the current record, and an indicator which is negative if the value is `NULL`. Note that if a value is `NULL`, the field `DA_CNTRL` will be returned a negative value, but the variable `val` will remain unchanged even after accessing field `DA_DATA`.

### 6.5.8 Example Program 2

The following program prompts the user for a query and executes it. The number of selected items and their data types are unknown until the query is performed. Note that the query entered by the user should not contain `?` parameters, as this example program does not perform parameter substitution when opening the cursor.

       #include <mscc.h>

       EXEC SQL INCLUDE SQLCA;
       EXEC SQL INCLUDE SQLDA;

       EXEC SQL BEGIN DECLARE SECTION;
          char   sql_str[1024];        /* string to contain SQL statement    */
          char   dname[33][25];        /* for storing item names             */
          int    dtype[25];            /* for storing data type of items     */
          int    da_num;               /* number of active descriptor areas  */
          int    area;                 /* descriptor item area               */
          char   *pstr;                /* for getting string data            */
          long   vlong;                /* for getting integer data           */
          double vdouble;              /* for getting float data             */
          int    i;
          typedef struct
          {
             long size;
             char data[1];
          } bulk;
          bulk   *pbulk;               /* for getting bulk data              */
          short  ctrl;                 /* control variable for null checking */

          typedef struct
          {
            long    gen_year;       /* actual year. e.g. 1999, 2000, etc */
            long    gen_month;      /* 1 to 12. 1 = January, ... */
            long    gen_day;        /* 1 to 31 */
            long    gen_hour;       /* 0 to 23 */
            long    gen_minute;     /* 0 to 59 */
            long    gen_second;     /* 0 to 59 */
            long    gen_microsec;   /* 0 to 999999 */
          } gen_date;
          gen_date  *pdate;

       EXEC SQL END DECLARE SECTION;

       main ()
       {
          int j;
          EXEC SQL INIT;                                  /* Initialization. */
          EXEC SQL DATABASE IS "db";

          /* Specify automatic memory allocation for pointer variables.      */

          EXEC SQL AUTOMATIC MEMORY ON;

          /* Create a descriptor with up to 25 areas.                        */

          EXEC SQL ALLOCATE DESCRIPTOR desc_out WITH MAX (25);

          printf ("Query: ");           /* Get SQL statement from the user.  */
          fgets (sql_str, 1024, stdin);

          /* Prepare the statement, declare and open a cursor.               */

          EXEC SQL PREPARE s1 FROM :sql_str;
          if (SQLCODE)  error("Prepare");
          EXEC SQL DECLARE curs CURSOR FOR s1;
          if (SQLCODE)  error("Declare");
          EXEC SQL OPEN curs;
          if (SQLCODE)  error("Open cursor");

          /* Associate output descriptor to the statement.                   */

          EXEC SQL DESCRIBE OUTPUT s1 INTO SQL DESCRIPTOR desc_out;
          if (SQLCODE)  error("Describe output");

          /* Find the number of selected items.                              */

          EXEC SQL GET DESCRIPTOR desc_out :da_num = COUNT;
          if (SQLCODE)  error("Get count");
          printf ("Number of selected items: %d\n",da_num);

          /* Find the data type and the name of each selected item.          */

          for (i=1; i<=da_num; i++)
             EXEC SQL GET DESCRIPTOR desc_out VALUE :i
                     :dtype[i]=DA_TYPE, :dname[i]=DA_NAME;

          /* Get the first record, using the descriptor to retrieve data.    */

          EXEC SQL FETCH curs INTO SQL DESCRIPTOR desc_out;
          while (SQLCODE != 100)
          {
             if (SQLCODE)  error("Fetch");

             /* Print out information on each selected item.                 */

             for (i=1; i<=da_num; i++)
             {
                printf ("Name: %s \tType: %d \t",dname[i], dtype[i]);
                switch (dtype[i])
                {
                  case DTGCHAR:          /* Get data using a string pointer. */
                  case DTGTEXT:
                    EXEC SQL GET DESCRIPTOR desc_out VALUE :i
                         :pstr = DA_DATA, :ctrl = DA_CNTRL;
                    if (SQLCODE)  error("Get string");
                    if (ctrl<0) printf ("Value: null\n");
                    else
                    {
                       printf ("value: %s\n",pstr);
                       EXEC SQL FREE :pstr;
                    }
                    break;
                  case DTGINTEGER:         /* Get data using a long integer. */
                    EXEC SQL GET DESCRIPTOR desc_out VALUE :i
                         :vlong = DA_DATA, :ctrl = DA_CNTRL;
                    if (SQLCODE)  error("Get integer");
                    if (ctrl<0) printf ("Value: null\n");
                    else printf ("Value: %d\n",vlong);
                    break;
                  case DTGFLOAT:                 /* Get data using a double. */
                  case DTGDECIMAL:
                    EXEC SQL GET DESCRIPTOR desc_out VALUE :i
                         :vdouble = DA_DATA, :ctrl = DA_CNTRL;
                    if (SQLCODE)  error("Get float");
                    if (ctrl<0) printf ("Value: null\n");
                    else printf ("Value: %f\n",vdouble);
                    break;
                  case DTGBULK:            /* Get data using a bulk pointer. */
                    EXEC SQL GET DESCRIPTOR desc_out VALUE :i
                         :pbulk = DA_DATA, :ctrl = DA_CNTRL;
                    if (SQLCODE)  error("Get bulk");
                    if (ctrl<0) printf ("Value: null\n");
                    else
                    {
                       printf ("Value: ");        /* Display first 10 bytes. */
                       for (j=0; j<pbulk->size; j++)
                       {
                          if (j==10) { printf ("..."); break; }
                          else printf ("%x ",(unsigned char)pbulk->data[j]);
                        }
                        printf ("\n");
                        EXEC SQL FREE :pbulk;
                     }
                     break;
                  case DTGDATE:
                  case DTGTIME:
                    EXEC SQL GET DESCRIPTOR desc_out VALUE :i
                         :pdate = DA_DATA, :ctrl = DA_CNTRL;
                    if (SQLCODE)  error("Get date");
                    if (ctrl<0) printf ("Value: null\n");
                    else {
                            if (dtype[i] == DTGDATE)
                                    printf ("Value: %d.%d.%d\n",
                                            pdate->gen_year, pdate->gen_month,
                                            pdate->gen_day);
                            else
                                    printf ("Value: %d.%d.%d %d:%d:%d\n",
                                            pdate->gen_year, pdate->gen_month,
                                            pdate->gen_day, pdate->gen_hour,
                                            pdate->gen_minute, pdate->gen_second);
                            EXEC SQL FREE :pdate;
                     }
                     break;
                  default:
                     printf ("Unknown data type\n");
                }
             }

             /* Get the next record */

             EXEC SQL FETCH curs INTO SQL DESCRIPTOR desc_out;

          }
          EXEC SQL CLOSE curs;                                  /* Clean-up. */
          EXEC SQL DEALLOCATE DESCRIPTOR desc_out;
          EXEC SQL DEALLOCATE PREPARE s1;
          EXEC SQL EXIT;
       }

       error (cmd)                            /* Function to handle errors. */
       char *cmd;
       {
          printf ("%s Error,  SQLCODE : %d\n", cmd, SQLCODE);
          printf ("SQLERRMC: %s\n", SQLERRMC);
          EXEC SQL EXIT;
          exit (1);
       }

## 6.6 Error Handling

After executing each SQL command, the global variable `SQLCODE` is set to an integer value. It is usually set to zero, but if an error occurred, it is set to a negative number. While `SQLCODE` can be used to identify the type of error, the global variable `SQLERRMC` is a string pointer which provides an appropriate error message.

You should always check the value of `SQLCODE` after each SQL command and exit the program if failure of this command will affect the execution of subsequent commands. For example, if the allocation of a SQL descriptor fails, any further attempt to use this descriptor will produce unpredictable results. And remember to perform a `SQL EXIT` before exiting your program.

If the execution of a SQL statement fails, check the following:

- Are you accessing the correct database?
- Do the tables and attributes exist in the database?
- Does the SQL statement have a syntax error?

With dynamic SQL, you may have difficulty to find out if a SQL statement contains a syntax error, because the statement is built at run-time. This is particularly true if you are executing a prepared SQL statement containing parameters. The global variable `SQLCMD` is provided to facilitate the debugging process. It is a string pointer which provides the complete SQL statement (with all the parameters, if any, replaced by actual data) after executing `EXECUTE`, `EXECUTE IMMEDIATE` and `OPEN` commands.

In the following program extract, a SQL statement is prepared and executed. After execution, we check if an error occurred:

       EXEC SQL PREPARE s1 FROM "insert patients (name, age) values (?, ?)"
       strcpy (str1, "Jack");
       v2 = 63;
       EXEC SQL EXECUTE s1 USING :str1, :v2;
       if (SQLCODE)
       {
          printf ("Error Code:    %d \n", SQLCODE);
          printf ("Error Message: %s \n", SQLERRMC);
          printf ("SQL Statement: %s \n", SQLCMD);
       }

The above SQL statement will fail with error:

       Error Code:    -65
       Error Message: Error detected by the SQL processor:
       *** User Error ***  Attribute specification not allowed
       SQL Statement: insert patients (name, age) values (Jack, 63)

The string returned by `SQLCMD` shows that the string `Jack` is not quoted in the SQL statement, causing a syntax error when we execute the statement.

Note that `SQLERRMC` and `SQLCMD` use temporary buffers which are overwritten after executing subsequent SQL commands.

The global variable `mroperr` can be used to obtain a more informative error code. It is set to one of the error codes listed in Empress' `include/mrerrno.h` header file whenever a database operation fails.

The following program extract illustrates the use of `mroperr`:

       EXEC SQL PREPARE s1 FROM "insert patients (name, age) values (?, ?)"
       strcpy (name1, "Mary");  age1 = 54;
       EXEC SQL EXECUTE s1 USING :name1, :age2;
       if (SQLCODE)
       {
          printf ("SQL Error Code:    %d \n", SQLCODE);
          printf ("SQL Error Message: %s \n", SQLERRMC);
          printf ("MR  Error Code:    %d \n", mroperr);
       }

If the table is locked at table level, the insert operation will fail:

       SQL Error Code:    -65
       SQL Error Message: *** Dynamic SQL error *** table 'patients' lock busy
       MR  Error Code:    6