![]() |
![]() |
|
|
![]() |
![]() |
ALLBASE/SQL Pascal Application Programming Guide: HP 9000 Computer Systems > Chapter 5 Runtime Status Checking and the SQLCA![]() Using the SQLCA |
|
The SQLCA is used for communicating information between the application program and ALLBASE/SQL. SQL places information in the SQLCA each time it is called. Since there is no guarantee that information from one call to SQL will be present after the next call to SQL, any information needed from the SQLCA must be obtained after each call to ALLBASE/SQL. Every ALLBASE/SQL Pascal main program must have the SQLCA declared in the global declaration section. You can use the INCLUDE command to declare the SQLCA:
When the Pascal preprocessor parses this command, it inserts the following type definition into the modified source file:
You can also use this type definition in the global declaration section of your source file instead of using the INCLUDE command to declare the SQLCA. The Pascal preprocessor generates the following record declaration for sqlca_type in the type include file. This portion of the type include file contains some conditional statements. The entire type include file can be found in the chapter, "Using the ALLBASE/SQL Pascal Preprocessor." (It is recommended that you initialize the SqlcaId element to blanks, one time, before the first SQL statement in your program.)
The following elements in this record are available for you to use in status checking. The other elements are reserved for use by ALLBASE/SQL only.
In conformance with XOPEN standards, SQLCODE can be used to address this particular element, and each SQLWARN element can be addressed without the use of square brackets. If you choose to use XOPEN standards addressing, you must include the following compiler directive in your source code:
(Note, use this compiler directive only if you are using XOPEN standards addressing.) The SQLCA must be passed whenever you call a subprogram that executes SQL commands. The recommended method of doing so is to declare the SQLCA globally in the main program. This is true even though your main program contains no other SQL statement. The SQLCA must be a VAR parameter to these subprograms in order to save space and attain the best performance.
Table 5-1 SQLCA Status Checking Fields
SQLCODE can contain one of the following values:
Note that when you prepare and execute UPDATE or DELETE commands and no rows qualify for the operation, SQLCODE is not set to 100. You can use SQLCA.SQLERRD[3] to detect this condition, as discussed later in this chapter. Negative SQLCODE values are the same as the numbers associated with their corresponding messages in the ALLBASE/SQL message catalog. For example, the error message associated with an SQLCODE of -2613 is:
SQLCODE is set by all SQL commands except the following directives:
When SQLCODE is -4008, -14024, or a greater negative value than -14024, ALLBASE/SQL automatically rolls back the current transaction. When this condition occurs, ALLBASE/SQL also sets SQLWARN[6] to W. Refer to the discussion later in this chapter on SQLWARN[6] for more on this topic. More than one SQLCODE is returned when more than one error occurs. For example, if you attempt to execute the following SQL command, two negative SQLCODE values result:
The following SQLCODES associated with the two errors are: ———-2308, which indicates the reserved name PUBLIC is invalid. ———-2318, which indicates you cannot add a group to itself. To obtain all SQLCODEs associated with the execution of an SQL command, you execute the SQLEXPLAIN command until SQLCODE is 0:
The procedure named SQLStatusCheck is executed when SQLCODE is a negative number. Before executing SQLEXPLAIN for the first time, the program has access to the first SQLCODE returned. Each time SQLEXPLAIN is executed subsequently, the next SQLCODE becomes available to the program, and so on until SQLCODE equals 0. This example explicitly tests the value of SQLCODE twice: first to determine whether it is equal to 100, then to determine whether it is <0. If the value 100 exists, no error will have occurred and the program will display the message, "No rows qualify for this operation." It is necessary for the program to display its own message in this case because SQLEXPLAIN messages are available to your program only when SQLCODE contains a negative number or when SQLWARN[0] contains a W. The SQLCODE is also used in implicit status checking in the following situations:
In the following situation, when ALLBASE/SQL detects a negative SQLCODE, the code routine at label 2000 is executed. When ALLBASE/SQL detects an SQLCODE of 100, the code routine at label 4000 is executed instead, as follows:
WHENEVER commands remain in effect for all SQL commands that appear physically after them in the source program until another WHENEVER command for the same condition occurs. The scope of WHENEVER commands is fully explained later in this chapter under "Implicit Status Checking Techniques." SQLERRD[3] can contain one of the following values:
A W in SQLWARN[0] in conjunction with a 0 in SQLCODE indicates that the SQL command just executed caused a warning condition. Warning conditions flag unusual but not necessarily important conditions. For example, if a program attempts to submit an SQL command that grants an already existing authority, a message such as the following would be retrieved when SQLEXPLAIN is executed:
In the case of the following warning, the situation may or may not indicate a problem:
This warning occurs when a program submits a RELEASE command without first terminating a transaction with a COMMIT WORK or ROLLBACK WORK. If the transaction did not perform any UPDATE, INSERT, or DELETE operations, this situation will not cause work to be lost. If the transaction did perform UPDATE, INSERT, or DELETE operations, the database changes are rolled back when the RELEASE command is processed. You retrieve the appropriate warning message by using SQLEXPLAIN. Note that you cannot explicitly test sqlwarn[0] the way you can test SQLCODE, since sqlwarn[0] always contains W when a warning occurs. An error and a warning condition may exist at the same time. In this event, SQLCODE is set to a negative number, and sqlwarn[0] is set to W. Messages describing all the warnings and errors can be displayed as follows:
If multiple warnings but no errors result when ALLBASE/SQL processes a command, SQLWARN[0] is set to W and remains set until the last warning message has been retrieved by SQLEXPLAIN or another SQL command is executed. In the following example, DisplayWarning is executed when this condition exists.
When you use the SQLWARNING option of the WHENEVER command, ALLBASE/SQL checks for a W in SQLWARN[0]. You can use the WHENEVER command to do implicit status checking (equivalent to that done explicitly above) as follows:
A W in sqlwarn[1] indicates truncation of at least one character string value when the string was stored in a host variable. Any associated indicator variable is set to the value of the string length before truncation, for example: For example:
If PartName was declared as a character array of 20 bytes, and the PartName column in the PurchDB.Parts table has a length of 30 bytes, then SQL performs the following tasks:
A W in sqlwarn[2] indicates that at least one null value was eliminated from the argument set of an aggregrate function. For example:
If any OrderQty values are null:
A W in sqlwarn[3] indicates that the number of columns specified in a dynamic SELECT or FETCH statement is unequal to the number of columns indicated in the sqld field of the SQLDA. Under normal circumstances, this error does not occur, because the DESCRIBE command sets the sqld field correctly. Look at this example:
The FETCH will fail and ALLBASE/SQL performs the following tasks:
When an error occurs that causes ALLBASE/SQL to roll back the current transaction, SQLWARN[6] is set to W. ALLBASE/SQL automatically rolls back transactions when SQLCODE is equal to -4008, or equal to or less than -14024. When such errors occur, ALLBASE/SQL does the following:
If you want to terminate your program any time ALLBASE/SQL has to roll back the current transaction, you can just test sqlwarn[6].
In this example, the program executes procedure SQLStatusCheck when an error occurs. The program terminates whenever ALLBASE/SQL has rolled back a transaction, but continues if an error has occurred but was not serious enough to cause transaction roll back. |
![]() |
||
![]() |
![]() |
![]() |
|||||||||
|