3 DSA Jobs

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

Options on a Job Object

The following options can be set on a job object

Filter Data

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

Separator Character

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.

Email

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 = yogi@bear.com

To toggle this option back off, just set Email to an empty string.

FTP

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.

Starting a Job

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:

Collection Input
Array Input
Db Parameter input

Collection 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.

Actual parameters:

Job Priority

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.

"Low"
"High"
"Medium"

Description

A description of this particular job.

DSA Name

The name of the DSA to run on the Oracle DataLens Server

Data Collection

A collection of collections of input data fields as Strings.

Run-time Locale

This locale to use for output for this job.

Return data:

Job ID

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

Array Input

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:

Array

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

Database Parameter Input

Here is the code to start a job with a database query as input. The dataArray is replaced with the arrQueryParameterList:

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)

Getting the Result Data from the Job

All of these results can be obtained synchronously or asynchronously. The last parameter in the getResultData and getResultStep data calls is the flag:

WaitForResults

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.

Simple DSA Job

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.

Synchronous Example

' 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

Asynchronous Example

' 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

Multiple Output DSA Job

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.

Synchronous Example

' Synchronously get the results;
Set TransformedRecords = oJob.getResultStepData(lJobId, sStepName, True)
   If TransformedRecords.IsError Then
       MsgBox (TransformedRecords.errorMsg)
       GoTo exception
   End If

Getting Result Data from the Individual Job Steps

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

Extracting Data from the Transformed Records

Pull the Data from the Transformed Records

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

Pull the Data from the Transformed Records as Tab-Separated Output

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

Optional - Waiting for the Job to Complete

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.

Syncing Returned Results with the Original Input Data

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