RE: runsqlstm

[rob@xxxxxxxxx]

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
This is exactly the bug we've been having.  And our workaround was to
rerun the statement and put an extra space where the joblog said the
offence occurred.  But I may implement your change.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Buck Calabro <Buck.Calabro@commsoft.net>
Sent by: midrange-l-admin@midrange.com
07/19/2002 01:10 PM
Please respond to midrange-l


        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        RE: runsqlstm


Rob said,

>We just didn't like the RUNSQL because of some
>strange bugs.
>
>RUNSQL came out of an ancient issue of
>news/400 and utilizes STRQMQRY,
>which all 400's have.

Rob, understanding the nature of those bugs will help with STRQMQRY
because
it isn't the RUNSQL utility that's at fault, but an artefact of the way
STRQMQRY behaves.  Let me give a very simplistic description and hope I
don't mangle it too badly.

QMQRYSRC MBR(RUNSQL)
&A&B&C

QCLSRC MBR(RUNSQL)
DCL &A *CHAR 50
DCL &B *CHAR 50
DCL &C *CHAR 50

/* SQLSTM is the input statement /*
/* i.e. select * from master */
CHGVAR &A %SST(&SQLSTM 0001 50)
CHGVAR &B %SST(&SQLSTM 0051 50)
CHGVAR &C %SST(&SQLSTM 0101 50)

STRQMQRY QMQRY(RUNSQL) +
         SETVAR((A &A) +
                (B &B) +
                (C &C))

Basically, the CMD object returns a single variable, SQLSTM to the CPP
which
then breaks it up into 50 byte pieces because STRQMQRY can't have
variables
longer than 55 (and I can't do the math unless it's easy numbers!)

STRQMQRY trims off trailing blanks from each variable.  Why, I can't say,
but it does.  So, if you have an SQL statement like
         1         2         3         4         5         6         7
1234567890123456789012345678901234567890123456789012345678901234567890
select flda,fldb,fldc,fldd,flde, fldf, fldg, fldh from master

The value of &A would become
'select flda,fldb,fldc,fldd,flde, fldf, fldg, fldh '
and &b would become
'from master                                       '

The statement that would try to execute would be
'select flda,fldb,fldc,fldd,flde, fldf, fldg, fldhfrom master'
because QMQRY would trim off that blank at &A column 50, then
concatenating
&B to that.  In CL it's roughly like chgvar &cmd (&a *tcat &b &tcat &c...)
Then you see an error like 'Column FLDHFROM not found in table MASTER.'
Adding a blank between ',fldh' and 'from' fixes this by putting a blank at
column 51, so that &B has a leading space, which looks right when
concatenated onto the trimmed &A.

I have a CL program that fixes this data up by sliding data around in the
SQL statement so that there are no blanks in the magic columns.  It may
still have a bug, but it hasn't shown up since 1995.

PGM &SQLSTM

DCL &SQLSTM *CHAR 1280
DCL &LEFT   *CHAR 1280
DCL &RIGHT  *CHAR 1280
DCL &OUTPUT *CHAR 1280
DCL &COLUMN *DEC (5 0)
DCL &COL_1  *DEC (5 0)
DCL &WORK   *DEC (5 0)

CHGVAR &COLUMN 50
CHGVAR &OUTPUT &SQLSTM

/* See if the block ends with a space */
CHECK:
IF (%SST(&OUTPUT &COLUMN 1) *NE ' ') (GOTO INCR)

/* Split block */
CHGVAR &COL_1 (&COLUMN + 1)
CHGVAR &WORK  (1280-&COLUMN)
CHGVAR &LEFT %SST(&OUTPUT 1 &COLUMN)
CHGVAR &RIGHT %SST(&OUTPUT &COL_1 &WORK)

/* End of statement? */
IF (&LEFT *EQ ' ') (GOTO END)
IF (&RIGHT *EQ ' ') (GOTO END)

/* Insert blank on the front of the right block */
CHGVAR &RIGHT (' ' *CAT &RIGHT)

/* Put the halves together */
CHGVAR &OUTPUT (%SST(&LEFT 1 &COLUMN) *CAT &RIGHT)

/* Test next block */
INCR:
CHGVAR &COLUMN (&COLUMN+50)
IF (&COLUMN *LE 1250) (GOTO CHECK)

END:
CHGVAR &SQLSTM &OUTPUT
ENDPGM

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