I would like to see that!

The DOCSQL command is great - it builds larger strings than the one I copied from the magazines, which is especially useful for building row 1 of an excel spreadsheet containing all the column headings from an Iseries PF with the ZFFDTOCSV command. These SQL commands can get to be 1000+ characters when a file has lots of lengthy field names.

Can I ask: do you know if DOCSQL gracefully handles the nasty little error when the end of a parm field is a blank? The RUNSQL in the magazines sends things like

"SELECT custNAME, custSTATEwhere custSTATE = 'TX' " if one of the parm happens to end in a space (instead of the proper space like "SELECT custNAME, custSTATE where custSTATE = 'TX'"
Space disappears in RUNSQL from magazines, causing SQL to croak

Im changing all our RUNSQL commands to DOCSQL for 3 reasons:

1) DOCSQL handles really long strings
2) DOCSQL wont compete with the name RUNSQL that IBM will introduce someday
3) DOCSQL is vendor supported

Credit to:
search for "Add column headings to a CSV file"
author: Herb Bujak January 2003

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Monday, April 02, 2012 12:13 PM
To: Midrange Systems Technical Discussion
Subject: Re: Is there a better way to run SQL in a CL pgm?

I like this too - in fact, I'm thinking of writing up something for our
customers, like you, Joel, that are using our DOCSQL command - very
slick technique!

I don't see this being widely used, however.


On 4/2/2012 10:49 AM, Stone, Joel wrote:
That looks pretty good - is this method widely used in the /400 community for building SQL command strings?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, April 02, 2012 10:45 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Is there a better way to run SQL in a CL pgm?

On 01-Apr-2012 17:41 , Jack Callahan wrote:
Have you considered using RTVMSG? Used to use this technique to setup
the QRYSLT parameter of the OPNQRYF command .

Tweak your message to as needed to get the parameter lengths and
quotes correct.

MSG('SQL STRING for my CL program.')
AND LG_SEQ=''&6'' AND LG_CLIENT=''&7''')
FMT((*CHAR 7) (*CHAR 10) (*CHAR 10) (*CHAR 10)
(*CHAR 10) (*CHAR 10) (*CHAR 10))

If using that technique, surely the use of *QTDCHAR data type for the
message data fields representing quoted character string literals would
be better, to also avoid the escaped apostrophes in the ADDMSGD?

FMT((*QTDCHAR 7) (*QTDCHAR 10) ...

Regards, Chuck

This thread ...


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

This mailing list archive is Copyright 1997-2019 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].