Variable file names in Select from clause in SQPRPGLE

I have a program that does Updates on the fly it reads a configuration
file and build SQL Statements to update files after they are populated
by our EDI Applications

I have Recently made a change that allows the Program to call stored
procedures form the application as well

This is used for our EDI Applications and is Useful for setting customer
specific values Like Qualifiers and Supplier codes

0028.00 FF59999 IF E K DISK

0029.00 D SqlStmt S 500
SQL to Execute
0030.00 D SqlTrl S 500
SQL to Execute
0031.00 D sq S 1A inz(x'7D')
Single Quote
0032.00 D FileLevel S 1A inz('Y')
File Level Process
0033.00 *--------------------------------------------------------

0034.00 C *ENTRY PLIST

0035.00 C PARM TPID1 15

0036.00 C PARM DOCTYP1 6

0037.00 C PARM FLEVEL1 10

0038.00

0039.00 C KEY1 KLIST

0040.00 C KFLD TPID1

0041.00 C KFLD DOCTYP1

0042.00 *

0043.00 C KEY2 KLIST

0044.00 C KFLD TPID1

0045.00 C KFLD DOCTYP1

0046.00 C KFLD FLEVEL1

0047.00

0048.00 C IF FLEVEL1 = *Blanks

0049.00 C Eval FileLevel = 'N'

0050.00 C Endif

0051.00

0052.00 *

0053.00 * Build the SQL Statement and update the files

0054.00 * --------------------------------------------

0055.00 *

0056.00

0057.00 C If FileLevel = 'N'

0058.00 C KEY1 Setll F59999

0059.00 C Else

0060.00 C KEY2 Setll F59999

0061.00 C Endif

0062.00 C*

0063.00 C Dou %EOF

0064.00 C If FileLevel = 'N'

0065.00 C KEY1 Reade F59999

0066.00 C Else

0067.00 C KEY2 Reade F59999

0068.00 C Endif

0069.00 C*

0070.00 C If not %EOF(F59999)

0071.00

0072.00 /FREE

0073.00

0074.00 SqlTrl = ' ';

0075.00

0076.00 // Process Stored Procedures

0077.00 If FLEVEL = 'STOREDPROC';

0078.00 Exsr RunStoredProc;

0079.00 Iter;

0080.00 Endif;

0081.00 // End-StoredProcedure Processing

0082.00

0083.00 If COND <> ' ';

0084.00

0085.00 SqlTrl = ' AND ' + %TRIM(COND);

0086.00

0087.00 Endif;

0088.00

0089.00 SqlStmt = 'UPDATE '+ %TRIM(FLEVEL) + ' SET ' +

0090.00 %TRIM(DOCSEG) + ' = ' + sq + %TRIM(SEGVAL) + sq + ' WHERE '
+
0091.00 %TRIM(TPFNM) + ' = ' + sq + %TRIM(TPID1) + sq + (SqlTrl);

0092.00

0093.00 /END-FREE

0094.00

0095.00 C/EXEC SQL

0096.00 C+ PREPARE SQL FROM :SqlStmt

0097.00 C/END-EXEC

0098.00

0099.00 C/EXEC SQL

0100.00 C+ EXECUTE IMMEDIATE: SqlStmt

0101.00 C/END-Exec

0102.00

0103.00 C/EXEC SQL

0104.00 C+ COMMIT

0105.00 C/END-Exec

0106.00 C Endif

0107.00 C Enddo

0108.00

0109.00 C Eval *INLR = *on

0110.00 C RETURN

0111.00 C*------------------------------------------------------------

0112.00 C* Subroutine Execution

0113.00 C*------------------------------------------------------------

0114.00 C RunStoredProc Begsr

0115.00 C/Exec SQL

0116.00 C+ CALL :SEGVAL

0117.00 C/End-Exec

0118.00 C Endsr

Sample Data
Trading Partner 00442811
Document 810
File Level OEPEIN
Trading Partner Field Nam ENECID
Segment Id ENN103
Condition ENN101 = 'ST'
Segment Value 91
HTH

Mike

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
rpg400-l-request@xxxxxxxxxxxx
Sent: Tuesday, June 17, 2008 9:54 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RPG400-L Digest, Vol 7, Issue 519

Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific than
"Re: Contents of RPG400-L digest..."


*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is meaningful.

Today's Topics:

1. Re: Variable file names in Select from clause in SQPRPGLE?
(Pete Helgren)
2. Re: Expat (Adam Glauser)
3. Re: Program Design Structure Question (Mike)
4. Re: CL Optional Parameter Question (Mike)
5. Get month from date zone (David FOXWELL)
6. Re: Get month from date zone (Francis Lapeyre)
7. Re: External DS in RPG 400 (David Gibbs)
8. Re: External DS in RPG 400 (Joe Pluta)
9. RE: Get month from date zone (Wilt, Charles)


----------------------------------------------------------------------

message: 1
date: Tue, 17 Jun 2008 06:10:58 -0600
from: Pete Helgren <Pete@xxxxxxxxxx>
subject: Re: Variable file names in Select from clause in SQPRPGLE?

Dave,

Others may have a better approach, but I would normally construct the
SQL string as a variable itself and insert the file name as a variable
in the string. In your case, you have specified in your SQL that the
statement should be run over a file named AUDITFILE. You might try a
host variable :AUDITFILE but I am not sure that would work. I would do
something like (pseudocode) :

// Build the SQL statement
AuditFile = 'MyfileName' ;

gSQLStmnt = 'Select * from ' + AuditFile + ' Where CRDY = ' + RunDate
+' Order by SEQU for Fetch Only';

PREPARE S1 FROM :gSQLStmnt;

DECLARE C1 CURSOR FOR S1;

Open C1;

You'll have to build the SQL statement as a static string, including
your date as a non-host variable, and then pass the whole thing as a
variable to your prepare and cursor.

There might be a better approach but this is how I'd go about it.

Pete Helgren




Dave Murvin wrote:

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

------------------------------

message: 2
date: Tue, 17 Jun 2008 08:22:42 -0400
from: Adam Glauser <adamglauser@xxxxxxxxxxxx>
subject: Re: Expat

DMarien@xxxxxxxxxxxx wrote:

Reading the XML-document
works fine, but is it possible to create an XML-document with Expat
and put it on the IFS again ? If so, can anyone can help me with some
examples ?

Nope, Expat is just a parser. It cannot create documents. I've had
success using CGIDEV2 to produce an XML document. Try googling for XML
CGIDEV2, you should find some good articles by the Jon Paris and Susan
Gantner to get you going.

Here is one article from my bookmarks to help get you going:
http://www.ibmsystemsmag.com/i5/enewsletterexclusive/13613p1.aspx


------------------------------

message: 3
date: Tue, 17 Jun 2008 08:19:32 -0500
from: Mike <koldark@xxxxxxxxx>
subject: Re: Program Design Structure Question

The different file is a header record. Some of the fields have been
removed and others added so we can "schedule" the record for recurring
processing.
The removed fields are not and cannot be used until the "regular" record
is created.

I never thought of an override. I might do that for ease of processing.
Thanks for the help guys. I knew I could get a couple ideas from you.

--
Mike Wills
Midrange Programmer/Analyst

Sick of corporate radio and hungry for something new?
http://thenextgenerationofradio.com

On Mon, Jun 16, 2008 at 5:40 PM, Scott Klement
<rpg400-l@xxxxxxxxxxxxxxxx>
wrote:

Hi Mike,

You say one of the files is different. Different in what way? Does

it

have different fields that are used for different purposes?

You mention using an IF statement so I'm expecting something like

this:

if FileType='ORANGE';
read ORANGEFIL;
else;
read APPLEFIL;
endif;

And, okay, that will work as far as it goes. But now which fields
have been loaded into the program? And what needs to be done
differently with the fields from ORANGEFIL vs APPLEFIL? I'm assuming
there must be something substantially different about the fields,
otherwise you'd have changed ORANGEFIL so that it works exactly the
same as APPLEFIL (or whatever your files happened to be named).

If the fields are substantially different and therefore require
separate code to handle them, then how can you call one a substitute
for the other? AT some point, there must be a common denominator,

right?

Personally, I'd try to find the common denominator. Write a service
program that encapsulates the differences between the files to
(probably the wrong word) "normalize" the file access. Then use that
service program instead of reading the file directly.

Make sense?

Mike wrote:

I need to bounce this off you guys to see if there is a better way.
This

is

bugging every aspect of me and am looking for ideas to simplify my

life.

I have two sets of files. Out of the 5 files in the set of files,
only

one

file in the set is different. So now I have a maintenance nightmare
of having two programs exactly the same (and keep the same) except
for the files they use. Is there a way I can make this one program?
I could use a flag and put an if statement on every
read/write/update/delete, but could there be a better way? The other

file is similar, but different enough

that

I think it would be best just to maintain two programs.

--
Mike Wills
Midrange Programmer/Analyst

Sick of corporate radio and hungry for something new?
http://thenextgenerationofradio.com

--
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.

------------------------------

message: 4
date: Tue, 17 Jun 2008 08:22:56 -0500
from: Mike <koldark@xxxxxxxxx>
subject: Re: CL Optional Parameter Question

You can call a CL with defined parameters, without passing parameters.
You just need to handle the MCH error that comes about from using the
non-initialized variable.

--
Mike Wills
Midrange Programmer/Analyst

Sick of corporate radio and hungry for something new?
http://thenextgenerationofradio.com
http://twitter.com/MikeWills | http://friendfeed.com/mikewills

On Mon, Jun 16, 2008 at 4:53 PM, Scott Klement
<rpg400-l@xxxxxxxxxxxxxxxx>
wrote:

Hi Bruce,

I'm no expert, I'm trying to figure this stuff out. I'm looking at
the following page of the Information Center:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rbam6/pass
p.htm

Here's a quote from that page:

- -

When calling an original program model (OPM) CL program, the number of

parameters that are passed to it must exactly match the number that is

expected by the program. The number that is expected is determined at
the time the program is created. (The operating system prevents you
from calling a program with more or fewer parameters than the program
expects). When calling an ILE program or procedure, the operating
system does not check the number of parameters that are passed on the
call. In addition, the space where the operating system stores the
parameters is not reinitialized between procedure calls. Calling a
procedure that expects "n" parameters with "n-1" parameters makes the
system use whatever is in the parameter space to access the "nth"
parameter. The results of this action are very unpredictable. This
also applies to procedures written in other ILE languages that call CL

procedures or are called by CL procedures.

- -

That paragraph states "When calling an ILE program or procedure", then
proceeds to say the space is not reinitialized between calls. Now

that

I'm reading this again after you've told me I'm wrong, I do see that
it uses the verbiage "procedure calls"... but the fact that (before
that) it says "program or procedure" makes this paragraph very easy to

interpret the way I originally interpreted it (that it applies to both

program and procedure calls).

As usual, IBM documentation is anything but clear.


Bruce Vining wrote:

Hi, Scott

Should have stopped on your first note lol. The code path for a
*PGM

call will get you the exception. There was a bug along these lines a
loooong time ago, but it has been corrected (perhaps as long as two
years ago?).

Bruce

Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx> wrote:
...and in ILE CL, the parameter storage isn't reinitialized
between calls, is it? So you couldn't rely on the MCH3601 being
generated each time, unless *OMIT is passed from the CALL command,

right?

I'm with Francis. Use a command front-end. Or, if it's REALLY
important that you use the CALL command, put an RPG front-end over
your CL program.


Scott Klement wrote:

Hi Bruce,

Wouldn't you still get a "Parameters passed on CALL do not match
those required." error when you tried to call this? Unless you used

ILE CL, that is?


Bruce Vining wrote:

Sure. But it does need to be a *PGM.

PGM PARM(&PARM1)
DCL VAR(&PARM1) TYPE(*CHAR) LEN(1) DCL VAR(&TEST) TYPE(*CHAR)
LEN(1) CHGVAR VAR(&TEST) VALUE(&PARM1) MONMSG MSGID(MCH3601)
EXEC(DO) RCVMSG MSGTYPE(*LAST) SNDPGMMSG MSG('Not sent')
TOPGMQ(*EXT) RETURN ENDDO IF COND(&PARM1 = Y) + THEN(SNDPGMMSG
MSG('Sent') TOPGMQ(*EXT)) ELSE CMD(SNDPGMMSG MSG('Sent but no Y')
+
TOPGMQ(*EXT))
ENDPGM

Bruce Vining

--
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.

------------------------------

message: 5
date: Tue, 17 Jun 2008 15:35:42 +0200
from: David FOXWELL <David.FOXWELL@xxxxxxxxx>
subject: Get month from date zone

Help !

gMs = %SUBDT ( AFinDt : *MONTHS );

But gMs is 2 character field.

I naively tried %EDITC 'X' which obviously gives me '00'

%CHAR gives '40' if the date is April.




------------------------------

message: 6
date: Tue, 17 Jun 2008 08:44:19 -0500
from: "Francis Lapeyre" <flapeyre@xxxxxxxxx>
subject: Re: Get month from date zone

David:

Try this:

gMs = %editc(%SUBDT( AFinDt : *MONTHS ):'X');

%SUBDT returns an integer, so you need to convert it to character. Edit
Code 'X' will do that (with a leading zero).

On Tue, Jun 17, 2008 at 8:35 AM, David FOXWELL <David.FOXWELL@xxxxxxxxx>
wrote:

Help !

gMs = %SUBDT ( AFinDt : *MONTHS );

But gMs is 2 character field.

I naively tried %EDITC 'X' which obviously gives me '00'

%CHAR gives '40' if the date is April.


--
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.

--
Francis Lapeyre

Nullum gratuitum prandium.


------------------------------

message: 7
date: Tue, 17 Jun 2008 08:47:14 -0500
from: David Gibbs <david@xxxxxxxxxxxx>
subject: Re: External DS in RPG 400

David FOXWELL wrote:

I've declared a data structure based on the DSPF that is already in
the F specs of my program. ( what I really wanted was to do a LIKEREC
format name ).

I don't think external data structures based on display files are a
great idea ... or even if they will work at all ... primarily because
indicators are defined in the DDS and thus imported included in the
generated structure.

External data structures are intended to be based on physical files, not
device files.

david

--
IBM System i - For when you can't afford to be out of business



------------------------------

message: 8
date: Tue, 17 Jun 2008 08:51:53 -0500
from: Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>
subject: Re: External DS in RPG 400

David Gibbs wrote:

David FOXWELL wrote:

I've declared a data structure based on the DSPF that is already in
the F specs of my program. ( what I really wanted was to do a LIKEREC
format name ).

I don't think external data structures based on display files are a
great idea ... or even if they will work at all ... primarily because
indicators are defined in the DDS and thus imported included in the
generated structure.

If you use INDARA on your display file, that will alleviate some of the
discomfort. As David says, it's not an ideal use of ExtDS, but it can
be done.

Joe


------------------------------

message: 9
date: Tue, 17 Jun 2008 09:51:48 -0400
from: "Wilt, Charles" <WiltC@xxxxxxxxxx>
subject: RE: Get month from date zone

The OP said he already tried that....

Instead try:
gMs = %editc(%dec(%SUBDT( AFinDt : *MONTHS ):2:0):'X');


Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx

[mailto:rpg400-l-bounces@xxxxxxxxxxxx]

On Behalf Of Francis Lapeyre
Sent: Tuesday, June 17, 2008 9:44 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Get month from date zone

David:

Try this:

gMs = %editc(%SUBDT( AFinDt : *MONTHS ):'X');

%SUBDT returns an integer, so you need to convert it to character.

Edit

Code
'X' will do that (with a leading zero).

On Tue, Jun 17, 2008 at 8:35 AM, David FOXWELL

<David.FOXWELL@xxxxxxxxx>

wrote:

Help !

gMs = %SUBDT ( AFinDt : *MONTHS );

But gMs is 2 character field.

I naively tried %EDITC 'X' which obviously gives me '00'

%CHAR gives '40' if the date is April.


--
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.

--
Francis Lapeyre

Nullum gratuitum prandium.
--
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.

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not
a named addressee you are hereby notified that you are not authorized to
read, print, retain, copy or disseminate this communication without the
consent of the sender and that doing so is prohibited and may be
unlawful. Please reply to the message immediately by informing the
sender that the message was misdirected. After replying, please delete
and otherwise erase it and any attachments from your computer system.
Your assistance in correcting this error is appreciated.


------------------------------