Embedded SQL in RPGLE (SQLRPGLE)

Hi Guys,

Today I am here with Embedded SQL Program.

How to write embedded SQL Program.

SQLRPGLE is very simple if we can understand the flow of program. SQLRPGLE basically consist of below keyword

1.        Declare Cursor

2.        Open Cursor

3.        Fetch Cursor

4.        Close Cursor

SQL: Why and When

This is the most important question. Always asked in Interview too…J

1.        Need to work on multiple rows together. SQL is good in selecting and changing multiple rows together. SQL does in single statement where as RPGLE its multiple iterations.

2.        Functions on columns can be very neatly used in SQL as compared to loops and various variable definitions in RPG

3.        Performance. No one can ever compete SQL

Embedding Basics

Issuing an SQL statement within your source code has some rules as well. In your RPG source code, you must indicate to the SQL pre-compiler that you are in fact executing SQL. To do so, you must wrap all SQL in Exec SQL and End-Exec compiler statements beginning in column 7:

c/exec sql

C+ MYSQL Code

c/end-exec

Notice that the lines between the two compiler directives all begin with c+. Also, only one SQL statement is allowed in each pair of directives.

Create table

c/exec sql

c+ create table mydevlib/sqltest

c+ ( id int not null primary key ,

c+   name char(35) not null with default ,

c+   address char(35) not null with default ,

c+   city char(35) not null with default ,

c+   state char(2) not null with default ,

c+   zip numeric(5,0) not null with default )

c/end-exec

Drop table

c/exec sql

c+ drop table mydevlib/sqltest

c/end-exec

Insert Into Table

d counter         s             10i 0 inz

d city            s             35a   inz( 'King Of Prussia' )

d zip             s              5s 0 inz( 19406 )

c                   for       counter=1 to 30

c/exec sql

c+                  insert into mydevlib/sqltest

c+                  ( id , city , state , zip )

c+                  values( :counter ,

c+                              :city ,

c+                              'VA' ,

c+                              :zip

c+                             )

c/end-exec

c                   endfor

Select Statement

SQL reads data from a database using the SELECT statement. In embedded SQL, you must specify the target(s) for the return value(s) to populate. We can accomplish this by adding the INTO clause in our SELECT statement:

d counter         s             10i 0 inz

c/exec sql

c+ select count(*)

c+   into :counter

c+   from mydevlib/sqltest

c/end-exec

Select Values in Data Structure

d lvDS            ds

d   id                          10a   varying

d   name                        35a

d   zip                          5s 0

c/exec sql

c+ select trim(char(id)), name, zip

c+   into :lvDS

c+   from mydevlib/sqltest

c/end-exec

Multi Record Fetch

If you execute the above query, you will only see the first record, but there are 30 records in the file. In order to process multiple rows, we need a facility of cursor.

The order of events for using a cursor is thus:

1.        Declare

2.        Open

3.        Fetch

4.        Close

Declare

Declaring a cursor for the select statement above would look like this:

c/exec sql

c+ declare csr1 Cursor

c+   for

c+ select trim(char(id)), name, zip

c+   from mydevlib/sqltest

c/end-exec

OPEN

Since we have declared a cursor, we need to indicate to the program that we are ready to use it so we must open it.

c/exec sql

c+ open csr1

c/end-exec

Fetch

Now its time to retrieve the data

d lvDS            ds                  occurs(10)

d   id                          10a   varying

d   name                        35a

d   zip                          5s 0

c                   dow       SQLSTT = '00000'

c/exec sql

c+ fetch next

c+  from csr1

c+  into :lvDS

c/end-exec

c                   enddo

SQLCOD and SQLSTT

We have used SQLSTT to control the loop. These variables both come from the "SQL Communications Area" (SQLCA). SQLCA is a data structure that is automatically included with every SQLRPGLE source member:

D*      SQL Communications area

D SQLCA           DS

D  SQLAID                 1      8A

D  SQLABC                 9     12B 0

D  SQLCOD                13     16B 0

D  SQLERL                17     18B 0

D  SQLERM                19     88A

D  SQLERP                89     96A

D  SQLERRD               97    120B 0 DIM(6)

D  SQLERR                97    120A

D   SQLER1               97    100B 0

D   SQLER2              101    104B 0

D   SQLER3              105    108B 0

D   SQLER4              109    112B 0

D   SQLER5              113    116B 0

D   SQLER6              117    120B 0

D  SQLWRN               121    131A

D   SQLWN0              121    121A

D   SQLWN1              122    122A

D   SQLWN2              123    123A

D   SQLWN3              124    124A

D   SQLWN4              125    125A

D   SQLWN5              126    126A

D   SQLWN6              127    127A

D   SQLWN7              128    128A

D   SQLWN8              129    129A

D   SQLWN9              130    130A

D   SQLWNA              131    131A

D  SQLSTT               132    136A

D*  End of SQLCA

Mainly SQLCOD and SQLSTT are used. Both of these fields return information to the program indicating the resulting state of the most recently executed SQL statement. SQLCODEs and SQLSTATEs always have corresponding values.'00000' indicates that when fetching, a row was found. '02000' basically means no rows were found.

Hope now you can write your own SQLRPGLE program.

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.

Happy Learning