Dave,
I don't think you can have variable file names in embedded SQL without using a variable string for the statement. You can accomplish your task, but you can't do it in the way you are attempting. You'll have to do something like this:
d strsql 2500 A varying inz('')
d auditfile 10 A varying
auditfile = DBXFIL;
strsql = 'select * from ' + auditfile + ' where crdy = ' + rundate +
' order by sequ for fetch only';
exec sql prepare SQL_STATEMENT from :strsql;
exec sql declare c1 cursor for SQL_STATEMENT;
You'll need to provide the proper escape syntax for the date, depending on format, but you get the idea.
As far as the fields changing, there's not much you can do about that short of defining a set of data structures and assigning one to each file. Regardless, you'll need to recompile every time a new field is added.
It kind of sounds like, if the files are constantly changing, that it might be easier to call use an external tool such as Excel to call a stored procedure rather than trying to maintain a "dynamic" program. It all depends on how many variations of the files you anticipate. If you can define the variations in data structures to avoid incompatibility, RPG will work for you. If you'll need to redefine and recompile every few days, it doesn't seem prudent to use RPG, unless you like to rewrite your code on a regular basis.
Tom Armbruster
________________________________
From: rpg400-l-bounces@xxxxxxxxxxxx on behalf of Dave Murvin
Sent: Tue 6/17/2008 2:21 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Variable file names in Select clause in SQLRPGLE
First try at sending this did not sem to work. Apologies if you get this twice.
I have a situation where I would like to run an embedded RPGLE SQL over an audit database file where the actual file name can change. The audit files are created by a 3rd party programs and I just have to use what they give me. I have to go through a couple of files to determine the current file name and library (library does not usually change except between environments). File names are typically M3DJD00002, M3DJD00005, Etc. depending on the file being audited.
I have tried variations of the following, but can not seem to make it work. Can the file names be variables?
AuditFile = DBXLIB + '.' + DBXFIL;
/End-Free
C/EXEC SQL
C+ Declare C1 Cursor for
C+ Select * from AUDITFILE
C+ Where CRDY = :RunDate
C+ Order by SEQU
C+ for Fetch Only
C/END-Exec
Position 26 Column definitions for table AUDITFILE in
*LIBL not found.
I have also tried
C+ Select * from :DBXLIB.:DBXFIL
Token : was not valid. Valid tokens: (TABLE LATERAL <IDENTIFIER>
C+ Select * from :DBXLIB/:DBXFIL
Token : was not valid. Valid tokens: (TABLE LATERAL <IDENTIFIER>
Just to make it easier, if someone adds a field to be audited, the file definition is changed and my fetch into a data structure is likely to get a level check.
Any suggestions?
Thanks
Dave
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
midrange.com
