Issue with single quote in Job parameter.
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
This time I tried something different.
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?
Code: Select all
My jobX:
Row_Generator=======> Transformer ========> Sequential File
(Generate 10 (Adds a column
dummy rows) with the value
from job parameter)
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?
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
Is there a way in which I can do this in Datastage?
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
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
I am trying to pass the whole query through a job parametershamshad wrote: What is the ACTUAL PARAMRETER value that you are trying to pass?
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.chulett wrote: 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 ...
Thanks for your help.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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]
-----------------------------
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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!
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!
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
@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.ArndW wrote:minhajuddin - you never answered on whether the eCase was "fixed" or not.
Thanks for your help OddJob, But this doesn't make the job parameters work.OddJob wrote: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!
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
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
@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.ArndW wrote:minhajuddin - you never answered on whether the eCase was "fixed" or not.
Thanks for your help OddJob, But this doesn't make the job parameters work.OddJob wrote: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!
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
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers