Embedded SQL

The general format of embedded SQL statements is:

$\begin{Bmatrix*}[l] \small\mathsf{EXEC} \\ \small\mathsf{EXECUTE} \end{Bmatrix*} \mathsf{SQL} \begin{Bmatrix*}[l] \mathsf{WHENEVER \begin{Bmatrix*}[l] \small\mathsf{NOTFOUND} \\ \small\mathsf{NOT\,FOUND} \\ \small\mathsf{SQLERROR} \\ \small\mathsf{SQLWARNING} \end{Bmatrix*} \begin{bmatrix} \small\mathsf{DO} \end{bmatrix} \space \small\mathit{statementList} } \\ \begin{Bmatrix*}[l] \small\mathit{sqlStatement} \\ \small\mathit{declareCursorStatement} \\ \small\mathit{connectStatement} \\ \small\mathit{disconnectStatement} \end{Bmatrix*} \end{Bmatrix*} \small\mathsf{END-EXEC}$

Where:

The WHENEVER statement declares a set of statements that will be executed whenever the specified condition occurs as the result of an embedded SQL statement execution. It remains in effect until another WHENEVER statement is encountered in the source programs lexical order. The actual effect of WHENEVER is to cause the generation of the appropriate check for the specified condition after every subsequent SQL operation that could cause the condition to occur.

DECLARE ... CURSOR

$\small\mathsf{DECLARE} \space \small\mathit{sqlIdentifier} \space \begin{bmatrix} \small\mathsf{SENSITIVE} \\ \small\mathsf{INSENSITIVE} \end{bmatrix} \begin{bmatrix} \small\mathsf{FORWARD} \\ \small\mathsf{STATIC} \\ \small\mathsf{KEYSET} \\ \small\mathsf{DYNAMIC} \\ \small\mathsf{SCROLL} \end{bmatrix} \begin{bmatrix} \small\mathsf{LOCK} \\ \small\mathsf{UPDATABLE} \\ \small\mathsf{OPTIMISTIC} \\ \small\mathsf{READ\,ONLY} \end{bmatrix} \small\mathsf{CURSOR} \begin{bmatrix} \tiny\mathsf{WITH} \space \tiny\mathsf{HOLD} \end{bmatrix} \small\mathsf{FOR} \begin{Bmatrix} \small\mathsf{sqlIdentifier} \\ \small\mathsf{selectStatement} \\ \small\mathsf{callStatement} \end{Bmatrix}$

Where:

This statement declares a cursor for later use by the program. It may appear in the data division or the procedure division of the program. The scope of the defined cursor is lexical. That is, the cursor named is defined according to the declaration until another DECLARE ... CURSOR statement with the same cursor name is encountered in the source program.

The sensitivity, motion, and concurrency of the cursor are accepted as part of the cursor declaration. P3/COBOL maps the effective cursor declaration to the corresponding JDBC result-set type and concurrency when the cursor is opened.

A cursor is treated as updatable when its SELECT statement, or the prepared statement to which it refers, contains an Oracle Pro*SQL-style FOR UPDATE [OF ...] phrase. A cursor is also treated as updatable when a positioned UPDATE or DELETE later references it with WHERE CURRENT OF even if the cursor declaration did not contain FOR UPDATE. In that case, P3/COBOL forces the cursor to an updatable, insensitive result set and reports a warning if this overrides an explicit cursor sensitivity or concurrency declaration such as READ ONLY.

The database and JDBC driver must still support an updatable result set for the selected query. Some queries, joins, or driver combinations may not be updatable even though the cursor is requested as updatable.

exec sql
    declare my-cursor cursor for select * from my-table
end exec

CONNECT

The syntax that may be used for the CONNECT statement is:

$\small\mathsf{CONNECT} \begin{Bmatrix*}[l] \mathsf{ \begin{bmatrix*}[l] \mathsf{TO} \begin{Bmatrix*}[l] \small\mathit{hostVariable\space_{dataSource}} \\ \small\mathit{literal\space_{dataSource}} \end{Bmatrix*} \end{bmatrix*} \begin{bmatrix*}[l] \small\mathsf{USER} \space \small\mathit{hostVariable\space_{userName}} \space \small\mathsf{USING} \space \small\mathit{hostVariable\space_{password}} \end{bmatrix*} } \\ \mathsf{ \small\mathit{hostVariable\space_{userName}} \begin{bmatrix*}[l] \mathsf{IDENTIFIED BY} \space \small\mathit{hostVariable\space_{password}} \end{bmatrix*} \begin{bmatrix*}[l] \mathsf{USING} \begin{Bmatrix*}[l] \small\mathit{hostVariable\space_{dataSource}} \\ \small\mathit{literal\space_{dataSource}} \end{Bmatrix*} \end{bmatrix*} } \\ \mathsf{RESET} \end{Bmatrix*}$

Where:

This statement (re)connects the active database to the specified database or data source.

Example of a DB2 style connect:

01  database-user.
    05 pic x occurs 0 to 4 times depending on database-user-len.
77  database-user-len   pic 9 value 2.
 
01  database-pass.
    05 pic x occurs 0 to 4 times depending on database-pass-len.
77  database-pass-len   pic 9 value 2.
 
...
 
move 4      to database-user-len
move "test" to database-user
move 4      to database-pass-len
move "pass" to database-pass
 
exec sql
    connect user :database-user using :database-pass
end-exec
 
exec sql
    connect reset
end-exec

Example of an Oracle style connect:

exec sql
    connect :database-user identified by :database-pass
end-exec
 
exec sql
    disconnect
end-exec
 

DISCONNECT

The syntax that may be used for the DISCONNECT statement is:

$\mathsf{DISCONNECT} \begin{bmatrix*} \mathsf{ALL} \end{bmatrix*}$

Example of a DISCONNECT:

exec sql
    disconnect
end-exec

DECLARE ... STATEMENT

$\mathsf{DECLARE} \space \mathit{sqlIdentifier\space_{statementName}} \space \mathsf{STATEMENT}$

Where:

This statement is for documentation purposes only. It affects neither generated code nor runtime behavior.

DECLARE ... TABLE

$\mathsf{DECLARE} \space \mathit{sqlIdentifier\space_{tableName}} \space \mathsf{TABLE} \space \mathit{tableDescription}$

Where:

This statement is for documentation purposes only. It affects neither generated code nor runtime behavior.

DELETE/INSERT/UPDATE

$\begin{bmatrix} \mathsf{FOR} \space \mathit{hostVariable\space_{intMaxOccurence}} \end{bmatrix} \begin{Bmatrix} \mathsf{DELETE}\\ \mathsf{INSERT}\\ \mathsf{UPDATE} \end{Bmatrix} \mathit{statementBody}$

Where:

This statement deletes, inserts, or updates the connected database according to the specified SQL DELETE, INSERT, or UPDATE statement body. The body may be specified using any SQL syntax that is considered valid by the actual connected database manager.

Positioned UPDATE and DELETE

P3/COBOL supports positioned UPDATE and DELETE statements using Oracle Pro*SQL-style WHERE CURRENT OF cursor references.

exec sql
    update customer
       set status = :new-status
     where current of customer-cursor
end-exec

exec sql
    delete customer
     where current of customer-cursor
end-exec

The referenced cursor must be declared and open. When P3/COBOL detects a positioned UPDATE or DELETE, it marks the referenced cursor as updatable. This allows common Pro*SQL-style code to omit FOR UPDATE from the cursor declaration while still obtaining an updatable JDBC result set.

If the cursor's SELECT statement includes FOR UPDATE [OF ...], the cursor is also opened as updatable even before a positioned statement is encountered. If both forms are absent, the cursor is opened read-only by default.

SELECT

The syntax that may be used for a SELECT statement is:

$\mathsf{SELECT} \space \mathit{statementBody} \space \begin{bmatrix} \mathsf{INTO} \begin{Bmatrix} \mathit{hostVariable} \end{Bmatrix} \cdots \end{bmatrix}$

Where:

This statement selects a result rowset whose rows can be retrieved using the FETCH statement, or a single row bound to the host variable(s) specified in the INTO clause, if present.

PREPARE

$\mathsf{PREPARE} \space \mathit{sqlIdentifier\space_{statementName}} \space \mathsf{FROM} \begin{Bmatrix*}[l] \mathit{hostVariable\space_{sqlStatement}} \\ \mathit{literal\space_{sqlStatement}} \\ \mathsf{SELECT\space_\mathit{statementBody}} \end{Bmatrix*}$

Where:

This statement prepares a SELECT statement query for later execution by OPENing a cursor on the prepared statement name or by using the EXECUTE statement.

FETCH

$\begin{bmatrix} \small\mathsf{FOR} \space \small\mathit{hostVariable\space_{intMaxOccurence}} \end{bmatrix} \small\mathsf{FETCH} \begin{bmatrix*}[l] \small\mathsf{FIRST}\\ \small\mathsf{LAST}\\ \small\mathsf{NEXT}\\ \small\mathsf{PREVIOUS}\\ \small\mathsf{PRIOR} \end{bmatrix*} \small\mathit{cursorName} \space \small\mathsf{INTO} \begin{bmatrix} \small\mathit{hostVariable} \end{bmatrix} \cdots$

Where:

This statement fetches rows from the rowset defined by the specified declared and open cursor. The cursor direction indicated is commentary only. That is, all cursors are fetched in the forward (NEXT) direction without regard to a contrary specification in this statement.

An OPEN cursor_name statement must precede the FETCH statement, and the cursor must be open. The data type of the host_variable values must be compatible with the data type of the corresponding database column.

The FOR clause, when present, causes the FETCH operation to be used for targeted host_variable occurrences up to the indicated host_integer index value.

ALTER

$\mathsf{ALTER} \space \mathit{statementBody}$

Where:

This statement is passed through to the connected database unchanged.

EXECUTE

$\small\mathsf{EXECUTE} \begin{Bmatrix*}[l] \mathsf{IMMEDIATE \begin{Bmatrix*}[l] \small\mathit{sqlIdentifier\space_{preparedStatement}} \\ \small\mathit{hostVariable\space_{sqlStatement}} \\ \small\mathit{literal\space_{sqlStatement}} \end{Bmatrix*} } \\ \small\mathsf{EXECUTE \small\mathit{sqlTail} } \\ \small\mathit{sqlIdentifier\space_{preparedStatement} \begin{bmatrix*}[l] \small\mathsf{USING} \space \small\mathit{hostVariable\space_{parameter}} \begin{bmatrix*}[l] \small\mathsf{,} \small\mathit{hostVariable\space_{parameter}} \end{bmatrix*} \cdots \end{bmatrix*} } \end{Bmatrix*}$

Where:

This statement executes the indicated SQL statement and may produce a result set that can be retrieved by the FETCH statement when the cursor form of this statement is used.

There are three variants of this statement:

• Immediate
• PL/SQL
• Prepared statement

CALL

$\mathsf{CALL} \space \mathit{sqlTail}$

Where:

This statement is passed through to the connected database unchanged.

CLOSE

$\mathsf{CLOSE} \space \mathit{cursorName}$

Where:

This statement closes an open cursor and discards any result set associated with the cursor.

COMMIT

$\mathsf{COMMIT} \begin{bmatrix} \mathsf{WORK} \end{bmatrix} \begin{bmatrix} \mathsf{COMMENT} \space \mathit{literal\space_{commentText}} \end{bmatrix}$

Where:

This statement commits all uncommitted changes made since last commit.

LOCK

$\small\mathsf{LOCK} \space \small\mathsf{TABLE} \space \small\mathit{sqlIdentifier\space_{tableName}} \begin{bmatrix*}[l] \small\mathsf{,} \small\mathit{sqlIdentifier\space_{tableName}} \end{bmatrix*} \cdots \small\mathsf{IN} \begin{Bmatrix*}[l] \small\mathsf{ROW} \begin{Bmatrix*}[l] \small\mathsf{SHARE} \\ \small\mathit{EXCLUSIVE} \end{Bmatrix*} \\ \small\mathsf{SHARE} \begin{bmatrix*}[l] \small\mathsf{UPDATE} \\ \small\mathsf{ROW} \space \small\mathsf{EXCLUSIVE} \end{bmatrix*} \\ \small\mathsf{EXCLUSIVE} \end{Bmatrix*} \small\mathsf{MODE} \begin{bmatrix*}[l] \small\mathsf{NOWAIT} \end{bmatrix*} \\$

Where:

This statement is currently treated as commentary. It affects neither generated code nor runtime behavior.

OPEN

$\mathsf{OPEN} \space \mathit{cursorName} \begin{bmatrix} \mathsf{USING} \space \mathit{hostVariable\space_{parameter}} \begin{bmatrix} \mathsf{,} \mathit{hostVariable\space_{parameter}} \end{bmatrix} \cdots \end{bmatrix}$

Where:

This statements opens the specified cursor an executes the prepared query. A parameter may be optionally provided, in which case the first parameter marker or host variable in the prepared statement is replaced with the value of the supplied parameter value.

ROLLBACK

$\small\mathsf{ROLLBACK} \begin{bmatrix*}[l] \small\mathsf{WORK} \\ \small\mathsf{TRAN} \\ \small\mathsf{TRANSACTION} \end{bmatrix*} \begin{bmatrix} \small\mathsf{TO} \begin{bmatrix} \small\mathsf{SAVEPOINT} \end{bmatrix} \small\mathit{sqlIdentifier\space_{savepointName}} \end{bmatrix} \begin{bmatrix} \small\mathsf{RELEASE} \end{bmatrix} \begin{bmatrix} \small\mathsf{COMMENT} \space \small\mathit{literal\space_{commentText}} \end{bmatrix}$

Where:

SAVEPOINT

$\mathsf{SAVEPOINT} \space \mathit{sqlIdentifier\space_{savepointName}}$

Where:

SET

$\mathsf{SET} \begin{Bmatrix*}[l] \mathit{transactionIdentifier} \begin{Bmatrix*}[l] \begin{Bmatrix*}[l] \mathsf{READ} \space \mathsf{ONLY} \\ \mathsf{READ} \space \mathsf{WRITE} \end{Bmatrix*} \\ \mathsf{USE} \space \mathsf{ROLLBACK} \space \mathsf{STATEMENT} \mathit{segmentName} \end{Bmatrix*} \\ \mathit{dbVariable} \begin{Bmatrix*} \mathsf{TO} \\ \mathsf{=} \end{Bmatrix*} \mathit{sqlTail} \end{Bmatrix*}$

Where: