SQL statements in ILE RPG applications

Defining the SQL communication area in ILE RPG applications

The SQL precompiler automatically places the SQL communication area (SQLCA) in the definition specifications of the ILE RPG program prior to the first calculation specification, unless a SET OPTION SQLCA = *NO statement is found.
INCLUDE SQLCA should not be coded in the source program. If the source program specifies INCLUDE SQLCA, the statement will be accepted, but it is redundant.

If a SET OPTION SQLCA = *NO statement is found, the SQL precompiler automatically places SQLCODE and SQLSTATE variables in the definition specification. They are defined as follows when the SQLCA is not included:

D SQLCODE           S           10I 0
D SQLSTATE          S            5A

Defining SQL descriptor areas in ILE RPG applications

• EXECUTE…USING DESCRIPTOR descriptor-name
• FETCH…USING DESCRIPTOR descriptor-name
• OPEN…USING DESCRIPTOR descriptor-name
• CALL…USING DESCRIPTOR descriptor-name
• DESCRIBE statement-name INTO descriptor-name
• DESCRIBE INPUT statement-name INTO descriptor-name
• DESCRIBE TABLE host-variable INTO descriptor-name
• PREPARE statement-name INTO descriptor-name

 Unlike the SQLCA, there can be more than one SQLDA in a program and an SQLDA can have any valid name.

Dynamic SQL is a programming technique. With dynamic SQL, your program can develop and then run SQL statements while the program is running. A SELECT statement with a variable SELECT list (that is, a list of columns to be returned as part of the query) that runs dynamically requires an SQL descriptor area (SQLDA). This is because you cannot know in advance how many or what type of variables to allocate in order to receive the results of the SELECT.
 You can specify an INCLUDE SQLDA statement in an ILE RPG program; however, it is not allowed in free format.

The format of the statement is:
C/EXEC SQL INCLUDE SQLDA
C/END-EXEC

The INCLUDE SQLDA generates the following data structure.
D*      SQL Descriptor area
D SQLDA           DS
D  SQLDAID                1      8A
D  SQLDABC                9     12B 0
D  SQLN                  13     14B 0
D  SQLD                  15     16B 0
D  SQL_VAR                      80A   DIM(SQL_NUM)
D                        17     18B 0
D                        19     20B 0
D                        21     32A
D                        33     48*
D                        49     64*
D                        65     66B 0
D                        67     96A
D*
D SQLVAR          DS
D  SQLTYPE                1      2B 0
D  SQLLEN                 3      4B 0
D  SQLRES                 5     16A
D  SQLDATA               17     32*
D  SQLIND                33     48*
D  SQLNAMELEN            49     50B 0
D  SQLNAME               51     80A
D*  End of SQLDA

Embedding SQL statements in ILE RPG applications

Fixed-form RPG

The keywords EXEC SQL indicate the beginning of an SQL statement. EXEC SQL must occupy positions 8 through 16 of the source statement, preceded by a / in position 7.

The keyword END-EXEC ends the SQL statement. END-EXEC must occupy positions 8 through 16 of the source statement, preceded by a slash (/) in position 7.

An UPDATE statement coded in an ILE RPG program might be coded as follows:
C/EXEC SQL UPDATE DEPARTMENT
C+           SET MANAGER = :MGRNUM
C+           WHERE DEPTNO = :INTDEP
C/END-EXEC

Free-form RPG

Each SQL statement must begin with EXEC SQL and end with a semicolon (;).

Example: An UPDATE statement coded in free form might be coded in the following way:
EXEC SQL UPDATE DEPARTMENT
  SET MGRNO = :MGR_NUM
  WHERE DEPTNO = :INT_DEP;

Comments in ILE RPG applications

In addition to SQL comments (--), ILE RPG comments can be included within SQL statements wherever SQL allows a blank character.

Fixed-form RPG

To embed an ILE RPG comment within the SQL statement, place an asterisk (*) in position 7.

Free-form RPG

Bracketed comments (/*...*/) are allowed within embedded SQL statements between positions 8 through 80 and whenever a blank is allowed, except between the keywords EXEC and SQL. Comments can span any number of lines. Single-line comments (//) can also be used.

Continuation for SQL statements in ILE RPG applications

 SQL statements can be continued across many records in ILE RPG.

Fixed-form RPG

When additional records are needed to contain the SQL statement, positions 9 through 80 can be used. Position 7 must be a plus sign (+), and position 8 must be blank. Position 80 of the continued line is concatenated with position 9 of the continuation line.

In this example, the SQL statement has a valid graphic constant of G'<AABBCCDDEEFFGGHHIIJJKK>'.

C/EXEC SQL    SELECT  *  FROM  GRAPHTAB  WHERE  GRAPHCOL  =   G'<AABBCCDDEE>
C+<FFGGHHIIJJKK>'
C/END-EXEC

Free-form RPG

SQL statements can be contained on one or more lines. To continue an SQL statement across multiple lines, the SQL statement can be split wherever a blank is allowed. The plus sign (+) can be used to indicate a continuation of a string constant. The literal continues with the first nonblank character on the next line.

Names in ILE RPG applications

Any valid ILE RPG variable name can be used for a host variable with these restrictions.

• Do not use host variable names or external entry names that begin with the characters SQ, SQL, RDI, or DSN. These names are reserved for the database manager.
• The length of host variable names is limited to 64.
• The names of host variables must be unique within the program. The one exception is that if a stand-alone field, parameter, or both, are defined exactly the same as another stand-alone field, parameter, or both, the duplicated name is accepted.
• If a host variable is a duplicated name and does not belong to the exceptional category mentioned in the previous item, but does have the same type, the precompiler issues SQL0314 as a severity 11 error instead of its normal severity of 35. If you want to ignore these severity 11 errors, change the GENLVL parameter value on the CRTSQLRPGI command to be 11 or higher.

Statement labels in ILE RPG applications

A TAG statement can precede any SQL statement. Code the TAG statement on the line preceding EXEC SQL.

Using host variables in ILE RPG applications that use SQL

All host variables used in SQL statements must be explicitly declared.

SQL embedded in ILE RPG does not use the SQL BEGIN DECLARE SECTION and END DECLARE SECTION statements to identify host variables. Do not put these statements in the source program.

All host variables within an SQL statement must be preceded by a colon (:).

The names of host variables must be unique within the program, even if the host variables are in different procedures. However, if a data structure has the QUALIFIED keyword, then the subfields of that data structure can have the same name as a subfield in a different data structure or as a stand-alone variable. The subfield of a data structure with the QUALIFIED keyword must be referenced using the data structure name to qualify the subfield name.

An SQL statement that uses a host variable must be within the scope of the statement in which the variable was declared.

If an error stating that a host variable is not defined or not usable is issued, look at the cross-reference in the precompiler listing to see how the precompiler defined the variable. To generate a cross-reference in the listing, run the precompile command with *XREF specified on the OPTIONS parameter.

Declaring host variables in ILE RPG applications that use SQL

The SQL ILE RPG precompiler only recognizes a subset of valid ILE RPG declarations as valid host variable declarations.

Most variables defined in ILE RPG can be used in SQL statements. A partial listing of variables that are not supported includes the following:

• Unsigned integers
• Pointer
• Tables
• UDATE
• UDAY
• UMONTH
• UYEAR
• Look-ahead fields
• Named constants
• Multiple dimension arrays
• Definitions requiring the resolution of %SIZE or %ELEM
• Definitions requiring the resolution of constants unless the constant is used in OCCURS or DIM.

Using host structure arrays in ILE RPG applications that use SQL

A host structure array is defined as an occurrence data structure or a data structure with the keyword DIM coded. Both types of data structures can be used on the SQL FETCH or INSERT statement when processing multiple rows.

The following list of items must be considered when using a data structure with multiple row blocking support.

• All subfields must be valid host variables.
• All subfields must be contiguous. The first FROM position must be 1 and there cannot be overlaps in the TO and FROM positions.
• If the date and time format and separator of date and time subfields within the host structure are not the same as the DATFMT, DATSEP, TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command (or in the SET OPTION statement), then the host structure array is not usable.

For all statements, other than the blocked FETCH and blocked INSERT, if an occurrence data structure is used, the current occurrence is used. For the blocked FETCH and blocked INSERT, the occurrence is set to 1.

The following example uses a host structure array called DEPARTMENT and a blocked FETCH statement to retrieve 10 rows from the DEPARTMENT table.
DDEPARTMENT               DS                  OCCURS(10)
D DEPTNO                 01     03A
D DEPTNM                 04     32A
D MGRNO                  33     38A
D ADMRD                  39     41A

DIND_ARRAY        DS                  OCCURS(10)
D INDS                           4B 0 DIM(4)
…
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR
C+    SELECT *
C+       FROM  CORPDATA.DEPARTMENT
C/END-EXEC
…

C/EXEC SQL
C+   FETCH C1 FOR 10 ROWS
C+     INTO :DEPARTMENT:IND_ARRAY
C/END-EXEC

Blocked FETCH and blocked INSERT are the only SQL statements that allow a data structure with the DIM keyword. A host variable reference with a subscript like MyStructure(index).Mysubfield is not supported by SQL.

Example 
Dfststruct        DS                      DIM(10)              QUALIFIED        
D sub1                           4B 0              
D sub2                           9B 0
D sub3                          20I 0              
D sub4                           9B 0  

C/EXEC SQL                                  
C+   FETCH C1 FOR 10 ROWS INTO :fststruct    

Sample SQLRPGLE program to Read data from db2 using Select and Fetch SQL Statements

d DataDS          ds                                   

d $cmp                           2s 0                  

d $empId                         5s 0                  

d $empName                      30a                    

d $empDate                       8s 0                  

d $empSalary                    11s 2                  

                                                       

 /free                                                 

                                                       

  //Set SQL options                                    

  exec sql                                             

   SET OPTION                                          

       commit=*none,                                   

       datfmt=*iso;                                    

                                                       

  //Read one Record from the SQL table using unique keys

  exec sql                                             

    Select d1cmp,d1id,d1name,d1sdat,d1saly into :DataDS 

    from qgpl/mydata where d1cmp = 1 and d1id = 102;    

                                                       

    if sqlcod = 0;                                     

    dsply $cmp;                                        

    dsply $empId;                                       

    dsply $empName;                             

    dsply $empDate;                             

    dsply $empSalary;                           

    else;                                       

    dsply sqlcod; //do something                

    endif;                                      

                                                

  //Read more than one record from the SQL table

                                                

  //Declare the cursor                          

  exec sql                                      

    Declare C1 cursor for                       

      Select d1cmp,d1id,d1name,d1sdat,d1saly    

      from qgpl/mydata where d1cmp = 1;          

                                                

  //Open the cursor                             

  exec sql                                      

    Open C1;                                    

                                                

  //Fetch data in a loop                        

  Dou sqlcod <> 0;                              

  exec sql                                      

    Fetch C1 INTO :DataDS;                      

                          

    If (sqlcod <> 0);     

      Leave;              

    Endif;                

                          

    dsply $cmp;           

    dsply $empId;         

    dsply $empName;       

    dsply $empDate;       

    dsply $empSalary;     

                          

  Enddo;                  

                          

  //Close the cursor      

  exec sql                

    Close C1;             

                          

  *inlr = *on;            

                          

 /end-free    

Free Form RPG

Overview

All RPG specs have free-form support except I and O specs.

Each free-form statement begins with an operation code and ends with a semicolon. Here is a list of the new operation codes:

• CTL-OPT for control specs (H)
• DCL-F for file specs (F)
• DCL-S, DCL-DS, DCL-SUBF, DCL-C, DCL-PR, DCL-PI, DCL-PARM for data specs (D)
• DCL-PROC for procedure specs (P)

Advantages

• Free-form RPG allows code to be specified as free-form statements rather than in specific fixed columns.
• Free-form code is still restricted to columns 8 – 80.
• The /FREE and /END-FREE compiler directives are tolerated, but are no longer required for free-form.
• Multiple line definitions are easier to code, easier to maintain, and easier to understand.
• Defining data items with long symbol names is no longer problematic.
• Free-form definitions can have /IF, /ELSEIF, /ELSE, and /ENDIF within the statement.

Changes for control specifications (H spec)

This section describes the changes for a control specification (H spec). The free-form keyword is CTL-OPT (Control Option). The free-form control statement starts with CTL-OPT, followed by zero or more keywords, and ends with a semicolon. Allowed keywords are the same keywords as an H spec.

Example Additional control statements

CTL-OPT OPTION(*SRCSTMT:*NODEBUGIO)

        DFTACTGRP(*No);

    // intermixed free-form and fixed-form

    CTL-OPT DATFMT(*ISO);

H TIMFMT(*ISO)

Changes for declaration statements (D spec)

This section describes the changes for declaration statements (D spec). A free-form data definition statement starts with DCL-<x>, and is followed by the data item name (which can be *N if the data item is unnamed), then by an optional data type, and then by zero or more keywords, and finally ends with a semicolon.

Example Fixed-form and free-form keywords

D* fixed-form declarations

D string        S       50A VARYING

D date          S         D DATFMT(*MDY)

D obj           S         O CLASS(*JAVA:'MyClass')

D ptr           S         * PROCPTR

   

    // free-form declarations

    DCL-S string VARCHAR(50);

    DCL-S date DATE(*MDY);

    DCL-S obj OBJECT(*JAVA:'MyClass');

    DCL-S ptr POINTER(*PROC);

Named constants

A named constant declaration starts with DCL-C, and is then followed by the name, then by the optional keyword CONST, and then by the value and finally ends with a semicolon.

Example  Named constants

// without the optional CONST keyword

DCL-C lower_bound -50;

DCL-C max_count 200;

DCL-C start_letter 'A';

// with the optional CONST keyword

DCL-C upper_bound CONST(-50);

DCL-C min_count CONST(200);

DCL-C end_letter CONST('A');

Specifying the CONST keyword makes no difference in the meaning of the declaration.

Stand-alone fields

A stand-alone field declaration starts with DCL-S.

Example  Stand-alone fields

DCL-S first_name CHAR(10) INZ('John');

DCL-S last_name  VARCHAR(20);

DCL-S index PACKED(6);

DCL-S salary PACKED(8:2);

Example  Stand-alone fields with LIKE

// Define using the LIKE keyword

DCL-S cust_index LIKE(index);

//Specify length adjustment with LIKE keyword

DCL-S big_index LIKE(index : +6);

Example  Declarations using named constants

DCL-C name_len CONST(10);

DCL-S one CHAR(name_len);

DCL-S two VARCHAR(name_len);

DCL-C digits 10;

DCL-C positions 3;

DCL-S value PACKED(digits:positions);

Example  Data structures

// Program described data structure

DCL-DS data_str_1;

    emp_name CHAR(10);

    first_name CHAR(10);

    salary PACKED(8:2);

END-DS;

// Program described data structure

DCL-DS data_str_2;

    value VARCHAR(4);

    index INT(10);

END-DS data_str_2;

//Unnamed data structure

DCL-DS *N;

    item VARCHAR(40);

END-DS;

Example Data structure using LIKEREC

DCL-DS custoutput LIKEREC(custrec);

Example Data structure with END-DS

DCL-DS PRT_DS LEN(132) END-DS;

Example Two equivalent data structures (END-DS)

DCL-DS myrecord EXT;

END-DS;

DCL-DS myrecord EXT END-DS;

Example Data structure with the DCL-SUBF keyword

DCL-DS record_one;

    buffer CHAR(25);

    DCL-SUBF read INT(3);

END-DS;

Example Two equivalent data structures (OVERLAY)

D* fixed-form declaration

D myds          DS

D   subf1           11      15A

D   subf2                    5P 2   OVERLAY(myds)

D   subf3                   10A     OVERLAY(myds:100)

D   subf4                   15A     OVERLAY(myds:*NEXT)

   

    //free-form declaration

    DCL-DS myds;

        subf1 CHAR(5) POS(11);

        subf2 PACKED(5:2) POS(1);

        subf3 CHAR(10) POS(100);

        subf4 CHAR(15);

    END-DS;

The OVERLAY(dsname:*NEXT) keyword means the same as no OVERLAY keyword at all.

Procedure prototypes

A procedure prototype declaration starts with DCL-PR, and is followed by a name and then by zero or more keywords, and ends with a semicolon.

Example Cosine procedure prototype

DCL-PR cosine FLOAT(8) EXTPROC('cos');

    angle FLOAT(8) VALUE;

END-PR;

Example GetCurTotal procedure prototype

DCL-PR getCurTotal PACKED(31:3) END-PR;

Example Procedure prototype parameter with the DCL-PARM keyword

DCL-PR proc_one;

    buffer CHAR(25) CONST;

    DCL-PARM read INT(3) VALUE;

END-PR;

Example  Procedure interface without parameters

DCL-PI *N CHAR(10);

END-PI;

Example Procedure interface parameter with the DCL-PARM keyword

DCL-PI proc_one;

    buffer CHAR(25) CONST;

    DCL-PARM read INT(3) VALUE;

END-PI;

Changes for procedure specifications (P spec)

This section describes the changes for a procedure specification (P spec). The free-form procedure statement declaration starts with DCL-PROC, and is followed by a name, then by an optional return type, and then by zero or more keywords, and ends with a semicolon.

Example Procedure statement

DCL-PR SubProc1 VARCHAR(100) DIM(2);

    varchar1    VARCHAR(10) CONST;

    ucs1        UCS2(5) CONST;

    varucs1     VARUCS2(5) CONST;

    graph1      GRAPH(20) CONST;

    vgraph1     VARGRAPH(50) CONST;

    packed1     PACKED(10) CONST;

    binary1     BINDEC(2) CONST;

    uns1        UNS(3) CONST;

    float1      FLOAT(4) CONST;

END-PR;

Changes for file specifications (F spec)

This section describes the changes for a file specification (F spec). The free-form file definition statement starts with DCL-F, and is followed by a file name and then by zero or more keywords, and ends with a semicolon.

Example File definition statement

DCL-F dspf WORKSTN

            EXTDESC('TABF035001');

Example 23. File definition statement (with additional keywords)

DCL-F dspf WORKSTN(*EXT) USAGE(*INPUT:*OUTPUT)

            EXTDESC('TABF035001');