Avoiding the stripping of single quote character

[vigneshra]

Hi All
We are facing a peculiar problem for which we need a solution. We are passing an entire query to DB2 Enterprise stage as a parameter. The query has lot of conditions which includes lot of single quote characters. The problem we are having here is, whenever we try to pass the query as a parameter to the job through dsjob command from command line, single quotes are stripped off which results in the failure of query.

For eg, Transaction_Date<'2004-01-01' in the query is interpreted as Transaction_Date<2004-01-01 which results in the query failure.

Can somebody help us in this regard. Any suggestions would be greatly appreciated. Thanks in advance. It's urgent!

[mpouet]

Hi alone,

You have to put \ or / (I don't remember) before the quote.

Have fun.
Matthieu.

[vigneshra]

Thanks Matthieu,
But even both the slashes did not work! Any other way of doing it?

[Sainath.Srinivasan]

Try '#DateParameter#'

[chulett]

You didn't read the whole post. They are passing the entire query as a parameter, not just the piece you've shown and they used to illustrate the problem. Your solution is fine (and exactly what we do) if that was all that was being sent.

Sometimes there is no solution as DataStage will strip quotes no matter what you do. However, a couple of suggestions:

1) Try quoting the quotes. Sometimes that means a single leading quote before each quote and sometimes that means quotes around every quote. Try both and see if either works.

2) If the stage supports a Custom SQL File option (not just Custom SQL) try writing your query to a file and then in the stage telling it the full pathname to the file. The path could be a job parameter, from what I recall.

[Sainath.Srinivasan]

In that case use a double quotes for the whole query and single quote for the parameter value.

Alternatively try ''' instead of ' in the parameter string.

[mpouet]

We use a query like this :

SELECT
KEY, Field1, Field2
FROM
TABLE
WHERE Field1 in (#PARAM1#)
OR Field2 in (#PARAM2#)

PARAM1 = \'VALUE1\'
PARAM2 = \'VALUE2\'

If we don't use \ then the query is seen like
SELECT
KEY, Field1, Field2
FROM
TABLE
WHERE Field1 in (VALUE1)
OR Field2 in (VALUE1)
and doesn't work.

Hope it can help
Matthieu

[vigneshra]

Hi
Nothing including one, two or three single quotes in place of one single quote is working. Is it a bug in DataStage? Is it fixed in latest version? We are using 7.0.1 ! Is there any other work around to achieve this? Craig, there is no such option called SQL file in DB2 enterprise stage.

I thought of something like cutting the entire query into parts based on the single quote like

SELECT * FROM EMP WHERE JOB_TYPE='CLERK' OR JOB_TYPE='ACCOUNTANT'

will be splitted into parts like
PARAM1=SELECT * FROM EMP WHERE JOB_TYPE=
PARAM2=CLERK
PARAM3=OR JOB_TYPE=
PARAM4=ACCOUNTANT

Later in the query section in DB2 stage, it will be put like
#PARAM1#'#PARAM2#'#PARAM3#'#PARAM1#'

Is it a good idea or is there any other neat way of achieving this? Please help!

[chulett]

It's a good idea (and should actually work!) provided your query can always be hacked into four parts like that. In keeping with the DataStage Way, you should really mention all of the fields being selected, not just short-cut it with an asterisk like that.

The question about the bug and a possible fix can only really be answered by Ascential Support - unless someone here knows it is fixed in a later version.

[T42]

The problem with quotes within parameters are simple:

1. If you call dsjob with parameters, quotes are stripped.
2. If you call the job sequencer which call individual jobs, quotes may be stripped.
3. If you call each individual jobs that calls an external tool (such as DB2 stage), quotes will be stripped.

There are many points where quotes can be stripped. To ensure that quotes are actually going to make to the stage, why not output the variable by adding it to a record, and spitting the result to a peek stage. Let us know if that actually happens.