Re: RUNSQL doesn't work with variable in CL

[Peter H. Coffin]

On Tue, 15 Sep 2015 04:24:15 -0700 (PDT), jd...@arcor.de wrote:

> I'm trying to use RUNSQL instead of OPNQRY. For testing pruposes, I
> created the following CL which works fine:
>
> PGM
> DLTF FILE(QTEMP/LAW01*)
> MONMSG MSGID(CPF0000)
> RUNSQL SQL('CREATE TABLE QTEMP/LAW01# AS (SELECT * +
> FROM ASGDATA/LAW01P WHERE +
> UPPER(BEZ2_LAW01P) LIKE ''%DOSE%'') WITH +
> DATA') COMMIT(*NONE)
> ENDPGM
>
> When I declare a variable and transfer the SQL parameter from another
> program (COBOL or RPG) identical (quotes included) to what you
> see here the system produces error SQL0104 (Token 'CREATE TABLE
> QTEMP/LAW01# AS invalid.)
>
> Can somebody help me with that problem?

What does the broken version look like? I'm specifically interested in
how you're building the SQL parm with the variable.

--
9. If a self-destruct mechanism is necessary, it will not be a large
red button labelled "Danger: Do Not Push". The big red button marked
"Do Not Push" will instead trigger a spray of bullets on anyone
stupid enough to disregard it. --Peter Anspach's Evil Overlord list

[CRPence]

On 15-Sep-2015 05:24 -0600, jd...@arcor.de wrote:
> I'm trying to use RUNSQL instead of OPNQRY. For testing pruposes, I
> created the following CL which works fine:
>
> PGM
> DLTF FILE(QTEMP/LAW01*)
> MONMSG MSGID(CPF0000)

FWiW the above /ignore-all/ coding is a very poor choice; ignored
failures tend to push the revelation of the failure into later
processing from which the effect tends to be inferred to be much more
confounding, as seemingly contradictory or even seemingly impossible.
Allowing the failure to be manifest by the CL default-handler as
unexpected, reveals the error exactly where is the problem.

> RUNSQL SQL('CREATE TABLE QTEMP/LAW01# AS (SELECT * +
> FROM ASGDATA/LAW01P WHERE +
> UPPER(BEZ2_LAW01P) LIKE ''%DOSE%'') WITH +
> DATA') COMMIT(*NONE)
> ENDPGM
>
> When I declare a variable and transfer the SQL parameter from
> another program (COBOL or RPG) identical (quotes included) to what
> you see here the system produces error SQL0104 (Token 'CREATE TABLE
> QTEMP/LAW01# AS invalid.)
>
> Can somebody help me with that problem?
>

The -104 suggests that the string being passed to the program is an
apostrophe-delimited string instead of just the string; i.e. instead of
passing the character string "'CREATE TABLE ...", pass the string
"CREATE TABLE ...".

If the program had been building the entire command-statement to be
interpreted, then encapsulating the entire command string in the
variable would have required escaping the apostrophes in the SQL-string
parameter; i.e. that string could be passed either directly to a command
interpreter [such as QCMDEXC] or more indirectly to a CLP that invokes a
command interpreter. However if the CL is coded as RUNSQL
SQL(&MYSQLSTMT), those extra apostrophes will cause the msg SQL0104
being diagnosed.

Anyhow, given both RPG and COBOL have built-in SQL capability via the
SQL pre-compiler, using the CL interpreter so indirectly to issue the
SQL is rather daft; just pass the SQL string directly to the SQL
interpreter. Or use the SQL Call Level Interface (CLI).

--
Regards, Chuck

[Dr.UgoGagliardelli]

Il 15.09.2015 13.24, jd...@arcor.de ha scritto:
> I'm trying to use RUNSQL instead of OPNQRY. For testing pruposes, I created the following CL which works fine:
>
> PGM
> DLTF FILE(QTEMP/LAW01*)
> MONMSG MSGID(CPF0000)

> RUNSQL SQL('CREATE TABLE QTEMP/LAW01# AS (SELECT * +
> FROM ASGDATA/LAW01P WHERE +
> UPPER(BEZ2_LAW01P) LIKE ''%DOSE%'') WITH +
> DATA') COMMIT(*NONE)
> ENDPGM
>
> When I declare a variable and transfer the SQL parameter from another program (COBOL or RPG) identical (quotes included)

Don't quote. Quote is used inside the CLP to make a literal string, if
the CLP receive the variable as a PARM, you need not quote.
e.g
This is the calling program:
DCL &QRY *CHAR 'CREATE TABLE QTEMP/LAW01# AS (SELECT...'
DCL &NUL *CHAR 1 value(x'00')
DCL &SQL *CHAR <longer than &QRY>
CHGVAR &SQL (&QRY *TCAT &NUL)
CALL QRYPGM &SQL

This is the called QRYPGM program:
PGM PARM (&SQL)
DCL &SQL <same size>
DCL &QRY
DCL &POS *DEC (3 0) value(1)
DCL &NUL *CHAR 1 value(x'00')

LOOP: if (%SST(&SQL &POS 1) *EQ &NUL) EXEC(GOTO LOOPEND)
CHGVAR &POS (&POS + 1)
GOTO LOOP
LOOPEND:
CHGVAR &POS (&POS - 1)
CHGVAR &QRY %SST(&SQL 1 &POS)
RUNSQL SQL(&QRY)

Appending &NUL is to avoid reading random characters from memory.

[jd...@arcor.de]

After posting this I found out that I had made an embarassing beginner's mistake by not removing the apostrophes from my interactive testing. I deleted my post but didn't know that it wouldn't fully disappear. I have therefore to apologize to Chuck and Ugo who were so kind to respond. Thanks, folks!