PreviousCursors Dynamic SQLNext

Chapter 5: Data Structures

All of the Server Express SQL preprocessors make use of the SQL Communications Area (SQLCA) and the SQL Descriptor Area (SQLDA) data structures.

5.1 SQL Communications Area

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:

Field
Contents
SQLCAID
The text string "SQLCA".
SQLCABC
The length of the SQLCA data structure.
SQLCODE
The status code for the last-run SQL statement.
SQLERRML
The length of the error message in SQLERRMC (0 through 70).
SQLERRMC
Error message text. Error messages longer than 70 bytes are truncated.
SQLERRP
Reserved (diagnostic information).
SQLERRD
An array of six integer status codes (those not listed below are reserved):
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.
SQLWARN:
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:
SQLWARN0
A summary of all warning fields. Blank means there are no warnings.
SQLWARN1
"W" indicates that data was truncated on output to a character host variable.
SQLWARN2
"W" indicates that a null value exists, but no indicator variable was provided.
SQLWARN3
"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.
SQLWARN4
"W" indicates a singleton select that returns more than one row (only the first row is returned).
FILLER
Reserved.
SQLSTATE
Status indicator for the last-run SQL statement.

The SQLCA contains two variables (sqlcode and sqlstate) plus a number of warning flags which are used to indicate whether an error has occurred in the most recently executed SQL statement.

COBSQL
For COBSQL, 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 case.

5.1.1 The SQLCODE Variable

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 sqlcode are:

Value
Meaning
0 The statement ran without error.
1 The statement ran, but a warning was generated. The values of the sqlwarn flags should be checked to determine the type of error.
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.

OpenESQL
If you are using OpenESQL, the following error codes are defined for SQLCODE.

SQLCODE
Message
Meaning
-1
Autoconnect failure
SQL(INIT) was used, and automatic CONNECT failed. Programs which use SQL(INIT) need to check SQLCODE immediately on startup.
<19000
ODBC driver or database specific error message. Check the contents of SQLERRMC to determine what happened.
Or
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 memory shortage.
-19085
Invalid ODBC catalog query
This is caused by invalid parameters to a QUERY ODBC statement.
-19101
Statement too long

-19199
ESQL Keyword(s) detected in PREPARE/EXECUTE IMMEDIATE statement

-19313
Too few host variables

-19413
Data overflow occurred during decimal data conversion

-19425
NULL value returned but no indicator variable supplied

-19501
No cursor declared

-19514
Cursor is not prepared

-19701 NULL connection name
or
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 disconnected.
-19702 Connection name not found
or
Attempt to close non-existent connection
-19703
Could not make connection.

-19707
Duplicate connection name.

-19822
Improperly initialized User SQLDA

-19957
Statement text not found or empty

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

COBSQL

5.1.2 The SQLSTATE Variable

The SQLSTATE variable was introduced in the SQL-92 standard and is the recommended mechanism for future applications. It is divided into two components:

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:

SQLSTATE
Error
01000
General warning
01002
Disconnect error
01004
Data truncated
01006
Privilege not revoked
01S00
Invalid connection string attribute
01S01
Error in row
01S02
Option value changed
01S03
No rows updated or deleted
01S04
More than one row updated or deleted
01S05
Cancel treated as SQLFreeStmt with the SQL_CLOSE option
01S06
Attempt to fetch before the result set returned the first rowset


07001
Wrong number of parameters
07006
Restricted data type attribute violation
07S01
Invalid use of default parameter


08001
Unable to connect to data source
08002
Connection in use
08003
Connection not open
08004
Data source rejected establishment of connection
08007
Connection failure during transaction
08S01
Communication link failure


21S01
Insert value list does not match column list
21S02
Degree of derived table does not match column list


22001
String data right truncation
22002
Indicator variable required but not supplied
22003
Numeric value out of range
22005
Error in assignment
22008
Datetime field overflow
22012
Division by zero
22026
String data, length mismatch


23000
Integrity constraint violation


24000
Invalid cursor state


25000
Invalid transaction state


28000
Invalid authorization specification


34000
Invalid cursor name


37000
Syntax error or access violation


3C000
Duplicate cursor name


42000
Syntax error or access violation


70100
Operation aborted


IM001
Driver does not support this function
IM002
Data source name not found and no default driver specified
IM003
Specified driver could not be loaded
IM004
Driver's SQLAllocEnv failed
IM005
Driver's SQLAllocConnect failed
IM006
Driver's SQLSetConnect-Option failed
IM007
No data source or driver specified; dialog prohibited
IM008
Dialog failed
IM009
Unable to load translation .dll file
IM010
Data source name too long
IM011
Driver name too long
IM012
DRIVER keyword syntax error
IM013
Trace file error


S0001
Base table or view already exists
S0002
Base table not found
S0011
Index already exists
S0012
Index not found
S0021
Column already exists
S0022
Column not found
S0023
No default for column


S1000
General error
S1001
Memory allocation failure
S1002
Invalid column number
S1003
Program type out of range
S1004
SQL data type out of range
S1008
Operation canceled
S1009
Invalid argument value
S1010
Function sequence error
S1011
Operation invalid at this time
S1012
Invalid transaction operation code specified
S1015
No cursor name available
S1090
Invalid string or buffer length
S1091
Descriptor type out of range
S1092
Option type out of range
S1093
Invalid parameter number
S1095
Function type out of range
S1096
Information type out of range
S1097
Column type out of range
S1098
Scope type out of range
S1099
Nullable type out of range
S1100
Uniqueness option type out of range
S1101
Accuracy option type out of range
S1103
Direction option out of range
S1105
Invalid parameter type
S1106
Fetch type out of range
S1107
Row value out of range
S1108
Concurrency option out of range
S1109
Invalid cursor position
S1110
Invalid driver completion
S1111
Invalid bookmark value
S1C00
Driver not capable
S1T00
Timeout expired

DB2
DB2 Universal Database returns SQL-92 compliant SQLSTATE values. DB2 Version 2.1 does not.

5.1.3 The Warning Flags

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.

5.1.4 The WHENEVER Statement

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
NOT FOUND 100
SQLWARNING +1
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".

5.1.4.1 COBSQL

For Oracle, Sybase and Informix, setting SQLWARN0 to W triggers the SQLWARNING clause.

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

5.1.5 SQLERRM

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:

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.

5.1.6 SQLERRD

The SQLERRD data area is an array of six integer status values.

5.1.6.1 COBSQL


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.

5.1.6.2 DB2

The third element of SQLERRD in the SQLCA, SQLERRD(3), records the number of rows processed for INSERT, UPDATE, DELETE and SELECT INTO statements. For FETCH statements, it records the cumulative sum of rows processed.

SQLERRD(3)
For DB2, SQLERRD(3) contains the following:

SQLERRD(4)
For DB2, SQLERRD(4) contains the following:

SQLERRD(5)
For DB2, SQLERRD(5) contains the following:

5.2 The SQL Descriptor Area (SQLDA)

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.

Field
Contains
SQLDAID
The text string “SQLDA“.
SQLDABC
Length of the SQLDA data structure (SQLN * 44 + 16).
SQLN
Total number of SQLVAR entries allocated, equal to the number of input parameters or output columns.
SQLD
Number of SQLVAR entries used.
SQLVAR
SQLVAR is a group item, the number of occurrences of which depends on the value of SQLD.
SQLTYPE
A number representing the data type of the column or host variable and indicating whether null values are allowed (see the table below for valid values).
SQLLEN
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.
SQLDATA
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.
SQLIND
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.
SQLNAME
A group item containing the name and length of the column (not used for FETCH, OPEN or EXECUTE).
SQLNAMEL
Length of the name column
SQLNAMEC
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

5.2.1 COBSQL

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.

5.2.1.1 Oracle

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.

5.2.1.2 Sybase

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.

5.2.1.3 Informix

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.

5.2.2 OpenESQL

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

5.2.3 Valid Values for SQLTYPE

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.

Code
Data Type
SQL Data Type
COBOL Data Type
384/385 (2)
10-byte date string
date
PIC X(10)
386/387 (1)
Date
date

388/389 (2)
8-byte time string
time
PIC X(8)
390/391 (1)
Time
time

392/393 (2)
26-byte timestamp string
timestamp
PIC X(26)
394/395 (1)
Timestamp
timestamp

404/405 (1)
Large variable length binary
long varbinary
01 NAME
49 PIC LEN S9(9) COMP-5
49 PIC VAL X(n)
408/409 (1)
Large variable length character
long varchar
01 NAME
49 PIC LEN S9(9) COMP-5
49 PIC VAL X(n)
444/445 (1,2)
Binary
binary
PIC X(n)
446/447 (1,2)
Variable length binary
varbinary 01 NAME
49 PIC LEN S9(4) COMP-5
49 PIC VAL X(n)
448/449 (1,2,3)
Variable length character
varchar 01 NAME
49 PIC LEN S9(4) COMP-5
49 PIC VAL X(n)
452/453 (1,2,3)
Fixed-length character
char
PIC X(n)
480/481 (1,2,3)
8-byte floating point
float or double
COMP-2
482/483 (1,2)
4-byte floating point
real
COMP-1
484/485 (1,2,3)
Decimal
decimal, numeric or bigint
PIC 9(n)V9(m)
COMP-3
496/497 (1,2,3)
4-byte integer
integer
PIC S9(9) COMP-5
500/501 (1,2,3)
2-byte integer
smallint
PIC S9(4) COMP-5
502/503 (1,2)
1-byte integer
tinyint
PIC S9(4) COMP-5

5.2.4 Using the SQLDA

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

5.2.4.1 The PREPARE and DESCRIBE Statements

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.

5.2.4.2 The FETCH Statement

Before performing a FETCH statement using an SQLDA structure, follow the procedure below:

  1. The application must initialize SQLN and SQLDABC as described above.

  2. The application must then insert, into the SQLDATA field, the address of each program variable that will receive the data from the corresponding column. (The SQLDATA field is part of SQLVAR).

  3. If indicator variables are used, SQLIND must also be set to the corresponding address of the indicator variable.

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.

5.2.4.3 The OPEN or EXECUTE Statements

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.

5.2.4.4 The DESCRIBE Statement

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.

PreviousCursors Dynamic SQLNext