ADBC Retrieval v1.0
The ADBC (Apama Database Connector) Retrieval block uses the ADBC adapter to retrieve data from a database. The ADBC adapter is a standard adapter provided with Apama. To make this block available to your scenario, add the ADBC for JDBC or ADBC for ODBC bundle to your project. Adding one of these bundles to your project automatically adds the ADBC Common bundle, which contains the ADBC blocks.
Description
The ADBC adapter is a standard adapter provided with Apama. It provides general database storage and retrieval (query) and also event capture and playback. The ADBC adapter supports both standard SQL and specialized databases. In particular, the adapter supports ODBC and JDBC. This support provides access to most commercial and open source SQL databases. ADBC provides a superset of the functionality that was available in the ODBC and JDBC Apama standard adapters.
The ADBC Retrieval block supports prepared queries, stored procedures, and query templates. For more information see:
Parameters
Parameter | Description |
service identifier | The name of the service to use, or blank for any service. |
database | The data source name of the database to connect to. |
user name | The username to use when connecting to the database. |
password | The password to use when connecting to the database (will be readable on screen). |
table name | The name of the table to retrieve data from. |
query string | The data source specific query statement to be used. If you specify a query template name, be sure to set the query parameters parameter as needed for the template. |
query parameters | If you specify a query template in the query string parameter, specify the parameters for the query template here. This is a comma separated list of name:value pairs, for example, TABLE_NAME:Trade, SORT_ORDER:asc. |
input types | The input types of the parameters in the query template that is specified in the query. These are listed in a comma separated list of types, such as Double, Double, Float. |
output types | The output types of the parameters in the query template that is specified in the query. These are listed in a comma separated list of types, such as Double, Double, Float. |
prepared query named id | A String that uniquely identifies this prepared query. |
prepared query params | The parameters to a prepared query in the form of a comma separated list of values. |
batch size | Number of rows to be buffered in the block. The default is 50. The maximum is 10,000. |
disable buffering | Boolean that indicates whether the results are streamed automatically as they are received. True indicates that they are. When set, the next rewind and reset operations have no effect since they are not needed. For use when wiring the ADBC Retrieval block’s output to another block. The default is false. |
unique connection | Boolean that indicates whether or not to create a new database connection. True indicates that you want the block to always create a new connection. False indicates that the block can use an existing connection. The default is false. |
Operations
Operation | Description |
connect | Establish a connection to the database. |
query | Perform the query operation. |
reset | Reset the output feed. |
next | Look up the next field/value pair. |
rewind | Rewind to the first result in the current buffered batch, without performing the operation again. |
stop | Stop the query, even if not complete. |
disconnect | Close the database connection. |
create prepared query | Create a prepared query for use later, passing in the correct input types. |
run prepared query | Run a previously created prepared query, passing in the relevant input parameters. |
delete prepared query | Delete an existing prepared query. |
retrieve query templates | Retrieve a full list of named queries available, including the query template name, parameters and description. |
Input feeds
This block has no input feeds.
Output feeds
Feed | Fields | Description |
schema | names | The field names of the results. |
types | The Apama types of the fields. |
indexable | The names of the fields that are indexes. |
results | number | The row number of the field/value pair. A number of -1 indicates the end of data. |
field | The name of the field the value was taken from. |
value | The value of the field. |
error | message | A message that describes the error if the store operation was unsuccessful. |
status | no more | true if the current query has been completed and no more field/value pairs are available after the current pair. |
more available | true if there is more data available to be read within the current batch and false otherwise. |
connected | true if connected to the database. |
prepared query | created | True if the query is successfully created; false otherwise. |
deleted | True if the query is successfully deleted; false otherwise. |
query templates | retrieved | false until the last query template is retrieved, at which point becomes true. |
query name | The identifying query name. |
query parameters | The list of parameters that the query requires. |
query description | A brief description of the purpose of the query. |
Note that it is possible for no more to be false and more available to be false; this means that the service is waiting for more results to become available, but they have not been supplied by the database yet. The scenario should wait until more available becomes true before calling next. As with the order manager iteration, the scenario will need to re-enter the state it is in while iterating, in order to re-evaluate all of the rules in that state.
Prepared queries
Creating prepared queries
1. The query string parameter should be set with the prepared query string, such as SELECT * FROM tablename WHERE intfield < ?
2. The input types of the input parameters in the prepared query being created. This is a comma-separated list of types, for example Double, Double, Float, etc.
3. The output types of the parameters in the prepared query being created should be set to a comma-separated list of types, for example Double, Double, Float if calling on a stored procedure.
4. In the block's prepared query named id parameter specify a unique identifier in the form of a user readable name (string) for this prepared query. Multiple prepared queries can exist in the block at any one time, so the identifier allows you to specify which query you want to use.
5. Call the create prepared query operation.
In the prepared query output feed, the created field will contain true if the query was successfully created.
Using prepared queries
1. In the block's prepared query named id specify the identifier of the prepared query you want to execute.
2. In the prepared query params parameter, list the values which should match, in types and number, those of the input types.
3. Call the run prepared query operation.
4. From this point on, the no more and more available fields and the next and stop operations behave in the same manner as they do for normal queries.
Deleting prepared queries
1. To delete a prepared query, set the prepared query named id parameter to the identifier of the prepared query you want to delete.
2. Call the delete prepared query operation.
In the prepared query output feed, the deleted field will contain true if the query was successfully deleted.
Stored procedures
Stored procedures must be created and deleted externally to the retrieval block, as in the case when creating a table in the database.
1. Once the stored procedure exists in the database you can create a prepared query, as in
Prepared queries, above. The syntax for using a stored procedure in a query string is in the form
{call demo_stored-procedure(?,?)}.
2. Specify the input types and output types parameters. Use NULL in the list of types for padding purposes. For example, given a Double (input only), Double (both input and output), and Float (output only), for the input types parameter specify Double, Double, NULL and for the output types parameter specify NULL, Double, Float.
3. Set an identifier in the prepared query named id parameter with this prepared query for future use.
4. Call the create prepared query operation.
In the prepared query output feed, the created field will contain true if the query was successfully created.
5. Using the prepared query associated with the stored procedure is the same as described in
Prepared queries, above.
Query templates
Retrieving query templates
You can retrieve the list of query templates that are associated with the project, by calling the retrieve query templates operation. In the query templates output feed, the query name, query paramters, and query description fields show each query template's name, parameters, and description, respectively. The retrieved field is true when all query templates have been retrieved.
Running query templates
1. Set the block's query string parameter to the name of the query template you want to run, such as findEarliest.
2. In the block's query parameters parameter specify the query parameters required by the query template, for example, TABLE_NAME:tableName,TIME_COLUMN_NAME:timefield.
3. Call the query operation to execute the query template, in the same way as for normal queries.
Prepared queries