Tuesday, July 24, 2007

Embedded SQL - RPGIV/RPGLE - Gotchas

Several of you have visited my earlier posting on Embedded SQL in RPGIV. It is clearly an under addressed subject. In that light, I wanted to present a couple of small things that cause programmers problems when using embedded SQL.

Let's take this series of commands as our working example:

Exec SQL Declare Lines Cursor for
Select PEPSN from F5542330 Where PEDOCO =1148919

Exec SQL Open Lines

Exec SQL Fetch next from Lines
for 100 rows into :PSNS

Exec SQL Close Lines

1) Open - it is a common confusion that Open opens the file for access within the program globally. This would imply that the program has full access to all fields in the file that cursor is over. Not true. The Open and Close statements open and close the cursor only.

2) Fetch - another common mistake is that into creates the variables that the fields are moving into. Not true. Variables prefixed with ':' must be declared in the D specs.

3) Select - old school coders might assume that a Select statement requires a cursor. Surprise! Not true. For single line retrievals the following can be used.

Exec SQL Select PTCUST into :Customer from CSTMST
Where PTCUST = 'Farber';

4) Fetch - though often used to gather several records, it can be used to retrieve a single record.

Between avoiding these common mistakes and creating a library of working examples, you can be well on your way to adding SQL to your programming toolbox.

Another RPG coder,

No comments: