SQL\400/Static Embedded SQL

Static Embedded SQL

Static Embedded SQL statements are written into the source code of the high level language program, then an SQL Precompiler converts the SQL statements to a form that is acceptable to the high level language compiler.

Dynamic SQL Statements are composed, prepared and run when the program is run.

SQL embedded in RPG

SQL statements can be placed within the calculations of an RPG program ... detail calculations, total calculations, or an RPG Subroutine. RPG identifies what section of a program, using a character in position 6 with a C for calculations. Here is how SQL statements are usually embedded in RPG program calculations.

 C/EXEC SQL
 C+ SQL Statement
 C+ SQL Statement
 C+ SQL Statement...
 C/END-EXEC

There can be any number of SQL Statements. Notice the slash at beginning of the first and last lines, that delineate the beginning and end of the SQL statements embedded in the RPG code. Notice the plus sign at the beginning of each SQL statement.

Conventional RPG uses files which are to be specified in F-spec of the program code. SQL statements can have files which may not be specified in F-spec of the program code.

Beginning with Release V5R4 embedded SQL can be used in (RPG) free format notation either. An embedded SQL statement starts with EXEC SQL which can be positioned at any place between position 8 and 80. The SQL statement can be coded immediately after the EXEC SQL but also can start in the next row. The SQL statement can be splitted into multiple rows, but must be specified between position 8 and 80. An embedded SQL statement in free format ends like any other free format RPG statement with a semi colon (;)

/Free
    Exec SQL  SQL Statement;

    Exec SQL  
      SQL Statement;

    Exec SQL  SQL Statement 1st Row
      SQL Statement 2nd Row
      ...
      SQL Statement nth Row;

Example:

 /Free
    Exec SQL  Select Coalesce(Max(OrdPos) + 1, 1) into :NewPos
                 From OrderPosition
                 Where OrderNo = :ParOrderNo;

SQL embedded in Cobol

In Cobol, SQL statements are embedded in specific program sections. The END-EXEC must be terminated with a period.

EXEC SQL
  SQL Statement
  SQL Statement
END-EXEC.

There can be any number of SQL statements.

Static SQL SELECT Modes

Static SQL offers two modes of operation, with secondary options.

• SELECT one row of a table, at a time.
• SELECT multiple rows of one or more tables into a Cursor.
  • A Serial Cursor is when each row of the results is to be fetched only once per open of the cursor, going through the data sequentially. Each time a row of data is read into the program, the cursor is moved to the next row of the result table. This is repeated until reaching end-of-data, when the cursor should be closed. To use the cursor again, close the current use of it, then re-issue the open statement. We cannot back up in a Serial Cursor.
  • A Scroll Cursor supports alternatives to going through the data sequentially. Access defaults to the Serial Cursor method, while the Fetch statement can position the cursor where to do the reading. When it is not specified that the Scroll Cursor is Dynamic, access is read-only. This kind of Cursor is usually in a program to display parts of the data base on screen of end user, who can then key in something to navigate the data, leading the Scroll Cursor to be positioned elsewhere in the data, to get what the user wants to see.
  • A Dynamic Scroll Cursor permits the update of rows that are accessed.

SQL Cursor

There are several ways this can be programmed. One way is to define a Data Structure which defines all the data elements that will be selected with each occurrence of executing the multiple rows. It reads in the data from the joined files very similar to reading a record from one file in RPG or Cobol.

Unlike many other AS/400 programming languages, where Variables can be defined wherever needed, such at the beginning of some Subroutine that will be using them, SQL/400 requires that anything, to be used in the program, where the SQL is embedded, be defined earlier in the program source code than where ever it is subsequently used. Thus, the sequence of processing an SQL cursor, embedded in another high level language, is:

1. Declare Cursor
2. Open Cursor
3. Fetch Rows using Select
4. Optionally Delete or Update
5. Close Cursor