Identifying the SQL Statement

Using the O Line Command

Type a single O in the line command area for a desired SQL statement to identify the entire statement as the target for a command. In the following figure, the O on line 000015 identifies the SQL statement beginning on line 000014 and ending on line 000023 as the target.

EDIT ---- Z13600MP.FOP.INSTALL(SAMPSQL) - 01.33 ----- LINE 00000000 COL 001 072
COMMAND ===>                                                   SCROLL ===> CSR 
****** ************************** TOP OF DATA *********************************
000001             EXEC SQL                                                
000002               SELECT * FROM FOPDEMO.CUSTOMERS
000003                    WHERE FOPDEMO.CUSTOMERS.STATE = 'NJ'
000004                    ORDER BY FOPDEMO.CUSTOMERS.ZIP
000005              END-EXEC
000006 
000007              EXEC SQL
000008                  UPDATE FOPDEMO.ITEMS 
000009                  SET UNIT_PRICE =UNIT_PRICE * 1.1
000010                  WHERE UNIT_PRICE > 15.00
000012             END-EXEC
000013
000014             EXEC SQL                                                
o00015               SELECT X.CUST_ID, X.CUSTNAME, X.STATE, Y.ORDER_ID, 
000016                      Y.ORDER_SALESMAN, X.YTD_SALES, Y.ORDER_DATE,
000017                      X.SALESMAN_ID
000018                INTO :A, :B, :C, :D, :E, :F, :G, 
000019                FROM FOPDEMO.CUSTOMERS X, FOPDEMO.ORDERS Y 
000020                  WHERE X.YTD_SALES > :T1 and Y.ORDER_DATE > :T2
000021                  AND Y.ORDER_SALESMAN = : T3 
000022                  ORDER BY X.STATE 
000023             END-EXEC                                                

The O line command is handled like any ISPF line command. Thus, you can overtype the O or use the RESET primary command to cancel identification of the target statement.

Block forms of the O line command are also available. You can target a group of SQL statements by typing the OO line command before the beginning of the first SQL statement and after the end of the last SQL statement in the group of statements or by using Onnnnnn. Access displays a prompt to confirm each target statement before executing the specified Access command. When the entire block is not displayed on a single screen, you can scroll the display to complete your entry.

Targeting a Portion

You can also use a block form of the O line command when SQL statements are nested (for example, nested sub-selects) to target a portion of the entire structure. Specify OO on the first and last lines of the block of lines in the target SQL statement.

Using the Cursor Position

Another way to specify an SQL statement is to position the cursor on the target statement.

If you do not select the statement with the cursor or the O line command, the first valid SQL statement in the member is used as the starting position, provided it is a valid SQL statement.

Determining the Target SQL

When you specify a single O line command or position the cursor to target an SQL statement, Access scans the source member to identify the bounds of the target SQL. The scan proceeds as follows:

1. Beginning at the specified line, Access scans backward one line to locate “EXEC SQL,” which identifies the first line in the target statement. If EXEC SQL is not located, the first line in the member is used as the start of the target SQL statement.
   1. Access then scans forward using the rules for statement boundaries, continuation, and end-of-statement that are appropriate for the language in use. For example, END-EXEC indicates the end of the target statement in a COBOL program and a semicolon indicates the end in a PL1 program. (Information about determining the language follows.) If the end of an SQL statement is not found, the last line in the member is used as the end of the target statement.
   2. Access then determines if the original target line is within the range of lines that comprise the SQL statement. If the target line is not in the range, Access displays an error message, “SQL STATEMENT NOT MARKED.”

Determining the Language

When scanning the member to determine the boundaries of the SQL statement, Access enforces the various rules for statement boundaries, continuation, and end-of-statement documented in the IBM® Application Programming and SQL Guide. This rule enforcement ensures that embedded SQL statements are in the correct columns and that the appropriate parsing rules for the language are used.

Using the rules appropriate for the language is important. For example, a hyphen in column 7 indicates a continuation in a COBOL program, but it represents a minus sign in a PL1 or C program.

You can use the LANG operand with the commands FOPFAD2P, FOPD2EXP, and FOPD2EXE to specify the language as COBOL, PL1, C, Fortran, or Assembler. However, the operand is necessary only if the language is Fortran or Assembler.

If the LANG operand is not used, Access attempts to ascertain the language in the following order: COBOL, PL1, and C. Access begins the scan using the rules for COBOL. If the statement is not formatted according to COBOL rules, Access reprocesses the statement using PL1 rules. If the format does not follow PL1 rules, Access reprocesses the statement using C rules. If a language cannot be determined, an error message is displayed.