AW: SQL Alias within RPG

["HauserSSS" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

you definitely have to use dynamic SQL.
With dynamic SQL you create the command string at runtime, convert this
string into an executable SQL statement and execute it. This sounds more
complex than it is!
To convert a string into an executable SQL statement and to run this
statement you have 2 possibilities:
1. Use the SQL-Statement PREPARE to convert the string into an executable
SQL statement
   Use the SQL-Statement EXECUTE to run the SQL statement.
   This solution should be prefered, if you want to execute the same
SQL-statement several times.
   That means you only have convert it once and can execute it multiple
times.
2. Instead of using 2 SQL commands you also can use the SQL statement
EXECUTE IMMEDIATE,
   that is a combination of PREPARE and EXECUTE

Here an example:

/Free
   CmdString = 'CREATE ALIAS MYALIAS FOR MYTABLE (' + %Trim(MyMember) + ')';
   Exec SQL  Execute Immediate :CmdString;
/End-Free

BTW I wouldn't create an alias at runtime. Aliases are permanent objects
(DDM-Files) that can be created once and used when ever needed. If the
members you have to read change every day, I'd suggest to use the CL-Command
OVRDBF to override the member and use it in the SQL statments

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Jeff Crosby
Gesendet: Dienstag, 18. Juli 2006 02:19
An: RPG programming on the AS400 / iSeries
Betreff: Re: SQL Alias within RPG


Cassidy, Alan wrote:
> > > Can I do this in freeform RPG for a multimember file where the member is
not
> > > known at compile time:
> > >    Exec SQL  Create Alias SALES for SLSHST(:mbr);
>
>
> > Could you use a prepared statement?
>
> ..Or an Execute Immediate?
What's that?

Guess I have some learning still to do.

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