Nonstop Database, HiRDB Version 9 UAP Development Guide
The figure below shows the basic SQL configuration in a UAP. This explanation assumes that the UAP is written in COBOL.
Figure 3-1 Basic SQL configuration in a UAP
The UAP must declare variables for transferring data between SQL and the UAP descriptive language so that the UAP can receive data retrieved by SQL statements and insert UAP data into SQL tables. Use embedded variables for this purpose. If a data item that includes a null value must be transferred, use an indicator variable along with the embedded variable for that item.
An example of declarations for embedded and indicator variables is shown as follows.
For details about how to specify embedded and indicator variables in SQL statements, see (5) Retrieval and update SQL (execution statements).
EXEC SQL BEGIN DECLARE SECTION ..........................1 END-EXEC. 77 XUSERID PIC X(7) ................................2 77 XPSWD PIC X(7) ................................2 77 XPCODE PIC X(4) ................................2 77 XPNAME PIC N(8) ................................2 77 XSTOCK PIC S9(9)COMP ...........................2 77 ISTOCK PIC S9(4)COMP ...........................3 EXEC SQL END DECLARE SECTION ...............................4 END-EXEC.
Explanation:
If the default value setting facility for null values is used, an embedded variable can accept a default value (0 for numerical data and a space for character data) in place of a null value when the retrieval result is a null value. When this facility is used, indicator variables do not have to be used if the default values and the null value do not have to be discriminated. For details about the default value setting facility for null values, see the HiRDB Version 9 SQL Reference manual.
This section reports the user's authorization identifier and password to HiRDB so that the UAP can use HiRDB. This is called connection with HiRDB. The SQL statements for connection with HiRDB are shown as follows:
EXEC SQL CONNECT :XUSERID IDENTIFIED BY :XPSWD END-EXEC.
Connects with HiRDB based on the authorization identifier stored in the embedded variable (:XUSERID) and the password stored in the embedded variable (:XPSWD).
This section uses the DECLARE CURSOR statement to declare the cursor that allows the UAP to extract multiple-row retrieval results one row at a time. Use the DECLARE CURSOR statement to retrieve, update, and delete data. To open the cursor, use the OPEN statement. To extract the retrieval results and move the cursor to the next line, use the FETCH statement. To close the cursor, use the CLOSE statement.
Embedded and indicator variables can be specified as retrieval condition values in the cursor declaration. If such variables are specified, the UAP passes the values in those variables to HiRDB when the OPEN statement for that cursor is executed.
For details about cursors, see 3.5 Use of a cursor.
The SQL statements for cursor declaration are shown as follows:
EXEC SQL DECLARE CR1 CURSOR FOR SELECT PCODE, PNAME, STOCK FROM STOCK END-EXEC.
Declares cursor CR1 for extracting PCODE, PNAME, and STOCK one row at a time from the STOCK table.
If a WHENEVER statement is specified before an SQL statement, the UAP can automatically determine whether an error occurred.
EXEC SQL WHENEVER SQLERROR GO TO error-handling-process END-EXEC.
EXEC SQL WHENEVER NOT FOUND GO TO retrieval-end-process END-EXEC.
A WHENEVER statement is effective for all SQL statements found between that WHENEVER statement and the next WHENEVER statement of the same type. For details about the effective range of the WHENEVER statement, see the HiRDB Version 9 SQL Reference manual.
In this section, specify SQL statements for retrieving, inserting, or deleting data. For details about how to specify the individual SQL statements, see 2. Database Operations.
This section explains how to use embedded and indicator variables.
Specify the embedded and indicator variables in the INTO clause of a 1-row SELECT or FETCH statement. Add a colon in front of each variable. Specify each indicator variable immediately after its corresponding embedded variable. An example is shown as follows:
The embedded variables that were specified in the INTO clause correspond to the column name sequence specified in the column lineup of the SELECT statement. The retrieval results are stored to the embedded variables according to this sequence.
If a retrieval result includes a null value, a negative value is stored in the indicator variable. You can, therefore, check the indicator variable value to determine whether the result is a null value. In this case, the value of the embedded variable is undefined. If the value of an indicator variable is 0, a value other than a null value was received. If the value is positive, character string data other than a null value was received but the right end was truncated, because the area length of the embedded variable was too short.
If an embedded variable is specified in a retrieval condition value, the retrieval condition value can be assigned during SQL execution.
Specify the embedded and indicator variables in the SET clause of an UPDATE statement or the VALUES clause of an INSERT statement. Add a colon in front of each variable. Specify each indicator variable immediately after its corresponding embedded variable. An example is shown as follows:
EXEC SQL UPDATE STOCK SET STOCK=:XSTOCK:ISTOCK WHERE PCODE=:XPCODE END-EXEC.
EXEC SQL INSERT INTO STOCK VALUES(:XPCODE,:XPNAME,:XCOLOR,:XPRICE, :XSTOCK:ISTOCK,:XSTOCK_CAPACITY,:XREQSTOCK) END-EXEC.
If the UPDATE or INSERT statement sets a null value in a table, specify a negative value in the indicator value before executing that SQL. No setting value is necessary for the embedded function. When passing a non-null value, set the indicator variable value to 0 or a positive value.
If an error occurs during SQL execution, the UAP checks SQLCODE and SQLSTATE to determine the return codes returned by HiRDB. The UAP uses the return codes to specify which process should then be executed. However, if an error-handling process has already been specified in Section (4) Error-handling process specification, the same process does not have to be specified in this section.
Do not execute error identification immediately after a declaration statement, such as DECLARE CURSOR. If error identification is executed, the UAP references an incorrect SQLCODE, and HiRDB malfunctions.
For details about error identification, see 3.6.1 Error identification.
If update processing was executed in a transaction, this section validates the updated database contents and terminates the transaction normally.
The SQL statements for validating a transaction are shown as follows:
EXEC SQL COMMIT END-EXEC.
Validates a transaction; to release the UAP from HiRDB after validating the transaction, specify RELEASE in the COMMIT statement, and execute the statement. If RELEASE is specified, the DISCONNECT statement does not have to be executed.
This section invalidates the database contents that were updated in a transaction and terminates the transaction. Specify this section to cancel a database update if the update processing in a translation is invalid.
The SQL statements for invalidating a transaction are shown as follows:
EXEC SQL ROLLBACK END-EXEC.
Invalidates a transaction; to release the UAP from HiRDB after terminating the transaction, specify RELEASE in the ROLLBACK statement, and execute the statement. If RELEASE is specified, the DISCONNECT statement does not have to be executed.
This section terminates a transaction normally and releases the UAP from HiRDB. The DISCONNECT statement executes the same processing executed by a COMMIT statement in which RELEASE is specified.
The SQL statements for terminating a transaction normally and releasing the UAP from HiRDB are shown as follows:
EXEC SQL DISCONNECT END-EXEC.
Terminates a transaction normally and releases the UAP from HiRDB. To cancel a transaction and then release the UAP from HiRDB, execute a ROLLBACK statement where RELEASE is specified.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.