DOCSQL uses a different technique for building the SQL statement string - it shall remain unstated, as you can understand. I made that change to get around not only this issue but another one we had that ADDed a blank at a certain position. I now know of a different way to handle THAT one, but it is what it is now.

It still uses the STRQMQRY command.

And you CAN use a source member as the container of the statement - and those could be put together beforehand, with proper quoting, maybe. I've not tried that.


On 4/2/2012 12:33 PM, Stone, Joel wrote:
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 ...


Return to Archive home page | Return to MIDRANGE.COM home page