ASA Programming Guide
Embedded SQL Programming
Static and dynamic SQL
In the C language, strings are stored in arrays of characters. Dynamic statements are constructed in C language strings. These statements can then be executed using the PREPARE and EXECUTE statements. These SQL statements cannot reference host variables in the same manner as static statements since the C language variables are not accessible by name when the C program is executing.
To pass information between the statements and the C language variables, a data structure called the SQL Descriptor Area (SQLDA) is used. This structure is set up for you by the SQL preprocessor if you specify a list of host variables on the EXECUTE command in the USING clause. These variables correspond by position to place holders in the appropriate positions of the prepared command string.
For information on the SQLDA, see The SQL descriptor area (SQLDA).
A place holder is put in the statement to indicate where host variables are to be accessed. A place holder is either a question mark (?) or a host variable reference as in static statements (a host variable name preceded by a colon). In the latter case, the host variable name used in the actual text of the statement serves only as a place holder indicating a reference to the SQL descriptor area.
A host variable used to pass information to the database is called a bind variable.
For example:
EXEC SQL BEGIN DECLARE SECTION;
char comm[200];
char address[30];
char city[20];
short int cityind;
long empnum;
EXEC SQL END DECLARE SECTION;
. . .
sprintf( comm, "update %s set address = :?,
city = :?"
" where employee_number = :?",
tablename );
EXEC SQL PREPARE S1 FROM :comm;
EXEC SQL EXECUTE S1 USING :address, :city:cityind, :empnum;This method requires the programmer to know how many host variables there are in the statement. Usually, this is not the case. So, you can set up your own SQLDA structure and specify this SQLDA in the USING clause on the EXECUTE command.
The DESCRIBE BIND VARIABLES statement returns the host variable names of the bind variables that are found in a prepared statement. This makes it easier for a C program to manage the host variables. The general method is as follows:
EXEC SQL BEGIN DECLARE SECTION;
char comm[200];
EXEC SQL END DECLARE SECTION;
. . .
sprintf( comm, "update %s set address = :address,
city = :city"
" where employee_number = :empnum",
tablename );
EXEC SQL PREPARE S1 FROM :comm;
/* Assume that there are no more than 10 host variables. See next example if you can't put
a limit on it */
sqlda = alloc_sqlda( 10 );
EXEC SQL DESCRIBE BIND VARIABLES FOR S1 USING DESCRIPTOR sqlda;
/* sqlda->sqld will tell you how many host variables there were. */
/* Fill in SQLDA_VARIABLE fields with values based on
name fields in sqlda */
. . .
EXEC SQL EXECUTE S1 USING DESCRIPTOR sqlda;
free_sqlda( sqlda );The SQLDA consists of an array of variable descriptors. Each descriptor describes the attributes of the corresponding C program variable or the location that the database stores data into or retrieves data from:
data type
length if type is a string type
precision and scale if type is a numeric type
memory address
indicator variable
For a complete description of the SQLDA structure, see The SQL descriptor area (SQLDA)
The indicator variable is used to pass a NULL value to the database or retrieve a NULL value from the database. The indicator variable is also used by the database server to indicate truncation conditions encountered during a database operation. The indicator variable is set to a positive value when not enough space was provided to receive a database value.
For more information, see Indicator variables.