CL quotes, QMQRY variables, etal

[Hal]

All,

I have this program I wrote with a *CMD front end, a CL program, and a
couple STRQMQRY calls within the CL. The basic gist of it is, I can
type DLTUSR (username) on an os/400 command line, and my program
deletes their information from several tables and then deletes the
user profile itself.

The parm that I pass in, the username, is a *CHAR, 10 bytes. I pad it
with a single quote in the CL and put the result into a *CHAR 12 byte
variable called &USR2. That way when I pass the variable into the
STRQMQRY call, it has single quotes around it.

I use these lines of code to do it, first the "delimiter" variable:

DCL VAR(&DLM) TYPE(*CHAR) LEN(1) VALUE('''')

CHGVAR VAR(&USR2) VALUE(&DLM *TCAT &USER *TCAT +
&DLM) /* Add quotes to either side of the +
user variable so it can be used in the +
query manager query */

The CHGVAR is pretty self-explanatory. I type dltusr joe, and that
gets passed into the program as &USER, and then I end up with a
variable called &USR2 and it has(I believe) the value 'JOE' in it, and
that gets passed to the STRQMRY command.

The problem is, if I issue a CALL QGPL.DLTUSR('JOE') from inside
interactive SQL, the job crashes. The STRQMQRY call blows up and based
on where it crashes it is obvious that the quotes around the variable
are not getting placed correctly, causing the SQL to have a syntax
error and the query subsequently fails to run.

So I figured okay, no problem. I'll add a check in the CL program, and
if the first byte of variable &USR is equal to a ' character, I'll do
the padding differently so that the proper number of quotes are around
the variable. I assumed that since I do not use single quotes on the
command line, but I am forced to put single quotes around the string
in SQL, that the source of my problem is that the username string
already has quotes around it when I am not expecting them to be there
if I call the program through SQL.

Unfortunately, I can't figure out how to do that check though, because
%SST (&USR 1 1) always returns a J using the above example. If I call
it from the command line or from inside SQL, I get a J. So I'm not
sure why dltusr joe from the command line works, but call
qgpl.dltusr('JOE') does not.

I already tried doing "where username = '&variablegoeshere' "in the
QMQRY definition, and that does not work. It would seem that the SQL
interpreter will not place a variable between the quotes if the quotes
are already in the query. So I have to assign the value 'JOE' to the
variable and have the QMQRY look like " where username =
&variablegoeshere".

Anyone run into something similar in the past?

Chris

[CRPence]

On 08-Nov-2011 17:31 , Hal wrote:
>
> I have this program I wrote with a *CMD front end, a CL program, and
> a couple STRQMQRY calls within the CL. The basic gist of it is, I
> can type DLTUSR (username) on an os/400 command line, and my program
> deletes their information from several tables and then deletes the
> user profile itself.
>

> <<SNIP>> I end up with a variable called &USR2 and it has(I believe)
> the value 'JOE' in it, that gets passed to the STRQMQRY command.

>
> The problem is, if I issue a CALL QGPL.DLTUSR('JOE') from inside

> interactive SQL, the job crashes. The STRQMQRY call blows up <<SNIP>>

Hmmm, no info about the CREATE PROCEDURE; nor the QMQRY source, nor
the CLP source statement doing the STRQMQRY SETVAR(()). No matter
probably, since...

AFaIK the invocation of QM within SQL fails with sqlcode=-752, or
message SQL0752 rc4, suggesting that "Connection cannot be changed.
Reason code is 4. Cause . . . . . : Connection cannot be made because
the application process is not in a connectable state." where the return
code is described by "4 -- The connection is locked by another
invocation of Interactive SQL".

Change the job and CLP to enable CL logging; LOGCLPGM(*YES), and
review the joblog by DSPJOBLOG to see the failing STRQMQRY and the
error(s) logged. Or within STRSQL issue CALL QSYS.QCMD and then either
call the CLP [via the command or not] and review the failure(s) there.

> Unfortunately, I can't figure out how to do that check though,

> because %SST(&USR 1 1) always returns a J using the above example.

> If I call it from the command line or from inside SQL, I get a J. So
> I'm not sure why dltusr joe from the command line works, but call
> qgpl.dltusr('JOE') does not.
>

Per above, presumably %SST(&USR2 1 1) is desired? Regardless, just
code DMPCLPGM in the CLP after the variable is set and before the
program fails [if the error is not monitored] and then DSPSPLF QPPGMDMP
SPLNBR(*LAST) to review the values of the variable, or use debug
[STRDBG] and DSPPGMVAR '&USR2' either at the unmonitored exception or at
a breakpoing [e.g. added by ADDBKP].

> <<SNIP>>

>
> Anyone run into something similar in the past?
>

Yep... the failures for stacked calls to QM while in STRSQL; can be
frustrating, and too often since I often use CALL QCMD in SQL and my
attention program is QCMD such that I often accidentally start a new
STRSQL accidentally or hit the QMQRY error having not even recalled
there was a STRSQL session earlier in the call stack.

If the program must be capable of being called as a stored procedure,
then instead of using STRQMQRY, the SQL processing should be made\done
as SQL stored procedures as well. The LANGUAGE REXX is somewhat nice
for a stored procedure, and the CMD can invoke REXX instead of the CLP,
so the CLP could even be eliminated.

Regards, Chuck

[Hal]

>    Hmmm, no info about the CREATE PROCEDURE; nor the QMQRY source, nor
> the CLP source statement doing the STRQMQRY SETVAR(()).  No matter
> probably, since...
>

Chuck...I apologize. I should have included all of the source code.
Let me preface the code briefly...because I'm thinking you'll shudder
when you see it. I have never had any as/400 programming training so
what you see is the result of self-taught fumbling. With that..here it
is.

0001.00 PGM
PARM(&USER)
0002.00 DCL VAR(&BADUSR) TYPE(*LGL)
VALUE('0')
0003.00 DCL VAR(&USER) TYPE(*CHAR) LEN(10) /*
DECLARE +
0004.00 THE PARM THAT IS PASSED IN
*/
0005.00 DCL VAR(&USR2) TYPE(*CHAR) LEN(12) /*
USERNAME +
0006.00 W/ QUOTES FOR STRQMQRY USE
*/
0007.00 DCL VAR(&DLM) TYPE(*CHAR) LEN(1)
VALUE('''')
0008.00 CHGVAR VAR(&USR2) VALUE(&DLM *TCAT &USER
*TCAT +
0009.00 &DLM) /* ADD QUOTES TO EITHER SIDE
OF THE +
0010.00 USER VARIABLE SO IT CAN BE USED IN
THE +
0011.00 QUERY MANAGER QUERY
*/
0012.00 STRQMQRY QMQRY(QGPL/DLTUSRP) RDBCNNMTH(*DUW)
+
0013.00 NAMING(*SQL) SETVAR((USR
&USR2))
0014.00 STRQMQRY QMQRY(QGPL/DLTUSPP) RDBCNNMTH(*DUW)
+
0015.00 NAMING(*SQL) SETVAR((USR
&USR2))
0016.00 STRQMQRY QMQRY(QGPL/DLTUSAP) RDBCNNMTH(*DUW)
+
0017.00 NAMING(*SQL) SETVAR((USR
&USR2))
0018.00 IF COND(&USER *EQ '***********')
THEN(CHGVAR +
0019.00 VAR(&BADUSR)
VALUE('1'))
0020.00 IF COND(&USER *EQ '*********')
THEN(CHGVAR +
0021.00 VAR(&BADUSR)
VALUE('1'))
0022.00 IF COND(&BADUSR *EQ '0')
THEN(DO)
0023.00 DLTUSRPRF USRPRF(&USER)
OWNOBJOPT(*DLT)
0024.00 MONMSG
MSGID(CPF2204)
0025.00
ENDDO
0026.00 IF COND(&BADUSR *EQ '1') THEN(SNDUSRMSG
+
0027.00 MSG('Not allowed to delete this
user') +
0028.00
MSGTYPE(*INFO))
0029.00
ENDPGM

The QM queries are all the same as far as the bottom variable goes, so
I will post the first one:

Type SQL
Statement
************************** Beginning of Data
*********************************
0001.00 DELETE FROM CPDATA.AUSAP
WHERE
0002.00 USAKUSR =
&USR
***************************** End of Data
************************************

This is where I had to pad the variable with the single quotes because
USAKUSR is a character field. I couldn't figure out how to do that in
the query, so I did it in the CL.

Now, I get a different error than the one you posted below when I
execute the call via DbVisualizer(JDBC app, using the JT400.JAR
driver).

Job . . : QZDASSINIT User . . : QUSER Number . . . :
029182

Job 029182/QUSER/QZDASSINIT started on 11/08/11 at 09:12:49 in
subsystem
QUSRWRK in QSYS. Job entered system on 11/08/11 at
09:12:49.
User BLAW from client 1.2.3.4 connected to
server.
Token CHRIS was not valid. Valid tokens: <END-OF-
STATEMENT>. ********
RUN QUERY command failed with SQLCODE
-104.
RUN QUERY command ended due to
error.
STRQMQRY command
failed.
Function check. QWM2701 unmonitored by DLTUSR at statement
1200,
instruction
X'001A'.
QWM2701 received by DLTUSR at 1200. (C D I
R)

What puzzles me is the line I put the asterisks on. "Token CHRIS was
not valid". I made the call in interactive SQL like this:

call qgpl.dltusr('DUMMY')

I'm not sure where the CHRIS value comes from.

I get a different error, and it is indeed exactly the one you posted,
if I try that same call as above inside a STRSQL green-screen
interactive SQL session.

Anyway, the program works fine from an os/400 command line. Any
suggestions on what I can do to improve the code? I did write a "Hello
world" REXX program today after I found some docs on your mention of
that being another method to do this type of thing. Maybe that would
be a better way to tackle this.

Chris

[CRPence]

On 09-Nov-2011 18:19 , Hal wrote:
>> Hmmm, no info about the CREATE PROCEDURE; nor the QMQRY source,
>> nor the CLP source statement doing the STRQMQRY SETVAR(()). No
>> matter probably, since...
>>
>

> I apologize. I should have included all of the source code.
> Let me preface the code briefly...because I'm thinking you'll
> shudder when you see it. I have never had any as/400 programming
> training so what you see is the result of self-taught fumbling.
> With that..here it is.
>

> PGM PARM(&USER)

> DCL VAR(&BADUSR) TYPE(*LGL) VALUE('0')

> DCL VAR(&USER) TYPE(*CHAR) LEN(10) /* DECLARE +

> THE PARM THAT IS PASSED IN */

> DCL VAR(&USR2) TYPE(*CHAR) LEN(12) /* USERNAME +

> W/ QUOTES FOR STRQMQRY USE */

> DCL VAR(&DLM) TYPE(*CHAR) LEN(1) VALUE('''')

> CHGVAR VAR(&USR2) VALUE(&DLM *TCAT &USER *TCAT &DLM) +

> /* ADD QUOTES TO EITHER SIDE OF THE +

> USER VARIABLE SO IT CAN BE USED IN THE +

> QUERY MANAGER QUERY */
> STRQMQRY QMQRY(QGPL/DLTUSRP) RDBCNNMTH(*DUW) +
> NAMING(*SQL) SETVAR((USR &USR2))
> STRQMQRY QMQRY(QGPL/DLTUSPP) RDBCNNMTH(*DUW) +
> NAMING(*SQL) SETVAR((USR &USR2))
> STRQMQRY QMQRY(QGPL/DLTUSAP) RDBCNNMTH(*DUW) +
> NAMING(*SQL) SETVAR((USR &USR2))
> IF COND(&USER *EQ '***********') +
> THEN( CHGVAR VAR(&BADUSR) VALUE('1'))
> IF COND(&USER *EQ '*********') +
> THEN( CHGVAR VAR(&BADUSR) VALUE('1'))

> IF COND(&BADUSR *EQ '0')
> THEN(DO)

> DLTUSRPRF USRPRF(&USER) OWNOBJOPT(*DLT)
> MONMSG MSGID(CPF2204)
> ENDDO
> IF COND(&BADUSR *EQ '1') +
> THEN( SNDUSRMSG MSG('Not allowed to delete this user') +
> MSGTYPE(*INFO) )

> ENDPGM
>
> The QM queries are all the same as far as the bottom variable goes,
> so I will post the first one:
>

> SQL Statement:
> ********* Begin of Data *********
> DELETE FROM CPDATA.AUSAP
> WHERE USAKUSR = &USR
> ********** End of Data **********

>
> This is where I had to pad the variable with the single quotes
> because USAKUSR is a character field. I couldn't figure out how
> to do that in the query, so I did it in the CL.

FWiW: Since the "Variable value" on the SETVAR parameter allows an
expression, the concatenation can be done as an expression on the SETVAR
rather than in a [temporary] variable.

strqmqry dlmusrqm setvar((USR (&dlm *cat &usr *cat &dlm)))

> Now, I get a different error than the one you posted below when I
> execute the call via DbVisualizer(JDBC app, using the JT400.JAR
> driver).
>

> Job: QZDASSINIT User: QUSER Number: 029182

> Job 029182/QUSER/QZDASSINIT started
> on 11/08/11 at 09:12:49 in subsystem QUSRWRK in QSYS.
> Job entered system on 11/08/11 at 09:12:49.
> User BLAW from client 1.2.3.4 connected to server.
> Token CHRIS was not valid. Valid tokens:<END-OF-STATEMENT>.

> RUN QUERY command failed with SQLCODE -104.
> RUN QUERY command ended due to error.
> STRQMQRY command failed.
> Function check. QWM2701 unmonitored by DLTUSR at statement
> 1200, instruction X'001A'.
> QWM2701 received by DLTUSR at 1200. (C D I R)
>
> What puzzles me is the line I put the asterisks on. "Token CHRIS was
> not valid". I made the call in interactive SQL like this:
>
> call qgpl.dltusr('DUMMY')
>
> I'm not sure where the CHRIS value comes from.

I infer the same invocation was made via JDBC.? On the old release I
have to test, the STRQMQRY has an obvious problem with NAMING(*SQL). I
get msgQWM2707 suggesting that "*LIBL not allowed when SQL naming
applied" even though I had no unqualified references in the query. I
presume a variation of the same problem may exist on the release where
the above JDBC was attempted, but now manifest with the "authorization
identifier", the user name CHRIS, being added to the statement text
similar to how the *LIBL seems to have impacted my similar invocation.

When I used NAMING(*SYS), the CLP as stored procedure [i.e. the
STRQMQRY request(s)] functioned well from DB2 command line in QShell and
from a REXX SQL CALL.

> I get a different error, and it is indeed exactly the one you
> posted, if I try that same call as above inside a STRSQL
> green-screen interactive SQL session.
>
> Anyway, the program works fine from an os/400 command line. Any
> suggestions on what I can do to improve the code?

Without knowing more about requirements, who\when called and why, I
have little to suggest; I would remove or resend the error(s), but that
is overkill for most operator-like utilities. I have a preference for
different formatting than what SEU provides, but what I prefer is
unlikely helpful to anyone.

> I did write a "Hello world" REXX program today after I found some
> docs on your mention of that being another method to do this type
> of thing. Maybe that would be a better way to tackle this.
>

Avoiding the restriction from STRSQL, and using system naming instead
of SQL naming, perhaps what exists already, could be made to function as
expected. I would probably use REXX instead though, just because of the
flexibility to use both CL and SQL; being new to REXX and SQL in REXX
might be a burden. Note: a CMD can invoke a REXX procedure instead of a
*PGM as CPP, and LANGUAGE REXX can be defined for an external stored
procedure. Also FWiW, for "destructive" work, I tend to generate a
script for running later or force prompting for running now; i.e.
DLTxxxyyy often might better be reviewed versus performed directly in
the program with little visibility to the invoker, beyond logged
[completion] messages.

Regards, Chuck