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:

1. Indicates the beginning of the embedded variable declaration section.
2. Declares an embedded variable; if data is to be transferred between SQL and the UAP, specify embedded variables according to the predetermined rules. For details about the SQL data types and data specifications, see E. SQL Data Types and Data Descriptions.
3. Declares an indicator variable for embedded variable (:xstock). The indicator variable declaration for a BLOB-type embedded variable is PIC S9(9) COMP.
4. Indicates the end of the embedded function declaration section.

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

(a) If an error occurs

EXEC SQL
  WHENEVER SQLERROR GO TO error-handling-process
END-EXEC.

WHENEVER SQLERROR

Declares the process to be executed if an error occurs.

GO TO error-handling-process

Switches the process to the specified clause or paragraph name (error-handling-process) if an error occurs. If an SQL Communications Area is referenced from within this process, return code information can be checked.

(b) If a row to be retrieved is not found

EXEC SQL
  WHENEVER NOT FOUND GO TO retrieval-end-process
END-EXEC.

WHENEVER NOT FOUND

Declares the process to be executed if the row to be retrieved is not found.

GO TO retrieval-end-process

Switches the process to the specified clause or paragraph name (retrieval-end-process), if the row to be retrieved is not found.

(c) Effective range of WHENEVER statement

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

(a) Specifying embedded and indicator variables in a 1-row SELECT or FETCH statement

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.

(b) Specifying an embedded or indicator variable in an UPDATE or INSERT statement

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:

UPDATE statement

EXEC SQL
UPDATE STOCK SET STOCK=:XSTOCK:ISTOCK WHERE PCODE=:XPCODE
END-EXEC.

INSERT statement

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.

Note

If you terminate a UAP without executing a DISCONNECT, COMMIT statement (with RELEASE specified), or ROLLBACK statement (with RELEASE specified), the system automatically executes a ROLLBACK statement (with RELEASE specified), and the transaction that was being executed becomes invalid.