Introduction

SQL/400 is a proprietary dialect of SQL for the IBM AS/400, which was later rebranded as iSeries and finally as SystemI, business eServer Computer Platform. It comes in several flavors, or ways that SQL can be run.

Interactive SQL/400 which can be run from the Command line. Invoke STRSQL {Start SQL/400} and get at what looks like another command line, where we can key in SQL statements for immediate execution, and/or use the F4 Prompt function to navigate IBM DB2/400 data base, which is a version of UDB, IBM's Universal Data Base to get at the 400'ds files (tables) and their fields (columns), for incorporation in the SQL statement. Interactive SQL/400 is often used to identify statements needed to perform various programming actions. When exiting an Interactive SQL/400 session, we have the option of saving a log of our activity, from which our successful SQL/400 statements may be copied into one of the other flavors of SQL/400.
SQL Query Manager is used to design and store SQL queries used to extract information, generate reports, and create procedures. Query Manager is an interface to SQL in which end users, unfamiliar with SQL, the programming language, can be prompted through what is needed to create inquiries and reports that are more sophisticated than what can be created by other tools, and can be rerun as needed.
Static Embedded SQL involves SQL statements hard coded inside another high level language program, such as RPG/400, Cobol/400, C/400, and REXX. This is done because there are some areas where SQL is more powerful than other languages, but there are some things they can do that SQL can not. See Comparative Computer Languages. So the AS/400 programmer can pick and choose the Computer Language that is best suited to the needed function, with programs in different languages calling code in other languages a variety of ways.
Dynamic Embedded SQL

All the AS/400 languages that support Static Embedded SQL also support Dynamic Embedded SQL.

Other SQL/400 Applications. Suppose you connect to the AS/400 using a Personal Computer which has many PC Applications, such as Microsoft Excel. You can put SQL statements inside an Excel cell which will access the AS/400 data base. When you open the Excel, it immediately shows you the latest data from the data base. You can then change the statements to change what data is selected for the Excel.

Languages that support Embedded SQL

A IBM System I computer.

C/400
Cobol/400
Fortran/400
PL/1
RPG (many different versions of RPG on the 400)
REXX

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:

Declare Cursor
Open Cursor
Fetch Rows using Select
Optionally Delete or Update
Close Cursor

Dynamic Embedded SQL

select *

from table.campo, table1.campo1

where table.campo = table1.campo1

SQL\400/Printable version

Contents

Introduction

Languages that support Embedded SQL

Static Embedded SQL

Static Embedded SQL

SQL embedded in RPG

SQL embedded in Cobol

Static SQL SELECT Modes

SQL Cursor

Dynamic Embedded SQL