INFORMIX-ESQL/C Programmer's Manual Using a System-Descriptor Area

The dyn_sql.ec program is an ESQL/C demonstration program that uses dynamic SQL. The program prompts the user to enter a SELECT statement for the stores7 demonstration database and then uses a system-descriptor area to execute the SELECT dynamically.

By default, the program opens the stores7 database. If the demonstration database was given a name other than stores7, however, you can specify the database name on the command line. The following command runs the dyn_sql executable on the mystores7 database:

dyn_sql mystores7

Use the following command to compile the dyn_sql program:

esql -o dyn_sql dyn_sql.ec

The -o dyn_sql option causes the executable program to be named dyn_sql. Without the -o option, the name of the executable program defaults to a.out. For more information on the esql preprocessor command, see Using the esql Command.

/*

This program prompts the user to enter a SELECT statement

for the stores7 database. It processes the statement using dynamic sql

and system descriptor areas and displays the rows returned by the

database server.

*/

#include <stdio.h>

#include <stdlib.h>

#include <ctype.h>

EXEC SQL include sqltypes;

EXEC SQL include locator;

EXEC SQL include datetime;

EXEC SQL include decimal;

#define WARNNOTIFY 1

#define NOWARNNOTIFY 0

#define LCASE(c) (isupper(c) ? tolower(c) : (c))

#define BUFFSZ 256

extern char statement[80];

These lines specify C and ESQL/C files to include in the program. The stdio.h file enables dyn_sql to use the standard C I/O library. The stdlib.h file contains string-to-number conversion functions, memory allocation functions, and other miscellaneous standard library functions. The ctypes.h file contains macros that check the attributes of a character. For example, one macro determines whether a character is uppercase or lowercase.

The sqltypes.h header file contains symbolic constants that correspond to the data types that are found in Informix databases. The program uses these constants to determine the data types of columns that the dynamic SELECT statement returns.

The locator.h file contains the definition of the locator structure (loc_t), which is the type of host variable needed for TEXT and BYTE columns. The datetime.h file contains definitions of the datetime and interval structures, which are the data types of host variables for DATETIME and INTERVAL columns. The decimal.h file contains the definition of the dec_t structure, which is the type of host variable needed for DECIMAL columns.

The exp_chk() exception-handling function uses the WARNNOTIFY and NOWARNNOTIFY constants (lines 14 and 15). The second argument of exp_chk() tells the function to display information in the SQLSTATE and SQLCODE variables for warnings (WARNNOTIFY) or not to display information for warnings (NOWARNNOTIFY). The exp_chk() function is in the exp_chk.ec source file. For a description, see Guide to the exp_chk.ec File.

Line 16 defines LCASE, a macro that converts an uppercase character to a lowercase character. Line 17 defines BUFFSZ to be the number 256. The program uses BUFFSZ to specify the size of arrays that store input from the user.

Line 18 declares statement as an external global variable to hold the name of the last SQL statement that the program asked the database server to execute. The exception-handling functions use this information. (See lines 399 to 406.)

EXEC SQL BEGIN DECLARE SECTION;

loc_t lcat_descr;

loc_t lcat_picture;

EXEC SQL END DECLARE SECTION;

mint whenexp_chk();

main(argc, argv)

mint argc;

char *argv[];

{

int4 ret, getrow();

short data_found = 0;

EXEC SQL BEGIN DECLARE SECTION;

char ans[BUFFSZ], db_name[30];

char name[40];

mint sel_cnt, i;

short type;

EXEC SQL END DECLARE SECTION;

printf("DYN_SQL Sample ESQL Program running.\n\n");

EXEC SQL whenever sqlerror call whenexp_chk;

if (argc > 2) /* correct no. of args? */

{

printf("\nUsage: %s [database]\nIncorrect no. of argument(s)\n",

argv[0]);

printf("\nDYN_SQL Sample Program over.\n\n");

exit(1);

}

strcpy(db_name, "stores7");

if(argc == 2)

strcpy(db_name, argv[1]);

sprintf(statement,"CONNECT TO %s",db_name);

EXEC SQL connect to :db_name;

printf("Connected to %s\n", db_name);

++argv;

Lines 19 to 23 define the global host variables that are used in SQL statements. Lines 20 and 21 define the locator structures that are the host variables for the cat_descr and cat_picture columns of the catalog table. Line 23 declares the whenexp_chk() function, which the program calls when an error occurs on an SQL statement.

The main() function is the point where the program begins to execute. The argc parameter gives the number of arguments from the command line when the program was invoked. The argv parameter is an array of pointers to command-line arguments. This program expects only one argument (the name of the database to be accessed), and it is optional.

Line 28 defines an int4 data type (ret) to receive a return value from the getrow() function. Line 28 also declares that the getrow() function returns a int4 data type. Lines 30 to 35 define the host variables that are local to the main() program block. Line 37 executes the WHENEVER statement to transfer control to whenexp_chk() if any errors occur in SQL statements. For more information on the whenexp_chk() function, see Guide to the exp_chk.ec File.

Lines 38 to 51 establish a connection to a database. If argc equals 2, the program assumes that the user entered a database name on the command line (by convention the first argument is the name of the program), and the program opens this database. If the user did not enter a database name on the command line, the program opens the stores7 database (see line 45), which is the default. In both cases, the program connects to the default database server that is specified by the INFORMIXSERVER environment variable because no database server is specified.

while(1)

{

/* prompt for SELECT statement */

printf("\nEnter a SELECT statement for the %s database",

db_name);

printf("\n\t(e.g. select * from customer;)\n");

printf("\tOR a ';' to terminate program:\n>> ");

if(!getans(ans, BUFFSZ))

continue;

if (*ans == ';')

{

strcpy(statement, "DISCONNECT");

EXEC SQL disconnect current;

printf("\nDYN_SQL Sample Program over.\n\n");

exit(1);

}

/* prepare statement id */

printf("\nPreparing statement (%s)...\n", ans);

strcpy(statement, "PREPARE sel_id");

EXEC SQL prepare sel_id from :ans;

/* declare cursor */

printf("Declaring cursor 'sel_curs' for SELECT...\n");

strcpy(statement, "DECLARE sel_curs");

EXEC SQL declare sel_curs cursor for sel_id;

/* allocate descriptor area */

printf("Allocating system-descriptor area...\n");

strcpy(statement, "ALLOCATE DESCRIPTOR selcat");

EXEC SQL allocate descriptor 'selcat';

/* Ask the database server to describe the statement */

printf("Describing prepared SELECT...\n");

strcpy(statement,

"DESCRIBE sel_id USING SQL DESCRIPTOR selcat");

EXEC SQL describe sel_id using sql descriptor 'selcat';

if (SQLCODE != 0)

{

printf("** Statement is not a SELECT.\n");

free_stuff();

strcpy(statement, "DISCONNECT");

EXEC SQL disconnect current;

printf("\nDYN_SQL Sample Program over.\n\n");

exit(1);

}

The while(1) on line 52 begins a loop that continues to the end of the main() function. Lines 55 to 58 prompt the user to enter either a SELECT statement or, to terminate the program, a semicolon. The getans() function receives the input from the user. If the first character is not a semicolon, the program continues to process the input.

The PREPARE statement prepares the SELECT statement (which the user enters) from the array ans[] and assigns it the statement identifier sel_id. The PREPARE statement enables the database server to parse, validate, and generate an execution plan for the statement.

The DECLARE statement (lines 72 to 75) creates the sel_curs cursor for the set of rows that the SELECT statement returns, in case it returns more than one row.

The ALLOCATE DESCRIPTOR statement allocates the selcat system-descriptor area in memory. The statement does not include the WITH MAX clause and, therefore, uses the default memory allocation, which is for 100 columns.

The DESCRIBE statement obtains information from the database server about the statement that is in the sel_id statement identifier. The database server returns the information in the selcat system-descriptor area, which the preceding ALLOCATE DESCRIPTOR statement creates. The information that DESCRIBE puts into the system-descriptor area includes the number, names, data types, and lengths of the columns in the select list.

The DESCRIBE statement also sets the SQLCODE variable to a number that indicates the type of statement that was described. To check whether the statement type is SELECT, line 85 compares the value of SQLCODE to 0 (the value defined in the sqlstypes.h file for a SELECT statement with no INTO TEMP clause). If the statement is not a SELECT, line 87 displays a message to that effect and the program frees the cursor and the resources that have been allocated. Then it closes the connection and exits.

/* Determine the number of columns in the select list */

printf("Getting number of described values from ");

printf("system-descriptor area...\n");

strcpy(statement, "GET DESCRIPTOR selcat: COUNT field");

EXEC SQL get descriptor 'selcat' :sel_cnt = COUNT;

/* open cursor; process select statement */

printf("Opening cursor 'sel_curs'...\n");

strcpy(statement, "OPEN sel_curs");

EXEC SQL open sel_curs;

/*

* The following loop checks whether the cat_picture or

* cat_descr columns are described in the system-descriptor area.

* If so, it initializes a locator structure to read the simplelarge-object

* data into memory and sets the address of the locator structure

* in the system-descriptor area.

*/

for(i = 1; i <= sel_cnt; i++)

{

strcpy(statement,

"GET DESCRIPTOR selcat: TYPE, NAME fields");

EXEC SQL get descriptor 'selcat' VALUE :i

:type = TYPE,

:name = NAME;

if (type == SQLTEXT && !strncmp(name, "cat_descr",

strlen("cat_descr")))

{

lcat_descr.loc_loctype = LOCMEMORY;

lcat_descr.loc_bufsize = -1;

lcat_descr.loc_oflags = 0;

strcpy(statement, "SET DESCRIPTOR selcat: DATA field");

EXEC SQL set descriptor 'selcat' VALUE :i

DATA = :lcat_descr;

}

if (type == SQLBYTES && !strncmp(name, "cat_picture",

strlen("cat_picture")))

{

lcat_picture.loc_loctype = LOCMEMORY;

lcat_picture.loc_bufsize = -1;

lcat_picture.loc_oflags = 0;

strcpy(statement, "SET DESCRIPTOR selcat: DATA field");

EXEC SQL set descriptor 'selcat' VALUE :i

DATA = :lcat_picture;

}

}

The GET DESCRIPTOR statement retrieves the COUNT value from the selcat system-descriptor area. The COUNT value indicates how many columns are described in the system-descriptor area.

The OPEN statement begins execution of the dynamic SELECT statement and activates the sel_curs cursor for the set of rows that it returns.

This section of the code uses the GET DESCRIPTOR statement to determine whether the simple large-object columns from the catalog table (cat_descr and cat_picture) are included in the select list. If you dynamically select a simple large-object column, you must set the address of a locator structure into the DATA field of the item descriptor to tell the database server where to return the locator structure.

First, however, the program initializes the locator structure, as follows:

• The data is returned in a memory buffer
  (loc_loctype = LOCMEMORY).
• The database server allocates the memory buffer (loc_bufsize = -1).

(For more information on how to work with the TEXT and BYTE data types, see Chapter 7, Working with Simple Large Objects.) Then the program uses the SET DESCRIPTOR statement to load the address of the locator structure into the DATA field of the descriptor area.

while(ret = getrow("selcat")) /* fetch a row */

{

data_found = 1;

if (ret < 0)

{

strcpy(statement, "DISCONNECT");

EXEC SQL disconnect current;

printf("\nDYN_SQL Sample Program over.\n\n");

exit(1);

}

disp_data(sel_cnt, "selcat"); /* display the data */

}

if (!data_found)

printf("** No matching rows found.\n");

free_stuff();

if (!more_to_do()) /* More to do? */

break; /* no, terminate loop */

}

}

/* fetch the next row for selected items */

int4 getrow(sysdesc)

EXEC SQL BEGIN DECLARE SECTION;

PARAMETER char *sysdesc;

EXEC SQL END DECLARE SECTION;

{

int4 exp_chk();

sprintf(statement, "FETCH %s", sysdesc);

EXEC SQL fetch sel_curs using sql descriptor :sysdesc;

return((exp_chk(statement)) == 100 ? 0 : 1);

}

The getrow() function retrieves the selected rows one by one. Each iteration of the while loop retrieves one row, which the program then processes with the disp_data() function (line 148). When all the rows are retrieved, getrow() returns a 0 (zero) and the while loop terminates. For more information on the getrow() function, see "Lines 157 to 167.".

The free_stuff() function frees resources that were allocated when the dynamic SELECT statement was processed. See "Lines 381 to 387.".

When all the selected rows are processed, the program calls the more_to_do() function, which asks whether the user would like to process more SELECT statements. If the answer is no, more_to_do() returns 0 and the break statement terminates the while loop that began on line 52. If the answer is yes, the program begins the next iteration of the while statement on line 52 to accept and process another SELECT statement.

The getrow() function moves the cursor to and then fetches the next row in the set of rows that are returned by the dynamic SELECT statement. It fetches the row values into the system-descriptor area that is specified in the sysdesc variable. If there are no more rows to fetch (exp_chk() returns 100), getrow() returns 0. If the FETCH encounters a runtime error, getrow() returns 1.

{/*

* This function loads a column into a host variable of the correct

* type and displays the name of the column and the value, unless the

* value is NULL.

*/

disp_data(col_cnt, sysdesc)

mint col_cnt;

EXEC SQL BEGIN DECLARE SECTION;

PARAMETER char *sysdesc;

EXEC SQL END DECLARE SECTION;

EXEC SQL BEGIN DECLARE SECTION;

mint int_data, i;

char *char_data;

int4 date_data;

datetime dt_data;

interval intvl_data;

decimal dec_data;

short short_data;

char name[40];

short char_len, type, ind;

EXEC SQL END DECLARE SECTION;

int4 size;

unsigned amount;

mint x;

char shdesc[81], str[40], *p;

printf("\n\n");

/* For each column described in the system descriptor area,

* determine its data type. Then retrieve the column name and its

* value, storing the value in a host variable defined for the

* particular data type. If the column is not NULL, display the

* name and value.

*/

for(i = 1; i <= col_cnt; i++)

{

strcpy(statement, "GET DESCRIPTOR: TYPE field");

EXEC SQL get descriptor :sysdesc VALUE :i

:type = TYPE;

switch(type)

{

case SQLSERIAL:

case SQLINT:

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

ind = INDICATOR,

:int_data = DATA;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

printf("\n%.20s: %d", name, int_data);

break;

case SQLSMINT:

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR,

:short_data = DATA;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

printf("\n%.20s: %d", name, short_data);

break;

case SQLDECIMAL:

case SQLMONEY:

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR,

:dec_data = DATA;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

{

if(type == SQLDECIMAL)

rfmtdec(&dec_data, "###,###,###.##", str);

else

rfmtdec(&dec_data, "$$$,$$$,$$$.$$", str);

printf("\n%.20s: %s", name, str);

}

break;

case SQLDATE:

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR,

:date_data = DATA;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

{

if((x = rfmtdate(date_data, "mmm. dd, yyyy",

str)) < 0)

printf("\ndisp_data() - DATE - fmt error");

else

printf("\n%.20s: %s", name, str);

}

break;

case SQLDTIME:

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR,

:dt_data = DATA;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

{

x = dttofmtasc(&dt_data, str, sizeof(str), 0);

printf("\n%.20s: %s", name, str);

}

break;

case SQLINTERVAL:

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR,

:intvl_data = DATA;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

{

if((x = intofmtasc(&intvl_data, str,

sizeof(str),

"%3d days, %2H hours, %2M minutes"))

< 0)

printf("\nINTRVL - fmt error %d", x);

else

printf("\n%.20s: %s", name, str);

}

break;

case SQLVCHAR:

case SQLCHAR:

strcpy(statement,

"GET DESCRIPTOR: LENGTH, NAME fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:char_len = LENGTH,

:name = NAME;

amount = char_len;

if(char_data = (char *)(malloc(amount + 1)))

{

strcpy(statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:char_data = DATA,

:ind = INDICATOR;

if(ind == -1)

printf("\n%.20s: NULL", name);

else

printf("\n%.20s: %s", name, char_data);

}

else

{

printf("\n%.20s: ", name);

printf("Can't display: out of memory");

}

break;

case SQLTEXT:

strcpy (statement,

"GET DESCRIPTOR: NAME, INDICATOR, DATA fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR,

:lcat_descr = DATA;

size = lcat_descr.loc_size; /* get size of data */

printf("\n%.20s: ", name);

if(ind == -1)

{

printf("NULL");

break;

}

p = lcat_descr.loc_buffer; /* set p to buf addr */

/* print buffer 80 characters at a time */

while(size >= 80)

{

/* mv from buffer to shdesc */

ldchar(p, 80, shdesc);

printf("\n%80s", shdesc); /* display it */

size -= 80; /* decrement length */

p += 80; /* bump p by 80 */

}

strncpy(shdesc, p, size);

shdesc[size] = '\0';

printf("%-s\n", shdesc); /* dsply last segment */

break;

case SQLBYTES:

strcpy (statement,

"GET DESCRIPTOR: NAME, INDICATOR fields");

EXEC SQL get descriptor :sysdesc VALUE :i

:name = NAME,

:ind = INDICATOR;

if(ind == -1)

printf("%.20s: NULL", name);

else

{

printf("%.20s: ", name);

printf("Can't display BYTE type value");

}

break;

default:

printf("\nUnexpected data type: %d", type);

EXEC SQL disconnect current;

printf("\nDYN_SQL Sample Program over.\n\n");

exit(1);

}

}

printf("\n");

}

The disp_data() function displays the values that are stored in each row that the SELECT statement returns. The function must be able to receive and process any data type within the scope of the dynamic SELECT statement (in this case, any column within the stores7 database). This function accepts two arguments: col_cnt contains the number of columns that are contained in the system-descriptor area, and sysdesc contains the name of the system-descriptor area that contains the column information. This second argument must be declared with the PARAMETER keyword because the argument is used in the FETCH statement.

The disp_data() function first defines host variables for each of the data types that are found in the stores7 database (lines 178 to 188), except for the locator structures that have been globally defined already for the cat_descr and cat_picture columns of the catalog table (lines 19 to 22).

For each column that is described in the system-descriptor area, disp_data() retrieves its data type with a GET DESCRIPTOR statement. Next, disp_data() executes a switch on that data type and, for each type (column), it executes another GET DESCRIPTOR statement to retrieve the name of the column, the indicator flag, and the data. Unless the column is null, disp_data() moves the column data from the DATA field of the system-descriptor area to a corresponding host variable. Then it displays the column name and the content of the host variable.

The disp_data() function uses the symbolic constants defined in sqltypes.h to compare data types. It also uses the ESQL/C library functions rfmtdec(), rfmtdate(), dttofmtasc(), and intofmtosc() to format the DECIMAL and MONEY, DATE, DATETIME, and INTERVAL data types, respectively, for display.

For the TEXT and BYTE data types, you can retrieve the value of the column with the following two-stage process, because the database server returns a locator structure rather than the data:

1. The GET DESCRIPTOR statement (lines 333 and 361) retrieves the locator structure from the system-descriptor area and moves it to the loc_t host variable.
2. The disp_data() function obtains the address of the data buffer from the locator structure, in loc_buffer, and retrieves the data from there.

In the case of the BYTE data type, for the sake of brevity disp_data() retrieves the locator structure but does not display the data. For an example of the type of logic required to display a BYTE column, see Guide to the dispcat_pic.ec File.

free_stuff()

{

EXEC SQL free sel_id; /* free resources for statement */

EXEC SQL free sel_curs; /* free resources for cursor */

/* free system descriptor area */

EXEC SQL deallocate descriptor 'selcat';

}

/*

* The inpfuncs.c file contains the following functions used in this

* program:

* more_to_do() - asks the user to enter 'y' or 'n' to indicate

* whether to run the main program loop again.

*

* getans(ans, len) - accepts user input, up to 'len' number of

* characters and puts it in 'ans'

*/

#include "inpfuncs.c"

/*

* The exp_chk.ec file contains the exception handling functions to

* check the SQLSTATE status variable to see if an error has occurred

* following an SQL statement. If a warning or an error has

* occurred, exp_chk() executes the GET DIAGNOSTICS statement and

* displays the detail for each exception that is returned.

*/

EXEC SQL include exp_chk.ec;

The free_stuff() function frees resources that were allocated to process the dynamic statement. Line 383 frees resources that were allocated by the application when it prepared the dynamic SELECT statement. Line 384 releases resources allocated by the database server to process the sel_curs cursor. The DEALLOCATE DESCRIPTOR statement releases the memory allocated for the selcat system-descriptor area and its associated data areas.

Several of the ESQL/C demonstration programs also call the more_to_do() and getans() functions. Therefore, these functions are also broken out into a separate C source file and included in the appropriate demonstration program. Neither of these functions contain ESQL/C, so the program can use the C #include preprocessor statement to include the file. For a description of these functions, see Guide to the inpfuncs.c File.

As a result of the WHENEVER statement on line 37, the whenexp_chk() function is called if an error occurs during the execution of an SQL statement. The whenexp_chk() function examines the SQLSTATE status variable to determine the outcome of an SQL statement. Because several demonstration programs use this function with the WHENEVER statement for exception handling, the whenexp_chk() function and its supporting functions have been broken out into a separate exp_chk.ec source file. The dyn_sql program must include this file with the ESQL/C include directive because the exception-handling functions use ESQL/C statements. The exp_chk.ec source file is described in Chapter 11, Exception Handling.

Tip: In a production environment, you would put functions such as more_to_do(), getans(), and whenexp_chk() into a library and include them on the command line when you compile the ESQL/C program.