...

When making an SQL query it must follow a certain format and the order of the columns in the query must remain the same in the statement as in the where_clause.

Select A,B,C  FROM Test_Table WHERE (A=? and B=? and C=?) 
AND ((A=? and B=? and C=?) OR (A=? and B=? and C=?))

If the where_clause in the SQL statement contains a range condition instead of an exact lookup key, the intended result cannot directly be associated back to the UDRs. Instead a result based on the significant columns will be returned. The result rows matching the range condition must be associated back to the UDRs using APL code. For further information, see the lookup lookup query example below.

The following functions for Database Bulk described here are:

sqlBulkPrepareStatement

This function prepares the the query. A JDBC connection is created and the bulkSize is set.

...

any sqlBulkPrepareStatement
(string  dbProfile ,
string  statement ,
string  static_where_clause ,  //set to null if not present
string  where_clause ,
int  bulkSize ,
int  significantColumns ,
int  totalColumns ,
int  timeout ,  //Optional
@Deprecated boolean disableCommit ,  //Optional)

...

SELECT Column1, Column2, Column3 FROM Test_Table
WHERE Column1=? AND Column2=? AND Column3 <=?;

significantColumns = 2
totalColumns = 3
where_clause = "Column1=? and Column2=? and Column3 <=?"
static_where_clause = null
bulksize = 100
dbprofile = "myFolder.myProfile"
statement = "select Column1, Column2, Column3 from Test_Table"

initialize {
    any pStatement = sqlBulkPrepareStatement("myFolder.myProfile", 
    "select Column1, Column2, Column3 from Test_Table",
    null, "Column1=? and Column2=? and Column3 <=?",
    100, 2,3);
}

sqlBulkExecuteQuery

When a UDR enters sqlBulkExecuteQuery the first met criteria of bulkSize or timeout triggers the database bulk lookup.

...

Next timeout occasion is calculated by adding the time of the last database lookup to the current timeout value.

list <any> sqlBulkExecuteQuery(any ps,
any  context ,
any value1, any value2, ...)

...

ps =  pStatement object from sqlBulkPrepareStatement()
context = myUDR
values =  myValues1, myValue2, myValue3

consume{
    list <any> result = sqlBulkExecuteQuery(pStatement,
    myUDR, 
    10,2,3); 
}

The function executes the SQL query before the bulkSize is reached.

list <any> sqlBulkDrain(any ps)

sqlBulkResultGetContext

The function finds the context object from one result object in the list returned either by the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.

any sqlBulkResultGetContext(any presult)

sqlBulkResultGetSize

The function states the size of a result object in the list returned from the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.

int sqlBulkResultGetSize(any presult)

sqlBulkResultGetValue

The function gets a result value from a result object in the list returned by either the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.

any sqlBulkResultGetValue(any presult,
    int index,
    int column)

sqlBulkElapsedNanoTime

This function accumulates the time of the execution of the database queries that is end time of execution minus start time of execution.

long sqlBulkElapsedNanoTime()

sqlBulkClose

The function closes down the database connection as well as the SQL statement. If the sqlBulkClose is not called the connection will be closed when the workflow stops.

void sqlBulkClose(any  ps )

Example Workflow

The example shows a basic workflow where a Database Bulk lookup with a range condition is performed.

 Image RemovedExample workflow

...

Prerequisites

To use the sqlBulk related APL commands there must be a database profile configuration prepared. For further information, see Database Profile in the Desktop user's guide.

Image Removed

...

Decoder_1 Agent

The incoming UDRs contains name and number to 10 people.

...

The database lookups are in this example done in a database table with a content shown here:

Image Removed

...

Encoder_1 Agent

When the encoder has encoded the information from the analysis agent the result sent to the disk out agent is shown here:

^{Katerine,Stenberg,46123456,,10,10}

...

^{Kalle,Andersson,46789123,,10}

...

^{Mia,Karlsson,46999111,,10}

...

^{Karina,Hansson,46222333,,10}

...

^{PerErik,Larsson,46111999,,11,11}

...

^{Mikael,Grenkvist,46333444,,10}

...

^{Jonas,Bergsten,46555666,,10}

...

^{Petra,Sjoberg,46777888,,10}

...

^{Karl,Kvistgren,46444555,,10}

...

^{FiaLotta,Bergman,46666222,,10}