× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Yeah, that's why I almost always use STRQMQRY for programmatic SQL, RUNSQLSTM only for setting up a collection and other DDL tasks (data definition as opposed to data manipulation - DML)

Does this item use substitution variables in a QMQRY? Or does it build a RUNSQLSTM source member? I find the first to be easier. Of course, you can execute only one statement in a QMQRY. OTOH, RUNSQLSTM cannot do SELECT statements at all.

So with QMQRY you can front the STRQMQRY command with a CL that puts in the values you want. E.g.,

SELECT &FIELDLIST from &FILESPEC WHERE &QUERYSELECT

could be the statement in your QMQRY - use STRQM if you have it, otherwise CTRSRCPF QQMQRYSRC RCDLEN(91) --- yes, 91. Then add a member and put the above statement into it. Then use CRTQMQRY to create a *QMQRY object called what you want - say, GENERICSQL

To use it?

STRQMQRY GENERICSQL SETVAR((FIELDLIST '*') (FILESPEC 'SYSIBM/SYSDUMMY1') (QUERYSELECT '1 = 1'))

This runs the following statement:

SELECT * from SYSIBM/SYSDUMMY1 WHERE 1 = 1

The last item is a way to get all records - there are other ways, I hope, but this is on short notice. E.g., take the "WHERE" out of the QMQRY and always put it into the SETVAR - if you want all records, set it to ' ' - a single blank.

SELECT &FIELDLIST from &FILESPEC &QUERYSELECT

STRQMQRY GENERICSQL SETVAR((FIELDLIST 'field1,field2') (FILESPEC 'SYSIBM/SYSDUMMY1') (QUERYSELECT ' '))

runs

SELECT field1,field2 from SYSIBM/SYSDUMMY1

In case you can't see them, there are 2 blanks at the end     ;-)

So this is a way to programatically change the fields and files you are querying against. Since whatever you put in the SETVAR replaces its corresponding substitute variable, you can also use this for WHERE clauses and HAVING and whatever to have ad hoc search criteria.

There are also QMFORMs (RCDLEN(162) - yeah, don't ask). These give you the layout for screen and reports, similarly to Query/400. With Query Management (QM) objects, the data and the presentation are in separate objects, with Query/400 they are combined in the one *QRYDFN object. And there are RTVQMxxx commands to get QMQRYs and QMFORMs that are close to what the QRYDFN has.

There have been generic SQL statement executers that consist of a string of substitution variables

&A&B&C&D&E&F&G&H&I&J

Would let you run any SQL statement up to 550 characters. This usually works but has a couple problems - each setvar is limited to 55 characters, hence the 550 (10 * 55). And trailing blanks are often stripped, yielding unexpected results. I've rewritten our version of this technique that we have used to take care of these issues, so I can't tell you what I've done - have to kill you! But the technique described works pretty well most of the time. Your CL needs to substring your statement variable into segments, and watch out for those trailing blanks.

I think there is one called EXCSQLSTM - google that

Enjoy!
Vern

At 08:19 AM 6/18/2005, you wrote:

> While waiting on the book, go to InfoCenter

Thanks.  I already have (had) the manuals downloaded to my PC.  Some of the
other links are new, though.

Late Friday afternoon, I had a situation where I needed to pass a parm into
the RUNSQLSTM member, which you can't do.  I found, on the iseriesnetwork
site, an SQL command, CL, and RPG program designed to do this.  From 1998 I
believe.  Renamed it ADHOCSQL so I would remember it wasn't from IBM.  Now I
can do things like:

CHGVAR &SQLSTR 'DELETE FROM somefile WHERE somefield = somevalue'
ADHOCSQL CMD(&SQLSTR)

and it works like a champ.

--
Jeff Crosby
jlcrosby@xxxxxxxxxxxxxxxx
Opinions expressed are mine only and not necessarily the views of my
company.  Unless I say so.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.