Jump-Start Embedded SQL into RPG

1
Jump-Start Embedded SQL into RPG

• Presented by
• Robert Arce

2
Why SQL in RPG?

• Useful for ad hoc retrieval and update
• Easy manipulation of data
• SQL alone may not provide logic capabilities to
  perform required operations

• SQL has no user interface
• ad hoc statements can be complex
• sorting possibilities are enhanced by SQL

3
SQL embedded types

• Static statements structure of the statement
  does NOT change
• Static select into statements
• Dynamic Statements
• Cursors to handle multiple retrieves

4
SQL source type

• SQLRPG
• DB2/400 Query Manager RPG
• SQLRPGLE
• DB2 Query Manager RPG/400 Integrated Language
  Environment

5
Create SQL ILE RPG Object

• CRTSQLRPGI
• Precompiles SQL statements creates RPGLE source
  file in QTEMP/QSQLTEMP1 (ToSrcFile option)
• Create Bound RPG program CRTBNDRPG
• Voila! you have a program

6
Precompiler Directives

• Only one SQL statement
• C/exec sql --open sql code
  
• C update CUSMS
• C set CMREPS233
• C where CMREPS5
• C/end-exec -- close sql code

7
Host Variables

• RPG program variables are use within the SQL
  statements
• C/exec sql --open sql code
  
• C update CUSMS
• C set CMREPSnewslsrep
• C where CMREPSoldslsrep
• C/end-exec -- close sql code

8
Indicator Variable Null

• Indicator variable can optionally be coded after
  the host variable
• Two ways to declare Indicator variable
• Dindnul_1 s 5i 0
• Dindnul_2 s 4b 0
• Assign negative (-1) as null or positive (0) as
  not null

9
Indicator Variable

• C set SHIPTOnewshiptoindship
• Same as
• if indship -1
• SHIPTO null
• else
• SHIPTO newshipto
• endif

10
Select Into

• ONLY retrieve ONE row or NADA!
• Into clause must list one host variable for each
  column in the select list
• Optionally and is suggested to use an indicator
  variable per each row
• Can select all fields and assign them into
  the proper Data structure
• sqlstt '21000 ? more than one row

11
Select Into

• Set host variables
• C customer700583
• C/exec sql
• C select CMCSNO, --customer number
• C CMCSNM, --customer name
• C CMDFSH --default shipto
• C into csCMCSNO,
• C csCMCSNM,
• C csCMDFSH inCMDFSH
• C from CUSMS
• C where CMCSNOcustomer
• C/end-exec

12
Dynamic SQL

• Construct in string variables on the fly
• Execute immediate vs Prepare and execute
• Prepare takes extra time building statement but
  execution runs faster
• Parameter markers ? ? define in the Using
  clause with one host variable per marker

13
Dynamic execute immediate

• Assign SQL statement
• dynsqlstm'delete from CUSMS where '
• 'CMCSNM LIKE ''''CAR''
• default when create pgm COMMIT CHG
  
• C/exec sql set option commitnone
• C/end-exec
• C/exec sql
• C execute immediate dynsqlstm
• C/end-exec

14
Dynamic Prepare-execute

• Prepare Statement-Name from host-variable
• C/exec sql --Part 1
  
• C prepare dltcuscar
• C from dynsqlstm
• C/end-exec
• C if sqlstt '00000'
• C/exec sql -- Part 2
  
• C execute dltcuscar
• C/end-exec

15
Dynamic Prepare-Parameter Marker

• dynsqlstm'delete from CUSMS where CMCSNM like
  ?'
• C/exec sql
• C prepare dltcustcar
• C from dynsqlstm
• C/end-exec
• C if sqlstt '00000'
• C eval namelike 'CAR
• C/exec sql
• C execute dltcustcar
• C using namelike
• C/end-exec

16
Cursors

• Cursors open more than one row
• Is based on Select statements
• Allow you to do data manipulation
• Sorts
• Sums
• Use Host Variable for sorts and selections
• Declare as read-only or updatable

17
Cursors

• Declare cursor
• Open the cursor
• Fetch like read records (rows) from cursor
  (optionally update or delete the most recently
  fetched record)
• Close cursor (cursor must be open)

18
Cursor - Declare

• C/exec sql
• C declare custcursor cursor
• C for select CMCSNO, CMCSNM, CMDFSH
• C from CUSMS
• C where CMCSNM like namelike
• C order by custsort
• C for read only
• C/end-exec

19
Cursor - Open

• Host variables are evaluated only when the cursor
  is open
• sets values for the host variables
• C eval namelike 'CAR'
• C eval custsort 'CMCSNO'
• C/exec sql
• C open custcursor
• C/end-exec

20
Cursor - Fetch

• C dow morerows
• C/exec sql
• C Fetch Next
• C from custcursor
• C into csCMCSNO,
• C csCMCSNM,
• C csCMDFSH
• C/end-exec

21
Cursors - Update

• In the declare cursor statement replace
• for read only by for update of CMREPS
• After the fetch do the update
• C/exec sql
• C update CUSMS
• C set CMREPSnewslsrep
• C where current of custcursor --name of the
  cursor
• C/end-exec

22
Cursor - Dynamic

• C/exec sql
• C declare custcursor cursor
• C for selcustcar
• C/end-exec
• Asssign sql to host variable
• sqlstm'select CMCSNO,CMCSNM,CMDFSH '
• 'from CUSMS '
• 'where CMCSNM like ? '
• 'order by CMCSNO'

23
Cursor - Dynamic

• C/exec sql
• C prepare selcustcar
• C from dynsqlstm
• C/end-exec
• C if sqlstt '00000'
• sets parameter marker value
• C eval namelike'CAR'
• C/exec sql
• C open custcursor
• C using namelike
• C/end-exec

24
Fetch Position

• Next - Prior
• First - Last
• Before ? before the first row
• After ? after the last row
• Current ? no change in position
• Relative n ?
• negative previous
• positive next

25
references

• DB2 UDB for iSeries SQL programming
• http//publib.boulder.ibm.com/iseries/v5r1/ic2924
  /index.htm?info/sqlp/rbafymst02.htm
• SQL/400 developers guide by Paul Conte and Mike
  Cravitz