Re: RUNSQL Command: Help & Escape Messages

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hi Sam

Since I've written several of these SQL statement processors on my own 
and now the ones we have in our products, I can't agree that putting in 
OUTPUT parameters is all that difficult.

It's easy to use an API to get the exact parameter definitions from the 
STRQMQRY command, for example. Or just look at a prompt and imitate it - 
close enough.

Then it's simply a matter of passing the value to the STRQMQRY command 
in the command-processing program for your command.

Even using the QM commands directly instead of STRQMQRY, this is pretty 
trivial stuff, seems to me. But I've been playing with this for years, 
so maybe I'm jaded!!

Regards
Vern

On 11/14/2012 5:05 PM, Sam_L wrote:
> In defense of not supporting SELECT, there is quite a bit more coding
> effort to provide support for where the output of the select goes to.
>
> Now this variation on Rob's select does work:
>
> RUNSQL SQL('create table qtemp/zz_table as (SELECT SYSTEM_TABLE_SCHEMA,
> count(*) as the_count FROM syscolumns WHERE system_table_schema=''QIWS''
> GROUP BY SYSTEM_TABLE_SCHEMA) with data') COMMIT(*NONE)
>
> Basically it amounts to adding a create table and giving a name to the
> count(*) column.
>
> And you can also do the same kind of thing with RUNSQLSTM.
>
> Sam
>
> On 11/14/2012 8:17 AM, rob@xxxxxxxxx wrote:
> > Does RUNSQL support a simple select?  When I run this:
> > runsql 'SELECT SYSTEM_TABLE_SCHEMA, count(*)
> >                  FROM syscolumns WHERE system_table_schema=''ERPLXF''
> >                           GROUP BY SYSTEM_TABLE_SCHEMA'
> >
> > I get nothing.  Nothing returns to my screen.  No joblog information.
> > Nothing.
> > Command Entry
> > > CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES)
> > > runsql 'SELECT SYSTEM_TABLE_SCHEMA, count(*)
> >                    FROM syscolumns WHERE system_table_schema=''ERPLXF''
> >                             GROUP BY SYSTEM_TABLE_SCHEMA'
> >
> > But this works fine
> > > runsql 'CREATE TABLE ROB/NATHANs (WTF CHAR (5 ) NOT NULL WITH DEFAULT)'
> >     Ownership of object NATHANS in ROB type *FILE changed.
> >
> > Guess not:
> > http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafyrunsql.htm
> >
> > What a major disappointment.  In the stuff that uses wrap arounds for
> > STRQMQRY you can use a simple select.  But when I tried to write a
> > imbedded sql in RPG type solution I think I had the same limitation that a
> > simple select (without an into or some such thing) wouldn't work.  IBM
> > probably ran into the same thing.  I guess there's still situations for
> > the renamed command I use to run an sql statement.
> >
> >
> >
> > Rob Berendt