DSSetParam doesn't seem to set the value of the parameter

[jdulaney]

I am trying to write a routine that reads a hashed file and, if a certain value exists, sets the value of the parameter which becomes the BEFORE SQL in a DRS Stage in the next job in the sequence. (There are other ways that I can think of to do this, but, due to the number of rows projected in the tables, I do not want any joins or extraneous where clauses in any SQL, nor do I want to run the BEFORE SQL if the rows do not exist in the database.)

I have written the following code to set the parameter to the value I need, but, although I'm getting a good return value, the value of the parameter isn't set. I can see it isn't getting set from the messages returned, but there are no errors returned and I can't tell WHY it isn't being set.

Can anyone give me some pointers on debugging this routine or tell me what I did wrong? I have searched posts and read documentation, but can't seem to find anything on this topic that helps. I've tried setting the parameter to a variable, a string, and a number and that doesn't seem to make a difference. (The message statements are simply there for debugging).

Code: Select all

$INCLUDE DSINCLUDE JOBCONTROL.H
      CallingProgName = "GetPayrollCRId"
      If NOT(Initialized) Then
         * Not initialised. Attempt to open the file.
         Initialized = 0
         Open "SDKPayPItoProcess" TO PayrollInstanceIDFile Else
            * Open failed. Create the sequence file.
            EXECUTE "CREATE.FILE SDKNumPaytoProcess 2 1 1"
            Open "SDKPayPItoProcess" TO PayrollInstanceIDFile Else Before_SQL = -1
         End

         * Attempt to read the named record from the file.
         Readv CalRunID From PayrollInstanceIDFile, JobName, 2 Else
            CallingProgName = JobName
            Message = "Failed to Retrieve CalRunID ": CalRunID : " Jobname is " : JobName
            Call DSLogWarn(Message,CallingProgName)
         End
      End
      Close PayrollInstanceIDFile



      CalRunID = TrimB(CalRunID)

      Open "HashCalRunIDs" TO CalRunIDFile Then

         * Attempt to read the named record from the file.
         Read PROCESS_INSTANCE from CalRunIDFile, CalRunID Then


            If TrimB(Delete_Rows) = "Y" Then
               Before_SQL = "DELETE FROM ": OWS_SCHEMA : "PS_GP_EPM_GUI_TMP WHERE CAL_RUN_ID = '": CalRunID : "'"

               JobHandle = DSAttachJob("J_Stage_PS_GP_EPM_GUI_TMP", DSJ.ERRNONE)
               Message = "1 " : JobHandle
               Call DSLogInfo(Message,CallingProgName)

               ErrCode = DSSetParam(JobHandle, 'DELETE_SQL',Before_SQL)
               Message = "2 " : ErrCode
               Call DSLogInfo(Message,CallingProgName)

               ParmValue = DSGetParamInfo(JobHandle, "DELETE_SQL",DSJ.PARAMVALUE)
               Message = ParmValue
               Call DSLogInfo(Message,CallingProgName)

               ErrCode2 = DSDetachJob(JobHandle)
               Message = "3 " : ErrCode2
               Call DSLogInfo(Message,CallingProgName)


            End
         End
         Else
            Message = "Read Failed " : PROCESS_INSTANCE
            Call DSLogInfo(Message,CallingProgName)
            Before_SQL = "Read Failed"
         End
      End
      Else
         Before_SQL = -1
      End

      Ans = 0

      Message = Ans : " " : CalRunID
      Call DSLogInfo(Message,CallingProgName)
      Close CalRunIDFile

Here are the results of the test...

Code: Select all

TEST #1
*******
 
Arg1 = Y
Arg2 = psepm.
Arg3 = J_Cntrl_PS_Z_GP_CAL_INTRFC_2
Arg4 = New Value
 
Test completed.
 
Program "DSU.GetPayrollCRId": Line 6, Variable "Initialized" previously undefined.  Zero used.
DSLogInfo called from : GetPayrollCRId
Message to be logged is...
> 1 2
DSLogInfo called from : GetPayrollCRId
Message to be logged is...
> 2 0
DSLogInfo called from : GetPayrollCRId
Message to be logged is...
> select count(*) from ps.psdbowner
DSLogInfo called from : GetPayrollCRId
Message to be logged is...
> 3 0
DSLogInfo called from : GetPayrollCRId
Message to be logged is...
> 0 BH 2005S07

Result = 0

Thanks for your help,
Jeanne

[chulett]

Where/how are you actually using this routine? You'd need to run the job after setting the parameters... once you release the handle, the settings will be lost.

[jdulaney]

I wanted to be able to set the parameter here and use it when invoking the next job in the sequence. I didn't use a return value, because I need to set the parameter for 2 different jobs to 2 different values. So, I'm gonna have to parse the return value, I guess.

Thanks, guys.

Jeanne

[chulett]

I was wondering if that was the case. Yes, you'll need to pass back a delimited list of the values to be assigned and have the downstream job pick them up. If you are on a recent version, the actual parsing can be done right in the parameter assignment field.