Response to Private message on Update to sdkseq

[gateleys]

This is a Private message I received , which is as shown below. It is a query based on one of my recent posts on updating sdksequnces file.

This question is specific to you , so i am sending a private mail.Hope you help me.

Did you create this routine as a tranformer type routine?

How did you pass the input values to the Arg1 and Arg2 ? have you created afile with all the sequencenames and passed it?

i have the same requirement to do done.

Please let me know ?

Appreciate your help

Thanks
Paddu

My earlier post -

Hi, I was writing this test routine to update the SDKSequences next values. I have a bunch of sequences whose next values need to be updated. Of course, I could issue the command to do so via the Admin client, but that would take a lot of time. So, I wrote this simple routine -
Code:
NextValue = "'":Arg1:"'"
SequenceName = "'":Arg2:"'"
TclCommand = "UPDATE SDKSequences USING DICT VOC SET F1 = NextValue WHERE @ID = SequenceName;"
CALL DSExecute("TCL",TclCommand,ScreenOutput,ReturnCode)



It accepts 2 arguments Arg1 and Arg2 for NextValue and SequenceName, respectively and updates the SDKSequences file. However, I get the following error ..... seems like it doesn't like the fact that the way the Args are passed. I tried all the combinations (wit/without quotes) ... it doesn't perform the update.
Code:
Message to be logged is...
> Cmd log is: DataStage/SQL: syntax error. Unexpected symbol. Token was "NextValue".
> Scanned command was UPDATE SDKSequences USING DICT VOC SET F1 = NextValue
>

Of course, it does update the sequence if I hard code the values of the arguments (within single quotes).
How do I get to do that with the arguments?

[gateleys]

This is a Private message I received , which is as shown below. It is a query based on one of my recent posts on updating sdksequnces file.

This question is specific to you , so i am sending a private mail.Hope you help me.

Did you create this routine as a tranformer type routine?

How did you pass the input values to the Arg1 and Arg2 ? have you created afile with all the sequencenames and passed it?

i have the same requirement to do done.

Please let me know ?

Appreciate your help

Thanks
Paddu

Based on Craig's suggestions, I changed my query. The routine in its raw form is as follows -

Code: Select all

FUNCTION UpdateSDKSeq (Arg1, Arg2)

EQUATE RoutineName To 'UpdateSDKSeq'
NextValue    = "'":Arg1:"'"
SequenceName = "'":Arg2:"'"

TclCommand = "UPDATE SDKSequences USING DICT VOC SET F1 = ":NextValue:" WHERE @ID = ":SequenceName: ";"
CALL DSExecute("TCL",TclCommand,ScreenOutput,ReturnCode)
*You may embed code to handle exceptions here
Ans = 0

RETURN(Ans)

The code itself looks self-explanatory. But to answer your questions -

Did you create this routine as a tranformer type routine?
As you can see, it is a function

How did you pass the input values to the Arg1 and Arg2 ? have you created afile with all the sequencenames and passed it?
Since the SDKSequences can be accessed via a hashed file, use it (or a UV stage), with the filename SDKSequences and columns SeqName (varchar[20]) and NextValue (decimal [15]). So, your job design should be - HashedFile (pointing to SDKseq) ----> Transformer (call the above function with the 2 input cols as args)---->DummyOutput_Sequential_file.

Let me know if you have any questions.

[paddu]

Thanks a lot .


Thanks
Paddu

[ray.wurlod]

This could also be built as a server job, containing just a Transformer stage and a Hashed File stage, that generates exactly one row containing the sequence name and new value.