DSXchange

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

Did you try using backslash before the equal to symbol ?

You can change the SQL using IN clause.

Yes tried but it still truncate the from eqal sign.

Yes we can reaplce equal sign by IN clause ..but that is last option I was thinking to chnage the control table query.

Thanks for your input...

Is this from the command line using dsjob? Have you tried quoting the entire string?

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..

You've got single quotes in the parameter so you'd have to use double-quotes around it. Can you post your routine so we can see how you are passing this?

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\'

$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.

I tried the same but it result as follows:

select * from emp where empname

you solution will be applicable handling the single quotes.

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.

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:

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.

Partheev,

Yes I agree with you..if you pass SQL query manulay as paraemter value then it works for me. But my requirement is to pass the parameter at run time and it does not work...

Thanks,

chulett wrote:Me thinks the problem lies here.

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 

You've specifically just pulled everything before the second equal sign and left the rest behind. This should help:

Code: Select all

Param(ParamNum,2) = Field(Field(Params,'|',ParamNum),'=',2,2)

I would this solution and keep you posted..thanks

Thank you chulett..You solution solve my probelm. Appreaciate your help.