× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.