Using SQL data

SQL system requirements and setup

The SQL data source requires a database with a JDBC driver. See Setting up SQL database connections.

You must also set the dashboard properties using the command line. In order to use a direct JDBC connection to communicate with a database, add your JDBC jar file to your dashboard properties.

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 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 Datadialog in quotes when an SQL query is run. This is useful when attaching to databases that support quoted case-sensitive table and column names.

To connect an object to your database using an SQL query

1. Right-click 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 Datadialog 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.

   Info

   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.

   Info

   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.

   Info

   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.
      Info

      By default, Apama will attempt to communicate with your database using a connection that is not password protected. If you are using a direct JDBC connection, you will need to add your database in Application Options | SQL.

Validation colors

Fields in the dialog change colors according to the information entered. These colors indicate whether or not information is valid. Information entered into the dialog is validated against the selected database or the Database Repository file. See Specifying application options for information on how to create a Database Repository file.

The following describes the significance of the Attach to SQL Data validation colors:

• Blue: Unknown, that is, entry does not match any known database (or you have not attempted a connection—see INFO below).
• Yellow: Offline, that is, not connected to database.
• White: Valid.
• Red: Invalid. Database is valid, but Table or Column(s) selected are not.

Substitutions

Substitutions allow you to build open-ended displays in which data attachments depend on values defined at the time the display is run. Generic names, such as $table1 and $table2, are used instead of specific values. Later when the display is running, these generic values are defined by the actual names, such as production_table and system_table. In this way, a single display can be reused to show data from a number of different databases.

Select table columns

From the Attach to SQL Data dialog you can specify which table columns to display and in what order they will appear. In order to populate the listing of available columns, you must first select a valid database and table.

To specify which table columns to display and in what order they will appear

1. Right-click the Property Name from the Object Properties window and select Attach to Data > SQL.

   The Attach to SQL Data dialog displays.

2. Click on the ellipses button in the Column(s) field (or right-click in the Column(s) field and click Select Columns).

   The Select Columns dialog displays, which contains a list of Available Columns that you can add to your table.

3. To add a column, select an item from the Available Columns list and click Add button.

   If the item you require is not listed, type your selection into the Enter Column Name field.

4. Click the Remove button to delete an item previously added to the Selected Columns list.

5. Control the order of columns in a table by arranging the items in the Selected Columns list with the Move Up and Move Down buttons.

Validation colors indicate whether selected columns are valid. However, if even one column selected is invalid the Column(s) field in the Attach to SQL Data dialog will register as an invalid entry.

Defining SQL commands

From theObject Properties window you can access the Define SQL Command dialog. This dialog is used to assign SQL commands allowing you to issue commands from within a dashboard.

To assign SQL commands

1. Right-click the appropriate command property in the Object Properties window and select Define Command > SQL.

   The Define SQL Command dialog displays, which provides a drop down menu with available databases and a field to enter a SQL statement.

   

2. In the Database Name drop down menu, enter 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. Drop down menus populate based on databases added from the Application Options dialog or those typed directly into the Database Name field.

3. In the SQL Command field, enter a SQL statement to run using standard SQL syntax.

   Info

   This option is for advanced users, SQL syntax will not be validated or checked for errors.

4. In the Queries field, if Run Affected Queries After Command is selected, Apama immediately runs all queries, including static queries, that use the database table modified by the command. This causes table changes to be displayed immediately, rather than waiting for the next scheduled query update.

   This option is only supported for update, insert, and delete operations in which the name of the database table to be modified is specified explicitly. If a command performs another SQL operation (such as running a stored procedure that modifies tables), the results of the operation will not be displayed until the next scheduled update of each affected query. Display of the modified data may be delayed for other reasons, for example, if the database does not commit the results immediately and instead returns the old data on the next query.

5. Do not modify the Data Serverfield.

6. 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 assigned command (once Apply or OK is selected).
   • Cancel: Closes the dialog with last values applied.

Validation colors

The Database Name field changes colors according to the information entered. These colors indicate whether or not information is valid. Information entered into the dialog is validated against the selected database or the Database Repository file. See Specifying application options for information on how to create a Database Repository file.

The following describes the significance of the Define SQL Command validation colors:

• Blue: Unknown. Entry does not match any known database (or you have not attempted a connection—See INFO below).
• Yellow: Offline. Not connected to specified database.
• White: Valid. Database name is valid.

Special values

When an actionCommand is run $value is replaced with the value from the control. This value may be used in any field in the Define SQL Command dialog.

Specifying application options

To access the Application Options dialog, in the Builder select Tools > Options.

Options specified in the SQL tab can be saved in an initialization file (OPTIONS.ini). On startup, the initialization file is read by the Builder, Viewer, display server, and data server to set initial values. If no directory has been specified for your initialization files and OPTIONS.ini is not found in the directory where you started the application, then Apama will search under lib in your installation directory.

SQL tab

This tab allows you to add or remove your databases and set the default database. In order for Apama to communicate with your databases, you must set up a JDBC connection.

When you add a database to the list it will be highlighted in yellow indicating that it is not connected. To attempt to connect to a database, click OK, Apply, or Save. If the background remains yellow, then Apama was unable to make a connection to your database.

Check your database connection and see Setting up SQL database connections for information on how to set up your driver correctly.

If the connection is successful, and the Get Tables and Columns from Database checkbox is selected, Apama will use information from this database to populate drop down menus in the Attach to Data dialog with available tables and columns. If a database repository is found, information from your database will be merged with data from the repository file. If you deselect the Get Tables and Columns from Database checkbox Apama will no longer query your database for this information, but the database repository will still be used to populate drop down menus. Using a database repository to populate drop down menus makes it possible to specify which tables and columns from your database will be listed in the Attach to Data dialog and gives you the ability to build displays while databases are offline.

If you are using a direct JDBC connection you must click Save in order to record your options in OPTIONS.ini. This will allow Apama to reconnect with your database the next time you run the Builder or the Viewer.

The Application Options dialog has the following fields and buttons:

• Default Database: Name of database used as the default for data attachments. Select from drop down menu to change default setting.
• Add Database: Click to open the Add Database dialog. To edit, select a database from the list and double-click. Databases that are updating objects in a current display cannot be renamed.

Adding a Database

The Add Database dialog has the following fields:

• Database Name: The name to use when referencing this database connection in your data attachments.

• User Name: The user name to pass into this database when making a connection. This parameter is optional.

• Password: The password to pass into this database when making a connection. This parameter is optional.

• Use Client Credentials: If selected, the user name and password from the Apama login will be used instead of the User Name and Password entered in the Add Database dialog. Connections to this database will only be made when you are running with login enabled and a display is opened that accesses this database.

• As a result, this connection will not be made when you click OK or Apply in the Application Options dialog and will remain yellow. If you will be using the data server or the display server with a database connection that has this option enabled, you must enable Use Client Credentials for Database Login in these applications.

• Table Types: Specify the types of tables to retrieve when querying the database for available tables. Refer to your database manual for a list of valid table types. This parameter is optional. Table types are entered as a comma delimited list, for example, TABLE, VIEW.

• Run Queries Concurrently: If selected, each query on the connection is run on its own execution thread. The default is disabled.

  Info

  This option should be used with caution since it may cause SQL errors when used with some database configurations and may degrade performance due to additional database server overhead. See your database documentation to see whether it supports concurrent queries on multiple threads.

• JDBC Driver Class Name: The fully qualified name of the JDBC driver class to use when connecting to this database. The path to this driver must be included in the RTV_USERPATH environment variable.

• JDBC Database URL: The full database URL to use when connecting to this database using the specified JDBC driver. Consult your JDBC driver documentation if you do not know the database URL syntax for your driver.

• Remove Database: Select a database from the list and click Remove Database to delete. Databases that are updating objects in a current display cannot be removed.

• Suppress Permission Errors From Database: If selected, SQL errors with the word “permission” in them will not be printed to the console. This is helpful if you have selected the Use Client Credentials option for a database. In this case, your login does not allow access for some data in their display, you will not see any errors.

• Get Tables and Columns from Database: If selected, information from your database will automatically populate drop down menus in the Attach to Data dialog and you will be able to select from available tables and columns in your database.

  Info

  If a database repository is found, information from your database will be merged with data from the repository file.

• Save Database Repository: Click to save a file that records available tables and columns in your database and applies values to drop down menus in the Attach to Data dialog.

Instead of using the Add Database dialog, it is possible enter this information manually into OPTIONS.ini. See Entering database information directory into OPTIONS.ini.

Database repository

Click Save Database Repository to save a file that contains available information for tables and columns in your database. Before saving a database repository, you must add the database or databases from which the file will retain information.

Information stored in the database repository file will be used to populate the initial values of drop down menus in the Attach to Data dialog.

When you click Save Database Repository, a confirmation dialog will appear to verify in which directory you would like to save the database repository file. If you specified a directory for your initialization files, all repository files will be saved to, and read from, that directory. If you select the lib directory, the repository file will be available from any directory where you run Apama. If you do not select the lib directory, the repository file will be saved in the directory where you started the current session and will only be available when you run Apama from that particular directory.

See Setting up SQL database connections for details on editing an existing database repository file.

Entering database information directory into OPTIONS.ini

To add an SQL database by entering information directly into OPTIONS.ini (instead of using the Add Database dialog—see Adding a Database, add a line of text of the following form:

sqldb databaseName username password jdbcUrl jdbcClassName tableTypes
   useClientCredentials-boolean runQueriesConcurrently-boolean

You must supply all fields. Use “-” for fields that do not have a value.

Following is an example:

sqldb myDatabase - - - - - false false

In the example above, the databaseName is myDatabase, and both useClientCredentials and runQueriesConcurrently are false. All other fields are not specified.

For JDBC databases jdbcUrl and jdbcClassName must be set.

See also Generating encrypted passwords for SQL data sources.

Generating encrypted passwords for SQL data sources

If you are adding an SQL data source by entering information directly into OPTIONS.ini (see Entering database information directory into OPTIONS.ini), and you specify a username and password, use the dashboard_management utility in order to generate an encrypted version of the password. Use the encrypted version in the sqldb line of OPTIONS.ini.

Commands of the following form yield the encrypted string as output:

dashboard_management -e | --encryptString password

Following is an example:

dashboard_management -e sunshine

This yields the following output:

0134901351013440134901338013390134401335

Following is a sample sqldb line that includes the encrypted password shown above:

sqldb test2 username 0134901351013440134901338013390134401335 - - - true false

Setting up SQL database connections

Apama communicates with your database using a direct JDBC connection that requires some set up before Apama can communicate with your database.

Once you have set up your database connection, you will need to add your database in the Builder from the Application Options dialog on the SQL tab (see SQL tab). Apama will attempt to connect to your database. If Apama is unable to connect to your database, this means that either the driver is not set up correctly or that you do not have permission to access the database.

If the connection is successful, and the Get Tables and Columns from Database checkbox is selected in the Application Options dialog, Apama will use information from this database to populate drop down menus in the Attach to Data dialog with available tables and columns. If a Database Repository is found, information from your database will be merged with data from the repository file. If you deselect the Get Tables and Columns from Database checkbox Apama will no longer query your database for this information, but the Database Repository will still be used to populate drop down menus. Using a Database Repository to populate drop down menus makes it possible to specify which tables and columns from your database will be listed in the Attach to Data dialog and gives you the ability to build displays while databases are offline.

Apama includes a JDBC database driver for the following Apama-certified databases:

• Microsoft SQL Server
• Oracle

These database drivers eliminate the need to install database-vendor-supplied drivers. The JDBC drivers can be used with any Apama component.

For more information on the supplied database drivers, see the documentation available in the following location:

apama_install_dir\doc\db_drivers\jdbc

Direct JDBC connection

In order for Apama to communicate with your database using a straight JDBC connection, you must have a JDBC driver for your database.

Apama includes JDBC database drivers that eliminate the need to install database-vendor-supplied drivers. When you add a database to a dashboard you can specify the use of one of these Apama drivers. To add a database to a dashboard, see SQL tab, which provides information about the Add Database dialog. To use the Apama JDBC database driver for an added database, enter values for JDBC Options in the Add Database dialog. Also, be sure to add the jar file that contains the appropriate driver class to your dashboard properties.

To use the Apama JDBC driver, specify the following according to the type of SQL database you want to add. In the URL, replace HOSTNAME, PORT and DATABASENAME or DATABASESID with the actual values for the particular database you want to connect to.

• MSSQL (eysqlserver.jar is in the apama_install_dir\lib folder)

  URL: jdbc:sag:sqlserver://HOSTNAME::PORT;databaseName=DATABASENAME

  Class name: com.apama.jdbc.sqlserver.SQLServerDriver

• Oracle (eyoracle.jar in the apama_install_dir\lib folder)

  URL: jdbc:sag:oracle://HOSTNAME::PORT;SID=DATABASESID

  Class name: com.apama.jdbc.oracle.OracleDriver

JDBC drivers are available from most database vendors.

To make a non-Apama database driver available to Apama:

1. Locate the driver on your machine and add the jar that contains the driver class to your dashboard properties.
2. Add the path to the JDBC driver jar file to the APAMA_DASHBOARD_CLASSPATH environment variable or to run the dashboard processes with --dashboardExtraJars option. This is required for the data server, display server or dashboard builder to be able to find and load the JDBC driver class. You can add paths to multiple driver classes.
3. In the Add Database dialog, provide the database URL and the class name for your JDBC driver. The database URL typically contains the protocol and sub-protocol strings for your database as well as the path to the database and a list of properties. If you do not know the syntax for your database URL, consult the documentation for your JDBC driver.

Setting SQL data source options

The Builder, Viewer, data server, and display server executables support the following command-line option:

-q | --sql [retry:<ms> | fail:<n> | noinfo | nopererr | quote]

• retry: Specify the interval (in milliseconds) to retry connecting to a database after an attempt to connect fails. Default is -1, which disables this feature.
• fail: Specify the number of consecutive failed SQL queries after which to close this database connection and attempt to reconnect. Default is -1, which disables this feature.
• noinfo: Query database for available tables and columns in your database. If a Database Repository file is found, it is used to populate drop down menus in the Attach to SQL Data dialog.
• nopererr: SQL errors with the word permission in them will not be printed to the console. This is helpful if you have selected the Use Client Credentials option for a database. In this case, if your login does not allow access for some data in their display, you will not see any errors.
• quote: Encloses all table and column names specified in the Attach to SQL Datadialog in quotes when an SQL query is run. This is useful when attaching to databases that support quoted case-sensitive table and column names.
  Info

  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 -sqlquote option is specified.