passing variable in CL Program using QMQRY and SQL

160 pts.
Tags:
CL Program
Physical File
QMQRY
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Bggas400
    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
    160 pointsBadges:
    report
  • Bggas400
    Never mind Tom. the 'q' was capitalized 'Q'. It works fine now. Thanks. Barry
    160 pointsBadges:
    report
  • TomLiotta
    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:
    1. You can't have quotes around a replacement variable as part of the compiled statement.
    2. And you can't let the statement run without any quotes when the replacement variable represents a character.
    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:
    DELETE FROM mylib/myfile WHERE myfile = &q&USER&q
    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:
    DELETE FROM mylib/myfile WHERE myfile = &Q&USER&Q
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following