|Oracle® Enterprise Data Quality for Product Data COM API Interface Guide
Part Number E23724-02
The Job object is used to start jobs, check on the job status, and retrieve the results from finished jobs. The Transform Server object is used to define the server that the job will be run against.
There is one option that is required to be set after creating the job object
Transform Server - The server object of the server on which to run the DSA.
' Create the Job object Dim oJob As Job Set oJob = New Job oJob.DataServer = oTransformServer
The following options can be set on a job object
This will filter out invalid control characters from the input data that prevent the HTTP transfer of data. This is ignored if the input data is from a database query.
oJob.filterData = False Dim lJobId As Long
To define a user-specified separator character, other than the default tab character. This character is used to delineate the input data fields and result data fields
oJob.separatorChar = "|"
To toggle this option back off and revert to the default Tab separator character, just set
separatorChar to an empty string.
Setting this will email the result of the job to the specified email address. Note that this will use the email server configured in the Oracle DataLens Server Administration Web Pages. If this is specified, then the job results will not be returned to the application program, but will be emailed instead.
oJob.Email = firstname.lastname@example.org
To toggle this option back off, just set Email to an empty string.
Setting this will FTP the results of the job to the named FTP configuration. The FTP configuration is setup in the Oracle DataLens Server Administration Web Pages. If this is specified, then the job results will not be returned to the application program, but will be FTPd instead.
oJob.ftp = "CompanyCFG"
To toggle this option back off, just set FTP to an empty string.
A job can be started in asynchronous mode. The job is started and a job id is returned that is used to check on the status of the job and to get the result data when the job has finished. There are three different types of input that can be used to start a job. They are as follows:
Db Parameter input
A collection of String input-fields of data is passed to the start method and a single job ID is returned. The start Method is called just a single time with the collection.
This is the priority the job will be given on the server for processing. Large batch overnight jobs should be given a priority of low. Small jobs with few input records, or requests that need a quick response, such as users waiting for a response should get a priority of High. All other jobs should use a priority of medium. Note that the number of concurrent jobs that can be run on the server is also controlled by the priority of the job (See the Server configuration pages of the Oracle DataLens Administration Web pages for more information). There are the priority values that can be used.
A description of this particular job.
The name of the DSA to run on the Oracle DataLens Server
A collection of collections of input data fields as Strings.
This locale to use for output for this job.
The DSA Job ID obtained from the Start call.
' Rem Start the WFG Job with an input data Collection lJobId = oJob.Start(sPriority, sDescription, _ sProcessMap, dataCollection, sRuntimeLocale) If oJob.IsError Then Call reportError(oJob.errorMsg) Exit Function End If
An array of tab-separated data is passed to the start method and a single job ID is returned. The start Method is called just a single time with a single one-dimensional array of data.
Actual parameters are the same as for the collection-based start method. The difference is that the fourth parameter is an array of tab-separated data:
The Array of tab-separated input data to be processed.
' Rem Start the WFG Job with an input data array Dim lJobId As Long lJobId = oJob.StartArr(sPriority, sDescription, _ sProcessMap, dataArray, sRuntimeLocale) If oJob.IsError Then Call reportError(oJob.errorMsg) Exit Function End If
Here is the code to start a job with a database query as input. The
dataArray is replaced with the
Dim arrQueryParameterList arrQueryParameterList = Split("p1|p2|p3, "|") ' Run a job with database input and an array of Db parameters lJobId = oJob.startDb(sPriority, sDescription, _ sProcessMap, arrQueryParameterList, sRuntimeLocale)
All of these results can be obtained synchronously or asynchronously. The last parameter in the
getResultStep data calls is the flag:
This flag is true for synchronous and false for asynchronous
The advantage of synchronous calls is that the code is very simple and straight-forward. The call will not return until the data has finished processing.
The advantage of asynchronous calls is the application code can continue processing while the job finishes processing until the result data is ready. This is useful for doing additional processing for large DSA jobs and for keeping a user-interface responding to user-input, rather than hanging until the result data is returned.
A simple DSA job is defined as a job with no approvals or reviews and only a single output step. This is also a very common scenario for DSA jobs called from the API.
' Synchronously get the results; ' The function call will not return till the data is ready Set TransformedRecords = oJob.getResultData(lJobId, True) If TransformedRecords.IsError Then MsgBox (TransformedRecords.errorMsg) GoTo exception End If
' Asynchronously get the results Do Set TransformedRecords = oJob.getResultData(lJobId, False) If TransformedRecords.IsError Then If TransformedRecords.errorCode = oJob.JOB_NOT_COMPLETED Then Sleep (5 * 1000) Else MsgBox (TransformedRecords.errorMsg) GoTo exception End If End If Loop While TransformedRecords.errorCode = oJob.JOB_NOT_COMPLETED
The transformed result data can also be retrieved directly from the job object by using named output steps. This is useful for integrating a DSA process into an application where there are multiple outputs that need to be used for different purposes within the application.
The transformed result data can also be obtained directly from "output" Job Steps as follows. This gives the application programmer control of the job down to the individual DSA step level. This can be useful for an application where the actual names of the output steps are not known at run-time.
'Retrieve result sets for all output steps Dim JobStep As JobStep Dim TransformedRecords As TransformedRecords For Each JobStep In oJob.steps If JobStep.isOutputStep Then If oJobStep.isCompleted Then Set TransformedRecords = JobStep.getResults(TransformServer, True) If TransformedRecords.IsError Then Call reportError(TransformedRecords.errorMsg) GoTo exception End If End If End If Next
The individual fields can be extracted from each transformed record.
'Iterate through the collection of transformed records 'and the collection of transformed fields Dim TransformedRecord As TransformedRecord Dim sField As String For Each TransformedRecord In TransformedRecords.Items Dim TransformedField As TransformedField For Each TransformedField In TransformedRecord.Items sField = TransformedField.Value Next Next
This is useful if there is only a single field of data returned. This can also be a quick check for debugging to determine the correctness of the records being returned.
By default, the data is tab-separated, but this can be changed during the job.start call be defining a different separator character.
'Iterate through the collection of transformed records Dim TransformedRecord As TransformedRecord For Each TransformedRecord In TransformedRecords.Items tab_separated_data = TransformedRecord.tabSepResult Next
The job is running on the server when the Job Id is returned from the job.start function call. The job can be checked for completion before the results can be obtained as demonstrated in the following:
Note:Each individual job step can be checked for completion, rather than checking the entire job as is done in the following code example.
Note:Job data can also be retrieved synchronously, eliminating the need to check the status of the job.
'Get the job from the server Dim oJob As Job Set oJob = g_TransformServer.getJob(jobId) If oJob.IsError Then Call reportError(oJob.errorMsg) GoTo exception End If 'Results can only be retrieved once If oJob.isResultsRetrieved Then GoTo exception End If 'Wait for the job to finish Do While Not oJob.IsDone Sleep (5 * 1000) Set oJob = g_TransformServer.getJob(jobId) If oJob.IsError Then Call reportError(oJob.errorMsg) GoTo exception End If Loop
The results of the job are in the job object once the job has finished.
The DSA Maps are not guaranteed to be in records input and in records returned. They are also not guaranteed that the output records will be in the same order as the input records. For this reason, the Id values need to be matched up with the input records if the input records are to be updated with the results from the DSA call.
A simple example follows where we have input data in a RecordSet called RS and we are simply updating the description with the matching ID values:
RS.MoveFirst For lRsCounter = 0 To RS.RecordCount - 1 ' Get the returned record based on the ID Dim TransformedRecord As TransformedRecord sID = UCase(Trim(RS("ID"))) On Error Resume Next Set TransformedRecord = TransformedRecords.Item(sID) ' Error will be thrown if we attempt to access an item that does not ' exist in the returned data; in that case we skip to the next record If Err Or (TransformedRecord Is Nothing) Then Err.Clear GoTo nextRecord End If On Error GoTo 0 iFieldCount = TransformedRecord.Count() ' Pull out the SCS Returned data Fields from the matching records Dim TransformedField As TransformedField For Each TransformedField In TransformedRecord.Items sField = TransformedField.Value If (Count = 0 And iFieldCount > 0) Then sScsId = sField ' 1st field has the Id ElseIf (Count = 1 And iFieldCount > 1) Then sScsDesc = sField ' Next field has the description ElseIf (Count = 2 And iFieldCount > 2) Then sScsCategory = sField ' Next field has the category End If Count = Count + 1 Next RS.Edit RS("Description") = Trim(sScsDesc) If sScsCategory <> "" Then RS("Category") = Trim(sScsCategory) End If RS.Update nextRecord: RS.MoveNext Next lRsCounter