| Cursors | Dynamic SQL | |
All of the NetExpress 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).
Full details of the layout of the SQLCA data structure are given in the online help file. Look under SQLCA in the help file index.
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.
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. |
Full details of SQLCODE values are given in the online help file. Look under "SQLCODE" in the help file index.
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
For details about the range of positive values that SQLCODE can be set to,
consult your Oracle or Sybase Error Messages manual.
COBSQL
The value +100 is the ANSI standard for 'data not found'. Oracle can return
another value for 'data not found'. To get Oracle to return the value +100
for 'data not found', the Oracle precompiler directive MODE=ANSI must be set.
This will affect other aspects of the way the Oracle precompiler handles SQL
statements. For more details on the Oracle precompiler MODE directive, refer
to the Programmer's Guide to the ORACLE Precompilers.
COBSQL
Even when SQLCODE contains zero, a warning may have been generated. The
values of the sqlwarn flags should be checked to determine the
type of warning. For both Oracle and Sybase, sqlwarn0 will
always be set when the database server has sent a warning back to the
application.
DB2
DB2 Universal Database returns SQL-92 compliant SQLSTATE values. DB2 Version
2.1 does not.
The sqlstate variable was introduced in the SQL-92 standard
and is the recommended mechanism for future applications. It is divided into
two components:
class code. 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.
subclass code.A value of "00000" indicates that the previous embedded SQL statement executed successfully.
Full details of SQLSTATE values are given in the online file. Look under "SQLSTATE" in the help file index.
Some statements may cause warnings to be generated. To determine the type
of warning, your application should examine the contents of the sqlwarn
flags. The value of the flag will be set to "W" if that particular
warning occured, otherwise the value will be a blank (space).
Each sqlwarn flag has a specific meaning. For more
information on the meaning of the sqlwarn flags, refer to the
online help - look under "SQLCA" in the help file index.
Explicitly checking the value of sqlcode or sqlstate
after each embedded SQL statement can involve writing a lot of code; an
alternative is to check the status of the SQL statement by using a WHENEVER
statement in your application.
The WHENEVER statement is not an executable statement; it is a directive to the Compiler to generate automatically 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) |
COBSQL
For Oracle, the 'NOT FOUND' condition will always be triggered when no data
is returned from a SELECT or FETCH statement, regardless of the setting of
the Oracle precompiler directive, MODE.
COBSQL
For Oracle and Sybase, the 'SQLWARNING' clause will be triggered when
sqlwarn0is set to 'W'.
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".
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 and SQLERRMC.
SQLERRML holds the length of the error message and 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.
The SQLERRD data area is an array of six integer status
values.
COBSQL
Oracle and Sybase may set one (or more) of the six values within the SQLERRD
array. These indicate how may rows were effected 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 SQLDA is unique to each precompiler. The Oracle SQLDA is not compatible with that used by Sybase, OpenESQL or DB2 and vice versa.
COBSQL
For both Oracle and Sybase, the SQLDA is only required if your program uses
dynamic SQL.
COBSQL
Neither Oracle nor Sybase allow the SQLDA to be included in your program
using the following syntax:
EXEC SQL INCLUDE SQLDA END-EXEC
statement. For both, the SQLDA must be defined as a standard COBOL copyfile.
COBSQL
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. For more information on setting Oracle precompiler options, refer to the Programmer's Guide to the Oracle Precompilers.
COBSQL
Oracle does not supply an SQLDA but the Programmer's Guide to the Oracle
Precompilers contains a definition of the layout.
COBSQL
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 documentaion also describes how to get
Sybase to convert between COBOL and Sybase data types.
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.
OpenESQL
The SQLDA structure is supplied in the file sqlda.cpy in the source
directory under your NetExpress 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
Full details of the OpenESQL SQLDA are given in the online help file. Look under "SQLDA" in the help file index.
Before an SQLDA structure is used, your application must initialise 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 initialised 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, the application must initialize SQLN and SQLDABC as described above. It 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). 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.
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 © 1998 Micro Focus Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Cursors | Dynamic SQL | |