DATASTAGE ROUTINES ________________________________________ BL: BOT v2.3.0 Returns BLANK if passed value is NOT NULL or BLANK, after trimming spaces DataIn = "":Trim(Arg1) If IsNull(DataIn) or DataIn = "" Then Ans = "" End Else Ans = DataIn End CheckFileRecords: Function CheckFileRecords(Arg1,Arg2) vParamFile = Arg1 : "/" : Arg2 vCountVal = 0 OpenSeq vParamFile To FileVar Else Call DSLogWarn("Cannot open ":vParamFile , "Cannot Open ParamFile") End Loop ReadSeq Dummy From FileVar Else Exit ;* at end-of-file vCountVal = vCountVal + 1 Repeat CloseSeq FileVar Ans=vCountVal Return (vCountVal) CheckFileSizes: DIR = "/interface/dashboard/dashbd_dev_dk_int/Source/" FNAME = "GLEISND_OC_02_20040607_12455700.csv" *CMD = "ll -tr ":DIR:"|grep ":FNAME CMD = "cmp -s ":DIR:"|grep ":FNAME Call DSExecute("UNIX", CMD, Output, SystemReturnCode)
Ans = Output CheckIdocsSent: Checks If Idoc delivery job actually sent any Idocs to SAP. This routine will atempt to read the DataStage Director log for the job name specified as an argument. If the job has a fatal error with "No link file", the routine will copy the IDOC link file(s) into the interface error folder. In case the fatal error above is not found the routine aborts the job. A simple log of which runs produce error link file is maintained in the module's log directory. $INCLUDE DSINCLUDE JOBCONTROL.H vRoutineName = "CheckIdocsSent" Ans = "Ok" If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Move = 'move ' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Move = 'mv -f ' End vJobHandle = DSAttachJob(JobName, DSJ.ERRFATAL) vLastRunStart = DSGetJobInfo(vJobHandle, DSJ.JOBSTARTTIMESTAMP) vLastRunEnd = DSGetJobInfo(vJobHandle, DSJ.JOBLASTTIMESTAMP) * Get the delivery log for the last run vLogSummary = DSGetLogSummary ( vJobHandle, DSJ.LOGANY, vLastRunStart, vLastRunEnd, 500) vLogSummary = Change(vLogSummary,@FM,'') * Manipulate vLogSummary within routine to return status PosOfStr = Index(Downcase(vLogSummary),"sent",1) vLogMsg = vLogSummary[PosOfStr,20] * Now work out Status If PosOfStr = 0 then Status = 'NOT SENT' vLogMsg = ''
end else Status = 'SENT' vLogMsg = vLogSummary[PosOfStr,20] end Ans = Status vErr = DSDetachJob(vJobHandle) Call DSLogInfo("Job " : JobName : " Detached" , vRoutineName)
***** Make a log entry to keep track of how often the pack doesn't work ******** vMessageToWrite = Fmt(Module_Run_Parm, "12' 'L") : Fmt(Status, "10' 'L") : " - " : vLogMsg vIdocLogFilePath = Interface_Root_Path_Parm: OsDelim : "logs" : OsDelim : "IdocSentLog.log" ******** Open the log file OPENSEQ vIdocLogFilePath TO vIdocLogFile Then Call DSLogInfo("IdocSentLog Open" , vRoutineName) ** Label to return to if file is created FileCreated: *** Write the log entry vIsLastRecord = @Null Loop Until vIsLastRecord Do READSEQ vRecord From vIdocLogFile Then *Call DSLogInfo("Record Read - " : vRecord , vRoutineName) End Else *Call DSLogInfo("End of file reached " , vRoutineName) vIsLastRecord = @True End Repeat WRITESEQ vMessageToWrite To vIdocLogFile Then Call DSLogInfo("Log entry created : " : vMessageToWrite, vRoutineName) End Else Call DSLogFatal("Cannot write to " : vIdocLogFilePath, vRoutineName) End End Else Call DSLogInfo("Could not open file - " : vIdocLogFilePath , vRoutineName) Call DSLogInfo("Creating new file - " : vIdocLogFilePath , vRoutineName) CREATE vIdocLogFile ELSE Call DSLogFatal("Could not create file - " : vIdocLogFilePath , vRoutineName) WEOFSEQ vIdocLogFile WRITESEQ Fmt("Module Run", "12' 'L") : Fmt("Status", "10' 'L") : " " : "Message" To vIdocLogFile Else ABORT Call DSLogInfo("Log file created : " : vIdocLogFilePath , vRoutineName)
GOTO FileCreated End **** Abort the delivery sequence and write error message to the log. ************ If Status = 'NOT SENT' Then Call DSLogInfo("No Idocs were actually sent to SAP - Trying to clean up IDOC Link Files: ", vRoutineName) vIdocSrcLinkPath = Field(Interface_Root_Path_Parm, OsDelim, 1, 4) : OsDelim : "dsproject" : OsDelim : Field(Interface_Root_Path_Parm, OsDelim, 4, 1) vIdocTgtLinkPath = Interface_Root_Path_Parm: OsDelim : "error" OsCmd = Move : " " : vIdocSrcLinkPath : OsDelim : JobName : ".*.lnk " : vIdocTgtLinkPath : OsDelim Call DSExecute(OsType, OsCmd, OsOutput, OsStatus) If OsStatus <> 0 Then Call DSLogWarn("Error when trying to move link file(s)", vRoutineName) LogMessMoveFail = 'The move command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput Call DSLogWarn(LogMessMoveFail, vRoutineName) Call DSLogFatal("Cleaning up of IDOC Link Files failed", vRoutineName) End Else LogMessMoveOK = "Link files were moved to " : vIdocTgtLinkPath Call DSLogInfo(LogMessMoveOK, vRoutineName) LogMessRetry = "Job " : JobName : " is ready to be relaunched." Call DSLogInfo(LogMessRetry, vRoutineName) End End Else Call DSLogInfo("Delivery job log indicates run OK ", vRoutineName) End ClearMappingTable: SUBROUTINE ClearMappingTable (Clear_Mapping_Table, Errorcode) Error Code = 0 ;* set this to non-zero to stop the stage/job **If Clear_Mapping_Table_Parm = 'Y' Then EXECUTE "CLEARFILE Vendor_Map_HF.GEN" **End Else **End ComaDotRmv: DataIn = "":(Arg1) If IsNull(DataIn) or DataIn = "" Then Ans = "" End Else DataIn = Ereplace(DataIn, ".", "")
DataIn = Ereplace(DataIn, ",", "") Ans = DataIn End CopyFiles: Move files from one directory to another Function CopyofFiles(sourceDir,SourceFileMask,TargetDir,TargetFileMask,Flags) RoutineName = "CopyFiles" If SourceDir = '' Then SourceDir = '.' If TargetDir = '' Then TargetDir = '.' If SourceFileMask = '' Or SourceDir = TargetDir Then Return(0) ! If SourceDir # '.' Then ! OpenPath SourceDir To Fv Else ! Call DSU.DSMkDir(MkStatus,SourceDir,'','777') ! End ! End ! If TargetDir # '.' Then ! OpenPath TargetDir To Fv Else ! Call DSU.DSMkDir(MkStatus,TargetDir,'','777') ! End ! End If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Copy = 'copy ' Flag = Flags End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Copy = 'cp -f ' End If Flags <> "" then Flag = NonOsDelim:Flags Else Flag = "" SourceWorkFiles = Trims(Convert(',',@FM,SourceFileMask)) SourceFileList = Splice(Reuse(SourceDir),OsDelim,SourceWorkFiles) Convert NonOsDelim To OsDelim In SourceFileList TargetWorkFiles = Trims(Convert(',',@FM,TargetFileMask)) TargetFileList = Splice(Reuse(TargetDir),OsDelim,TargetWorkFiles)
Convert NonOsDelim To OsDelim In TargetFileList OsCmd = Copy:' ' : Flag : " " :SourceFileList:' ':TargetFileList Call DSLogInfo('Copying ': SourceFileList: ' to ':TargetFileList,RoutineName) Call DSExecute(OsType,OsCmd,OsOutput,OsStatus) If OsStatus Then Call DSLogWarn('The Copy command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput, RoutineName) End Else Call DSLogInfo('Files moved...','DSMoveFiles') End Ans = OsStatus CopyofComareROWS: Function copyofcompareRows(Column_Name,Column_Value) $INCLUDE DSINCLUDE JOBCONTROL.H vJobName=DSGetJobInfo(DSJ.ME, DSJ.JOBNAME) vStageName=DSGetStageInfo(DSJ.ME, DSJ.ME, DSJ.STAGENAME) vCommonName=CheckSum(vJobName) : CheckSum(vStageName) : CheckSum(Column_Name) Common /vCommonName/ LastValue vLastValue=LastValue vNewValue=Column_Value If vNewValue<>vLastValue Then Ans=1 Else Ans=0 LastValue=vNewValue CopyOfZSTPKeyLookup Check if key passed exists in file passed Arg1: Hash file to look in Arg2: Key to look for Arg3: Number of file to use "1" or "2" * Routine to look to see if the key passed exists in the file passed * If so, then the non-key field from the file is returned * If not found, "***Not Found***" is returned * * The routine requires the UniVerse file named to have been created previously *
$INCLUDE DSINCLUDE JOBCONTROL.H EQUATE RoutineName TO 'ZSTPKeyLookup' * Call DSLogInfo("Routine started",RoutineName) Common /ZSTPkeylookup/ Init1, SeqFile1, Init2, SeqFile2, RetVal, msgtext Ans = 0 If NOT(Init1) And Arg3 = "1" Then * Not initialised. Therefore open file Init1 = 1 Open Arg1 TO SeqFile1 Then Clearfile SeqFile1 Else Call DSLogInfo("Open failed 1",RoutineName) msgtext = "Cannot open ZSTP creation control file ":Arg1 Call DSLogFatal(msgtext,RoutineName) Ans = -1 End End If NOT(Init2) And Arg3 = "2" Then * Not initialised. Therefore open file Init2 = 1 Open Arg1 TO SeqFile2 Then Clearfile SeqFile2 Else Call DSLogInfo("Open failed 2",RoutineName) msgtext = "Cannot open ZSTP creation control file ":Arg1 Call DSLogFatal(msgtext,RoutineName) Ans = -1 End End * Read the file to get the data for the key passed, if not found, return "***Not Found***" If Arg3 = "1" Then Read RetVal From SeqFile1, Arg2 Else RetVal = "***Not Found***" End Else Read RetVal From SeqFile2, Arg2 Else RetVal = "***Not Found***" End Ans = RetVal Create12CharTS: Function Create12CharTS(JobName) $INCLUDE DSINCLUDE JOBCONTROL.H
vJobHandle = DSAttachJob(JobName, DSJ.ERRFATAL) vJobStartTime = DSGetJobInfo(vJobHandle, DSJ.JOBSTARTTIMESTAMP) vDate = Trim(vJobStartTime, "-","A") vDate = Trim(vDate, ":","A") vDate = Trim(vDate, " ", "A") vDate = vDate[1,12] Ans=vDate CreateEmptyFile: Function CreateEmptyFile(Arg1,Arg2) *Create Empty File vParamFile = Arg1 : "/" : Arg2
OpenSeq vParamFile To FileVar Else Call DSLogWarn("Cannot open ":vParamFile , "Cannot Open ParamFile") End WeofSeq FileVar CloseSeq FileVar Ans="1" Datetrans: DateVal Function Datetrans(DateVal) Function DeleteFiles(SourceDir,FileMask,Flags) * Function ReverseDate(DateVal) * Date mat be in the form of DD.MM.YY i.e. 01.10.03 * convert to YYYYMMDD SAP format Ans = "20" : DateVal[7,2] : DateVal[4,2] : DateVal[1,2]
DeleteFiles: RoutineName = "DeleteFiles"
If SourceDir = '' Then SourceDir = '.' If FileMask = '' SourceDir = '' Then Return(0) If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Delete = 'del ' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Delete = 'rm ' : Flags : ' ' End WorkFiles = Trims(Convert(',',@FM,FileMask)) FileList = Splice(Reuse(SourceDir),OsDelim,WorkFiles) Convert NonOsDelim To OsDelim In FileList OsCmd = Delete :' ' : FileList Call DSLogInfo('Deleting ':FileList,RoutineName) Call DSExecute(OsType,OsCmd,OsOutput,OsStatus) If OsStatus Then Residx= Index(OsOutput,"non-existent",1) if Index(OsOutput,"non-existent",1) = 0 then Call DSLogInfo('The Delete command (':Residx:OsCmd:') returned status ':OsStatus:':':@FM:OsOutput,RoutineName) End Else Call DSLogInfo('No Files matched Wild Card - Delete was not required...',RoutineName) OsStatus = 0 End End Else Call DSLogInfo('Files deleted...',RoutineName) End Ans = OsStatus DisconnectNetworkDrive: Map a Network Drive on a Windows Server: Function Disconnectnetworkdrive(Drive_Letter) RoutineName = "MapNetworkDrive"
If Drive_Letter = '' Then Return(0) OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Copy = 'copy ' OsCmd = 'net use ' : Drive_Letter : ": /delete" Call DSLogInfo('Disconnecting Network Drive: ' : OsCmd,RoutineName) Call DSExecute(OsType,OsCmd,OsOutput,OsStatus) If OsStatus Then Call DSLogWarn('The Copy command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput, RoutineName) End Else Call DSLogInfo('Drive: ' : Drive_Letter : 'Disconnected ',RoutineName) End Ans = OsStatus DosCmd: Move files from one directory to another: Function DosCmd(Cmd) RoutineName = "DosCmd" If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' End OsCmd = Cmd Call DSLogInfo("CMD = " : Cmd,RoutineName) Call DSExecute(OsType,OsCmd,OsOutput,OsStatus) If OsStatus Then Call DSLogWarn('The command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput, RoutineName) End Else Call DSLogInfo('The command (':OsCmd:') was successfull ':OsStatus:':':@FM:OsOutput,RoutineName)
End Ans = OsStatus : " - " : OsOutput DSMoveFiles: Move files from one directory to another: f SourceDir = '' Then SourceDir = '.' If TargetDir = '' Then TargetDir = '.' If FileMask = '' Or SourceDir = TargetDir Then Return(0) If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Move = 'move ' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Move = 'mv -f ' End WorkFiles = Trims(Convert(',',@FM,FileMask)) FileList = Splice(Reuse(SourceDir),OsDelim,WorkFiles) Convert NonOsDelim To OsDelim In FileList OsCmd = Move:' ' : FileList: ' ':TargetDir Call DSLogInfo('Moving ':FileList: ' to ':TargetDir,'DSMoveFiles') Call DSExecute(OsType,OsCmd,OsOutput,OsStatus) If OsStatus Then Call DSLogInfo('The move command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput,'DSMoveFiles') End Else Call DSLogInfo('Files moved...','DSMoveFiles') End Ans = OsStatus Routine Name:ErrorMgmtDummy: Value: The Value to Be Mapped FieldName: The Name of the source field that the Value is contained in Format: The name of the Hash file containing the mapping data Default: The Default value to return if value is not found Msg: ny text you want to store against an error SeverityInd: The Error Severity Indicator: I-Information, W-Warning, E-Error, F-Fatal ErrorLogInd: An Indicator to indicate of errors should be logged (Note this is not yet
implemented) HashFileLocation: A Hashfile could be either local to the Module or Generic. Enter "G" for Generic "L" for Local * FUNCTION Map(Value,FieldName,Format,Default,Msg,ErrorLogInd) * * Executes a lookup against a hashed file using a key * * Input Parameters : Arg1: Value = The Value to be Mapped or checked * Arg2: FieldName = The Name of the field that is either the Target of the Derivation or the sourceField that value is contained in * Arg3: Format = The name of the Hash file containing the mapping data * Arg4: Default = The Default value to return if value is not found * Arg5: Msg = Any text you want stored against an error * Arg6: SeverityInd = An Indicator to the servity Level * Arg7: ErrorLogInd = An Indicator to indicate if errors should be logged * Arg8: HashfileLocation = An Indicator to indicate of errors should be logged (Note this is not yet implemented) * * Return Values: If the Value is not found, return value is: -1. or the Default value if that is supplied * If Format Table not found, return value is: -2 * * * RoutineName = 'Map' Common /HashLookup/ FileHandles(100), FilesOpened Common /TicketCommon/ Ticket_Group, Ticket_Sequence, Set_Key, Mod_Root_Path, Generic_Root_Path, Chk_Hash_File_Name, Mod_Run_Num DEFFUN LogToHashFile(ModRunNum,Ticket_Group,Ticket_Sequence,Set_Key,Table,FieldNam e,Key,Error,Text,SeverityInd) Calling 'DSU.LogToHashFile' If (Ans = "-1" or Ans = "-2" or UpCase(Ans)= "BLOCKED") and ErrorLogInd = "Y" Then Ret_Code=LogToHashFile(Mod_Run_Num,Ticket_Group,Ticket_Sequence,Set_Key,Ta ble,FieldName,Chk_Value,Ans,Msg,SeverityInd) End RETURN(Ans) FileExists: Move files from one directory to another Function File Exits(Filename) Routine Name = "File Exists"
File Found = @TRUE OPENSEQ FileName TO aFile ON ERROR STOP "Cannot open file (":FileName:")" THEN CLOSESEQ aFile END ELSE FileFound = @FALSE ;* file not found END Ans = FileFound FileSize: Returns the size of a file Function FileSize(FileName) RoutineName = "FileSize" FileSize = -99 OPENSEQ FileName TO aFile ON ERROR STOP "Cannot open file (":FileName:")" THEN status FileInfo from aFile else stop FileSize=Field(FileInfo,@FM,4) * FileSize=FileInfo CLOSESEQ aFile END ELSE FileSize = -999 END Ans = FileSize FindExtension: FunctionFindExtesion(Arg1) File_Name=Arg1 * Gets rid of the extension part of the filename LengthofFileName = Len(File_Name) Extension = Index(File_Name, ".", 1) If Extension <> 0 Then LengthofExtension = LengthofFileName - Extension + 1 File_Extension=File_Name[Extension,LengthofExtension] End Else End Ans = File_Extension FindFileSuffix:
Function FindFileSuffix(Arg1) File_Name=Arg1 * Gets rid of the extension part of the filename Extension = Index(File_Name, ".", 1) If Extension <> 0 Then MyLenRead=Index(File_Name, ".", 1) - 1 File_Name=File_Name[0,MyLenRead] End Else End * Gets the timestamp. Doesn't handle the case where there are suffix types and timestamp only contains 5 digits without "_" inbetween If Index(File_Name, "_", 6) = 0 Then MyLenRead=Index(File_Name, "_", 4) + 1 MyTimestamp = File_Name[MyLenRead,Len(File_Name)-1] End Else MyTimestamp = Field(File_Name,"_",5):"_":Field(File_Name,"_",6) End TimestampEndPos = Index(File_Name,MyTimestamp,1) + Len(MyTimestamp) MySuffix = File_Name[TimestampEndPos + 1, Len(File_Name)] Ans = MySuffix FindTimeStamp: Function FindTimeStamp(Arg1) File_Name=Arg1 * Gets rid of the extension part of the filename Extension = Index(File_Name, ".", 1) If Extension <> 0 Then MyLenRead=Index(File_Name, ".", 1) - 1 File_Name=File_Name[0,MyLenRead] End Else End * Gets the timestamp. Doesn't handle the case where there are suffix types and timestamp only contains 5 digits without "_" inbetween If Index(File_Name, "_", 6) = 0 Then MyLenRead=Index(File_Name, "_", 4) + 1 Timestamp = File_Name[MyLenRead,Len(File_Name)-1] End Else Timestamp = Field(File_Name,"_",5):"_":Field(File_Name,"_",6) End Ans = Timestamp
formatCharge: Function FormatCharge(Arg1) vCharge=Trim(Arg1, 0, "L") vCharge=vCharge/100 vCharge=FMT(vCharge,"R2") Ans=vCharge formatGCharge: Ans=1 vLength=Len(Arg1) vMinus=If Arg1[1,1]='-' Then 1 Else 0 If Arg1='0.00' Then Ans=Arg1 End Else If vMinus=1 Then vString=Arg1[2,vLength-1] vString='-':Trim(vString, '0','L') End else vString=Trim(Arg1, '0','L') end Ans=vString End FTPFile: Script_Path: he path to where the Unix Script file lives File_Path: The Value to Be Mapped File_Name: The Name of the source field that the Value is contained in IP_Address: The name of the Hash file containing the mapping data User_ID: The Default value to return if value is not found Password: Any text you want to store against an error Target_Path: The target path where the ifle is to saved on the target server
* FUNCTION FTPFile(Script_Path,File_Path,File_Name,IP_Address, User_ID,Password,Target_Path) * * RoutineName = 'FTPFile' OsCmd = Script_Path : "/ftp_put.sh":" ":File_Path:" ":File_Name:" ":IP_Address:" ":User_ID:" ":Password:" ":Target_Path :" ":Script_Path Call DSLogInfo('Ftp ':File_Name: ' to ' : IP_Address : ' ' :Target_Path,'FTPFile') Call DSLogInfo('Ftp Script = ':Script_Path,'FTPFile') Call DSExecute("UNIX",OsCmd,OsOutput,OsStatus) If OsStatus Then Call DSLogInfo('The FTP command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput,'DSMoveFiles') End Else Call DSLogInfo('Files FTPd...': '(':OsCmd:')','FTPFile') End Ans = OsStatus RETURN(Ans) FTPmget: * FUNCTION FTPFile(Script_Path,Source_Path,File_Wild_Card,IP_Address, User_ID,Password,Target_Path) * * RoutineName = 'FTPmget' OsCmd = Script_Path:"/ftp_Mget.sh":" ":Source_Path:" ":File_Wild_Card:" ":IP_Address:" ":User_ID:" ":Password:" ":Target_Path:" ":Script_Path *OsCmd = Script_Path : "/test.sh" Call DSLogInfo('Ftp ':File_Wild_Card: ' From ' : IP_Address : ' ' :Source_Path : ' to ' :Target_Path,RoutineName) Call DSExecute("UNIX",OsCmd,OsOutput,OsStatus) If OsStatus Then Call DSLogInfo('The FTP command (':OsCmd:') returned status ':OsStatus:':':@FM:OsOutput,'DSMoveFiles')
End Else Call DSLogInfo('Files FTPd...': '(':OsCmd:')',RoutineName) End Ans = OsStatus RETURN(Ans) Concatenate All Input Arguments to Output using TAB character Concatenate All Routine="GBIConcatItem" t = Char(009) If ISNULL(IND) THEN Pattern = "" ELSE Pattern = IND [1,1] If ISNULL(VKORG) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : VKORG [1,4] If ISNULL(VTWEG) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : VTWEG [1,2] If ISNULL(SPART) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : SPART [1,2] If ISNULL(WERKS) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : WERKS [1,4] If ISNULL(AUART) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : AUART [1,4] If ISNULL(FKDAT) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : FKDAT [1,8] If ISNULL(KUNAG) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : KUNAG [1,10] If ISNULL(KUNRE) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : KUNRE [1,10] If ISNULL(MATNR) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : MATNR [1,18] If ISNULL(PSTYV) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : PSTYV [1,4] If ISNULL(KWMENG) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : KWMENG [1,15] If ISNULL(XBLNR) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : XBLNR [1,16] If ISNULL(VGPOS) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : VGPOS [1,6] If ISNULL(FKARA) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : FKARA [1,4] If ISNULL(ZOR_DT_PCODE) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : ZOR_DT_PCODE [1,8] If ISNULL(ZAWB) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : ZAWB [1,16] If ISNULL(LGORT) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : LGORT [1,4] If ISNULL(VKAUS) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : VKAUS [1,3] If ISNULL(VKBUR) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t :
VKBUR [1,4] If ISNULL(VKGRP) VKGRP [1,3] If ISNULL(ZLSCH) ZLSCH [1,1] If ISNULL(ZTERM) ZTERM [1,4] If ISNULL(KURSK) KURSK [1,9] If ISNULL(TAXM1) TAXM1 [1,1] If ISNULL(VRKME) VRKME [1,3] If ISNULL(ARKTX) ARKTX [1,40] If ISNULL(KTGRM) KTGRM [1,2] If ISNULL(ZZTAXCD) ZZTAXCD [1,2] If ISNULL(LAND2) LAND2 [1,3] If ISNULL(NAME1) NAME1 [1,35] If ISNULL(PSTLZ) PSTLZ[1,10] If ISNULL(ORT01) ORT01 [1,35] If ISNULL(KOSTL) KOSTL[1,10] If ISNULL(WAERS) WAERS [1,5] If ISNULL(KUNRG) KUNRG [1,10] If ISNULL(KUNWE) KUNWE [1,10]
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
Ans = Pattern GBIConcatItem: Concatenate All Input Arguments to Output using TAB character: Routine="GBIConcatItem" t = Char(009) If ISNULL(IND) THEN Pattern = "" ELSE Pattern = IND [1,1] If ISNULL(KNUMV) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : KNUMV [1,16] If ISNULL(KPOSN) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : KPOSN [1,6] If ISNULL(KSCHL) THEN Pattern = Pattern : t ELSE Pattern = Pattern : t : KSCHL [1,4]
If ISNULL(KBETR) [1,11] If ISNULL(KWERT) KWERT [1,13] If ISNULL(WAERS) WAERS [1,5] If ISNULL(KAWRT) KAWRT [1,15] If ISNULL(KHERK) [1,1]
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t : KBETR
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t :
THEN Pattern = Pattern : t
ELSE Pattern = Pattern : t : KHERK
Ans = Pattern GCMFConvert: Receive GCMF string and change known strings to required values: DataIn = "":Trim(Arg1) If IsNull(DataIn) or DataIn = "" Then Ans = "" End Else DataIn = Ereplace (DataIn,"$B$","") DataIn = Ereplace (DataIn,"NULL","") DataIn = Ereplace (DataIn,"<","<") DataIn = Ereplace (DataIn,">",">") DataIn = Ereplace (DataIn,""",'"') DataIn = Ereplace (DataIn,"'","'") DataIn = Ereplace (DataIn,"&","&") DataIn = Ereplace (DataIn,"|","|") Ans = DataIn End GCMFFormating: * * FUNCTION GCMFFormating(Switch, All_Row) * * Replaces some special characters when creating the GCMF file * * Input Parameters : Arg1: Switch = Step to change. * Arg2: All_Row = Row containing the GCMF Record. * DataIn=Trim(All_Row) If Switch=1 Then If IsNull(DataIn) or DataIn= "" Then Ans = "$B$" End Else DataInFmt = Ereplace (DataIn ,"&", "&") DataInFmt = Ereplace (DataInFmt ,"'", "'") DataInFmt = Ereplace (DataInFmt ,'"', """)
Ans = DataInFmt End End Else If Switch=2 Then DataInFmt = Ereplace (DataIn ,">", ">") DataInFmt = Ereplace (DataInFmt ,"<", "<") Ans = DataInFmt End Else * Final Replace, After the Merge of all GCMF segments DataInFmt = Ereplace (DataIn ,"|", "|") Ans = DataInFmt End End GeneralCounter: COMMON /Counter/ OldParam, TotCount NextId = Identifier IF UNASSIGNED(OldParam) Then OldParam = NextId TotCount = 0 END IF NextId = OldParam THEN TotCount += 1 END ELSE OldParam = NextId TotCount = 1 END Ans = TotCount GetNextCustomerNumber: Sequence number generator. Routine to get the next sequence number to use for a customer from a file, and save the usde value in the file. The routine argument is the name associated with the super group that the customer is being created in. The routine uses a file to store the next available number. It reads the number, then increments and stores the value in common, writing the next value back to file each time.
* Routine to generate the next customer number. The argument is a string used to * identify the super group for the customer. * * The routine uses a UniVerse file to store the next number to use. This * value is stored in a record named after the supplied argument. The * routine reads the number, then increments and stores the value * in common storage, writing the next value back to file each time. * * Declare shared memory storage. Common /CustSequences/ Initialized, NextVal, SeqFile EQUATE RoutineName TO 'GetNextCustomerNumber' If NOT(Initialized) Then * Not initialised. Attempt to open the file. Initialized = 1 Open "IOC01_SUPER_GRP_CTL_HF" TO SeqFile Else Call DSLogFatal("Cannot open customer number allocation control file",RoutineName) Ans = -1 End End * Read the named record from the file. Readu NextVal From SeqFile, Arg1 Else Call DSLogFatal("Cannot find super group in customer number allocation control file",RoutineName) Ans = -1 End Ans = NextVal * Increment the sequence value, and write back to file. NextVal = NextVal + 1 If Len(NextVal) < 10 then NextVal = Substrings("0000000000",1,10Len(NextVal)):NextVal Writeu NextVal On SeqFile, Arg1 Else Call DSLogFatal("Update to customer number allocation control file failed",RoutineName) Ans = -1 End GetNextErrorTableID: Sequence number generator in a concurrent environment. Routine to generate a sequential number. The routine argument is the name associated with the sequence. The routine uses a file to store the next available number. It reads the number from the file on each invocation; a lock on the file prevents concurrent access.
* Routine to generate a sequential number. The argument is a string used to * identify the sequence. * * NOTE: This routine uses locking to allow multiple processes to access the * same sequence. * * The routine uses a UniVerse file to store the next number to use. This * value is stored in a record named after the supplied argument. The * routine always attempts to read the number from the file, so that the * record for the sequence becomes locked. It increments and stores the * value in common storage, writing the next value back to file each * time. Writing back this value frees the lock. * * Declare shared memory storage. Common /ErrorTableSequences/ Initialized, NextVal, SeqFile EQUATE RoutineName TO 'GetNextErrorTableID' If NOT(Initialized) Then * Not initialised. Attempt to open the file. Initialized = 1 Open "ErrorTableSequences" TO SeqFile Else * Open failed. Create the sequence file. EXECUTE "CREATE.FILE ErrorTableSequences 2 1 1" Open "ErrorTableSequences" TO SeqFile Else Ans = -1 End End * Read the named record from the file. * This obtains the lock (waiting if necessary). Readu NextVal From SeqFile, Table_Name Else NextVal = 1 End Ans = NextVal NextVal = NextVal + 1 * Increment the sequence value, and write back to file. * This releases the lock. Write NextVal On SeqFile, Table_Name Else Ans = -1 GetNextModSeqNo: Gets the Next Mod Run Code from an Initialised Sequence This routine gets the next Mod Run Number in a squenced that was initialised,. The arguments are Mod_Code_Parm and Supplier_ID_Parm which combined form the key for this instance of a sequence GetParameterArray:
* GetParameterArray(Arg1) * Decription: Get parameters * Written by: * Notes: * Bag of Tricks Version 2.3.0 Release Date 2001-10-01 * Arg1 = Path and Name of Parameter File * * Result = ( <1> = Parameter names, <2> = Parameter values) * -----------------------------------------------------------DEFFUN FileFound(A) Calling 'DSU.FileFound' cBlank = '' cName = 1 cValue = 2 vParamFile = Arg1 aParam = cBlank vParamCnt = 0 vCurRoutineName = 'Routine: GetParameterArray' vFailed = @FALSE Done = @FALSE IF vParamFile AND FileFound(vParamFile) Then OPENSEQ vParamFile TO hParamFile Then Loop READSEQ vLineRaw FROM hParamFile ON ERROR Call DSLogWarn('Error from ':vParamFile:'; Status = ':STATUS(),vCurRoutineName) CLOSE hParamFile vFailed = @TRUE Done = @TRUE End Then vLine = TRIM(vLineRaw) vFirstChar = LEFT(vLine,1) vRemark = LEFT(vLine,4) IF NOT(vFirstChar = cBlank OR vFirstChar = '#' OR vFirstChar = '*' OR vFirstChar = '"' OR vFirstChar = "'" OR vFirstChar = ';' OR vFirstChar = ':' OR vFirstChar = '[' OR vRemark = 'REM ') THEN vParamCnt += 1 ; * Add to any parameter array passed as an argument aParam<1,vParamCnt> = TRIM(FIELD(vLine,'=',cName)) aParam<2,vParamCnt> = FIELD(vLine,'=',cValue) END END ELSE Done = @TRUE END Until Done Do Repeat CLOSE hParamFile End Else Call DSLogWarn('Error from ':vParamFile:'; Status = ':STATUS(),vCurRoutineName) vFailed = @TRUE End
End Else vFailed = @TRUE End Call DSLogInfo("Values loaded from file: ":vParamFile:@AM:aParam, vCurRoutineName) If vFailed Then Ans = "ERROR" End Else Ans = aParam End LastDayofMonth: Returns the Last Day of the Month Deffun DSRMessage(A1,A2) Calling "*DataStage*DSR_MESSAGE" Equate TransformName To "ConvertMonth" * Check the format of the input value. If IsNull(Arg1) or (Len(Arg1) < 6) Then Ans = "" GoTo ExitLastDayMonth End InYear = Substrings(Arg1,1,4) InMonth = Substrings(Arg1,5,2) If InMonth < 1 Or InMonth > 12 Then Ans = "" GoTo ExitLastDayMonth End * Generate the required output, depending on the Action argument. Begin Case Case InMonth = "1" * Internal date for first day of month. OutDt ="31" Case InMonth = "2" * Internal date for first day of month. if mod(Num(InYear),4)<>0 then OutDt = "28" end if mod(Num(InYear),4)=0 then OutDt = "29" end Case InMonth = "3" * Internal date for first day of month. OutDt = "31"
Case InMonth = "4" * Internal date for first day of month. OutDt = "30" Case InMonth = "5" * Internal date for first day of month. OutDt ="31" Case InMonth = "6" * Internal date for first day of month. OutDt ="30" Case InMonth = "7" * Internal date for first day of month. OutDt ="31" Case InMonth = "8" * Internal date for first day of month. OutDt ="31" Case InMonth = "9" * Internal date for first day of month. OutDt ="30" Case InMonth = "10" * Internal date for first day of month. OutDt ="30" Case InMonth = "11" * Internal date for first day of month. OutDt ="31" Case InMonth = "12" * Internal date for first day of month. OutDt ="31" End Case Ans=OutDt:"-":InMonth:"-":InYear ExitLastDayMonth: LogToErrorFile: Logs errors to an error hashed file * FUNCTION LogToErrorFile(Table,Field_Name,Check_Value,Error_Number,Error_Text_1, Error_Text_2, Error_Text_3,Additional_Message) * * * Places the current Writes Error Messages to a Hash File *
* Input Parameters : Arg1: Table = The name of Control table being checked * Arg2: Field_Name = The name of the Field that is in error * Arg3: Check_Value = The value used to look up in the Hash file to get try and get a look up match * Arg4: Error_Number = The error number returned * Arg5: Error_Text_1 = First error message argument. Used to build the default error message * Arg6: Error_Text_2 = Second error message argument. Used to build the default error message * Arg7: Error_Text_3 = Thrid error message argument. Used to build the default error message * Arg8: Additional_Message = Any text that could be stored against an error * RoutineName = "LogToErrorFile" Common /HashLookup/ FileHandles(100), FilesOpened Common /TicketErrorCommon/ ModRunID, TicketFileID, TicketSequence, TicketSetKey, JobStageName, ModRootPath $INCLUDE DSINCLUDE JOBCONTROL.H Ans = "ERROR" If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Move = 'move ' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Move = 'mv -f ' End JobName = DSGetJobInfo (DSJ.ME , DSJ.JOBNAME) Path = ModRootPath : OsDelim :"error" : OsDelim FileName = "ErrorLog_HF." : ModRunID PathFile = Path : FileName Call DSLogInfo(Path:"-- checking --" : PathFile,RoutineName) vMessage = "INLOG Error Log = " : PathFile *Call DSLogInfo(vMessage, RoutineName ) vMessage = "INLOG Error Log Data = " : ModRunID : "|" : TicketFileID : "|" : TicketSequence : "|" : TicketSetKey : "|" : Table : "|" : Field_Name : "|" : Check_Value : "|" : Error_Number : "|" : Additional_Message *Call DSLogInfo(vMessage, RoutineName )
Key = JobName : JobStageName : ModRunID: TicketFileID : TicketSequence : TicketSetKey : Table : Field_Name Err_Rec = "" Err_Rec <1> = JobName Err_Rec <2> = JobStageName Err_Rec <3> = ModRunID Err_Rec <4> = TicketFileID Err_Rec <5> = TicketSequence Err_Rec <6> = TicketSetKey Err_Rec <7> = Table Err_Rec <8> = Field_Name Err_Rec <9> = Check_Value Err_Rec <10> = Error_Number Err_Rec <11> = Error_Text_1 Err_Rec <12> = Error_Text_2 Err_Rec <13> = Error_Text_3 Err_Rec <14> = Additional_Message
* Attempt to find the table name in our cache. Locate FileName in FilesOpened Setting POS Then Write Err_Rec To FileHandles(POS), Key Then TAns = 0 End Else TAns = -1 End End Else * Table is not in cache of opened tables, so open it. Openpath PathFile To FileHandles(POS) Then FilesOpened<-1> = FileName Write Err_Rec To FileHandles(POS), Key Then TAns = 0 Else TAns = -1 End End Else TAns = -2 End End Ans = "ERROR" Return(Ans) LogToHashFile: * FUNCTION LogToHashFile(ModRunNum,TGrp,TSeg,SetKey,Table,FieldNa,KeyValue,Error,Msg,Se verityInd) * *
* Places the current Writes Error Messages to a Hah File * * Input Parameters : Arg1: ModRunNum = The unique number allocated to a run of an Module * Arg2: Ticket_Group = The Ticket Group Number of the Current Row * Arg3: Ticket_Sequence = The Ticket Sequence Number of the Current Row * Arg4: Set_Key = A Key to identify a set of rows e.g. an Invoice Number to a set of invoice lines * Arg5: Table = The name of Control table being checked * Arg6: FieldNa = The name of the Field that is in error * Arg7: KeyValue = The value used to look up in the Hash file to get try and get a look up match * Arg8: Error = The error number returned * Arg9: Msg = Any text that could be stored against an error * Arg10: SeverityInd = An Indicator to state the error severity level RoutineName = "LogToHashFile" Common /HashLookup/ FileHandles(100), FilesOpened Common /TicketCommon/ Ticket_Group, Ticket_Sequence, Set_Key, Job_Stage_Name, Mod_Root_Path, Generic_Root_Path, Chk_Hash_File_Name, Mod_Run_Num $INCLUDE DSINCLUDE JOBCONTROL.H TAns = 0 If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Move = 'move ' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Move = 'mv -f ' End JobName = DSGetJobInfo (DSJ.ME , DSJ.JOBNAME) * StageName = DSGetStageInfo (DSJ.ME,DSJ.ME, DSJ.STAGENAME) Path = Mod_Root_Path : OsDelim :"error" : OsDelim FileName = "ErrorLog_HF." : Mod_Run_Num PathFile = Path : FileName *Message = "INLOG Error Log = " : PathFile *Call DSLogInfo(Message, RoutineName )
*Message = "INLOG Error Log Data = " : ModRunNum : "|" : TGrp : "|" : TSeq : "|" : Set_Key : "|" : Table : "|" : FieldNa : "|" : KeyValue : "|" : Error : "|" : Msg *Call DSLogInfo(Message, RoutineName ) Key = JobName : Job_Stage_Name : ModRunNum: TGrp : TSeq : SetKey : Table : FieldNa Err_Rec = "" Err_Rec <1> = JobName Err_Rec <2> = Job_Stage_Name Err_Rec <3> = ModRunNum Err_Rec <4> = TGrp Err_Rec <5> = TSeq Err_Rec <6> = SetKey Err_Rec <7> = Table Err_Rec <8> = FieldNa Err_Rec <9> = KeyValue Err_Rec <10> = Error Err_Rec <11> = Msg Err_Rec <12> = SeverityInd * Attempt to find the table name in our cache. Locate FileName in FilesOpened Setting POS Then Write Err_Rec To FileHandles(POS), Key Then TAns = 0 End Else TAns = -1 End End Else * Table is not in cache of opened tables, so open it. Openpath PathFile To FileHandles(POS) Then FilesOpened<-1> = FileName Write Err_Rec To FileHandles(POS), Key Then TAns = 0 Else TAns = -1 End End Else TAns = -2 End End Ans = TAns RETURN(Ans) MandatoryFieldCheck: Check whether the field name passed is mandatory Routine to check to see if the passed field is populated, and if not, to check to see if it is mandatory. If the field contains "?", then it is handled as if it is blank.
The routine uses a control table containing process name, field name, group name and exclusion flag to control mandatory or not. The routine arguments are the field name, the field, the group key, whether this is the first mandatory check for the record, and the process name when the first check flag is "Y". A variable kept in memory (Mandlist) is used to record the mandatory check failures. When the passed field name is "Getmand", no processing is performed except to return the Mandlist field. * Routine to check whether the passed field is filled, and if not, whether it is mandatory. * * The routine uses a UniVerse file "MANDATORY_FIELD_HF" which contains the mandatory field controls * * Arg1 Field name to be checked (literal) * Arg2 Field value * Arg3 Group name * Arg4 1st call for record * Arg5 The process name on the first call (this is saved in storage for subsequent calls) * * Declare shared memory storage. Common /Mandatory/ Initialized, SeqFile, DataIn, GroupIn, GroupV, Mandlist, ProcessIn, ProcessV EQUATE RoutineName TO 'MandatoryFieldCheck' * Call DSLogInfo("Routine started":Arg1,RoutineName) If NOT(Initialized) Then Initialized = 1 * Call DSLogInfo("Initialisation Started",RoutineName) Open "MANDATORY_FIELD_HF" TO SeqFile Else Call DSLogFatal("Cannot open Mandatory field control file",RoutineName) Ans = -1 End * Call DSLogInfo("Initialisation Complete",RoutineName) End If Arg4 = "Y" Then Mandlist = "" ProcessIn = "":Trim(Arg5) If IsNull(ProcessIn) or ProcessIn = "" Then ProcessV = " " Else ProcessV = ProcessIn End
If Arg1 = "Getmand" Then Ans = Mandlist Else DataIn = "":Trim(Arg2) GroupIn = "":Trim(Arg3) If IsNull(GroupIn) or GroupIn = "" Then GroupV = " " Else GroupV = GroupIn If IsNull(DataIn) or DataIn = "" or DataIn = "?" Then * * Field is blank - check for mandatory * * Call DSLogInfo(Arg1:" blank - checking whether mandatory",RoutineName) * mystring = ProcessV:Arg1:GroupV:"X" Read Retval From SeqFile, mystring then * Call DSLogInfo(Arg1:" Group specifically excluded",RoutineName) Ans = 0 end else mystring = ProcessV:Arg1:GroupV Read Retval From SeqFile, mystring then * Call DSLogInfo(Arg1:" Group specifically included",RoutineName) Ans = 1 end else mystring = ProcessV:Arg1:"ALL" Read Retval From SeqFile, mystring then * Call DSLogInfo(Arg1:" Global mandatory",RoutineName) Ans = 1 end else * Call DSLogInfo(Arg1:" blank - not mandatory",RoutineName) Ans = 0 end end end End Else Ans = 0 * Call DSLogInfo(Arg1:" Not blank",RoutineName) End If Ans = 1 Then If Mandlist = "" Then Mandlist = Arg1 Else Mandlist = Mandlist:",":Arg1 end
End Map:(Routinue Name) * FUNCTION Map(Value,FieldName,Format,Default,Msg,ErrorLogInd) * * Executes a lookup against a hashed file using a key * * Input Parameters : Arg1: Value = The Value to Be Mapped * Arg2: FieldName = The Name of the field that is either the Target of the Derivation or the sourceField that value is contained in * Arg3: Format = The name of the Hash file containing the mapping data * Arg4: Default = The Default value to return if value is not found * Arg5: Msg = Any text you want stored against an error * Arg6: SeverityInd = An Indicator to the servity Level * Arg7: ErrorLogInd = An Indicator to indicate if errors should be logged * Arg8: HashfileLocation = An Indicator to indicate of errors should be logged (Note this is not yet implemented) * * Return Values: If the Value is not found, return value is: -1. or the Default value if that is supplied * If Format Table not found, return value is: -2 * * * RoutineName = 'Map' Common /HashLookup/ FileHandles(100), FilesOpened Common /TicketCommon/ Ticket_Group, Ticket_Sequence, Set_Key, Job_Stage_Name, Mod_Root_Path, Generic_Root_Path, Chk_Hash_File_Name, Mod_Run_Num *Message = "Map Job Stage Name ==>" : Job_Stage_Name * Call DSLogInfo(Message, RoutineName ) *Message = "Map Mod Root Path ==>" : Mod_Root_Path * Call DSLogInfo(Message, RoutineName ) *Message = "Generic Root Path ==>" : Generic_Root_Path * Call DSLogInfo(Message, RoutineName ) *Message = "Map Chk_Hash_File_Name ==>" : Chk_Hash_File_Name * Call DSLogInfo(Message, RoutineName ) *Message = "Map Mod_Run_Num ==>" : Mod_Run_Num * Call DSLogInfo(Message, RoutineName ) DEFFUN
LogToHashFile(ModRunNum,Ticket_Group,Ticket_Sequence,Set_Key,Table,FieldNam e,Key,Error,Text,SeverityInd) Calling 'DSU.LogToHashFile' * If Len(Chk_Hash_File_Name) = 3 And HashFileLocation = "G" Then Format_Extn = Chk_Hash_File_Name Else Format_Extn = Mod_Run_Num [1,5] If System(91) Then OsType = 'NT' OsDelim = '\' NonOsDelim = '/' Move = 'move ' End Else OsType = 'UNIX' OsDelim = '/' NonOsDelim = '\' Move = 'mv -f ' End ColumnPosition = 0 PositionReturn = 0 Table = Format If HashFileLocation = "G" then PathFormat = Generic_Root_Path : OsDelim :"format" : OsDelim: Format : "_HF." : Format_Extn End Else PathFormat = Mod_Root_Path : OsDelim :"format" : OsDelim : Format : "_HF." : Format_Extn End If IsNull(Value) then Chk_Value = "" Else Chk_Value = Value *Message = "Map PathFormat ==>" : PathFormat *Call DSLogInfo(Message, RoutineName ) *Message = "Value ==>" : Value *Call DSLogInfo(Message, RoutineName ) *Message = "Format ==>" : Format *Call DSLogInfo(Message, RoutineName ) *Message = "Default ==>" : Default *Call DSLogInfo(Message, RoutineName ) *Message = "ErrorLogInd ==>" : ErrorLogInd *Call DSLogInfo(Message, RoutineName ) * Set the Default Answer for if a value is not found
Begin Case Case UpCase(Default) = "NODEF" Default_Ans = "-1" Case Default = "PASS" NumFields = Dcount(Chk_Value, "|") If NumFields > 1 Then Default_Ans = Field(Chk_Value,"|",2) *Message = "Num Fields > 1 Default_Ans ==>" : Default_Ans : "#" : Chk_Value *Call DSLogInfo(Message, RoutineName ) End Else Default_Ans = Chk_Value *Message = "Num Fields NG 0 Default_Ans ==>" : Default_Ans : "#" : Chk_Value *Call DSLogInfo(Message, RoutineName ) End Case @TRUE If UpCase(Field(Default,"|",1)) <> "BL" Then Default_Ans = Default Else Default_Ans = -1 End Case * Determine if we are returning one column or entire row. If Num(ColumnPosition) then ColumnPosition = Int(ColumnPosition) If ColumnPosition > 0 and ColumnPosition < 99999 Then PositionReturn = 1 End End * Attempt to find the table name in our cache. Locate Format in FilesOpened Setting POS Then Read Rec From FileHandles(POS), Chk_Value Then If PositionReturn Then Ans = Rec Else Ans = Rec End Else Ans = Default_Ans End End Else * Table is not in cache of opened tables, so open it. Openpath PathFormat To FileHandles(POS) Then FilesOpened<-1> = Format Read Rec From FileHandles(POS), Chk_Value Else Rec = Default_Ans End If PositionReturn And Rec <> Default_Ans Then Ans = Rec End Else
Ans = Rec End End Else Ans = "-2" End End If UpCase(Field(Default,"|",1)) = "BL" and Ans <> -2 Then If Chk_Value = "" then Ans = Field(Default,"|",2) End End *Message = "Outside LOGGING" : Mod_Run_Num: "|" : Ticket_Group : "|" : Ticket_Sequence "|" : Set_Key : "|" : Table : "|" : FieldName : "|" : Chk_Value : "|" : Msg *Call DSLogInfo(Message, RoutineName ) *Message = "OUTSIDE PASS Trans Default_Ans ==>" : Default_Ans : " Ans ==> " : Ans *Call DSLogInfo(Message, RoutineName ) LogPass = "N" If (Default = "PASS" and Default_Ans <> Ans) then LogPass = "Y" If LogPass = "Y" Then *Message = "PASS Trans Default_Ans ==>" : Default_Ans : " Ans ==> " : Ans *Call DSLogInfo(Message, RoutineName ) End If (Ans = "-1" or Ans = "-2" or UpCase(Ans)= "BLOCKED" or LogPass = "Y" or SeverityInd = "I") and ErrorLogInd = "Y" Then *Message = "Write to Log Ans==> " : Ans : " ErrorInd==> " : ErrorLogInd *Message = "LOGGING" : Mod_Run_Num: "|" : Ticket_Group : "|" : Ticket_Sequence : "|" : Table : "|" : FieldName : "|" : Chk_Value : "|" : Ans *Call DSLogInfo(Message, RoutineName ) Ret_Code=LogToHashFile(Mod_Run_Num,Ticket_Group,Ticket_Sequence,Set_Key,Ta ble,FieldName,Chk_Value,Ans,Msg,SeverityInd) End RETURN(Ans) OutputJobStats: Outputs the job link statistics
$INCLUDE DSINCLUDE JOBCONTROL.H hJob = DSAttachJob(JobName, DSJ.ERRFATAL) Start_TS = DSGetJobInfo (hJob, DSJ.JOBSTARTTIMESTAMP) End_TS = DSGetJobInfo (hJob,DSJ.JOBLASTTIMESTAMP) Elapsed_Secs_Cnt = DSGetJobInfo (hJob,DSJ.JOBELAPSED) Job_Term_Status = DSGetJobInfo (hJob,DSJ.JOBINTERIMSTATUS) User_Status = DSGetJobInfo (hJob,DSJ.USERSTATUS) ErrCode = DSDetachJob(hJob) Ans = Start_TS : "|" : End_TS : "|" : Elapsed_Secs_Cnt : "|" : Job_Term_Status : "|" : User_Status Pattern: Routine="Pattern" Var_Len = len(Value) Pattern = Value
For i = 1 To Var_Len If Num(Value [i,1]) Then Pattern [i,1] = "n" end Else If Alpha(Value [i,1]) Then Pattern[i,1] = "a" end Else Pattern[i,1] = Value [i,1] end end Next i Ans = Pattern Checks a passed field to see if it matches the pattern which is also passed.: The input field is checked to see if it conforms to the format that is also passed as a second parameter. The result of the routine is True is the pattern matches the required format, and false if it does not. If the second parameter is empty, then true is returned.
Equate TransformName To "PatternMatchCheck" Begin Case
Case Arg2 = "" ;* No pattern - so return true Ans = 1 Case Arg3 = "" ;* Only 1 pattern passed Ans = Arg1 Matches Arg2 Case 1 ;* All other cases Ans = Arg1 Matches Arg2 : CHAR(253) : Arg3 End Case PrepareJob: $INCLUDE DSINCLUDE JOBCONTROL.H Job_Handle = DSAttachJob (Job_Name, DSJ.ERRWARN) ErrCode1=DSPrepareJob(Job_Handle) ErrCode2 = DSDetachJob(Job_Handle) Ans= ErrCode2 RangeCheck: * FUNCTION Map(Value,FieldName,Format,Default,Msg,ErrorLogInd) * * Executes a lookup against a hashed file using a key * * Input Parameters : Arg1: Value = The Value to be checked * Arg2: MinValue = The Min Value allowed * Arg3: MaxValue = The Max Value allowed * Arg4: FieldName = The Name of the Source field being checked * Arg5: Msg = Any text you want stored against an error * Arg6: SeverityInd = An Indicator to the servity Level * Arg7: ErrorLogInd = An Indicator to indicate if errors should be logged * * Return Values: If the Value is not found, return value is -1. else the value supplied is returned * * * RoutineName = 'RangeChk' Common /TicketCommon/ Ticket_Group, Ticket_Sequence,Set_Key, Mod_Root_Path, Generic_Root_Path, Chk_Hash_File_Name, Mod_Run_Num DEFFUN LogToHashFile(ModRunNum,Ticket_Group,Ticket_Sequence,Set_Key,Table,FieldNam e,Key,Error,Text,SeverityInd) Calling 'DSU.LogToHashFile' Table = "Min: " : MinValue "to Max: " : MaxValue Msg1 = "" Msg2 = ""
Msg3 = "" Msg4 = "" Ans = "" If Num (Value) = 0 then Msg1 = "-Value is not a number" Ans = -2 End If Num (Value) = 0 then Msg2 = "-MinValue is not a number" Ans = -2 End If Num (Value) = 0 then Msg3 = "-MaxValue is not a number" Ans = -2 End If Ans <> -2 Then If Value < MinValue Or Value > MaxValue Then Msg4 = "-Value is outside the Range" Ans = -1 End End OutputMsg = Msg : Msg1 : Msg2 : Msg3: Msg4 *Call DSLogInfo(OutputMsg, RoutineName ) If Ans <> -1 and Ans <> -2 then Ans = Value If (Ans = "-1" or Ans = "-2") and ErrorLogInd = "Y" Then Ret_Code=LogToHashFile(Mod_Run_Num,Ticket_Group,Ticket_Sequence,Set_Key,Ta ble,FieldName,Value,Ans,OutputMsg,SeverityInd) End RETURN(Ans) ReadParameter: Read parameter value from configuration file * * Function : ReadParameter - Read parameter value from configuration file * Arg : ParameterName (default=JOB_PARAMETER) * DefaultValue (default='') * Config file (default=@PATH/config.ini) * Return : Parameter value from config file Function Readparameters(parametersname,Defaultvalue,ConfigFile)
* Function : ReadParameter - Read parameter value from configuration file * Arg : ParameterName (default=JOB_PARAMETER) * DefaultValue (default='') * Config file (default=@PATH/config.ini) * Return : Parameter value from config file * If ParameterName = "" Then ParameterName = "JOB_PARAMETER" If ConfigFile = "" Then ConfigFile = @PATH:"/config.ini" ParameterValue = DefaultValue OpenSeq ConfigFile To fCfg Else Call DSLogFatal("Error opening file ":ConfigFile, "ReadParameter") Loop While ReadSeq Line From fCfg If Trim(Field(Line,'=',1)) = ParameterName Then ParameterValue = Trim(Field(Line,'=',2)) Exit End Repeat CloseSeq fCfg Ans = ParameterValue RETURN(Ans) ReturnNumber: String=Arg1 Slen=Len(String) Scheck=0 Rnum="" For Scheck = 1 to Slen Schar=Substrings(String,Scheck,1) If NUM(Schar) then Rnum=Rnum:Schar End Next Outer Ans=Rnum
ReturnNumbers: length=0 length=LEN(Arg1); length1=1; Outer=length; postNum='' counter=1; For Outer = length to 1 Step -1 Arg2=Arg1[Outer,1] If NUM(Arg2) then length2=counter-1 if length2 = 0 then length2=counter postNum=RIGHT(Arg1,length2) END else postNum=RIGHT(Arg1,counter) END END counter=counter+1 Next Outer Ans=postNum ReverseDate: Function ReverseDate(DateVal) * Function ReverseDate(DatelVal) * Date mat be in the form of DDMMYYYY i.e. 01102003 or DMMYYYY 1102003 If Len(DateVal) = 7 then NDateVal = "0" : DateVal End Else NDateVal = DateVal End Ans = NDateVal[5,4] : NDateVal[3,2] : NDateVal[1,2] RunJob: The routine runs a job. Job parameters may be supplied. The result is a dynamic array containing the job status, and row count information for each link. The routine UtilityGetRunJobInfo can be used to interpret this result.
As well as the job name and job parameters, the routine parameters allow the job warning limit and row count limit to be set. Format of returned dynamic array: Status<1>=Jobname=FinishStatus Status<2>=Jobname Status<3>=JobStartTimeStamp Status<4>=JobStopTimeStamp Status<5>=LinkNames (value mark @VM delimited) Status<6>=RowCount (value mark @VM delimited) FunctionRunJob(Arg1,Arg2,Arg3,Arg4) * Demonstrate how to run a job within the GUI development enviroment. Arguments may * be passed in. The result is a dynamic array with the resulting status and run * statistics (row counts for every link on every stage in the job) * $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 JobStartTime = DSRTimestamp() 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) JobEndTime = DSRTimestamp() If Status = DSJS.RUNFAILED Then Message = DSRMessage( 'DSTAGE_TRX_E_0020', 'Job Failed: %1', RunJobName) Call DSLogWarn(Message, RoutineName) End * Retrieve more information about this job run. Message = DSRMessage('DSTAGE_TRX_I_0016', 'Getting job statistics', '' ) Call DSLogInfo(Message, RoutineName) StageList = DSGetJobInfo(JobHandle,DSJ.STAGELIST) Message = DSRMessage('DSTAGE_TRX_I_0017', 'List of Stages=%1', StageList ) Call DSLogInfo(Message, RoutineName) StageCount = Dcount(StageList, ',') ; * Count number of active stages. Info<1> = RunJobName Info<2> = JobStartTime ;* StartTime (Timestamp format) Info<3> = JobEndTime ;* Now/End (Timestamp format) FOR Stage = 1 To StageCount * Get links on this stage. LinkNames = DSGetStageInfo(JobHandle,Field(StageList,',',Stage),DSJ.LINKLIST) Message = DSRMessage( 'DSTAGE_TRX_I_0018', 'LinkNames for Stage.%1 = %2', Field(StageList,',',Stage):@FM:LinkNames) Call DSLogInfo(Message, RoutineName) LinkCount = Dcount(LinkNames,',') For StageLink = 1 To LinkCount * Get Rowcount For this linkname
RowCount = DSGetLinkInfo(JobHandle,Field(StageList,',',Stage),Field(LinkNames,',',StageLink),DSJ. LINKROWCOUNT) Message = DSRMessage( 'DSTAGE_TRX_I_0019', 'RowCount for %1.%2=%3', Field(StageList,',',Stage):@FM:Field(LinkNames,',',StageLink):@FM:RowCount) Call DSLogInfo(Message, RoutineName) Info<4,-1> = Field(StageList,',',Stage):'.':Field(LinkNames,',',StageLink) Info<5,-1> = RowCount Next StageLink Next Stage Message = DSRMessage( 'DSTAGE_TRX_I_0020', 'RunJob Status=%1', Info ) Call DSLogInfo(Message, RoutineName) Ans = RunJobName:'=':Status:@FM:Info
RunJobAndDetach: The routine runs a job. Job parameters may be supplied. The job is detached from so tht others may be started immediately and the control job finish. As well as the job name and job parameters, the routine parameters allow the job warning limit and row count limit to be set. FunctionRunDetachJob(Arg1,Arg2,Arg3,Arg4) * Run a job, and detach from it so that this job can end * $INCLUDE DSINCLUDE JOBCONTROL.H Equate RoutineName To 'RunJobAndDetach' 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 * Attach to the job JobHandle = DSAttachJob(RunJobName, DSJ.ERRWARN) If JobHandle = 0 Then Call DSLogInfo("Job ":RunJobName:" not started - attach failed",RoutineName) Else * 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 = DSDetachJob(JobHandle) End Ans = 0 RunShellCommandReturnStatus: Function RunShellcommandreturnstatus(Command) Call DSLogInfo('Running command:':Command,'RunShellCommandReturnStatus') Call DSExecute('UNIX',Command,Ans,Ret) Call DSLogInfo('Output from command:':Ans,'RunShellCommandReturnStatus') Return(Ret)
SegKey: Segment_Num: An Integer number representing the order number of the Segment in the IDoc Segment_Parm: A Segment Parameter containing a string of Y's and N's in order of Segment_Num denoting of the segment should be written to in this Module Key: The Value to Be Mapped ErrorLogInd: An Indicator to indicate of errors should be logged (Note this is not yet implemented) Function Seqkey(Segment_Num,segmentparam,key,ErrorLogInd) * FUNCTION SegKey(Value,ErrorLogInd) * * Executes a lookup against a hashed file using a key * * Input Parameters : Arg1: Segment_Num * Arg2: Segment_Parm * Arg1: Key = An ordered Pip separated set of Seqment Primary Key Fields * Arg2: ErrorLogInd = An Indicator to indicate of errors should be logged (Note this is not yet implemented) * * Return Values: If the Value is not found, return value is: -1. or the Default value if that is supplied * If Format Table not found, return value is: -2 * * * RoutineName = 'SegKey' BlankFields = "" CRLF = Char(13) : Char(10) Message = "IN Seg Key" : Segment_Num : "|" : Segment_Parm : "|" : Key : "|" : ErrorLogInd : "|" * Call DSLogInfo(Message, RoutineName ) * Determine if this segment should output Write_Ind = Field(Segment_Parm,"|",Segment_Num) If Write_Ind = "Y" then * Count how many keys NumKeys = Dcount(Key,"|") * Make a list of any keys that are missing
Blank_Key_Cnt = 0 ReturnKey = "" For i = 1 to NumKeys Key_Part = Field(Key,"|",i) if Key_Part = "" Then Blank_Key_Cnt = Blank_Key_Cnt + 1 BlankFields = i end ReturnKey = ReturnKey : Key_Part Next i If Blank_Key_Cnt > 0 and ErrorLogInd = "Y" then Message = "Error in Segment Key: ": Segment_Num : " There are " : Blank_Key_Cnt : " Missing Key Parts " : "The Missing Key Parts are" : BlankFields * Call DSLogInfo(Message, RoutineName ) End If Blank_Key_Cnt > 0 then Ans = "Invalid_Key" End Else Ans = ReturnKey End End Else Ans = "Invalid_Key" End SetDSParamsFromFile: A before job subroutine to set Job parameters from an external flat file Input Arg should be of the form: ParamDir,ParamFile If ParamDir is not supplied, the routine assumes the Project directory If ParamFile is not supplied, the routine assumes the Job Name (this could be dangerous) The routine will abort the job if anything doesn't go to plan Note: a lock is placed to stop the same job from running another instance of this routine. The second instance will have to wait for the routine to finish before being allowed to proceed. The lock is released however the routine terminates (normal, abort...) The parameter file should contain non-blank lines of the form ParName = ParValue White space is ignored. The Routine may be invoked via the normal Before Job Subroutine setting, or from
within the 'Job Properties->Job Control' window by entering "Call DSU.SetParams('MyDir,MyFile',ErrorCode)" For Andrew Webb's eyes only The routine could be made to work off a hashed file, or environment variables quite easily. It is not possible to create Job Parameters on-the-fly because they are referenced within a Job via an EQUATE of the form JobParam%%1 = STAGECOM.STATUS<7,1> JobParam%%2 = STAGECOM.STATUS<7,2> etc This is then compiled up....So forget it! Subroutinues SetDsparmsformfile(inputArg,Errorcode) $INCLUDE DSINCLUDE DSD_STAGE.H $INCLUDE DSINCLUDE JOBCONTROL.H $INCLUDE DSINCLUDE DSD.H $INCLUDE DSINCLUDE DSD_RTSTATUS.H Equ SetParams To 'SetDSParamsFromFile' ErrorCode = 0 ; * set this to non-zero to stop the stage/job JobName = Field(STAGECOM.NAME,'.',1,2) ParamList = STAGECOM.JOB.CONFIG If ParamList = '' Then Call DSLogWarn('Parameters may not be externally derived if the job has no parameters defined.',SetParams) Return End Call DSLogInfo("SetDSParmsFromFile inputarg >" : InputArg : "<", SetParms) ArgList = Trims(Convert(',',@FM,InputArg)) ParamDir = ArgList<1> If ParamDir = '' Then ParamDir = '.' End ParamFile = ArgList<2> If ParamFile = '' Then ParamFile = JobName End If System(91) Then Delim = '\'
End Else Delim = '/' End ParamPath = ParamDir:Delim:ParamFile Call DSLogInfo('Setting Job Parameters from external source ':ParamPath,SetParams) Call DSLogInfo(JobName:' - ':ParamList,SetParams) OpenSeq ParamPath To ParamFileVar On Error ErrorCode = 1 Call DSLogFatal('File open error on ':ParamPath:'. Status = ':Status(),SetParams) End Else Call DSLogWarn('File ':ParamPath:' not found - using default parameters.',SetParams) Return End StatusFileName = FileInfo(DSRTCOM.RTSTATUS.FVAR,1) Readvu LockItem From DSRTCOM.RTSTATUS.FVAR, JobName, 1 On Error Call DSLogFatal('File read error for ':JobName:' on ':StatusFileName:'. Status = ':Status(),SetParams) ErrorCode = 1 Return End Else Call DSLogFatal('Failed to read ':JobName:' record from ':StatusFileName,SetParams) ErrorCode = 2 Return End
StatusId = JobName:'.':STAGECOM.WAVE.NUM Readv ParamValues From DSRTCOM.RTSTATUS.FVAR, StatusId, JOB.PARAM.VALUES On Error Release DSRTCOM.RTSTATUS.FVAR, JobName On Error Null ErrorCode = 1 Call DSLogFatal('File read error for ':StatusId:' on ':StatusFileName:'. Status = ':Status(),SetParams) Return End Else Release DSRTCOM.RTSTATUS.FVAR, JobName On Error Null ErrorCode = 2 Call DSLogFatal('Failed to read ':StatusId:' record from ':StatusFileName,SetParams) Return End Loop ReadSeq ParamData From ParamFileVar On Error Release DSRTCOM.RTSTATUS.FVAR, JobName On Error Null ErrorCode = 4
Call DSLogFatal('File read error on ':ParamPath:'. Status = ':Status(),SetParams) Return End Else Exit End Convert '=' To @FM In ParamData ParamName = Trim(ParamData<1>) Del ParamData<1> ParamValue = Convert(@FM,'=',TrimB(ParamData)) Locate(ParamName,ParamList,1;ParamPos) Then If Index(UpCase(ParamName),'PASSWORD',1) = 0 Then Call DSLogInfo('Parameter "':ParamName:'" set to "':ParamValue:'"',SetParams) Else Call DSLogInfo('Parameter "':ParamName:'" set but not displayed on log',SetParams) End Else Call DSLogWarn('Parameter ':ParamName:' does not exist in Job ':JobName,SetParams) Continue End ParamValues<1,ParamPos> = ParamValue Repeat Writev ParamValues On DSRTCOM.RTSTATUS.FVAR, StatusId, JOB.PARAM.VALUES On Error Release DSRTCOM.RTSTATUS.FVAR, JobName On Error Null ErrorCode = 5 Call DSLogFatal('File write error for ':StatusId:' on ':StatusFileName:'. Status = ':Status(),SetParams) Return End Else Release DSRTCOM.RTSTATUS.FVAR, JobName On Error Null ErrorCode = 6 Call DSLogFatal('Unable to write ':StatusId:' record on ':StatusFileName:'. Status = ':Status(),SetParams) Return End Release DSRTCOM.RTSTATUS.FVAR, JobName On Error Null STAGECOM.JOB.STATUS = ParamValues setParamsForFileSplit: Using values from a control file this routine will run a job multiple times loading the specified number of rows for each job run. Function setParamsForFileSplit:(ControlFilename,Jobname) *********************************************************************** * Nick Bond.... * * This routine retrieves values from a control file and passes them as paramters to *
* a job which is run once for each record in the control file. * ** *********************************************************************** $INCLUDE DSINCLUDE JOBCONTROL.H Equate Routine TO 'setParamsForFileSplit' Call DSLogInfo('Starting Routine ', Routine) vFileName = ControlFileName vJobName = JobName vRecord = 1 ******** Open Control File and retrieve split values. Call DSLogInfo('Opening File: ':vFileName, Routine) OPEN vFileName TO vFILE ELSE Call DSLogFatal("Can't open file: ":vFileName, Routine) Call DSLogInfo('File is open: ':vFileName, Routine) ******** Start loop which gets parameters from control file and runs job. Loop ** Check record exists for record id READ vStart FROM vFILE, vRecord Then Call DSLogInfo('Loop Started: ':vFileName, Routine) Call DSLogInfo('Control File ID: ':vRecord, Routine) READV vStart FROM vFILE, vRecord, 4 Then READV vStop FROM vFILE, vRecord, 5 Then Call DSLogInfo('Load Records: ':vStart: ' to ' :vStop, Routine) End End ** Set Job Parameters and Run Job. vNewFile = 'SingleInvoice':vRecord vJobHandle = DSAttachJob(vJobName, DSJ.ERRFATAL) ErrCode = DSSetParam(vJobHandle, 'StartID', vStart) ErrCode = DSSetParam(vJobHandle, 'StopID', vStop) ErrCode = DSSetParam(vJobHandle, 'newfile', vNewFile ) ErrCode = DSRunJob(vJobHandle, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(vJobHandle)
vRecord = vRecord+1 End Else ** If record is empty leave loop GoTo Label1 End Repeat ******** End of Loop Label1: Call DSLogInfo('All records have been processed', Routine) Ans = vStart : ', ' : vStop SetUserStatus: Function Setuserstatus(Arg1) Call DSSetUserStatus(Arg1) Ans=Arg1 SMARTNumberConversion Converts numbers in format 1234,567 to format 1234.57 Function SMARTNUMBERconversion(arg1) INP = CONVERT(",",".",Arg1) ; * Commas to decimal point WRK = ICONV(INP,"MD33") ; * convert to internal to 3 decimal places Ans = OCONV(WRK,"MD23") ; * convert to external t 2 decimal places TicketErrorCommon Required to use the "LogToErrorFile" Routine. This stores variables used by the routine in shared memory: * FUNCTION TicketErrorCommon(Mod_Run_ID,Ticket_Group,Ticket_Sequence,Ticket_Set_Key,Job _Stage_Name,Mod_Root_Path) * * Places the current Row Ticket in Common * * Input Parameters : Arg1: Mod_Run_ID = The unique number allocated to a run of an Module * Arg2: Ticket_File_ID = The File ID assigned to the source of the Current Row * Arg3: Ticket_Sequence = The Ticket Sequence Number of the Current Row * Arg4: Ticket_Set_Key = Identifies a set of rows e.g. an Invoice number to set of invoice lines * Arg5: Job_Stage_Name = The Name of the Stage in the Job you want recorded in the error log
* Arg6: Mod_Root_Path = Root of the module - used for location of error hash file * * Don't Return Ans but need to keep the compiler happy Ans = "" RoutineName = 'ErrorTicketCommon' Common /TicketErrorCommon/ ModRunID, TicketFileID, TicketSequence, SetKey, JobStageName, ModRootPath
ModRunID = Mod_Run_ID TicketFileID = Ticket_File_ID TicketSequence = Ticket_Sequence SetKey = Ticket_Set_Key JobStageName = Job_Stage_Name ModRootPath = Mod_Root_Path RETURN(Ans) TVARate: Function TvaRate(mtt_Base,mtt_TVA) BaseFormated = "":(Mtt_Base) TvaFormated = "":(Mtt_TVA) If IsNull(BaseFormated) or BaseFormated = "0" or BaseFormated= "" Then Ans = 0 End Else TvaFormated = Ereplace(TvaFormated, ".", "") TvaFormated = Ereplace(TvaFormated, ",", "") BaseFormated = Ereplace(BaseFormated, ".", "") BaseFormated = Ereplace(BaseFormated, ",", "") Ans = Ereplace(TvaFormated/BaseFormated, ".", "") End TVATest: Function Tvatest(Mtt_TVA,Dlco) Country = TRIM(Dlco):";" TestCountry = Count("AT;BE;CY;CZ;DE;DK;EE;ES;FI;GB;GR;HU;IE;IT;LT;LU;LV;MT;NL;PL;PT;SE;SI; SK;", Country) Begin Case Case Mtt_TVA <> 0 Reply = "B3" Case Mtt_TVA = 0 And Dlco = "FR" And TestCountry = 0
Reply = "A1" Case Mtt_TVA = 0 And Dlco <> "FR" And TestCountry = 1 Reply = "E6" Case Mtt_TVA = 0 And Dlco <> "FR" And TestCountry = 0 Reply = "E7" Case @True Reply = "Error" End Case Ans = Reply UnTarFile: Function Untarfile(Arg1) DIR = "/interface/dashboard/dashbd_dev_dk_int/Source/" FNAME = "GLEISND_OC_02_20040607_12455700.csv" *CMD = "ll -tr ":DIR:"|grep ":FNAME *CMD = "cmp -s ":DIR:"|grep ":FNAME CMD = "tar -xvvf ":DIR:FNAME *-------------------------------*---syntax= tar -xvvf myfile.tar *--------------------------------Call DSExecute("UNIX", CMD, Output, SystemReturnCode) Ans = Output UtilityMessageToControllerLog Write an informational message to the log of the controlling job This routine takes a user defined message and displays it in the job log of the controlling sequence as an informational message. The routine should be used sparingly in production jobs to avoid degrading the performance. The return value of the function is always 1.: Function UtilityMessageToControllerLog(Arg1) * Write an informational message to the log of the controlling job. * * This function is mainly intended for development purposes, but can be used * within a production environment for tracing data values. The user should
* use this function cautiously, as it will cause a decrease in performance * if called often. * $include DSINCLUDE JOBCONTROL.H Equate RoutineName To "UtilityMessageToControllerLog" InputMsg = Arg1 If Isnull(InputMsg) Then InputMsg = " " End Call DSLogToController(InputMsg) Ans = 1 UTLPropagateParms: Routine allows a job to inherit parameter values from Job Control. This routine allows a job to inherit parameter values from Job Control by listing the parameters of child job and thereafter find the parameter in the parent job, getting value and setting parameter value in child job. Input Argument : Job handle (set by using DSAttachJob in Job Control) Output : If a parameter is not found the routine returns 3, otherwise 0. Function UTLprapagateparam(Handle) #include DSINCLUDE JOBCONTROL.H Equ Me To 'UTLJobRun' Ans = 0 ParentJobName = DSGetJobInfo(DSJ.ME,DSJ.JOBNAME) ChildParams = Convert(',',@FM,DSGetJobInfo(Handle,DSJ.PARAMLIST)) ParamCount = Dcount(ChildParams,@FM) If ParamCount Then ParentParams = Convert(',',@FM,DSGetJobInfo(DSJ.ME,DSJ.PARAMLIST)) Loop ThisParam = ChildParams<1> Del ChildParams<1> *** Find job parameter in parent job and set parameter in child job to value of parent. Locate(ThisParam,ParentParams;ParamPos) Then ThisValue = DSGetParamInfo(DSJ.ME,ThisParam,DSJ.PARAMVALUE) ParamStatus = DSSetParam(Handle,ThisParam,ThisValue) Call DSLogInfo ("Setting: ":ThisParam:" To: ":ThisValue, "UTLPropagateParms") End Else *** If the parameter is not found in parent job:
*** - write a warning to log file. *** - return code changed to 3. Call DSLogWarn ("Parameter : ":ThisParam:" does not exist in ":ParentJobName, "UTLPropagateParms") Ans = 3 End While ChildParams # '' Do Repeat End Return(Ans) UTLRunReceptionJob: This routines allows generic starting of reception jobs without creating specific Reception Processing Sequence. This routines allows generic starting of reception jobs without creating specific Reception Processing Sequence. - Determines job to launch (sequence or elementary job) - Attaches job - Propagates parameters using routine UTLPropagateParms. - Runs job and takes action upon result (any warning will lead to a return code NOT OK) Obligatory parameters in input are : - Country_Parm - Fileset_Name_Type_Parm - Abort_Msg_Parm - Module_Run_Parm Function Utilrunrece[pationjob(countryparam,fileset_name_typeparam,modulerunparam,Abort_m sg_param) $INCLUDE DSINCLUDE DSJ_XFUNCS.H $INCLUDE DSINCLUDE JOBCONTROL.H EQU Time$$ Lit "Oconv(Time(), 'MTS:'):': '" Ans = -3 vRecJobNameBase = Country_Parm : "_" : Fileset_Name_Type_Parm : "_Reception" *************************************************************************************** *** ################### *** *************************************************************************************** *** Define job to launch - Sequence or Job (START) *** *** *** L$DefineSeq$START: summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0057\%1 (JOB %2) started",
"ReceptionJob":@FM:vRecJobNameBase)) ** If Sequential Job exists - start Sequential Job. vJobSuffix = "_Seq" vRecJobName = vRecJobNameBase : vJobSuffix GoTo L$AttachJob$START L$DefineJob$START: ** If no Sequential Job - start Elementary Job vJobSuffix = "_Job" vRecJobName = vRecJobNameBase : vJobSuffix GoTo L$AttachJob$START L$ErrNoJob$START: ** If no job found - warn and end job Msg = DSMakeMsg("No job found to attach" : vRecJobNameBase : "_Seq or _Job", "") MsgId = "@ReceptionJob" GoTo L$ERROR L$AttachJob$START: Call DSLogInfo(DSMakeMsg("Checking presence of " : vRecJobName : " for " : Module_Run_Parm, ""), "") jbRecepJob = vRecJobName hRecepJob = DSAttachJob(jbRecepJob, DSJ.ERRNONE) If (Not(hRecepJob)) Then AttachErrorMsg$ = DSGetLastErrorMsg() If AttachErrorMsg$ = "(DSOpenJob) Cannot find job " : vRecJobName Then If vJobSuffix = "_Seq" Then GoTo L$DefineJob$START Else GoTo L$ErrNoJob$START End End Msg = DSMakeMsg("DSTAGE_JSG_M_0001\Error calling DSAttachJob(%1)%2", jbRecepJob:@FM:AttachErrorMsg$) MsgId = "@ReceptionJob"; GoTo L$ERROR GoTo L$ERROR End If hRecepJob = 2 Then GoTo L$RecepJobPrepare$START End *** *** *** Define job to launch - Sequence or Job (END) *** *************************************************************************************** *** ################### *** *************************************************************************************** *** Setup , Run and Wait for Reception Job (START) *** *** *** L$RecepJobPrepare$START: *** Activity "ReceptionJob": Setup, Run and Wait for job hRecepJob = DSPrepareJob(hRecepJob) If (Not(hRecepJob)) Then Msg = DSMakeMsg("DSTAGE_JSG_M_0012\Error calling DSPrepareJob(%1)%2",
jbRecepJob:@FM:DSGetLastErrorMsg()) MsgId = "@ReceptionJob"; GoTo L$ERROR End summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0057\%1 (JOB %2) started", "ReceptionJob":@FM:vRecJobName)) GoTo L$PropagateParms$START L$PropagateParms$START: *** Activity "PropagateParms": Propagating parameters from parent job to child job using separate routine. summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0058\%1 (ROUTINE %2) started", "PropagateParms":@FM:"DSU.UTLPropagateParms")) RtnOk = DSCheckRoutine("DSU.UTLPropagateParms") If (Not(RtnOk)) Then Msg = DSMakeMsg("DSTAGE_JSG_M_0005\BASIC routine is not cataloged: %1", "DSU.UTLPropagateParms") MsgId = "@PropagateParms"; GoTo L$ERROR End Call 'DSU.UTLPropagateParms'(rPropagateParms, hRecepJob) summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0064\%1 finished, reply=%2", "PropagateParms":@FM:rPropagateParms)) IdAbortRact%%Result1%%1 = rPropagateParms IdAbortRact%%Name%%3 = "DSU.UTLPropagateParms" *** Checking result of routine. If <> 0 then abort processing. If (rPropagateParms <> 0) Then GoTo L$ABORT GoTo L$RecepJobRun$START L$RecepJobRun$START: ErrCode = DSRunJob(hRecepJob, DSJ.RUNNORMAL) If (ErrCode <> DSJE.NOERROR) Then Msg = DSMakeMsg("DSTAGE_JSG_M_0003\Error calling DSRunJob(%1), code= %2[E]", jbRecepJob:@FM:ErrCode) MsgId = "@ReceptionJob"; GoTo L$ERROR End ErrCode = DSWaitForJob(hRecepJob) GoTo L$RecepJob$FINISHED *** *** *** Setup , Run and Wait for Reception Job (END) *** *************************************************************************************** *** ################### *** *************************************************************************************** *** Verification of result of Reception Job (START) *** *** *** L$RecepJob$FINISHED:
jobRecepJobStatus = DSGetJobInfo(hRecepJob, DSJ.JOBSTATUS) jobRecepJobUserstatus = DSGetJobInfo(hRecepJob, DSJ.USERSTATUS) summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0063\%1 finished, status=%2[E]", "ReceptionJob":@FM:jobRecepJobStatus)) IdRecepJob%%Result2%%5 = jobRecepJobUserstatus IdRecepJob%%Result1%%6 = jobRecepJobStatus IdRecepJob%%Name%%7 = vRecJobName Dummy = DSDetachJob(hRecepJob) bRecepJobelse = @True If (jobRecepJobStatus = DSJS.RUNOK) Then GoTo L$SeqSuccess$START; bRecepJobelse = @False If bRecepJobelse Then GoTo L$SeqFail$START *** *** *** Verification of result of Reception Job (END) *** *************************************************************************************** *** ################### *** *************************************************************************************** *** Definition of actions to take on failure or success (START) *** *** *** L$SeqFail$START: *** Sequencer "Fail": wait until inputs ready Call DSLogInfo(DSMakeMsg("Routine SEQUENCER - Control End Sequence Reports a FAIL on Reception Job", ""), "@Fail") GoTo L$ABORT L$SeqSuccess$START: *** Sequencer "Success": wait until inputs ready Call DSLogInfo(DSMakeMsg("Routine SEQUENCER - Control End Sequence Reports a SUCCESS on Reception Job", ""), "@Success") GoTo L$FINISH *** *** *** Definition of actions to take on failure or success (END) *** *************************************************************************************** *** ################### *** L$ERROR: Call DSLogWarn(DSMakeMsg("DSTAGE_JSG_M_0009\Controller problem: %1", Msg), MsgId) summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0052\Exception raised: %1", MsgId:", ":Msg)) bAbandoning = @True GoTo L$FINISH L$ABORT: summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0056\Sequence failed", "")) Call DSLogInfo(summary$, "@UTLRunReceptionJob") Call DSLogWarn("Unrecoverable errors in routine UTLRunReceptionJob, see entries
above", "@UTLRunReceptionJob") Ans = -3 GoTo L$EXIT ************************************************** L$FINISH: If bAbandoning Then GoTo L$ABORT summary$<1,-1> = Time$$:Convert(@VM, " ", DSMakeMsg("DSTAGE_JSG_M_0054\Sequence finished OK", "")) Call DSLogInfo(summary$, "@UTLRunReceptionJob") Ans = 0 ValidateField: Checks the length and data type of a value. Also checks value is a valid date if the type is Date. Any errors are logged to the Error Hash File Field_Value: The value from the field being validated Field_Name: The name of the field being validated Length: The maximum length of the field being validated Data_Type: The data type expected - possible values (Numeric, Alpha, Date, Char) Date_Format: If Data_Type is 'Date' Then the format must be specified. The syntax for this is the same as for the Iconv function. i.e "D/YMD[4,2,2]" for a date in the format 2004/12/23 $INCLUDE DSINCLUDE JOBCONTROL.H vRoutineName = 'ValidateField' DEFFUN LogToErrorFile(Table,Field_Name,Check_Value,Error_Number,Text_1,Text_2, Text_3, Message) Calling "DSU.LogToErrorFile" Common /HashLookup/ FileHandles(100), FilesOpened Common /TicketErrorCommon/ ModRunID, TicketFileID, TicketSequence, TicketSetKey, JobStageName, ModRootPath Ans = "START" vData_Type = Downcase(Data_Type) BEGIN CASE ******** Check the arguments * Value being checked is null CASE isNull(Field_Value) Call DSTransformError("The value being checked is Null - Field_Name = " : Field_Name, vRoutineName) * Argument for the data type is not valid CASE vData_Type <> "char" AND vData_Type <> "alpha" AND vData_Type <> "numeric" AND vData_Type <> "date" Call DSTransformError("The value " : Data_Type : " is not a valid data type for routine: ", vRoutineName) * Length is not a number CASE Not(Num(Length)) Call DSTransformError("The length supplied is not a number : Field Checked " : Field_Name, vRoutineName)
CASE vData_Type = "date" And (Date_Format = "" OR isNull(Date_Format)) END CASE ********* ******** Check The Values *** Check the data type of supplied value *** If vData_Type = 'numeric' Then If Num(Field_Value) Then vErr = 'OK' End Else vErr = LogToErrorFile("No Table",Field_Name,Field_Value,'10002','Text1','Text2','Text3','Value provided is not numeric') Ans = ' [Not Numeric]' End End Else If vData_Type = 'alpha' Then If Alpha(Field_Value) Then vErr = 'OK' End Else vErr = LogToErrorFile("No Table",Field_Name,Field_Value,'10003','Text1','Text2','Text3','Value provided is not alpha') Ans = ' [Not Alpha]' End End Else If vData_Type = 'date' Then vErr = Iconv(Field_Value,Date_Format) vErr = Status() If vErr <> 0 Then vErr = LogToErrorFile("No Table",Field_Name,Field_Value,'10004','Text1','Text2','Text3','Value provided is not a valid date for mask ':Date_Format) Ans = ' [Invalid Date]' End End Else End End End *** Check the length of the supplied value *** If Len(Field_Value) <= Length Then vErr = 'OK' End Else vErr = LogToErrorFile("No Table",Field_Name,Field_Value,'10001','Text1','Text2','Text3','Value provided is not the correct length') Ans = Ans : ' [Length Error]'
End Ans = Ans VatCheckSG: Function VatcheckSg(Arg1) String=Arg1 Slen=Len(String) Scheck=0 CharCheck=0 For Scheck = 1 to Slen Schar=Substrings(String,Scheck,1) If NUM(Schar) <> 1 then CharCheck=CharCheck+1 end Next Ans=CharCheck WriteParmFile: Function writeparamfile(Arg1,Arg2,arg3,arg4) Arg1; File Path Arg2: File Name Arg3: Parameter Name Arg4: Parameter Value vParamFile = Arg1 : "/" : Arg2 vParamName = Arg3 vParamValue = Arg4 If Arg4 = -256 Then vParamValue = "" End OpenSeq vParamFile To FileVar Else Call DSLogWarn("Cannot open ":vParamFile , "Cannot Open ParamFile") End Loop ReadSeq Dummy From FileVar Else Exit ;* at end-of-file Repeat
MyLine= vParamName : "=" : vParamValue *Write New Error File WriteSeqF MyLine To FileVar Else Call DSLogFatal("Cannot write to ": FileVar , "Cannot write to file") End WeofSeq FileVar CloseSeq FileVar Ans=MyLine WriteSeg: * FUNCTION SegKey(Value,ErrorLogInd) * * Executes a lookup against a hashed file using a key * * Input Parameters : Arg1: Segment_Num * Arg2: Segment_Parm * * Return Values: If the Segment should be written return value is "Y" * If If not return value is "N" * * * RoutineName = 'WriteSeg' * Determine if this segment should output Write_Ind = Field(Segment_Parm,"|",Segment_Num) If Write_Ind = "Y" then Ans = "Y" End Else Ans = "N" End
FUNCTION ReadParameter(ParameterName, DefaultValue,ConfigFile) * * Function : ReadParameter - Read parameter value from configuration file * Arg : ParameterName (default=JOB_PARAMETER) * DefaultValue (default='') * Config file (default=@PATH/config.ini) * Return *
: Parameter value from config file
If ParameterName = "" Then ParameterName = "JOB_PARAMETER" If ConfigFile = "" Then ConfigFile = @PATH:"/config.ini" ParameterValue = DefaultValue OpenSeq ConfigFile To fCfg Else Call DSLogFatal("Error opening file ":ConfigFile, "ReadParameter") Loop While ReadSeq Line From fCfg If Trim(Field(Line,'=',1)) = ParameterName Then ParameterValue = Trim(Field(Line,'=',2)) Exit End Repeat CloseSeq fCfg Ans = ParameterValue RETURN(Ans) You can define parameters values in you config file (/ds/config?ini as an example) in the form : Parameter = Value InputFile = /ds/file.dat To set the value of parameter 'InputFile', in your Job code :
DEFFUN ReadParameter(ParameterName, DefafaultValue, ConfigFile) CALLING "DSU.ReadParameter" PrmName PrmConfig
= 'InputFile' = '/ds/config.ini'
PrmDefault = DSGetParamInfo(DSJ.ME, PrmName, DSJ.PARAMDEFAULT) PrmValue = ReadParameter(PrmName, PrmDefault, PrmConfig) ErrCode = DSSetParam(DSJ.ME, PrmName, PrmValue) Call DSLogInfo("InputFile is ":PrmValue, DSJobName) *************************************************** ** FUNCTION ExecSQLProc(dataSource,userName,password) ==> RETURN (Ans) *************************************************** $INCLUDE UNIVERSE.INCLUDE ODBC.H ** Initialise parameters ODBC Ans = -1 henv = SQL.NULL.HENV hConn = SQL.NULL.HDBC hstmt = SQL.NULL.HSTMT ** Connect to DataSource status = SQLAllocEnv(hEnv) status = SQLAllocConnect(hEnv, hConn) status = SQLConnect(hConn, dataSource, userName , password ) If status = SQL.ERROR Then Call DSLogInfo("Connection KO " , "ExecSQLProc") Ans = -1 Goto Finally End Else Call DSLogInfo("Connection OK " , "ExecSQLProc") End
** Execution request (here, my stored procedure ) status = SQLAllocStmt(hConn, hStmt)
Stmt= "exec MyStoredProc ;" Call DSLogInfo("request SQL: " : Stmt, "ExecSQLProc") status = SQLExecDirect(hStmt, Stmt) If status<>SQL.SUCCESS AND status<>SQL.SUCCESS.WITH.INFO Then Call DSLogInfo("Request KO ", "ExecSQLProc") Ans = -1 Goto Finally End Else Call DSLogInfo("Request OK ", "ExecSQLProc") End
** If my stored proc return a result , i put it in 'res' (=> Ans) status = SQLBindCol(hStmt, 1, SQL.B.DEFAULT, res) status = SQLFetch(hStmt) If status<>SQL.SUCCESS AND status<>SQL.SUCCESS.WITH.INFO Then Call DSLogInfo("Result KO", "ExecSQLProc") Ans = -1 Goto Finally End Else Call DSLogInfo("Result OK ", "ExecSQLProc") Call DSLogInfo("The result is " : res, "ExecSQLProc") Ans = 0 End
** Free allocated ressources Finally: status = SQLFreeStmt(hStmt, SQL.DROP) status = SQLDisconnect(hConn) status = SQLFreeConnect(hConn) status = SQLFreeEnv(hEnv)
