passing variable in CL Program using QMQRY and SQL
Hi, I'm setting up a process where I can add or delete a record in a physical file. I'm using a simple CL program that runs a couple QM Queries. QMQRY - REVEALADD INSERT INTO mylib/myfile (myfile) VALUES(user) QMQRY - REVEALDLT DELETE FROM mylib/myfile WHERE myfile = '&USER'
CL code IF COND(&OPTION *EQ 'DLT') THEN(GOTO CMDLBL(DLT)) ADD: STRQMQRY QMQRY(REVEALADD) SETVAR((USER &USER)) GOTO CMDLBL(END) DLT: STRQMQRY QMQRY(REVEALDLT) SETVAR((USER &USER))
The ADD statement workds fine but the delete does not. Is there something wrong with the SQL statement in QMQRY #2? I've tried many different variations of it but to no avail.
Thanks for any help Barry
Software/Hardware used:
CL code IF COND(&OPTION *EQ 'DLT') THEN(GOTO CMDLBL(DLT)) ADD: STRQMQRY QMQRY(REVEALADD) SETVAR((USER &USER)) GOTO CMDLBL(END) DLT: STRQMQRY QMQRY(REVEALDLT) SETVAR((USER &USER))
The ADD statement workds fine but the delete does not. Is there something wrong with the SQL statement in QMQRY #2? I've tried many different variations of it but to no avail.
Thanks for any help Barry
Software/Hardware used:
ASKED:
September 13, 2010 2:15 PM
UPDATED:
September 14, 2010 11:03 PM
Answer Wiki:
What's wrong is this statement:<pre>
DELETE FROM mylib/myfile WHERE myfile = '&USER'</pre>
You don't have the USER replacement variable anywhere in that statement.
It looks like you want to have it as part of the WHERE clause, but you actually have just a literal constant in the place where the the replacement variable should be. Compare what you have to this statement:<pre>
DELETE FROM mylib/myfile WHERE myfile = &USER</pre>
The second statement doesn't have quotes, so the literal constant is gone and the replacement variable can be recognized by the statement processor. The statement processor ignores everything inside of quotes because literals are always ignored by essentially all languages.
If you had a variable in RPG named USER and had a statement like MOVE 'USER' MYUSER, would you expect the value of the USER variable to show up in MYUSER? Or would you expect MYUSER simply to receive the value 'USER'? In CL, you might have CHGVAR &MYUSER '&USER'. What will &MYUSER contain after that statement runs?
In a QM query statement, you need to add the quotes at run-time. You can't have them be part of the compiled statement if you need them to enclose a replacement variable. If you compile the QM query with the quotes in place, the variable becomes a simple constant.
Fortunately, any part of the QM statement can be replaced by a replacement variable -- even quote marks.
There are two general ways to get the quote marks into your statement. Your CL program can concatenate quotes into the variable value that you pass into the query. Or you can use a separate replacement variable that just holds a quote mark that you put in different places in the QM statement.
For the first case, you might have a couple new variables in your CL program that look like this:<pre>
DCL &qUSER *CHAR 12
DCL &q *CHAR 1 VALUE( '''' )</pre>
The new &qUSER variable needs to be long enough to allow you to add quotes at the beginning and end. Then you might have a statement like this:<pre>
CHGVAR &qUSER VALUE( &q *cat &USER *tcat &q )</pre>
Your STRQMQRY would then pass the value of &qUSER into the USER replacement variable.
For the second case, you would change your QM statement to look like this:<pre>
DELETE FROM mylib/myfile WHERE myfile = &q&USER&q</pre>
There are now two places where a new replacement variable is going to be inserted. The new replacement variable is &q. (I used a lower-case "q" just to make it stand out a little. Be aware that replacement variable names actually are case-sensitive.)
To make that work, you need to have your STRQMQRY statement pass another value in:<pre>
STRQMQRY QMQRY(REVEALDLT)
SETVAR((USER &USER) ( 'q' '''' )</pre>
Now, wherever the &q appears in your QM statement, it will be replaced with a single-quote mark. (The value in the SETVAR() parameter is made up of four single-quote marks.)
In short, choose one alternative or the other. Either add quote marks to each of the actual character values that you pass in, or use a replacement variable to put single-quote marks at all of the places where replacement variables need to be surrounded by quotes.
I almost always use the second method. I can use the replacement variable in many places in the QM statement, and it has very minimal impact on any CL program that uses it.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Thanks Tom.
I had tried variations of your first option to no avail. However I modified it exactly how you show it and I get ‘Column MISBXG not in specified tables.’ which is what I’ve been getting all along.
Your statement of “DELETE FROM mylib/myfile WHERE myfile = &USER” I had tried before.
On your second option, I keep getting a prompt to “Type a value for variable “q” and press Enter.”
Once I input a ‘ quote, the record is deleted.
Now how do I get that to stop prompting?
Thanks,
Barry
Never mind Tom. the ‘q’ was capitalized ‘Q’. It works fine now. Thanks.
Barry
However I modified it exactly how you show it and I get ‘Column MISBXG not in specified tables.’ which is what I’ve been getting all along.
Since I don’t see a reference to “MISBXG” in your code, I assume that it arises from this:
Your statement of “DELETE FROM mylib/myfile WHERE myfile = &USER” I had tried before.
It’s almost certain that that won’t work, if I understand what you want. The purpose of the example was to demonstrate what happens when the quotes are left off of the statement. The result is that the value of &USER is inserted into the statement without any quotes. SQL will then interpret the value as the name of a column. So, if you pass “MISBXG” in for &USER, SQL will expect to find a column named MISBXG.
So, you should see that:
Since neither of those two alternatives will work, you must use a third alternative.
If the quotes can’t be there when you compile the QM query and if the quotes must be there before the SQL statement executes, the only reasonable third alternative is to add the quotes at run-time.
You can add them to the character value that you pass in to the &USER variable or you can pass in an extra replacement variable that will carry the quote mark.
To stop prompting for a replacement variable, you need to pass the variable into the SETVAR() parameter. I showed one way to do that:
STRQMQRY QMQRY(REVEALDLT) SETVAR((USER &USER) ( 'q' '''' )The &q variable is referenced in the SETVAR() parameter. But notice that it is a lower-case “q”. You don’t have to use a lower-case “q”. You can use an upper-case “Q” or any other valid name.
If you use a lower-case replacement variable name in the SQL statement, you must pass a lower-case name into the SETVAR() parameter. If you look at the SETVAR() parameter, you should see that the name is quoted. That keeps it as a lower-case letter.
It was an example:
The SQL statement has the name “&q” as a lower-case name. The name in the SETVAR() parameter must be exactly the same. If you use a lower-case name in the SQL statement, you must pass a lower-case name into the SETVAR() parameter.
You could use an upper-case name:
Then you would call the statement like this:
STRQMQRY QMQRY(REVEALDLT) SETVAR((USER &USER) ( Q '''' )Or this would also result in an upper-case name:
STRQMQRY QMQRY(REVEALDLT) SETVAR((USER &USER) ( q '''' )Since there are no quote marks around the “q”, it will automatically be upper-cased.
I can’t be certain what is causing the prompting in your program because you didn’t show your new CL nor your new SQL statement. But I’d guess it’s because you have one of them as upper-case and the other as lower-case.
Tom
BTW, this editor (and perhaps some browser features) doesn’t always handle single-quotes and double-quotes correctly. The example code should show four consecutive single-quote marks. However, sometimes are are incorrectly shown as two double-quote marks.
Take care when trying to understand how four single-quote marks work.
Tom