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