RE: LVLCHK(*NO) and record format names

["Johnson, Duane" <djohnson@xxxxxxxxxxxxx>]

Richard,
Here is what I use to run dynamic SQL from CL. (and from inside RPG programs
without using an SQL compiler)  It is very simple to do.  Create a source
file using the following code:

&LINE1
&LINE2
&LINE3
&LINE4
&LINE5

Use the CRTQMQRY process to create a QMQuery from the source. (I've named
mine UT005Q.)
Next create a CL program to run the query.  My code for that looks like
this:

 /********************************************************************/

 /*                                                                  */

 /* UT005J - CREATE SQL INPUT TO UT005Q                              */

 /*                                                                  */

 /********************************************************************/



           PGM PARM(&LINE1 &LINE2 &LINE3 &LINE4 &LINE5)



             DCL VAR(&LINE1)   TYPE(*CHAR) LEN(50)

             DCL VAR(&LINE2)   TYPE(*CHAR) LEN(50)

             DCL VAR(&LINE3)   TYPE(*CHAR) LEN(50)

             DCL VAR(&LINE4)   TYPE(*CHAR) LEN(50)

             DCL VAR(&LINE5)   TYPE(*CHAR) LEN(50)



             STRQMQRY QMQRY(UTILITY/UT005Q) SETVAR((LINE1 &LINE1) (LINE2
&LINE2) +
                      (LINE3 &LINE3) (LINE4 &LINE4) (LINE5 &LINE5))



 ENDPGM:

           ENDPGM


Once it is compiled, this CL program is callable from other CL and RPG
programs, thus providing for dynamic SQL.
It is also possible to create a command for this.  The command I created is,
of course, UT005C and the code is as follows:

             CMD        PROMPT('Run SQL Statements')
             PARM       KWD(LINE1) TYPE(*CHAR) LEN(50) CHOICE('Line 1')
             PARM       KWD(LINE2) TYPE(*CHAR) LEN(50) PROMPT('Line 2')
             PARM       KWD(LINE3) TYPE(*CHAR) LEN(50) PROMPT('Line 3')
             PARM       KWD(LINE4) TYPE(*CHAR) LEN(50) PROMPT('Line 4')
             PARM       KWD(LINE5) TYPE(*CHAR) LEN(50) PROMPT('Line 5')

Compile it to use the UT002J CL program, and you have interactive SQL that's
promptable.  It is, of course, not anywhere near as nice as the interactive
SQL from IBM, but it'll do in a pinch, or if your company doesn't feel the
need to purchase an interactive SQL package.

  You'll notice that all the lines are 50 Characters long.  That's just the
length I chose, I don't know what the limitations are.


> -----Original Message-----
> From: Richard B Baird [SMTP:rbaird@esourceconsulting.com]
> Sent: Friday, September 13, 2002 1:11 PM
> To:   rpg400-l@midrange.com
> Subject:      RE: LVLCHK(*NO) and record format names
>
>
> Carel,
>
> way ahead of you.  I've been using query manager for years.  I use sql for
> a lot of one-off stuff, mostly useing EXCSQL a lot (Martin Rowe's open
> source tool).
>
> For application stuff, I try to stay away for too many so-called
> work-arounds, due to the fact i'm a short timer here (contractor), and the
> in-house staff isn't interested in maintaining stuff that's out of thier
> element.  if I were on-staff, it would be a different story - they would
> have full boat sql, websFear, etc.
>
> like i say, you work wit whatchu got.
>
> rick
>
> ---------original message---------
> Richard,
>
> No SQL??
>
> Have you thought about creating a SRCPF, length 91, and as a text member
> write your SQL statement.
> With the command CRTQMQRY and STRQMQRY you can run your statements. Those
> commands should be on the system at all times. It is in a way flexible,
> that you can pass parameters to those queries.
>
> Just a thought.
>
> Regards,
> Carel Teijgeler.
>
>
> _______________________________________________
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
> or email: RPG400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.