|
 iSeries / AS400 FAQ : General :
Pass parameter in STRQMQRY |
| I have a problem for to pass parameter to STRQMQRY in a CL program. If i run the command STRQMQRY fron command line run correctly but if i call the cl program with parameter the result in the form is null. I have create a CMD object for to pass parameter to cl program but the result is the same. Thanks Alberto. | |
| You have to put extra single quotes (apostrophes) around your text value in your CL program. When you run the STRQMQRY command in your CL program, it is going to look something like this: STRQMQRY QMQRY(somequery) SETVAR((QRYVAR &VAR1))Now, if you were to run the same query from the command line, it would look like this: STRQMQRY QMQRY(somequery) SETVAR((QRYVAR '''myvalue''')))The outer single quotes are simply to indicate the presence of a text string. The two pairs of inner quotes, when passed to the QM query, will each be resolved down to a single quote. Thus, in your query, the value you are trying to pass in SETVAR((QRYVAR '''myvalue'''))) will most likely appear in a WHERE clause, as (for example) WHERE COLUMN1 = 'myvalue' The trick is to get the appropriate number of single quotes into &VAR1 in your CL program. And it *is* tricky. What you have to remember is that the CL program, in passing the parameter to the QM query, will *automatically* insert single quotes around your text variable. Since the goal is to have three single quotes on either side of your text string in the variable, this means that you have to put two of them there explicitly. Let's say you have a two-byte text value you want to pass to your QM query. The, the CL variable is going to have to be 6 bytes in length, in order to have contain two single quotes on either side of your text value. To do this, and assuming that your two-byte value comes into the CL program as a parameter, you'd have to specify something like this: CHGVAR VAR(&VAR1) VALUE('''' || &PARM2 || '''')
If &PARM2 contained the value 'XX' (without the quotes), then &VAR1, following the CHGVAR command above, would contain ''XX'' (*with* the quotes). Then, when the STRQMQRY command runs, the operating system will *automatically* put another set of single quotes around the contents of &VAR1, so that if you have command logging in effect (LOGCLPGM(*YES)) and you look at the command in the job log, it would appear as:STRQMQRY QMQRY(somequery) SETVAR((QRYVAR '''XX''')))Note: in the CHGVAR command, you *cannot* use a double quote in place of the two single quotes outside the two concatenation operators; that is, CHGVAR VAR(&VAR1) VALUE('"' || &PARM2 || '"')
will not work. If you were to try this, the STRQMQRY command would look like this:STRQMQRY QMQRY(somequery) SETVAR((QRYVAR '"XX"')))and the query will fail; it will consider "XX" (*with* the double quotes) to be a column name. I hope this helps. | |
All the above is correct, but I'd just like to point out that if you use the
parameter to substitute something like a file name in a QMQUERY that has been
converted to SQL, then you DO have to use double-quotes rather than two single
ones.
For instance, if the statement is:
DELETE
FROM "MYLIB"/&FILE
WHERE FLD1 = 'ABC'
then if the file name were CUSTMAST, the relevant parts of the CL would read:
chgvar &var1 ('"' || CUSTMAST || '"')
.
.
.
STRQMQRY QMQRY(somequery) SETVAR((FILE &VAR1))
But if it were 'ABC' that were being substituted with a variable,
then it would have to be two single quotes either side as the last
post specified.
| |
 Append to This Answer | |
| brueraa, jonball52, arnie_flangehead |
| Previous: |
 OS400 Release Support Dates
|
| Next: |
How do I tune the AS/400 for Domino?
|
| ||||||||