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.
...
| Info | ||||||
|---|---|---|---|---|---|---|
| ||||||
Lookup query with one of the To prepare a query:
The call to the function and the values for the above example:
The call to the APL function:
|
sqlBulkExecuteQuery
When a UDR enters sqlBulkExecuteQuery the first met criteria of bulkSize or timeout triggers the database bulk lookup.
...
| Parameter | Description |
|---|---|
| A |
Returns: | A list containing objects that in their turn contain a context object and a list with results. |
sqlBulkResultGetContext
The function finds the context object from one result object in the list returned either by the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.
...
| Parameter | Description |
|---|---|
| A result object from the list returned by the |
Returns: | The context object associated with the |
sqlBulkResultGetSize
The function states the size of a result object in the list returned from the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.
...
| Parameter | Description |
|---|---|
| A result object from the list returned by the |
Returns: | An integer representing the size of the result |
sqlBulkResultGetValue
The function gets a result value from a result object in the list returned by either the sqlBulkExecuteQuery() or the sqlBulkDrain() functions.
...
| Parameter | Description |
|---|---|
| A result object from the list returned by the |
| The index of the result table |
| The column number |
Returns: | The value of the result object |
sqlBulkElapsedNanoTime
This function accumulates the time of the execution of the database queries that is end time of execution minus start time of execution.
...
| Parameter | Description |
|---|---|
Returns: | A long integer representing the accumulated nanoseconds. |
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.
...
| Parameter | Description |
|---|---|
| A preparedStatement object returned by the |
Returns: | Nothing |
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 8.5 see Database Profile in the Desktop in the Desktop user's guide.
Database profile example
Decoder_1 Agent
The incoming UDRs contains name and number to 10 people.
| Code Block |
|---|
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:
...