Using named queries
To use a named query
1. Create a new Query event.
2. Initialize the query by calling the Query event's initNamedQuery() action, passing the name of the database's Connection event, the name of the query template, and a dictionary<string, string> containing the names and values of the named query's parameters.
3. Call the Query event's setReturnType() action to specify the return type to be ResultEvent. When a query is run, each row in the result set will be mapped to a dictionary event field in a ResultEvent event.
4. Call the Query event's setReturnEventCallback() action to specify the callback action that will handle the results returned by the query.
5. If the query will return a large number of events (on the order of thousands):
a. Call the Query event's setBatchSize() action passing an integer that sets the batch size. The query returns results in batches of the specified size.
b. Call the Query event's setBatchDoneCallback() action passing the name of the handler action.
c. Define the setBatchDoneCallback() action to define what to do when a batch is complete. You must call the Query event's getNextBatch() action to continue receiving the query results. The batch size for the next batch is set by passing an integer parameter for the batch size. You could also call the stop action to stop the query, rather than continuing to receive batches of data.
6. Call the Query event's start() action passing the name of the handler action that will be called when the query completes.
7. Create the callback action that you specified in Step 4, to handle the results returned by the query.
8. Each row of data that matches the query results in a call to the callback action, returning the row results in a parameter of ResultEvent type. The ResultEvent type contains a dictionary field that contains the row data.
9. Create the action that specifies what to do when the query completes (when all results are returned).
The following example uses the initNamedQuery() action call to initialize the query, specifying the findEarliest named query and stock_tables as the value for the named query's TABLE_NAME parameter.
using com.apama.database.Connection;
using com.apama.database.Query;
using com.apama.database.ResultEvent;
monitor ADBCexample {
Connection conn;
Query query;
string serviceId := "com.apama.adbc.JDBC_INSTANCE_1";
string dbUrl := "jdbc:mysql://127.0.0.1:3306/exampledb";
string user := "root";
string password := "mysql";
string queryString := "SELECT * FROM sys.tables";
string tableName := "stock_table";
dictionary<string,string> paramTable :=
{"TABLE_NAME":tableName,"TIME_COLUMN_NAME":"tbd"};
action onload() {
conn.openDatabase(serviceId, dbUrl, user, password, "",
handleOpenDatabase);
}
action handleOpenDatabase (Connection conn, string error){
if error.length() != 0 {
log "Error opening database : " + error at ERROR;
}
else {
log "Database is open." at INFO;
runQuery();
}
}
action runQuery() {
query.initNamedQuery(conn, "findEarliest", paramTable);
query.setReturnType("ResultEvent");
query.setResultEventCallback("token", handleResultEvent);
query.start(handleQueryComplete);
}
action handleResultEvent(Query q, ResultEvent result,string token) {
log result.toString() at INFO;
}
action handleQueryComplete(Query query, string error,
integer eventCount, float lastEventTime) {
if error.length() != 0 {
log "Error running query '" + queryString + "': " +
error at ERROR;
}
else {
log " Query '" + queryString + "' successfully run." at INFO;
log " Total events: " + eventCount.toString() at INFO;
if lastEventTime > 0.0 {
log " Last Event Time: " + lastEventTime.toString()
at INFO;
}
}
conn.closeDatabase(handleCloseDatabase);
}
action handleCloseDatabase(Connection conn, string error) {
if error.length() != 0 {
log "Error closing database : " + error at ERROR;
}
else {
log "Database closed." at INFO;
}
}
}