How to handle Equal Sign in Paramter
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
How to handle Equal Sign in Paramter
Hello All,
I am trying to pass the Oracle SQL query as paramter in DS job and when I run the job datastage simply trim the string where it find the first equal sign inside the query.
Could anyone of you tell how to handle the equal sign if paramter value itlself containing equal sign.
For Example : I am passing following SQL as paramter
[b]select * from emp where empname='ABC'[/b]
Datastage read only as follows:
[b]P_SQL=select * from emp where empname[/b]
where P_SQL is parameter name.
Thanks,
Satish
I am trying to pass the Oracle SQL query as paramter in DS job and when I run the job datastage simply trim the string where it find the first equal sign inside the query.
Could anyone of you tell how to handle the equal sign if paramter value itlself containing equal sign.
For Example : I am passing following SQL as paramter
[b]select * from emp where empname='ABC'[/b]
Datastage read only as follows:
[b]P_SQL=select * from emp where empname[/b]
where P_SQL is parameter name.
Thanks,
Satish
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
Yes I tried putting sigle quotes as well but it truncate from equal sign.
Well I have 2 jobs one is master(Server Job) and one is detail(Parrellel Job). Master job invoke detail job by server routine and server routine need SQL query as paramter. And SQL query is coming from control table.That's the rqeirement.
Hope this help..
Well I have 2 jobs one is master(Server Job) and one is detail(Parrellel Job). Master job invoke detail job by server routine and server routine need SQL query as paramter. And SQL query is coming from control table.That's the rqeirement.
Hope this help..
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
The equal sign shouldn't be a problem, I have jobs that do that right now. I believe it is your single quotes causing you grief. You need to escape these special characters using the backslash.
select * from emp where empname=\'ABC\'
select * from emp where empname=\'ABC\'
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
$INCLUDE DSINCLUDE JOBCONTROL.H
Equate RoutineName To 'RunJob'
Equate RunJobName to Arg1
Equate Params To Arg2
Equate RowLimit To Arg3
Equate WarnLimit To Arg4
Dim Param(100,2) ; * Limited to max of 100 parameters
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Deffun DSRTimestamp Calling "DSR_TIMESTAMP"
JobHandle = ''
Info = ''
ParamCount = Dcount(Params,'|')
If RowLimit = '' Then RowLimit = 0
If WarnLimit = '' Then WarnLimit = 0
For ParamNum = 1 to ParamCount
Param(ParamNum,1) = Field(Field(Params,'|',ParamNum),'=',1)
Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2)
Next ParamNum
JobHandle = DSAttachJob(RunJobName, DSJ.ERRFATAL)
* Prepare the job
ErrorCode = DSPrepareJob(JobHandle)
Message = DSRMessage('DSTAGE_TRX_I_0014', 'Attaching job for processing - %1 - Status of Attachment = %2', RunJobName:@FM:JobHandle )
Call DSLogInfo(Message, RoutineName)
LimitErr = DSSetJobLimit(JobHandle, DSJ.LIMITROWS, RowLimit)
LimitErr = DSSetJobLimit(JobHandle, DSJ.LIMITWARN, WarnLimit)
* Need to check if error occurred.
ListOfParams = DSGetJobInfo(JobHandle, DSJ.PARAMLIST)
ListCount = Dcount(ListOfParams,',')
For ParamNum = 1 To ParamCount
Message = DSRMessage('DSTAGE_TRX_I_0015', 'Setting Job Param - %1 Setting to %2', Param(ParamNum,1):@FM:Param(ParamNum,2))
Call DSLogInfo(Message, RoutineName)
ErrCode = DSSetParam(JobHandle, Param(ParamNum,1),Param(ParamNum,2))
Next ParamNum
ErrCode = DSRunJob(JobHandle, DSJ.RUNNORMAL)
ErrCode = DSWaitForJob(JobHandle)
Status = DSGetJobInfo(JobHandle, DSJ.JOBSTATUS)
If Status = DSJS.RUNFAILED Then
Message = DSRMessage( 'DSTAGE_TRX_E_0020', 'Job Failed: %1', RunJobName)
Call DSLogWarn(Message, RoutineName)
End
Ans = Status
Arg 2 is important paramter that accept multiple values sepereated by piple and one them is SQL query.
Equate RoutineName To 'RunJob'
Equate RunJobName to Arg1
Equate Params To Arg2
Equate RowLimit To Arg3
Equate WarnLimit To Arg4
Dim Param(100,2) ; * Limited to max of 100 parameters
Deffun DSRMessage(A1, A2, A3) Calling "*DataStage*DSR_MESSAGE"
Deffun DSRTimestamp Calling "DSR_TIMESTAMP"
JobHandle = ''
Info = ''
ParamCount = Dcount(Params,'|')
If RowLimit = '' Then RowLimit = 0
If WarnLimit = '' Then WarnLimit = 0
For ParamNum = 1 to ParamCount
Param(ParamNum,1) = Field(Field(Params,'|',ParamNum),'=',1)
Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2)
Next ParamNum
JobHandle = DSAttachJob(RunJobName, DSJ.ERRFATAL)
* Prepare the job
ErrorCode = DSPrepareJob(JobHandle)
Message = DSRMessage('DSTAGE_TRX_I_0014', 'Attaching job for processing - %1 - Status of Attachment = %2', RunJobName:@FM:JobHandle )
Call DSLogInfo(Message, RoutineName)
LimitErr = DSSetJobLimit(JobHandle, DSJ.LIMITROWS, RowLimit)
LimitErr = DSSetJobLimit(JobHandle, DSJ.LIMITWARN, WarnLimit)
* Need to check if error occurred.
ListOfParams = DSGetJobInfo(JobHandle, DSJ.PARAMLIST)
ListCount = Dcount(ListOfParams,',')
For ParamNum = 1 To ParamCount
Message = DSRMessage('DSTAGE_TRX_I_0015', 'Setting Job Param - %1 Setting to %2', Param(ParamNum,1):@FM:Param(ParamNum,2))
Call DSLogInfo(Message, RoutineName)
ErrCode = DSSetParam(JobHandle, Param(ParamNum,1),Param(ParamNum,2))
Next ParamNum
ErrCode = DSRunJob(JobHandle, DSJ.RUNNORMAL)
ErrCode = DSWaitForJob(JobHandle)
Status = DSGetJobInfo(JobHandle, DSJ.JOBSTATUS)
If Status = DSJS.RUNFAILED Then
Message = DSRMessage( 'DSTAGE_TRX_E_0020', 'Job Failed: %1', RunJobName)
Call DSLogWarn(Message, RoutineName)
End
Ans = Status
Arg 2 is important paramter that accept multiple values sepereated by piple and one them is SQL query.
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
I have you tried running the job from director to isolate an issue from the job from an issue with your job script. I do not have any issues running jobs with equal signs in a string parameter.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
Me thinks the problem lies here.
You've specifically just pulled everything before the second equal sign and left the rest behind. This should help:
Code: Select all
For ParamNum = 1 to ParamCount
Param(ParamNum,1) = Field(Field(Params,'|',ParamNum),'=',1)
Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2)
Next ParamNum
Code: Select all
Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2,2)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 20
- Joined: Sun Dec 20, 2009 10:46 pm
I agree with Willams. In my jobs, I am also passing the sqlquery as parameter but I see no problem with '=' sign.
But whenever I need to use quotes I am used backslash operator.
But whenever I need to use quotes I am used backslash operator.
Last edited by partheev123 on Wed Jun 16, 2010 9:28 am, edited 1 time in total.
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am
I would this solution and keep you posted..thankschulett wrote:Me thinks the problem lies here.
You've specifically just pulled everything before the second equal sign and left the rest behind. This should help:Code: Select all
For ParamNum = 1 to ParamCount Param(ParamNum,1) = Field(Field(Params,'|',ParamNum),'=',1) Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2) Next ParamNum
Code: Select all
Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2,2)
-
- Participant
- Posts: 28
- Joined: Mon Nov 28, 2005 1:23 am