Developing Apama Applications > Building Dashboards > Using SQL Data > Attaching visualization objects to SQL data
Attaching visualization objects to SQL data
From the Object Properties window you can access the Attach to SQL Data dialog, which is used to connect an object to your database using an SQL query. Once an object has been attached to your database it can receive periodic or on-demand updates.
When an object property is attached to data, the Property Name and Value in the Object Properties window will be displayed in green. This indicates that editing this value from the Object Properties window is no longer possible.
To remove the data attachment and resume editing capabilities in the Object Properties window, right-click on the Property Name and select Detach from Data. You will recognize that an object property has been detached from the database when the Property Name and Value are no longer green.
Use the --sql quote command line option to enclose all table and column names specified in the Attach to SQL Data dialog in quotes when an SQL query is run. This is useful when attaching to databases that support quoted case-sensitive table and column names. Note: If a case-sensitive table or column name is used in the Filter field, or you are entering an advanced query in the SQL Query field, they must be entered in quotes even if the --sql quote option is specified.
To connect an object to your database using an SQL query:
1. Right-click on the Property Name from the Object Properties window and select Attach to Data > SQL.
The Attach to SQL Data dialog displays.
The Attach to SQL Data dialog provides drop down menus and an optional filter field that allow you to specify information that will be used to create an SQL query for the selected database. Alternatively, select the Enter SQL Query checkbox in order to enter an advanced query.
2. From the Database Name drop down menu, select the name of database to query.
The Database Name drop down menu lists all available databases. The Database Name field automatically displays the name of the default database. If the item you require is not listed, type your selection into the field.
A Database Repository file can be used to populate the initial values of drop down menus for Table Name and Column(s). See Specifying application options for information on how to create a Database Repository file. Otherwise, drop down menus populate based on databases added from the Application Options dialog or those typed directly into the Database Name field.
3. Check the Enter SQL Query checkbox in order to enter an advanced query.
If selected, the SQL Query text field, where you can enter your query, will replace the Table Name, Column(s) and Filter fields.
This option is for advanced users; SQL syntax will not be validated or checked for errors
4. In the Table Name field, enter the name of table in database to query.
You can create a file to exclude tables from the Table Name drop down menu. See Setting up SQL database connections for details.
5. From the Column(s) pull down menu, select the columns in table to display.
A Database Repository file can be used to populate the initial values of drop down menus for Table Name and Column(s). See Specifying application options for information on how to create a Database Repository file.
6. In the Filter field, optionally, enter SQL filter to apply to query.
Uses standard SQL syntax.
7. From the Update Mode pull down menu, select one of the following:
*Run Query Once: Select this if the data returned by this query is static. If selected, Apama will run this query only once. This is the default setting.
*Run Query Every Update Period: Select to run this query each update period. See Specifying application options for information on setting the update period.
*Run Query Every Query Interval: Select to run this query once every Query Interval.
*Run Query On Demand: Select to run this query each time a display that uses the query is opened and each time a substitution string that appears in the query string has changed.
8. In the Query Interval (seconds) field, enter the time in seconds to control how often Apama will run this query.
The query interval is evaluated during each update pass, so the amount of time elapsed between queries may be longer than the value entered. For example, if the update period is 2 seconds and the query interval is 5 seconds, the query will get run every six seconds. This option is only available if the Update Mode is Run Query Every Query Interval.
9. In the Maximum Rows field, enter the maximum number of rows to return from this query.
On some objects an additional property may further reduce the number of data points displayed. For example, the maxNumberOfRows property on the table or the maxPointsPerTrace property on the trend graph.
10. Do not modify Data Server field.
11. Click OK to apply the value and close the dialog.
You can also choose the following:
*Apply: Applies values without closing the dialog.
*Reset: Resets all fields to last values applied.
*Clear: Clears all fields. Detaches object from database (once Apply or OK is selected).
*Cancel: Closes the dialog with last values applied.
By default Apama will attempt to communicate with your database using a JDBC-ODBC bridge connection that is not password protected. If you are using a direct JDBC connection or a password protected ODBC-JDBC bridge connection, you will need to add your database in Application Options | SQL.
Copyright © 2013 Software AG, Darmstadt, Germany and/or Software AG USA Inc., Reston, VA, USA, and/or Terracotta Inc., San Francisco, CA, USA, and/or Software AG (Canada) Inc., Cambridge, Ontario, Canada, and/or, Software AG (UK) Ltd., Derby, United Kingdom, and/or Software A.G. (Israel) Ltd., Or-Yehuda, Israel and/or their licensors.