Static vs dynamic SQL

Part V. The SQL Anywhere Programming Interfaces

Chapter 34. The Embedded SQL Interface
Static vs dynamic SQL

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

Static 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.Host variables. All examples to this point have used static Embedded SQL statements. Remember that 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).

The following commands can be used statically:

     ALTER TABLE

     INSERT

     CHECKPOINT

     PREPARE TO COMMIT

     COMMIT

     RELEASE SAVEPOINT

     COMMENT

     REVOKE

     CONNECT

     ROLLBACK

     CREATE

     ROLLBACK TO SAVEPOINT

     DELETE

     SAVEPOINT

     DISCONNECT

     SET CONNECTION

     DROP

     SET OPTION

     EXPLAIN

     UPDATE

     GRANT

     VALIDATE TABLE

The SELECT statement is different in Embedded SQL than in interactive SQL (ISQL).

As described earlier, if the SELECT statement retrieves only one row from the database, then the statement can be coded as a normal SELECT command except that an INTO clause must follow the select list. If the select statement retrieves multiple rows from the database, then a cursor must be used for the select command as described previously.

Dynamic statements

Dynamic statements are SQL statements which are constructed in C language strings. (In the C language, strings are stored in arrays of characters.) These statements can then be executed using the Embedded SQL 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.

In order to pass information between the statements and the C language variables, a data structure called the SQL Descriptor Area (SQLDA) is used (see "The SQL descriptor area (SQLDA)"). This structure will be 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 prepared command string.

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 that SQLDA in the USING clause on the EXECUTE command. The DESCRIBE BIND VARIABLES statement will return the host variable names of the bind variables 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 (see "The SQL descriptor area (SQLDA)" for a complete description of the SQLDA structure). Each descriptor describes the attributes of the corresponding C program variable or location at which the database will store data into or retrieve data from:

data type,
length if type is a string type,
precision and scale if type is a numeric type,
memory address,
indicator variable.

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 engine 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. See "Indicator variables" for more information.

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 an 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 will set up an 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 using the FETCH USING DESCRIPTOR statement. The typical scenario is:

     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 will be 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 a complete example using cursors for a dynamic select statement , see "Dynamic cursor example". For details of the functions mentioned above, see "Library functions".

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.

SQL 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 SQLDA must contain nnn * (columns per row) variables. The first row will be placed in SQLDA variables 0 to (columns per row)-1 and so on.

The engine returns in SQLCOUNT the number of records fetched and will always return a SQLCOUNT greater than zero unless there is an error. Older versions of the engine or server will only return a single row and the SQLCOUNT will be set to zero. Thus a SQLCOUNT of zero with no error condition indicates one valid row has been fetched.

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 an SQLDA to be used for fetching from the statement identified
     // by "stat0". "width" rows will be 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

In 16-bit environments (including extended 32-bit DOS and Windows 3.x) the limit on the size of a SQLDA is 1450 columns. Thus the number of rows times the number of columns per row must be no more than 1450. In these environments, the SQLDA itself (not including data items) must fit in a single 64K segment. alloc_sqlda will return NULL in those environments on an attempt to allocate a SQLDA that is too large.
In the function DoWideFetches, the SQLDA memory is allocated using the alloc_sqlda function, which allows space for indicator variables, rather than using the alloc_sqlda_noind function. This is required, as when fewer than the requested number of rows are fetched (at the end of the cursor, for example) the SQLDA items corresponding to the rows not fetched are returned as NULLs by setting the indicator value. If no indicator variables are present, an error is generated (SQLE_NO_INDICATOR: no indicator variable for NULL result).
If a row being fetched has been updated, generating an SQLE_ROW_UPDATED_WARNING warning, the fetch stops on the row which caused the warning and the values for all rows processed to that point (including the row that caused the warning) are returned. SQLCOUNT contains the number of rows fetched and includes the row that caused the warning. All remaining SQLDA items are marked as NULL.
If a row being fetched has been deleted or is locked, generating an SQLE_NO_CURRENT_ROW or SQLE_LOCKED error, SQLCOUNT contains the number of rows read prior to the error, not including the row that caused the error, but the SQLDA does not contain values for those rows, since SQLDA values are not returned on errors. The SQLCOUNT value can be used to reposition the cursor, if necessary, to read the rows.

`ALTER TABLE`	`INSERT`
`CHECKPOINT`	`PREPARE TO COMMIT`
`COMMIT`	`RELEASE SAVEPOINT`
`COMMENT`	`REVOKE`
`CONNECT`	`ROLLBACK`
`CREATE`	`ROLLBACK TO SAVEPOINT`
`DELETE`	`SAVEPOINT`
`DISCONNECT`	`SET CONNECTION`
`DROP`	`SET OPTION`
`EXPLAIN`	`UPDATE`
`GRANT`	`VALIDATE TABLE`