CONNX Data Integration Suite 14.8.0 | CONNX Embedded SQL for Adabas | Programming Guide - General Concepts | SQL Programming - General Concepts | The SQL Communications Area (SQLCA)
 
The SQL Communications Area (SQLCA)
Any application program needs to be able to check the success or failure of any particular SQL statement once the statement has been executed. At least one special host variable structure needs to be declared in the program, so that there is always one in scope for each SQL statement. For this purpose, a host variable structure, called SQL communication area (SQLCA) is used. Embedded SQL for Adabas updates certain fields of the structure depending on the nature of the particular SQL statement and the outcome of its execution. The application program can verify the successful execution of an SQL statement by inspecting the contents of the sqlcode element of the SQLCA.
Declaring the SQLCA
As stated above, the SQLCA is a special type of host variable structure. In order to ensure that the structure has the correct format, the application program should use the definition of the SQLCA provided by the Adabas SQL Gateway Embedded SQL. To facilitate this, the following SQL statement should be embedded in the application program:
INCLUDE SQLCA;
Executing this statement has the effect of generating an appropriate SQLCA definition and declaration at the point where it is specified. Thus, the SQLCA obeys the rules of scoping set by the host language relative to the position of the INCLUDE SQLCA statement.
Application programs can explicitly declare an SQLCA without using the INCLUDE statement. It is then the responsibility of the programmer to ensure that the structure is correctly defined and declared. Failure to do so may lead to unpredictable results.
Using the SQLCA
Once the SQL statement execution has completed, the application program should check the SQLCODE field of the SQLCA. The program logic should then be in a position to deal with any eventuality. This may be done for every SQL statement. However, by using the precompiler directive WHENEVER, such coding can be generated automatically.
Currently, not all fields in the SQLCA are used.
The following statements do not result in any update to the SQLCA:
* DECLARE CURSOR
* BEGIN DECLARE SECTION
* END DECLARE SECTION
* WHENEVER
* INCLUDE
Field
Description
sqlcaid
An eight-byte character string containing the constant SQLCA. This field serves mainly as an eye-catcher for easy memory dump interpretation.
sqlcabc
A four-byte integer variable containing the length in bytes of the SQLCA. It normally contains the value 136.
sqlcode
A four-byte integer variable containing the status of the executed SQL command. The standard defines four categories of results:
*zero - The command has been successfully executed. (There may have been warning messages).
*negative - An error has occurred. The negative number indicates the nature of the error. Adabas SQL Gateway Embedded SQL allows the installation to define its own error values. Thus, compatibility with different SQL DBMSs can be achieved. (The ANSI/ISO standard does not specify which negative values should be used with a particular error status). When a negative code is returned, the SQLERROR condition of the WHENEVER statement is activated.
*positive - The command executed successfully, but an exceptional condition occurred.
*+100 - This value is returned to indicate that the command was successfully executed but processed no rows. It is used in conjunction with the following commands: DELETE, FETCH, INSERT, SELECT, UPDATE.
sqlerrm
A variable containing two fields holding the actual values to replace the variables contained in error messages.
*sqlerrml - A two-byte integer field. This field is currently not used.
*sqlerrmc - A character string of variable length which may not exceed 70 characters. This field is currently not used.
The string contains one or more actual values for the variables of the associated error messages. As many error messages contain no text variables, this field is not always filled.
Each value in the string is terminated by one byte containing the hex value FF.
sqlerrp
An eight-byte character variable. This field is currently not used.
sqlerrd1 - 6
A group of six integer fields, each four bytes in length.
*sqlerrd1 - Currently not used.
*sqlerrd2 - Currently not used.
*sqlerrd3 - Specifies how many rows were processed by the SQL statement.
*sqlerrd4 - Currently not used.
*sqlerrd5 - Currently not used.
*sqlerrd6 - Currently not used.
sqlwarn0 - 7
A group of eight character variables, each one byte in length. The default content is blank. This set of fields is currently not used.
sqlext
An eight-byte character string. This field is currently not used.