Collection Contents Index Fetching data The SQL descriptor area (SQLDA) pdf/chap2.pdf

Programming Interfaces Guide
   CHAPTER 2. The Embedded SQL Interface     

Static and dynamic SQL

There are two ways to embed SQL statements into a C program:

Until now, we have been discussing static SQL. This section compares static and dynamic SQL.

Top of page  Static SQL statements

All standard SQL data manipulation and data definition statements can be embedded in a C program by prefixing them with EXEC SQL and suffixing the command with a semicolon (;). These statements are referred to as static statements.

Static statements can contain references to host variables, as described in the section.Using host variables. All examples to this point have used static Embedded SQL statements.

Host variables can only be used in place of string or numeric constants. They cannot be used to substitute column names or table names; dynamic statements are required to do those operations.

Top of page  Dynamic SQL statements

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 Info     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.

Example 

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 );

SQLDA contents 

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:

For Info     For a complete description of the SQLDA structure, see The SQL descriptor area (SQLDA)

Indicator variables and NULL 

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 Info     For more information, see Indicator variables.

Top of page  Dynamic SELECT statement

A SELECT statement that returns only a single row can be prepared dynamically, followed by an EXECUTE with an INTO clause to retrieve the one-row result. SELECT statements that return multiple rows, however, are managed using dynamic cursors.

With dynamic cursors, results are put into a host variable list or a SQLDA that is specified on the FETCH statement (FETCH INTO HOSTLIST and FETCH USING DESCRIPTOR SQLDA). Since the number of select list items is usually unknown to the C programmer, the SQLDA route is the most common. The DESCRIBE SELECT LIST statement sets up a SQLDA with the types of the select list items. Space is then allocated for the values using the fill_sqlda() function, and the information is retrieved by the FETCH USING DESCRIPTOR statement.

The typical scenario is as follows:

EXEC SQL BEGIN DECLARE SECTION;
   char comm[200];
EXEC SQL END DECLARE SECTION;
   int actual_size;
   SQLDA * sqlda;

. . .
sprintf( comm, "select * from %s", table_name );
EXEC SQL PREPARE S1 FROM :comm;
/* Initial guess of 10 columns in result. If it is
   wrong, it is corrected right after the first
   DESCRIBE by reallocating sqlda and doing DESCRIBE    again. */
sqlda = alloc_sqlda( 10 );
EXEC SQL DESCRIBE SELECT LIST FOR S1 USING DESCRIPTOR sqlda;
if( sqlda->sqld > sqlda->sqln ){
   actual_size = sqlda->sqld;
   free_sqlda( sqlda );
   sqlda = alloc_sqlda( actual_size );
   EXEC SQL DESCRIBE SELECT LIST FOR S1
      USING DESCRIPTOR sqlda;
}
fill_sqlda( sqlda );
EXEC SQL DECLARE C1 CURSOR FOR S1;
EXEC SQL OPEN C1;
EXEC SQL WHENEVER NOTFOUND {break};
for( ;; ){
   EXEC SQL FETCH C1 USING DESCRIPTOR sqlda;
   if( SQLCODE == SQLE_NOTFOUND ) break;
   /* do something with data */
}
EXEC SQL CLOSE C1;
EXEC SQL DROP STATEMENT S1;

Drop statements after use    
You should ensure that statements are dropped after use, to avoid consuming unnecessary resources.

For Info     For a complete example using cursors for a dynamic select statement , see Dynamic cursor example. For details of the functions mentioned above, see Library functions.

Top of page  Fetching more than one row at a time

The FETCH statement can be modified to fetch more than one row at a time, which may improve performance. This is called a wide fetch.

For Info     Adaptive Server Anywhere also supports wide puts and inserts. For information on these, see PUT statement and EXECUTE statement.

To use wide fetches in Embedded SQL, include the fetch statement in your code as follows:

EXEC SQL FETCH . . . ARRAY nnn

where ARRAY nnn is the last item of the FETCH statement. The fetch count nnn can be a host variable. The number of variables in the SQLDA must be the product of nnn and the number of columns per row. The first row is placed in SQLDA variables 0 to (columns per row)-1, and so on.

The server returns in SQLCOUNT the number of records that were fetched, which is always greater than zero unless there is an error. A SQLCOUNT of zero with no error condition indicates that one valid row has been fetched.

Example 

The following example code illustrates the use of wide fetches. The example code is not compilable as it stands.

EXEC SQL BEGIN DECLARE SECTION;
static unsigned             FetchWidth;
EXEC SQL END DECLARE SECTION;

static SQLDA * DoWideFetches( a_sql_statement_number  stat0,
                              unsigned                *num_of_rows,
                              unsigned                *cols_per_row )
/*********************************************************************/
// Allocate a SQLDA to be used for fetching from the statement identified
// by "stat0".  "width" rows is retrieved on each FETCH request.
// The number of columns retrieved per row is assigned to "cols_per_row".
{
    int                     num_cols;
    unsigned                i, j, offset;
    SQLDA *                 sqlda;
    EXEC SQL BEGIN DECLARE SECTION;
    a_sql_statement_number  stat;
    EXEC SQL END DECLARE SECTION;

    stat = stat0;
    sqlda = alloc_sqlda( 100 );
    if( sqlda == NULL ) return( NULL );
    EXEC SQL DESCRIBE :stat INTO sqlda;
    *cols_per_row = num_cols = sqlda->sqld;
    if( (num_cols * *num_of_rows) > sqlda->sqln ) {
        free_sqlda( sqlda );
        sqlda = alloc_sqlda( num_cols * width );
        if( sqlda == NULL ) return( NULL );
        EXEC SQL DESCRIBE :stat INTO sqlda;
    }
    sqlda->sqld = num_cols * *num_of_rows;
    offset = num_cols;
    for( i = 1; i < width; ++i ) {
        for( j = 0; j < num_cols; ++j, ++offset ) {
            sqlda->sqlvar[offset].sqltype = sqlda->sqlvar[j].sqltype;
            sqlda->sqlvar[offset].sqllen = sqlda->sqlvar[j].sqllen;
            memcpy( &sqlda->sqlvar[offset].sqlname,
                    &sqlda->sqlvar[j].sqlname,
                    sizeof( sqlda->sqlvar[0].sqlname ) );
        }
    }
    fill_sqlda( sqlda );
    return( sqlda );
}

long DoQuery( char * qry )
/************************/
{
    long                    rows;
    unsigned                cols_per_row;
    SQLDA *                 sqlda;
    EXEC SQL BEGIN DECLARE SECTION;
    a_sql_statement_number  stat;
    static unsigned         num_of_rows;
    EXEC SQL END DECLARE SECTION;

    rows = 0L;
    FetchWidth = 20;

    EXEC SQL WHENEVER SQLERROR GOTO err;

    stmt = qry;
    EXEC SQL PREPARE :stat FROM :stmt;

    EXEC SQL DECLARE QCURSOR CURSOR FOR :stat FOR READ ONLY;

    EXEC SQL OPEN QCURSOR;
    sqlda = DoWideFetches( stat, &num_of_rows, &cols_per_row );
    if( sqlda == NULL ) {
        printf( "Maximum allowable fetch width exceeded\n" );
        return( SQLE_NO_MEMORY );
    }

    for( ;; ) {
        EXEC SQL FETCH QCURSOR INTO DESCRIPTOR sqlda ARRAY :FetchWidth;
        if (SQLCODE != SQLE_NOERROR) break;
    if( SQLCOUNT == 0 ) {
        rows += 1;
    } else {
        rows += SQLCOUNT;
    }
    }

    EXEC SQL CLOSE QCURSOR;
    EXEC SQL DROP STATEMENT :stat;
    free_sqlda( sqlda );

err:
    if (SQLCODE != SQLE_NOERROR) {
        printf( "Error detected\n" );
    }

    return (SQLCODE);
}

Notes on using wide fetches 

Top of page  

Collection Contents Index Fetching data The SQL descriptor area (SQLDA) pdf/chap2.pdf