...

The Database Bulk functions enable you to bundle multiple queries into one SQL statement. This significantly improves the throughput due to reduced need of context switching and network traversals. The functions are intended for batch usage only. 

Making an SQL Query

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

...

sqlBulkResultGetContext

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

...

sqlBulkResultGetSize

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

...

sqlBulkResultGetValue

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

...

sqlBulkElapsedNanoTime

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

...

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.

...

Example Workflow

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

Example workflow

Prerequisites

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

Decoder_1 Agent

The incoming UDRs contains name and number to 10 people.

Katerine,Stenberg,0046123456
Kalle,Andersson,0046789123
Mia,Karlsson,0046999111
Karina,Hansson,0046222333
PerErik,Larsson,0046111999
Mikael,Grenkvist,0046333444
Jonas,Bergsten,0046555666
Petra,Sjoberg,0046777888
Karl,Kvistgren,0046444555
FiaLotta,Bergman,0046666222

Analysis_1 Agent

The Analysis agent does the database bulk lookup. In this case, the number of calls made by the people listed in the incoming UDRs.

...

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

Database Table

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:

...