Joel,
I wouldn't use OPNQRYF to write a lick of new code. It was a great tool
in it's time but now I would use imbedded SQL.
There are two paths for imbedded SQL. The easiest one is if you have the
command STRSQL on your system. Not that you need to use that but it will
tell you if 57##ST1 shows up in DSPSFWRSC and so many programmers find the
existence of a command easier to check for than what they consider system
admin tools.
If you have STRSQL then I would use imbedded SQL. If not, then I would
use SQL-CLI in which CLI stands for Call Level Interface. Been a long
time since I've studied SQL-CLI.
With imbedded SQL it is quite easy to receive parameters into RPG. SQL
considers these "host variables". When you use them in an imbedded SQL
statement you prefix them with a colon. So if I passed in two parameters
LOWEND and HIGHEND I could use them like this:
/exec sql
create table qtemp.temptable as (
select this, that, otherthing
from realtable
where this > :lowend and this < :highend)
with data;
You can also process the stuff within your rpg more directly without
creating the temporary table
/exec sql
declare C1 cursor for
select this, that, otherthing
from realtable
where this > :lowend and this < :highend;
/exec sql
open C1;
dow sqlstate<>endofcursor
fetch c1 into :wthis, :wthat, :wotherthing;
// process this row and do as you will
EndDo
/exec sql
close C1;
And if you need really flexible statements like the user's prompt screen
selects which column to sort by then you can store the sql statement into
a variable and use the PREPARE and the EXECUTE statements.
Now, if you think this is enough to get started and we'll continue to
debug the program without you having to crack a manual this cowboy is
going to ride off into the sunset.
Some suggested reading:
SQL/400 Developer's Guide by Paul Conte and Mike Cravitz
Embedded SQL Programming
http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajp/rzajpkickoff.htm?lang=en-us
Rob Berendt
midrange.com
