Dynamic SQL introduces several new commands for the Precompiler. These must be preceded by the keywords EXEC SQL, as for static SQL commands.
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).
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.
Executes a prepared SQL statement, assigning values to parameters in the prepared statement before execution. Queries cannot be executed.
These commands are similar to their static counterparts. They are used to process queries which have been prepared with the PREPARE command.
Create and remove data structures (descriptors) used to store information about parameters in a prepared SQL statement.
Obtain information about parameters in a SQL statement or items selected by a query and put that information into a descriptor.
Retrieve or change information held in descriptors.
Specify whether space is automatically allocated when retrieving data using pointers.
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.
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);
}
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.
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.
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.
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.
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.
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*'
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:
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).
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.
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).
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.
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.
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.
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.
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.
/* 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);
}
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.
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;
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;
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).
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);
}
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.
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.
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);
}
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:
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