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