escaping script variable strings

[#1]

I'm loading a script variable, say vS, with a load/peek which I then need to use in a subsequent load/SQL. The example below illustrates the problem using inline/resident rather than SQL data sources, but the issue is the same.

some_metadata:LOAD * INLINE [Sh'ello];temp:NOCONCATENATE LOAD SRESIDENT some_metadata;LET vS = PEEK('S', 0, 'temp');DROP TABLE temp;raw_data:LOAD * INLINE [raw_x,raw_y1,goodbye2,h'ello];data:LOAD raw_x as x, raw_y as yRESIDENT 'raw_data'WHERE raw_y = '$(vS)';

This works until S contains a single quote. I'm familiar with decomposing a string and then using '&' concatenation and CHR(39) in some contexts, but I'm not sure what to do in this case.

[Miguel_Angel_Baeyens]

Hi,

try the following in your last WHERE clause

WHERE Replace(raw_y, chr(39), chr(39) & chr(39)) = '$(vS)';

Hope that helps.

[boorgura]

Miguel,

that was a cool thing to know.

Thanks.

[#4]

Thanks, that works!

I assumed my problem was that the expansion of --'$(vS)'-- would be --'h'ello'-- and break the script. Since your suggestion worked, that suggests that QV is taking care of allowing script variables with single quotes to be expanded inside string literals (with single quotes) by doubling up the inner single quotes (which is also the SQL convention, I think). So the expansion becomes --'h''ello'--. Then doubling up the quotes that are literally present in the data evens things up for the comparison.

Can anyone comment on my interpretation of what QV is doing? Obviously thinking QV was doing the very simplest text substitution for the script variable expansion was wrong. It would be great to hear someone describe the complete picture. The documentation I've read doesn't go deep enough for me. For example, do single quotes always turn into pairs of single quotes during expansion, or only if the expansion is inside single quotes? Are there any other things to know about expansion?

[Anonymous]

In similar cases, I simply remove single quote from the string completely:
LET vS = purgechar(peek(...), chr(39))
WHERE purgechar(ray_y, chr(39)) = ...

[Anonymous]

You're right, QlikView script replaces
WHERE raw_y = '$(vS)'
with
WHERE raw_y = 'h'ello'
You can see this in the log file.

Now, the problem here is that the correct syntax
WHERE raw_y = 'h'
is followed by ello, which it doesn't understand at all. And, the last quota is interpreted as the opening of a string that maybe never ends - the script is broken.

[#7]

Thanks for your input. I'm confused. I tested what Miguel posted, and it works. How can what you're saying also be true? In my last post, I was saying that I had falsely assumed what you are describing would happen.

Do you have a test case which illustrates what you are stating in your post? I just tested it again. I trust you're seeing that in the log, but maybe the log isn't presenting things accurately. I added more side effects to observe what happens after the statement in question when the script variable contains a single quote. The never-closing single quote problem is not happening in my test.

[Anonymous]

I don't see any contradiction between Miguel's solution and my statement. I'm certain that it works, only suggesting an alternative which I prefer. There are often many ways to achieve the same result.
As for the log - turrn the logging on, and see what will be created. I've never had a situation where log misrepresents actual script execution.

[#9]

Agreed; there are many ways to achieve the same result. I like the purgechar() approach too.

The contradiction involves your 11-05-2010 2:25 PM post. Miguel's solution works. It leaves the --'$(vS)'-- in the script. The value of S in the data table is --h'ello--, so by your statement, the script is broken when --'$(vS)'-- expands if vS contains a single quote. But it's not broken, and/or I'm making mistakes in my testing. Here's my new test case. Can you explain why it does work?

some_metadata:
LOAD * INLINE [
S
h'ello
];

temp:
NOCONCATENATE LOAD S
RESIDENT some_metadata;
LET vS = PEEK('S', 0, 'temp');
DROP TABLE temp;

raw_data:
LOAD * INLINE [
raw_x,raw_y
h'ello,goodbye
2,h'ello
];

data:
LOAD
 raw_x as x, raw_y as y
RESIDENT 'raw_data'
WHERE
 Replace(raw_y, chr(39), chr(39) & chr(39)) = '$(vS)' OR
 Replace(raw_x, chr(39), chr(39) & chr(39)) = '$(vS)';

vTest=999;

[Anonymous]

OK, I just tried. Have to tell, that to my surpise, the line
WHERE raw_y = '$(vS)'
was translated to 'h''ello' - two quotes between h and ello, although the value of the variable is "h'ello", with only one quote. So, there is no never-closing quote, there are two - 'h', and 'ello'. Something to think about...
I also tried the purgechar() - worked as expected.