RE: WG: Running SQL statement from CLP or CLLE

Well said Joe. Having never visited your article I had assumed that few other than myself used REXX.

However, because REXX parms are input only then if the SQL derived values must be retrieved by the CL program this requires an additional mechanism such as populating a data area or environment variable for the CL to retrieve.
An alternative to REXX would be to create the SQL statements as a stored procedure, but you still need to put the result in a place subsequently accessible by the CL.

Of course, if you just need to execute an SQL statement other than a SELECT, like an INSERT, UPDATE or DELETE which do not return anything, then this can be achieved simply by passing the statement to a call to QZDFMDB2.

Peter

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Monday, 9 August 2010 2:47 a.m.
To: RPG programming on the IBM i / System i
Subject: Re: WG: Running SQL statement from CLP or CLLE

Hi Bernd!

This is a job for REXX! REXX allows you to intersperse SQL and CL
commands very easily. Here's an article I wrote a while back:

http://www.mcpressonline.com/tips-techniques/sql/techtip-calling-sql-from-rexx.html

Joe

Von: Bernd Dworrak
Gesendet: Freitag, 6. August 2010 07:24
An: 'rpg400-l-bounces@xxxxxxxxxxxx'
Betreff: Running SQL statement from CLP or CLLE



Hello,

I'm not sure whether this is the right forum to ask the following
question but I try.

I have a CLP in which I want to get information from a file.

As the file is quite big I do not want to get the info from it by RCVF
command because it could take a lot of time to get the information back.

MYFILE1 is a keyed file with MYFIELD1,.2,.3,.4 as unique key.

Of course I could call a RPG program from the CLP to get the requested
info returned by a parameter, but why not getting it by a single
"SELECT" statement?

The point is, that I want to do it like this: "SELECT MYFIELDx FROM
MYLIB/MYFILE1 WHERE MYFIELD1 = &PARM1 AND MYFIELD2 = &PARM2 AND MYFIELD3
= &PARM3 AND MYFIELD4 = &PARM4 ".

I haven't found any info or examples yet.

Maybe one of you know how to do it best or has another brilliant idea.

Thanx in advance

Bernd