Re: Is there a better way to run SQL in a CL pgm?

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.

Vern

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:
ZFFDTOCSV
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.

Vern

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.

ADDMSGD MSGID(TMP0001)
MSGF(mylib/mymsgf)
MSG('SQL STRING for my CL program.')
SECLVL('UPDATE TESTFILE
SET LG_ERRMSG=''&1'' LG_ERPATH=''&2''
WHERE LG_DATE=&3 AND LG_TIME=&4 AND LG_ID=''&5''
AND LG_SEQ=''&6'' AND LG_CLIENT=''&7''')
FMT((*CHAR 7) (*CHAR 10) (*CHAR 10) (*CHAR 10)
(*CHAR 10) (*CHAR 10) (*CHAR 10))

<<SNIP>>

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?

ADDMSGD
...
SECLVL('UPDATE TESTFILE
SET LG_ERRMSG=&1, LG_ERPATH=&2 ...
FMT((*QTDCHAR 7) (*QTDCHAR 10) ...

Regards, Chuck