Creating query templates
If you want to create a new query template, you need to specify them in the project’s query template file. The query template file is found in the project’s bundle_instance_files folder and is named ADBC-queryTemplates-SQL.xml if you are using an ODBC or JDBC data source or ADBC-queryTemplates-Sim.xml if you are using a Sim data source. To add a query template:
1. In the Project Explorer view, expand the project’s bundle_instance_files folder and then expand either the ADBC_for_ODBC, ADBC_for_JDBC, or ADBC_for_Sim folder as appropriate.
2. Double click the query templates file, either ADBC-queryTemplates-SQL.xml or or ADBC-queryTemplates-Sim.xml. The file opens in the Apama Studio text editor. The following is the standard findLatest query template for ODBC and JDBC data sources.
3. The query templates are defined in the <query> element. The available attributes you can specify are:
name — A short name that will be displayed in the Query Template drop-down list.
description — A short description of the query.
implementationFunction — Currently query templates support the
substitution function. This allows a token to be used as a place holder in a SQL query.
inputString — For ODBC and JDBC data sources, use standard SQL syntax, from the example above:
inputString="select * from ${TABLE_NAME} order by
${TIME_COLUMN_NAME} desc limit 1">
In the above example, $(TABLE_NAME) and $(TIME_COLUMN_NAME) are parameters that will be replaced with a database table and column name when you create a query from the template (see parameter, below).
For Sim data sources use the TIME keyword plus the comparators =, ><>=, <= plus the time. You can use one or two TIME expressions. Note, because you are adding the expression to an xml file, you need to use > and < instead of the characters > and <. Do not use spaces before or after > and <.
The inputString expression can also use the INCLUDE, and EXCLUDE keywords, for example:
TIME=12345;INCLUDE=(com.apama.something);EXCLUDE=(com.apama.something.abc)
TIME>12345;TIME<20000
parameter — For ODBC and JDBC data sources you can specify parameters and default values for the query template. This allows you to create different queries from the same template that, for example, query different tables or different columns in the database. From the above example:
When you create a query from a template that has the parameters shown above in the Query Editor, you will specifiy the names of the database table and column to use with the query.
4. Save the query template file when you finish modifying it.