All of the Server Express SQL preprocessors make use of the SQL Communications Area (SQLCA) and the SQL Descriptor Area (SQLDA) data structures.
After each embedded SQL statement is executed, error and status information is returned in the SQL Communications Area (SQLCA).
The SQLCA data structure is shown below:
01 SQLCA. 03 SQLCAID PIC X(8) VALUE "SQLCA". 03 SQLCABC PIC S9(9) COMP-5 VALUE 136. 03 SQLCODE PIC S9(9) COMP-5 VALUE 0. 03 SQLERRM. 49 SQLERRML PIC S9(4) COMP-5. 49 SQLERRMC PIC X(70). 03 SQLERRP PIC X(8). 03 SQLERRD PIC S9(9) COMP-5 OCCURS 6 VALUE 0. 03 SQLWARN. 05 SQLWARN0 PIC X. 05 SQLWARN1 PIC X. 05 SQLWARN2 PIC X. 05 SQLWARN3 PIC X. 05 SQLWARN4 PIC X. 05 SQLWARN5 PIC X. 05 SQLWARN6 PIC X. 05 SQLWARN7 PIC X. 03 FILLER PIC X(3). 03 SQLSTATE PIC X(5).
Oracle, Sybase and Informix all have different versions of the SQLCA. The SQLCA presented above is for OpenESQL and the DB2 ECM. The Oracle, Sybase and Informix SQLCA's all have a SQLCODE, SQLERRML, SQLERRMC and a SQLWARN. The sizes and the positions of these fields can differ between the precompilers.
The table below describes the contents of the SQLCA data structure:
||The text string "SQLCA".
||The length of the SQLCA data structure.
||The status code for the last-run SQL statement.
||The length of the error message in SQLERRMC (0 through 70).
||Error message text. Error messages longer than 70 bytes are
||Reserved (diagnostic information).
||An array of six integer status codes (those not listed below are
SQLERRD(1) The native error code returned by the database.
SQLERRD(2) The severity of the error returned by the database. Severity levels are different on different database systems.
SQLERRD(3) The number of rows affected.
||Eight warning flags, each containing a blank or "W"
(those not listed below are reserved): A warning flag will be set if
SQLCODE contains a value of +1:
||A summary of all warning fields. Blank means there are no warnings.
||"W" indicates that data was truncated on output to a
character host variable.
||"W" indicates that a null value exists, but no indicator
variable was provided.
||"W" indicates that the number of columns is less than the
number of host variables or that the number of host variables provided
does not match the number of parameter markers in the statement. The
lower of the two numbers is used.
||"W" indicates a singleton select that returns more than
one row (only the first row is returned).
||Status indicator for the last-run SQL statement.
The SQLCA contains two variables (
plus a number of warning flags which are used to indicate whether an error
has occurred in the most recently executed SQL statement.
SQLSTATE is a separate data item. For the
currently supported versions of Oracle and Sybase, the SQLCA should be
used in preference to the SQLSTATE variable. The SQLSTATE variable will
eventually supersede the SQLCA as the preferred method of passing data
between the database and the client application, but this is not yet the
Testing the value of sqlcode is the most common way of
determining the success or failure of an embedded SQL statement. The
possible values for
|0||The statement ran without error.|
|1||The statement ran, but a warning was generated. The values of the
|100||Data matching the query was not found or the end of the results set has been reached. No rows were processed.|
|< 0 (negative)||The statement did not run due to an application, database, system, or network error.|
If you are using OpenESQL, the following error codes are defined for SQLCODE.
||SQL(INIT) was used, and automatic CONNECT failed. Programs
which use SQL(INIT) need to check SQLCODE immediately on startup.
||ODBC driver or database specific error message. Check the
contents of SQLERRMC to determine what happened.
||Unable to retrieve ODBC error
||An ODBC error occurred, but no more details are available.
This usually indicates a serious run-time condition, such as severe
||Invalid ODBC catalog query
||This is caused by invalid parameters to a QUERY ODBC
||Statement too long
||ESQL Keyword(s) detected in PREPARE/EXECUTE IMMEDIATE statement
||Too few host variables
||Data overflow occurred during decimal data conversion
||NULL value returned but no indicator variable supplied
||No cursor declared
||Cursor is not prepared
|-19701||NULL connection name
Connection name not found
|These two errors (-19701 and -19702) occur when a
program refers to a connection which does not exist. The most likely
cause is attempting to execute an Embedded SQL statement before a
CONNECT has executed successfully, or after all connections have been
|-19702||Connection name not found
Attempt to close non-existent connection
||Could not make connection.
||Duplicate connection name.
||Improperly initialized User SQLDA
||Statement text not found or empty
||Unimplemented embedded SQL feature
||The COBOL compiler may accept some Embedded SQL syntax
which is not yet supported by the OpenESQL run-time module. If an
attempt is made to execute such a statement, this condition will result.
COBSQL and DB2
For COBSQL and DB2, it is possible to get other positive values. This means that the SQL statement has executed but produced a warning.
sqlwarnflags should be checked to determine the type of warning. For Oracle, Sybase and Informix,
sqlwarn0will always be set when the database server has sent a warning back to the application.
The SQLSTATE variable was introduced in the SQL-92 standard and is the recommended mechanism for future applications. It is divided into two components:
classcode. Any class code that begins with the letters A through H or the digits 0 through 4 indicates a SQLSTATE value that is defined by the SQL standard or another standard.
A value of "00000" indicates that the previous embedded SQL statement executed successfully.
For specific details of the values returned in SQLSTATE when using Oracle, Sybase or Informix, refer to the relevant Database Error Messages manual.
Full details of SQLSTATE values are given below:
||Privilege not revoked
||Invalid connection string attribute
||Error in row
||Option value changed
||No rows updated or deleted
||More than one row updated or deleted
||Cancel treated as SQLFreeStmt with the
||Attempt to fetch before the result set returned the
||Wrong number of parameters
||Restricted data type attribute violation
||Invalid use of default parameter
||Unable to connect to data source
||Connection in use
||Connection not open
||Data source rejected establishment of connection
||Connection failure during transaction
||Communication link failure
||Insert value list does not match column list
||Degree of derived table does not match column list
||String data right truncation
||Indicator variable required but not supplied
||Numeric value out of range
||Error in assignment
||Datetime field overflow
||Division by zero
||String data, length mismatch
||Integrity constraint violation
||Invalid cursor state
||Invalid transaction state
||Invalid authorization specification
||Invalid cursor name
||Syntax error or access violation
||Duplicate cursor name
||Syntax error or access violation
||Driver does not support this function
||Data source name not found and no default driver
||Specified driver could not be loaded
||Driver's SQLAllocEnv failed
||Driver's SQLAllocConnect failed
||Driver's SQLSetConnect-Option failed
||No data source or driver specified; dialog prohibited
||Unable to load translation .dll file
||Data source name too long
||Driver name too long
||DRIVER keyword syntax error
||Trace file error
||Base table or view already exists
||Base table not found
||Index already exists
||Index not found
||Column already exists
||Column not found
||No default for column
||Memory allocation failure
||Invalid column number
||Program type out of range
||SQL data type out of range
||Invalid argument value
||Function sequence error
||Operation invalid at this time
||Invalid transaction operation code specified
||No cursor name available
||Invalid string or buffer length
||Descriptor type out of range
||Option type out of range
||Invalid parameter number
||Function type out of range
||Information type out of range
||Column type out of range
||Scope type out of range
||Nullable type out of range
||Uniqueness option type out of range
||Accuracy option type out of range
||Direction option out of range
||Invalid parameter type
||Fetch type out of range
||Row value out of range
||Concurrency option out of range
||Invalid cursor position
||Invalid driver completion
||Invalid bookmark value
||Driver not capable
DB2 Universal Database returns SQL-92 compliant SQLSTATE values. DB2 Version 2.1 does not.
Some statements may cause warnings to be generated. To determine the type of warning, your application should examine the contents of the SQLWARN flags.
Each flag returns one of the following values:
Each SQLWARN flag has a specific meaning. For more information on the meaning of the SQLWARN flags, refer to the section SQL Communications Area.
To check explicitly the value of SQLCODE or SQLSTATE after each embedded SQL statement can involve writing a lot of code. As an alternative, check the status of the SQL statement by using a WHENEVER statement in your application.
The WHENEVER statement is not an executable statement. The WHENEVER statement is a directive to the Compiler to generate automatically the code that handles errors after each executable embedded SQL statement.
The WHENEVER statement allows one of three default actions (CONTINUE, GOTO or PERFORM) to be registered for each of the following conditions:
|Condition||Value of sqlcode|
|SQLERROR||< 0 (negative)|
A WHENEVER statement for a particular condition replaces all previous WHENEVER statements for that condition.
The scope of a WHENEVER statement is related to its physical position in the source program, not its logical position in the run sequence. For example, in the following code if the first SELECT statement does not return anything, paragraph A is performed, not paragraph C:
EXEC SQL WHENEVER NOT FOUND PERFORM A END-EXEC. PERFORM B. EXEC SQL SELECT col1 into :host-var1 FROM table1 WHERE col2 = :host-var2 END-EXEC. A. DISPLAY "First item not found". B. EXEC SQL WHENEVER NOT FOUND PERFORM C. END-EXEC. C. DISPLAY "Second item not found".
For Oracle, Sybase and Informix, setting SQLWARN0 to W triggers the SQLWARNING clause.
When no data is returned from a SELECT or FETCH statement, the condition NOT FOUND is triggered, regardless of the setting of the Oracle precompiler directive MODE.
Informix allows you to perform a STOP or a CALL from within a WHENEVER statement. These are additions to the ANSI standard and are documented in the Informix ESQL/COBOL programmers manual.
The SQLERRM data area is used to pass error messages to the application from the database server. The SQLERRM data area is split into two parts:
SQLERRML holds the length of the error message
SQLERRMC holds the error text.
Within an error routine, the following code can be used to display the SQL error message:
IF (SQLERRML > ZERO) and (SQLERRML < 80) DISPLAY 'Error Message: ', SQLERRMC(1:SQLERRML) ELSE DISPLAY 'Error Message: ', SQLERRMC END-IF.
SQLERRD data area is an array of six integer status
Oracle, Sybase and Informix may set one (or more) of the six values within the SQLERRD array. These indicate how many rows were affected by the SQL statement just executed. For example, SQLERRD(3) holds the total number of rows returned by a SELECT or a series of FETCH statements.
The third element of
SQLERRD in the SQLCA,
records the number of rows processed for INSERT, UPDATE, DELETE and SELECT
INTO statements. For FETCH statements, it records the cumulative sum of
For DB2, SQLERRD(3) contains the following:
For DB2, SQLERRD(4) contains the following:
For DB2, SQLERRD(5) contains the following:
The SQLDA is unique to each precompiler. The Oracle SQLDA is not compatible with that used by Sybase, OpenESQL or DB2 and vice versa.
When either the number of parameters to be passed, or their data types, are unknown at compilation time, you can use an SQL Descriptor Area (SQLDA) instead of host variables.
An SQLDA contains descriptive information about each input parameter or output column. It contains the column name, data type, length, and a pointer to the actual data buffer for each input or output parameter. An SQLDA is ordinarily used with parameter markers to specify input values for prepared SQL statements but you can also use an SQLDA with the DESCRIBE statement (or the INTO option of a PREPARE statement) to receive data from a prepared SELECT statement.
Although you cannot use an SQLDA with static SQL statements, you can use a SQLDA with a cursor FETCH statement.
The following table describes the contents of the SQLDA data structure.
||The text string SQLDA.
||Length of the SQLDA data structure (SQLN * 44 + 16).
||Total number of SQLVAR entries allocated, equal to the number of
input parameters or output columns.
||Number of SQLVAR entries used.
||SQLVAR is a group item, the number of occurrences of which depends
on the value of SQLD.
||A number representing the data type of the column or host variable
and indicating whether null values are allowed (see the table below for
||Length of a value from a column. If the data is decimal (including
money), SQLLEN is split into two parts: the first byte contains the
precision; the second byte contains the scale.
||For FETCH, OPEN, and EXECUTE, the address of the host variable
(must be inserted by the application). For DESCRIBE and PREPARE, SQLDATA
is not used.
||For FETCH, OPEN, and EXECUTE, the address of an associated
indicator variable, if one exists. If the column does not permit a null
value, the field is undefined. If the column permits a null value,
SQLIND is set to -1 if the data value is null or to 0 if the data value
is not null. For DESCRIBE and PREPARE, SQLIND is not used.
||A group item containing the name and length of the column (not used
for FETCH, OPEN or EXECUTE).
||Length of the name column
||Name of the column. For a derived column, this field contains the
ASCII numeric literal value that represents the derived column's
original position within the select list
For Oracle, Sybase and Informix, the SQLDA is only required if your program uses dynamic SQL.
Oracle, Sybase and Informix do not allow the SQLDA to be included in your program using the following syntax statement:
EXEC SQL INCLUDE SQLDA END-EXEC
For Oracle, Sybase and Informix, the SQLDA must be defined as a standard COBOL copyfile.
Oracle provides an extra copyfile, ORACA, for use with dynamic SQL. This can be included in your program using the following syntax:
EXEC SQL INCLUDE ORACA END-EXEC
You must set the Oracle precompiler option, ORACA=YES before you can use the ORACA copyfile. For more information on setting Oracle precompiler options, refer to the Programmer's Guide to the Oracle Precompilers.
Oracle does not supply an SQLDA. For a clearer explanation of this and the ORACA copyfile, refer to the Programmer's Guide to the Oracle Precompilers.
Sybase does not supply an SQLDA copyfile. The Sybase precompiler documentation describes the layout of the SQLDA and how to assign values to the various items within it. The documentation also describes how to get Sybase to convert between COBOL and Sybase data types.
Informix does not supply an SQLDA copyfile. The Informix precompiler documentation describes the layout of the data items that need to be defined to be able to use Dynamic SQL with Informix.
The SQLDA structure is supplied in the file sqlda.cpy in the source directory under your Server Express base installation directory. You can include it in your COBOL program by adding the following statement to your Data Division:
EXEC SQL INCLUDE SQLDA END-EXEC
The SQLDA data structure is shown below:
01 SQLDA sync. 05 SQLDAID PIC X(8) VALUE "SQLDA ". 05 SQLDABC PIC S9(9) COMP-5 value 0. 05 SQLN PIC S9(4) COMP-5 value 0. 05 SQLD PIC S9(9) COMP-5 value 0. 05 SQLVAR OCCURS 0 to 1489 TIMES DEPENDING ON SQLN. 10 SQLTYPE PIC S9(4) COMP-5. 10 SQLLEN PIC S9(4) COMP-5. 10 SQLDATA USAGE POINTER. 10 SQLIND USAGE POINTER. 10 SQLNAME. 15 SQLNAMEL PIC S9(4) COMP-5. 15 SQLNAMEC PIC X(30).
Odd-numbered code values indicate that null values are allowed.
In the table below:
(1) - These types can be returned in COBOL by a PREPARE INTO or DESCRIBE statement.
(2) - These types can be set by an application using Dynamic SQL.
(3) - These types are supported for COBOL host variables.
||SQL Data Type
||COBOL Data Type
||10-byte date string
||8-byte time string
||26-byte timestamp string
||Large variable length binary
49 PIC LEN S9(9) COMP-5
49 PIC VAL X(n)
||Large variable length character
49 PIC LEN S9(9) COMP-5
49 PIC VAL X(n)
||Variable length binary
49 PIC LEN S9(4) COMP-5
49 PIC VAL X(n)
||Variable length character
49 PIC LEN S9(4) COMP-5
49 PIC VAL X(n)
||8-byte floating point
||float or double
||4-byte floating point
||decimal, numeric or bigint
||PIC S9(9) COMP-5
||PIC S9(4) COMP-5
||PIC S9(4) COMP-5
Before an SQLDA structure is used, your application must initialize the following fields:
|SQLN||This must be set to the maximum number of SQLVAR entries that the structure can hold.|
|SQLDABC||The maximum size of the SQLDA. This is calculated as SQLN * 44 + 16|
You can use the DESCRIBE statement (or the PREPARE statement with the INTO option) to enter the column name, data type, and other data into the appropriate fields of the SQLDA structure.
Before the statement is executed, the SQLN and SQLDABC fields should be initialized as described above.
After the statement has been executed, the SQLD field will contain the number of parameters in the prepared statement. A SQLVAR record is set up for each of the parameters with the SQLTYPE and SQLLEN fields completed.
If you do not know how big the value of SQLN should be, you can issue a DESCRIBE statement with SQLN set to 1 and SQLD set to 0. No column detail information is moved into the SQLDA structure, but the number of columns in the results set is inserted into SQLD.
Before performing a FETCH statement using an SQLDA structure, follow the procedure below:
The data type field (SQLTYPE) and length (SQLLEN) are filled with information from a PREPARE INTO or a DESCRIBE statement. These values can be overwritten by the application prior to a FETCH statement.
To use an SQLDA structure to specify input data to an OPEN or EXECUTE statement, your application must supply the data for the fields of the entire SQLDA structure, including the SQLN, SQLD, SQLDABC, and SQLTYPE, SQLLEN, and SQLDATA fields for each variable.
If the value of the SQLTYPE field is an odd number, the address of the indicator variable must also be supplied in SQLIND.
After a PREPARE statement, you can execute a DESCRIBE statement to retrieve information about the data type, length and column name of each column returned by the specified prepared statement. This information is returned in the SQL Descriptor Area (SQLDA):
EXEC SQL DESCRIBE stmt1 INTO :sqlda END-EXEC
If you want to execute a DESCRIBE statement immediately after a PREPARE statement, you can use the INTO option on the PREPARE statement to perform both steps at once:
EXEC SQL PREPARE stmt1 INTO :sqlda FROM :stmtbuf END-EXEC
Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.