Ü  Embedded SQL

·         It’s a way of embedding the SQL statements in our RPG source code.

Ü  Types of Embedded SQL

·         Static SQL

o   –This is the simplest way of implementing SQL. In this the SQL statement is hard coded in the program.

·         Dynamic SQL

o   –The SQL statement is dynamic in nature and can be changed at runtime based on the parameter input

o   –It requires more resource at run-time for preparing the statement

Ü  Source member type

·         SQLRPG

·         SQLRPGLE

Ü   Compilation command

·         Create SQL RPG Program (CRTSQLRPG)   

·         Create SQL ILE RPG Object (CRTSQLRPGI)

Ü  Compilation process

·         The compilation of embedded SQL is different than the normal RPG program.

·         The compilation is divided into 2 parts:

(1)   SQL precompilation: To validate the embedded SQL in the program and convert those into dynamic program calls. If there is any error in host variable or in SQL statement selection fields or any other sql statement related error, then the compilation stops and SQL precompilation report is generated.

We can check the converted source by seeing the spool file of the program. The converted source includes SQL communication area and many sql-call commands to open, process and close sql cursors.

e.g. 

CALL      SQLOPEN                     
PARM                    SQLCA         
PARM                    SQL_00000 
 
CALL      SQLROUTE                     
PARM                    SQLCA          
PARM                    SQL_00000 
   
 
CALL      SQLCLSE                                 
PARM                    SQLCA                     
PARM                    SQL_00032 

(2) Main program compilation: After there is no error in SQL precompilation then only the main program is compiled and the successful compilation report is generated.  

Ü  Embedded SQL structure

·         The embedded SQL always comes under a block of EXEC and END-EXEC.

e.g.

   
C/EXEC SQL
C+ SET OPTION COMMIT=*NONE
C/END-EXEC
*
C/EXEC SQL
C+ EXECUTE IMMEDIATE :SQLSTMT1
C/END-EXEC
 
C/EXEC SQL
C+ DECLARE C1 CURSOR FOR select count(*), PARTY from AMIT/ACCSUBPF
C+ group by PARTY  order by PARTY
C/END-EXEC
 
C/EXEC SQL
C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
C/END-EXEC
 
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
 
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC

Ü  Host Variables

·         Host variable are used when we want to pass parameter or fetch result while executing SQL.

·         Host variables are always preceded in SQL by a semi-colon.

·         In the below example S_COUNT, W_PNUM1,VAR1 is host variables.        

C/EXEC SQL                                       
C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1         
C/END-EXEC                                       
 
 
C/EXEC SQL                                                            
C+ DECLARE C1 CURSOR FOR select count(*), PARTY from AMIT/ACCSUBPF    
C+ group by :VAR1  order by :VAR1                                    
C/END-EXEC   

Ü  Embedded SQL creation steps

·         Normal program cycle of embedded SQL consists of the steps given below:

1.      Declaring the CURSOR.

2.      Opening the CURSOR.

3.      Fetching the record from the cursor one by one by checking the record found condition.

4.      Successful record found condition is checked by the condition SQLCOD=0

5.      Record not found condition is checked by the condition SQLCOD=100

6.      The error condition is checked by SQLCOD<0

7.      After all the records have been fetched we close the cursor.

0046.00 C/EXEC SQL
0047.00 C+ DECLARE C1 SCROLL CURSOR FOR              >>>>>>>>>>> Declaring the CURSOR
0048.00 C+ select PNAME, PNUM,PADDR from AMIT/PARTYD
0049.00 C+ order by PNUM
0050.00 C/END-EXEC
0051.00  *
0052.00  *
0053.00 C/EXEC SQL
0054.00 C+ OPEN C1                                   >>>>>>>>>>>>> Opening the CURSOR
0055.00 C/END-EXEC
0056.00  *
0057.00 C/EXEC SQL
0058.00 C+ FETCH FIRST FROM C1 INTO :S_PNUM, :S_PNAME,:S_PADDR  >>> Fetch First record from
0059.00 C/END-EXEC                                                         the CURSOR
0060.00  *
0061.00 C                   DOW       SQLCOD=0       >>>>> Check the record found condition
0062.00 C                   EVAL      RRN=RRN+1
0063.00 C                   IF        RRN>9999
0064.00 C                   LEAVE
0065.00 C                   ENDIF
0066.00 C                   WRITE     EMBED_SFL
0067.00 C/EXEC SQL
0068.00 C+ FETCH NEXT FROM C1 INTO :S_PNUM, :S_PNAME,:S_PADDR  >>>> Fetch next record one by one
0069.00 C/END-EXEC
0070.00 C                   IF        SQLCOD<0            >>>>>>>>>> Check for any error
0071.00 C                   LEAVE
0072.00 C                   ENDIF
0073.00 C                   IF        SQLCOD=100      >>>> Check for record not found condition
0074.00 C                   SETON                                        45
0075.00 C                   LEAVE
0076.00 C                   ENDIF
0077.00 C                   ENDDO
0078.00  *
0079.00  *
0080.00 C/EXEC SQL
0081.00 C+ CLOSE C1                                 >>>>>>>>>>>> Close the CURSOR
0082.00 C/END-EXEC

·         Some other steps can also be involved in case of dynamic embedded SQL:

EXECUTE IMMEDIATE
Prepares and executes a statement that does not use any host variables.
 
PREPARE
Turns the character string form of the SQL statement into an executable 
form of the statement, which when executed gives the result set.
 
EXECUTE
Executes a previously prepared SQL statement.

Ü  SQLCA (SQL communications area), SQLCOD (SQL code) and SQLSTT (SQL state)

·         An SQLCA is a data structure whose subfields get updated after the execution of any embedded SQL.

·         The subfields most used in SQLCA are SQLCOD and SQLSTT.

 
SQLCOD

It Contains  SQL return code which can be tested for successful or 
unsuccessful execution of an sql statement.

If SQLCOD=0, represents successful execution.

If SQLCOD>0, represents successful execution with some warnings.

If SQLCOD<0, represents unsuccessful execution with errors.

e.g.

    SQLCOD=100; Row not found

    SQLCOD=-552; Not authorized to an object

SQLSTATE

SQLSTATE is similar to SQLCODE but it returns more specific status code.

It consists of five characters in which the first two characters comprise 
of a code that defines the class as depicted below:

Class '00': It represents successful execution.

Class '01': It also represents successful execution but with warnings.

Class '02' It represents no data found.

All other classes represents unsuccessful executions.

·         Below is the structure of SQL communication area.

D*      SQL Communications area
D SQLCA           DS
D  SQLCAID                       8A   INZ(X'0000')
D  SQLAID                        8A   OVERLAY(SQLCAID)
D  SQLCABC                      10I 0
D  SQLABC                        9B 0 OVERLAY(SQLCABC)
D  SQLCODE                      10I 0
D  SQLCOD                        9B 0 OVERLAY(SQLCODE)       >>>>>>>>>>>>>>>>>>>>>>>
D  SQLERRML                      5I 0
D  SQLERL                        4B 0 OVERLAY(SQLERRML)
D  SQLERRMC                     70A
D  SQLERM                       70A   OVERLAY(SQLERRMC)
D  SQLERRP                       8A
D  SQLERP                        8A   OVERLAY(SQLERRP)
D  SQLERR                       24A
D   SQLER1                       9B 0 OVERLAY(SQLERR:*NEXT)
D   SQLER2                       9B 0 OVERLAY(SQLERR:*NEXT)
D   SQLER3                       9B 0 OVERLAY(SQLERR:*NEXT)
D   SQLER4                       9B 0 OVERLAY(SQLERR:*NEXT)
D   SQLER5                       9B 0 OVERLAY(SQLERR:*NEXT)
D   SQLER6                       9B 0 OVERLAY(SQLERR:*NEXT)
D   SQLERRD                     10I 0 DIM(6)  OVERLAY(SQLERR)
D  SQLWRN                       11A
D   SQLWN0                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN1                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN2                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN3                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN4                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN5                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN6                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN7                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN8                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWN9                       1A   OVERLAY(SQLWRN:*NEXT)
D   SQLWNA                       1A   OVERLAY(SQLWRN:*NEXT)
D  SQLWARN                       1A   DIM(11) OVERLAY(SQLWRN)
D  SQLSTATE                      5A
D  SQLSTT                        5A   OVERLAY(SQLSTATE)         >>>>>>>>>>>>>>>>>>>>>>
D*  End of SQLCA

Ü   CURSOR

·         A cursor is a temporary result set area created in the system memory when an embedded SQL statement is executed.

·         A cursor contains information on the statement executed and the rows of data accessed by it.

·         This temporary result set area contains the data retrieved from the database, and manipulate this data.

·         A cursor can hold more than one row, but can process only one row at a time.

·         The set of rows the cursor holds is called the active set.

Ü  Types of CURSOR:

·         There are 2 types of cursor:

  I.       Sequential / Serial Cursor
A sequential cursor is one defined without SCROLL key word
For serial cursor each row is fetched only once per OPEN.

Example

0051.00  *
0052.00 C/EXEC SQL
0053.00 C+ DECLARE C1 CURSOR FOR select count(*), PARTY from AMIT/ACCSUBPF
0054.00 C+ group by PARTY  order by PARTY
0055.00 C/END-EXEC
0056.00
0057.00 C/EXEC SQL
0058.00 C+ DECLARE C2 CURSOR FOR select PNAME, PNUM from AMIT/PARTYD
0059.00 C+ order by PNUM
0060.00 C/END-EXEC
0061.00  *
0062.00  *
0063.00 C/EXEC SQL
0064.00 C+ OPEN C1
0065.00 C/END-EXEC
0066.00  *
0067.00 C/EXEC SQL
0068.00 C+ OPEN C2
0069.00 C/END-EXEC
0070.00  *
0071.00 C                   DOW       SQLCOD=0
0072.00 C/EXEC SQL
0073.00 C+ FETCH FROM C1 INTO :S_COUNT, :W_PNUM1
0074.00 C/END-EXEC
0075.00 C/EXEC SQL
0076.00 C+ FETCH FROM C2 INTO  :S_PNAME, :W_PNUM2
0077.00 C/END-EXEC
0078.00 C                   IF        SQLCOD<0
0079.00 C                   LEAVE
0080.00 C                   ENDIF
0081.00 C                   IF        SQLCOD=100
0082.00 C                   SETON                                        45
0083.00 C                   LEAVE
0084.00 C                   ENDIF
0085.00 C                   IF        W_PNUM1<>W_PNUM2
0086.00 C                   ITER
0087.00 C                   ENDIF
0088.00 C                   EVAL      RRN=RRN+1
0089.00 C                   IF        RRN>9999
0090.00 C                   LEAVE
0091.00 C                   ENDIF
0092.00 C                   EVAL      S_PNUM=W_PNUM1
0093.00 C                   WRITE     EMBED_SFL
0094.00 C                   ENDDO
0095.00  *
0096.00 C/EXEC SQL
0097.00 C+ CLOSE C1
0098.00 C/END-EXEC
0099.00  *
0100.00 C/EXEC SQL
0101.00 C+ CLOSE C2
0102.00 C/END-EXEC

II.    Scrollable Cursor

·         It is defined with SCROLL key word.

·         Rows of cursor can be fetched many times.

·         When the FETCH is issued, the cursor is positioned to the row of the cursor table as per the used keywords FIRST, LAST, PREV, NEXT, RELATIVE.

·         The following key words are supported when fetching data from a scrollable cursor:

ü  NEXT  – It will FETCH the next row. Simple FETCH operation without NEXT does the same thing.

ü  FETCH – It will retrieve the result set record into the host variable mentioned.

ü  PRIOR – It will FETCH the previous row with respect to the current row.

ü  FIRST – It will FETCH the first row in the results set.

ü  LAST -  It will FETCH the last row in the results set

ü  CURRENT – It will re-FETCH the current row from the result set.

ü  BEFORE – It will positions the cursor before the first row of the results set.

ü  AFTER – It will positions the cursor after the last row of the results set.

ü  RELATIVE n – It will FETCH the row that is n rows away from the last row fetched. Here n can be 0,+ve or –ve integer.

Example

0046.00 C/EXEC SQL
0047.00 C+ DECLARE C1 SCROLL CURSOR FOR
0048.00 C+ select PNAME, PNUM,PADDR from AMIT/PARTYD
0049.00 C+ order by PNUM
0050.00 C/END-EXEC
0051.00  *
0052.00  *
0053.00 C/EXEC SQL
0054.00 C+ OPEN C1
0055.00 C/END-EXEC
0056.00  *
0057.00 C/EXEC SQL
0058.00 C+ FETCH LAST FROM C1 INTO :S_PNUM, :S_PNAME,:S_PADDR
0059.00 C/END-EXEC
0060.00  *
0061.00 C                   DOW       SQLCOD=0
0062.00 C                   EVAL      RRN=RRN+1
0063.00 C                   IF        RRN>9999
0064.00 C                   LEAVE
0065.00 C                   ENDIF
0066.00 C                   WRITE     EMBED_SFL
0067.00 C/EXEC SQL
0068.00 C+ FETCH PRIOR FROM C1 INTO :S_PNUM, :S_PNAME,:S_PADDR
0069.00 C/END-EXEC
0070.00 C                   IF        SQLCOD<0
0071.00 C                   LEAVE
0072.00 C                   ENDIF
0073.00 C                   IF        SQLCOD=100
0074.00 C                   SETON                                        45
0075.00 C                   LEAVE
0076.00 C                   ENDIF
0077.00 C                   ENDDO
0078.00  *
0079.00  *
0080.00 C/EXEC SQL
0081.00 C+ CLOSE C1
0082.00 C/END-EXEC

*** SENSETIVE & INSENSITIVE CURSOR

In SENSETIVE cursor the current updated data is fetched from the database. Hence if any kind of updating or insertion happens in the database the cursor is modified accordingly to provide the latest data.

In INSENSITIVE cursor, the data is copied to a temporary memory and then the cursor fetches the data from there. Therefore, modifications made to the database tables are not reflected in the data returned by fetches made to this cursor.

DECLARE C1 SENSITIVE SCROLL CURSOR
FOR SELECT * FROM PARTYD