Issue with single quote in Job parameter.

[chulett]

How are you setting this? The 'not appropriate' problem usually comes from @FM characters in captured command output.

[Minhajuddin]

This time I tried something different.

Code: Select all

My jobX:

Row_Generator=======> Transformer ========> Sequential File
(Generate 10               (Adds a column
 dummy rows)               with the value
                                 from job parameter)

First I created a sequence and added the jobX to it. I hardcoded the value of the job in the sequence as char(039), And when I saw the output it was writing a single quote appended with a new line to the target file.

In the second try I passed the value of the job parameter from the job sequence, And it was writing the string char(039) to the output instead of a single quote.

I am at the end of my wits. All I want to do is pass a single quote through a job parameter to a job. Is there a way I can do this?

[ArndW]

minhajuddin - if the eCase isn't resolved then I don't understand why you are surprised that you can't pass a single quote in a parameter.

[Minhajuddin]

Alright, so, does that mean nobody ever needed to pass a single quote in their job parameters? If so, did you guys find a workaround?

I need to pass an SQL Query using job parameters and some of the queries have the timestamp masks in them.

Code: Select all

#sql_query#==> select EMP_ID, EMP_NAME, to_char(TIMESTAMP, 'MM-DD-YY HH:MI:SS') TIMESTAMP from emp

Is there a way in which I can do this in Datastage?

[shamshad]

What is the ACTUAL PARAMRETER value that you are trying to pass?

[chulett]

So you've tried passing \' for each single quote and it didn't work for you? What about making each single quote a pair of single quotes?

Code: Select all

select EMP_ID, EMP_NAME, to_char(TIMESTAMP, ''MM-DD-YY HH:MI:SS'') TIMESTAMP from emp

[Minhajuddin]

What is the ACTUAL PARAMRETER value that you are trying to pass?

I am trying to pass the whole query through a job parameter

So you've tried passing \' for each single quote and it didn't work for you? What about making each single quote a pair of single quotes? Code: select EMP_ID, EMP_NAME, to_char(T ...

I have tried passing \' for each single quote and it didn't work. Let me try that again and let me also try using double quotes instead of a single one.

Thanks for your help.

[chulett]

Make sure you understand - not double quotes but two single quotes.

[Minhajuddin]

Tried the following things, But with no success, the first line is the job paramter being passed. The lines after that are directly taken from the target file, for a input job parameters I got a newlines after each line of target data.

Code: Select all

-----------------------------
job_param=single\'quote
single\quote


single\quote

-----------------------------
job_param = single''quote
single\quote
single\quote
-----------------------------
job_param = single\'\'quote
single\\\quote
single\\\quote
-----------------------------
job_param = single'\''quote
single\\quote


single\\quote


-----------------------------
job_param = single\'\'\'quote
single\\\\quote


single\\\\quote

-----------------------------
job_param = single"'"quote

main_program: Data set, file set, or file "rowgen:DSLink2.v": Non-pipe (or non-virtual data set) 

with .v in its name [sil\step_il.C:8076]
Data set, file set, or file "tx:DSLink5.v": Non-pipe (or non-virtual data set) with .v in its 

name [sil\step_il.C:8076]
Data set, file set, or file "rowgen:DSLink2.v": Non-pipe (or non-virtual data set) with .v in its 

name [sil\step_il.C:8076]
Data set, file set, or file "tx:DSLink5.v": Non-pipe (or non-virtual data set) with .v in its 

name [sil\step_il.C:8076]
-----------------------------

[ArndW]

minhajuddin - you never answered on whether the eCase was "fixed" or not.

[OddJob]

For your query passed as a parameter as follows:
MySQLParam=select EMP_ID, EMP_NAME, to_char(TIMESTAMP, 'MM-DD-YY HH:MI:SS') TIMESTAMP from emp

When calling the job from the sequence, instead of passing the parameter directly to the job, use the following to force the apostrophes to be correctly passed:

EReplace(MySQLParam,"'","\'")

This will convert all single apostrpohes (') to escaped apostrophes (\'), thus forcing DataStage to be more apostrophe friendly!

Hope this helps!

[Minhajuddin]

minhajuddin - you never answered on whether the eCase was "fixed" or not.

@ArndW: Thanks for the help, I don't know if this Ecase has been solved. But, I guess it would be the same with other installations as well. And fortunately we have another server with DS 8.x on it. And when I tested the passing single quotes in the job parameters it was not doing it properly. So, I guess this issue has not yet been fixed. But I was hoping to find a workaround.

For your query passed as a parameter as follows:
MySQLParam=select EMP_ID, EMP_NAME, to_char(TIMESTAMP, 'MM-DD-YY HH:MI:SS') TIMESTAMP from emp

When calling the job from the sequence, instead of passing the parameter directly to the job, use the following to force the apostrophes to be correctly passed:

EReplace(MySQLParam,"'","\'")

This will convert all single apostrpohes (') to escaped apostrophes (\'), thus forcing DataStage to be more apostrophe friendly!

Hope this helps!

Thanks for your help OddJob, But this doesn't make the job parameters work.

Code: Select all

I tried passing a job parameter "single\quote" to the sequence with the Ereplace(job_par,"'","\'") But it didn't work.
It wrote 
---------------
single\quote


---------------
in the target file

[Minhajuddin]

minhajuddin - you never answered on whether the eCase was "fixed" or not.

@ArndW: Thanks for the help, I don't know if this Ecase has been solved. But, I guess it would be the same with other installations as well. And fortunately we have another server with DS 8.x on it. And when I tested the passing single quotes in the job parameters it was not doing it properly. So, I guess this issue has not yet been fixed. But I was hoping to find a workaround.

For your query passed as a parameter as follows:
MySQLParam=select EMP_ID, EMP_NAME, to_char(TIMESTAMP, 'MM-DD-YY HH:MI:SS') TIMESTAMP from emp

When calling the job from the sequence, instead of passing the parameter directly to the job, use the following to force the apostrophes to be correctly passed:

EReplace(MySQLParam,"'","\'")

This will convert all single apostrpohes (') to escaped apostrophes (\'), thus forcing DataStage to be more apostrophe friendly!

Hope this helps!

Thanks for your help OddJob, But this doesn't make the job parameters work.

Code: Select all

I tried passing a job parameter "single\quote" to the sequence with the Ereplace(job_par,"'","\'") But it didn't work.
It wrote 
---------------
single\quote


---------------
in the target file

[OddJob]

Are passing the literal text...
single\quote
..?

If you are, then there is no single quote in there, so it's not going to convert anything!

Why not try the following...
This is a 'single' quote
...and see what happens. I tried this on a 7.5.1 build and it worked fine.

[chulett]

Arnd - the eCase seems to be specific to passing a single quote, as in one quote not two or more. And it says it breaks the next parameter so I don't think it's applicable here. This is just your classic case where DataStage strips quotes from parameter values.

OddJob - read the examples more closely, he's not trying to pass the text you posted. And the EReplace won't help as it escapes the quotes in a manner that he has already tried and reported as not working.

M - you need to find some way to 'escape' the quotes. What I've posted has worked for many other people so I know it's possible... maybe it's a Windows thing, who knows. Have you asked your official Support provider how to handle this on your platform?