Re: RUNSQL Command: Help & Escape Messages

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