DSXchange

Posted: **Mon Mar 27, 2006 9:55 am**

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

Posted: **Mon Mar 27, 2006 10:29 am**

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.

Posted: **Mon Mar 27, 2006 1:41 pm**

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

Posted: **Mon Mar 27, 2006 1:47 pm**

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.

DSXchange

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

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

That's what I was afraid of....