SQL Data Source

The SQL data source provides access to JDBC enabled databases. The Attach to SQL Data dialog makes it easy to browse, select data tables, filter information and institute query policies with a simple user interface. For those familiar with SQL, it is also possible to enter SQL commands to make database queries.

This section includes:

§        “SQL System Requirements and Setup” on page 653

§        “Attach to SQL Data” on page 654

§        “Define SQL Command” on page 664

§        “Application Options - SQL” on page 668

§        “RTView Deployment - SQL” on page 672

§        “SQL Demos” on page 674

§        “Quick Start Tutorial - SQL” on page 676

§        “SQL Database Connection Setup” on page 680

§        “SQL Data Source - Command Line Options” on page 682

SQL System Requirements and Setup

System Requirements

In addition to basic “System Requirements”, the SQL data source requires a database with a JDBC driver. See “SQL Database Connection Setup” for information.

If you use the Rich Client Browser with Direct Data deployment, you will need to setup applet permissions on each client to allow access to your database. See “Rich Client Browser Deployment Setup for Direct Data Connection” for more information.

Setup

In addition to general environment variables (see “Setup”), you must include the following if you will be using a direct JDBC connection to communicate with your database(s):

Attach to SQL Data

Note: The SQL data source may not be licensed in your RTView installation.

The Attach to SQL Data dialog, which is used to connect an object to your database using an SQL query, can be accessed via the Object Properties window. Once an object has been attached to your database it receives continuous 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.

Right-click on the Property Name from the Object Properties window and select Attach to Data>SQL to display the Attach to SQL Data dialog. 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 check box to enter an advanced query.

Use the -sqlquote command line or applet parameter options 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. See “Command Line Options: Display Builder and Display Viewer” and “Rich Client Browser with Direct Data Connection - Manual Setup” for more information.

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

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 “Application Options - SQL” for information on how to create a Database Repository file. You can also create a file to exclude tables from the Table Name drop down menu. See “SQL Database Connection Setup” for details. Otherwise, drop down menus populate based on databases added from the Application Options dialog or those typed directly into the Database Name field. The Filter field is optional and uses standard SQL syntax.

Note: If you are using a direct JDBC connection, you will need to add your database in “Application Options - SQL”.

Specifying a Timeout or ID For a Query

When you select Enter SQL Query, you can optionally specify a timeout, an ID, or both for your query.

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

Specify Timeout For Query

To specify a timeout for the SQL query, prepend the following text to the query string:

rtvTimeout=NN;

    where NN specifies the timeout interval in seconds.

For example: rtvTimeout=20; select * from MyTable

A semicolon must immediately follow the timeout value. You can also specify a substitution string.

For example: rtvTimeout=$timeout; select * from MyTable

When a query with a timeout is invoked by the SQL data adapter, on each subsequent update cycle (2 seconds, by default) the data adapter checks if the query has completed. If the query has not completed it checks if the timeout has expired. If the timeout has expired, the query is canceled and an error message is printed to the RTView console. For example:

ERROR: SQL query timeout after 20 seconds; db=MyDatabase; query=<select * from MyTable>

The status of the query is then set to "timeout" and an empty table is applied to the objects that are attached to the query result. In addition, the query is not executed again for at least the length of the timeout interval. For example, if the query is configured to run every 60 seconds and the timeout is 120 seconds, then after a timeout the next query occurs after 120 seconds, not 60 seconds.

Specify ID For Query

To specify an ID for an SQL query, terminate the ID string with a semicolon and prepend the following text to the query string:

rtvID=S;

where S specifies the ID string.

For example: rtvID=MyTable Query 1; select * from MyTable

The ID string cannot contain a semicolon (;) or equals (=) character. You can also specify a substitution string.

For example: rtvID=$table Query; select * from $table

The ID string has no effect on the execution of the query. The ID string appears as the value of the Query ID column in the Keys table of the “RTViewDs” database. The ID string also appears in any error messages issued by the SQL data adapter that involve the query.

Specify Timeout and ID For Query

To specify both a timeout and a query ID for the query, use the syntax described above and a semicolon after each parameter.

For example: rtvTimeout=120;rtvID=MyTable Query 1; select * from MyTable

The timeout and ID parameters can be specified in either order.

SQL Scheduler

The SQL Scheduler functionality consists of a schedule table and a rule table that allow you to define at what time of day and with what frequency your user-defined SQL queries are run. The schedule table and rule table, which can be created in a database, an XML file, or any other table that can be retrieved using an RTView data source, must contain the following columns:

Rule Table

Note: You can add additional formats for specifying dates in your rules using the sl.rtview.scheduler.dateFormat property in your .properties file. For example:

sl.rtview.scheduler.dateFormat=MMM dd, yyyy

Entering -scheduler.dumpFormats:true on the command line will print the accepted formats for days of the week, specific dates, and time zones.

Example Rule Table

Explanation of Rule Examples

Schedule Table

Example of Schedule Table Using Rules Defined Above

Enabling Schedule and Rule Tables

1.     To make the Schedule table and Rule table available in RTView:

§        Create a global function named RtvScheduleTable using Function Type=Reference and attach the function’s Table argument to the XML file, SQL query, or whatever RTView data attachment that retrieves the selected table.

§        Create a global function named RtvScheduleRuleTable using Function Type=Reference and attach the function’s Table argument to the RTView data attachment that retrieves the rule table.

See “Global Functions and Variables” for more information.

2.     In the “Attach to SQL Data” dialog, enter your query, select Run Query On Schedule from the Update Mode drop down, and select the desired schedule from the Schedule Name drop down.

Instead of selecting a schedule name from the Schedule Name drop down, there are two other ways of selecting schedules/rules to use:

§        You can enter a substitution string (for example, $mySchedule, which could have a value of weekday30 9to5) in the Schedule Name drop down. See “Substitutions” for more information.

§        You can enter a list of rule names separated by commas (rule1,rule2,rule3) in the Schedule Name drop down. This option might be useful when there is no schedule defined with the desired set of rules.

Note: If schedules and rules are added or modified at runtime, then the next update of the data attachment used as the input to the RtvScheduleTable and RtvScheduleRuleTable will apply those changes to all SQL queries using those schedules and rules.

Example Configuration

An example configuration of the SQL Scheduler is available in the custom/sql_scheduler directory, which includes a schedule table, rule table, and scheduled queries. See the README.txt file in the custom/sql_scheduler directory for more information.

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 “Application Options - SQL” for information on how to create a Database Repository file.

Note: Filters and advanced SQL queries are not validated.

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

*If your database is validated as Unknown, see Application Options>“SQL Tab” for information on how to add a database.

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. For more information on creating displays using substitution values, see “Substitutions”.

RTViewDs

The RTViewDs database contains two tables that contain metrics about SQL database connections and queries. In the Attach to SQL Data dialog, select RTViewDs from the Database Name drop down menu and then select either Connections or Keys from the Table Name drop down menu.

Note: RTViewDs tables do not support SQL queries, row filters, custom query intervals or maximum row settings.

The selected Update Mode affects the Keys table as follows:

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 bring up the Select Columns dialog, click on the ellipses button  in the Column(s) field (or right-click in the Column(s) field and click on Select Column(s). The dialog should contain a list of Available Columns that you can add to your table.

To add a column, select an item from the Available Columns list and click on the Add button. If the item you require is not listed, type your selection into the Enter Column Name field. Click the Remove button to delete an item previously added to the Selected Columns list. You can 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.

Note: Invalid columns will not update.

The following describes the Attach to SQL Data dialog commands:

Define SQL Command

Note: The SQL data source may not be licensed in your RTView installation.

The Define SQL Command dialog, which is used to assign SQL commands that allow you to issue commands from within an RTView display, can be accessed via the Object Properties window. If you execute a SQL command from a Thin Client with Direct Data Connection or any Served Data deployment, the command will execute on the server.   

Right-click on the appropriate command property in the Object Properties window and select Define Command>SQL to display the Define SQL Command dialog. The Define SQL Command dialog provides a drop down menu with available databases and a field to enter a SQL statement. See the “Define/Execute Command” section for information on how to execute a command.

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.

Note: If you are using a direct JDBC connection, you need to add your database in “Application Options - SQL”.

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 “Application Options - SQL” for information on how to create a Database Repository file.

Note: The SQL Command field is not validated.

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

*If your database is validated as Unknown, see “Application Options - SQL” for information on how to add a database.

Note: If you are using a direct JDBC connection, you will need to add your database in Application Options.

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 execute commands on a number of different databases. For more information on creating displays using substitution values, see “Substitutions”.

Special Values

The following describes Define SQL Command dialog commands:

Application Options - SQL

Select Tools>Options in the Display Builder to access the Application Options dialog.

Options specified in the SQL tab can be saved in an initialization file (OPTIONS.ini). On startup, the initialization file is read by the Display Builder, Display Viewer, Display Server, Data Server, and Historian 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 RTView will search under lib in your installation directory. See “RTV_JAVAOPTS” for more information.

Note: Options specified using command line arguments will override values set in initialization files. See “SQL Data Source - Command Line Options” for more information.

SQL Tab

This tab allows you to add or remove your databases and set the default database. In order for RTView to communicate with your databases, you must set up a direct JDBC connection. See “SQL Database Connection Setup” for more information.

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 RTView was unable to make a connection to your database.

Note: Databases that have been setup to Use Client Credentials will not connect unless you are logged in and you have objects in your display that are using that connection.

Check your database connection and see “SQL Database Connection Setup” for information on how to set up your driver correctly.

If the connection is successful, and the Get Tables and Columns from Database check box is selected, RTView will use information from this database to populate drop down menus in the “Attach to SQL 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 check box, RTView 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 RTView to reconnect with your database the next time you run the Display Builder or the Display Viewer.

Note: Regardless of which tab you are currently working from in the Application Options dialog, each time you click OK, Apply, or Save, RTView will attempt to connect to all unconnected databases except those that have Use Client Credentials checked.

Field Name	Description
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.
	Database Name -- Name to use when referencing this database connection in your data attachments.
	User Name -- 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. If you need to provide an encrypted password (rather than expose server password names in a clear text file, use the encode_string command line option with the following syntax: encode_string type mypassword where type is the key for the data source and mypassword is your plain text password. Note: The type argument is only required when you encrypt a string for a data source.
	For example, enter the following in an initialized command window (see “Initializing a Command Prompt or Terminal Window”): encode_string sql mypassword and you will receive an encrypted password: encrypted value: 013430135501346013310134901353013450134801334 Copy the encrypted value, paste it into the password field and click Save to save this value to the initialization (*.ini) file. Or, if necessary, manually edit the (*.ini) file to include the encrypted value. Note: If you need to manually edit a configuration (*.ini) file, contact SL Technical Support at support@sl.com for information about supported syntax.
	Use Client Credentials -- If selected, the User Name and Password from the RTView 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. See “Login” for more information. 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, e.g., TABLE, VIEW.
	Run Queries Concurrently -- If selected, each query on the connection is run on its own execution thread. The default is disabled. Note: 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 Options  JDBC Driver Class Name -- 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 -- 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. Note: If a value of __none (two underscores before the n) is entered for the JDBC Driver Class Name or JDBC Database URL, then the SQL data source will not attempt to connect to the database and ignore any SQL data attachments that specify that database. The RTViewDs.Connections table will contain a row for the database, but the Connected and Enabled columns will always show a value of false. If the -sqltrace command line option is used, then you'll see the following message appear once in the console: <dbname>: ignoring connection with driver/url=__none
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. Note: 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.

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(s) from which the file will retain information.

Note: If RTView does not make a connection with your database, then information from that database cannot be saved to the database repository file.

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.

Note: The saved file will be named sqlrepository.xml. If the name of the database repository file is changed, RTView will not be able to locate the file. As a result, drop down menus will populate based on databases added from the Application Options dialog or those typed directly into 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 RTView. 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 RTView from that particular directory. See “RTV_JAVAOPTS” for more information.

See “SQL Database Connection Setup” for details on editing an existing database repository file.

Excluding Tables From The Attach To SQL Data Dialog

To exclude tables from the Attach to SQL Data dialog, see “SQL Database Connection Setup” for details.

RTView Deployment - SQL

This section contains details about the deployment process that are specific to your data source. Please go to the Deployment section of this documentation for instructions on how to implement your RTView deployment option. Return to this page whenever you are instructed to refer to deployment information that is specific to your data source.

System Requirements and Setup

The SQL data source has additional System Requirements and Setup. See “SQL System Requirements and Setup” for more information.

Data Source Configuration File

RTView saves general application settings as well as data source configuration options in initialization files that are read at startup. If no directory has been specified for your initialization files and files are not found in the directory where you started the application, then RTView will search under lib in your installation directory. See “Application Options”, “Application Options - SQL”, and “RTV_JAVAOPTS” for more information.

Include the following initialization file when you deploy RTView with this data source:

Note: Options specified using command line and applet parameters override values set in initialization files.

Rich Client Browser Deployment Setup for Direct Data Connection

Note: If you are using history data you must include the SQL data source in your applet as detailed below.

A. Required Applet Parameters

In addition to basic applet parameters, you must specify the following in your HTML file to host the Display Viewer Applet. See “Rich Client Browser with Direct Data Connection - Manual Setup” for more information.

Include the SQL data source in your applet:

1.     Add the following to your ARCHIVE parameter (found under your installation directory in lib):

gmsjsqlds.jar 

2.     Add the following applet parameter:

name = ds 

value = com.sl.gmsjsqlds.GmsRtViewSqlDs 

Note: To specify multiple data sources for the value of the ds parameter, separate them with a ;.

3.     If your display contains SQL data attachments which use a JDBC driver, include the jar containing your JDBC driver class.

4.     If you are using a JDBC driver to connect to a database for any data attachments in your display, include the jar for your JDBC driver in the ARCHIVE parameter.

B. Optional Applet Parameters

The following options are read from OPTIONS.ini if not included in the applet parameters of your HTML file.

C. Setup Client

1.     If you will be viewing displays that include SQL data attachments or history data, modify your Java security settings to include the following permission:

permission java.util.PropertyPermission "file.encoding", "read";

2.     If you will be accessing a database on another system, modify your Java security settings to include the following permission:

permission java.net.SocketPermission "host", "accept, connect, listen, resolve"; 

Where host is the system where the database is running.

3.     If you are using a JDBC driver to connect to your database, include the jar for your driver in the ARCHIVE parameter. Depending on your driver, you may also need to add an accessClassInPackage RuntimePermission for your driver package.

D. Troubleshooting

You may encounter the following error(s):

Problem 

Console Error:

load: class com.sl.gmsjsqlds.GmsRtViewSqlDs.class not found

Solution 

The gmsjsqlds.jar is not specified in the ARCHIVE parameter or it is not in the directory containing the HTML file. If you specified a relative path for this jar in the ARCHIVE parameter, it was not found.

SQL Demos

Except where noted, all demos can be run in three ways, as an application, or via rich or thin client in a browser.

Before You Begin

Start the Demo Server 

Rich Client and Thin Client Demos only. 

There are rich and thin client demos already installed on the “RTView Demo Server”.

In an initialized command window (see “Initializing a Command Prompt or Terminal Window”):

Type run_startup_demoserver 

Data Source Demo

The Data Source Demo is designed to illustrate each data source.

1.     Setup Sample SQL Database. See “SQL Database Connection Setup” for more information.

2.     Run Demos - Application, Rich Client Browser, or Thin Client Browser

Application Demo 

1. In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/dstutorial directory.

2. To view the demo, type:

run_viewer 

3. To edit the demo, type:

run_builder 

Rich Client Browser Demo

“Start the Demo Server” if it is not running.

1. In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/dstutorial directory.

2. Start the Data Server by typing:

run_dataserver -socket 

3. In the Data Server dialog, select:

Start Serving Data

4. Open a browser and navigate to http://localhost:8068/dstutorial_applet.

Thin Client Browser Demo 

“Start the Demo Server” if it is not running.

1. In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/dstutorial directory.

2. Start the Display Server by typing:

run_displayserver

3. Open a browser and navigate to http://localhost:8068/dstutorial.

ElectroSphere Demo

The ElecroSphere demo, a Business Information Application, allows you to navigate through multiple types of business data.

Note: This demo requires Windows and Microsoft Access.

1.     Run Demos - Application, Rich Client Browser or Thin Client Browser

Application Demo

On Windows:

In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/esphere directory.

1. To view the demo, type:

run_viewer

2. To edit the demo, type:

run_builder

Rich Client Browser Demo

“Start the Demo Server” if it is not running.

On Windows:

In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/esphere directory.

1. Start the Data Server by typing:

run_dataserver -socket

2. In the Data Server dialog, select:

Start Serving Data

3. Open a browser and navigate to http://localhost:8068/esphere_applet.

Thin Client Browser Demo

“Start the Demo Server” if it is not running.

On Windows: 

In an initialized command window (see “Initializing a Command Prompt or Terminal Window”), go to the demos/esphere directory.

1. Start the Display Server by typing:

run_displayserver

2. To view the demo open a browser and navigate to http://localhost:8068/esphere.

Quick Start Tutorial - SQL

This Quick Start Tutorial provides you with the fundamentals on how to use RTView with the SQL data source. Once completed, you can swiftly apply this knowledge to building your own dashboard displays for visual access to your SQL data.

Learn to: 

§        Animate graphic objects with SQL data

§        Create a drill down display with SQL data

Get Started

This tutorial requires the following:

§        Microsoft Access 97+.

§        Setup the sample database - If you have not, you must setup the sample database used in this tutorial. See and “SQL Database Connection Setup” for information.

§        Register for a license key. If you have not, you must do so before continuing. See “Registration” for more information.

§        “Quick Start Tutorial” - This tutorial requires that you have a working knowledge of RTView. We recommend that you complete the Quick Start Tutorial before continuing.

Start the Display Builder

If you are already logged onto the Display Builder, skip this section and go to “Create a Display”.

1.     In an initialized terminal window (see “Initializing a Command Prompt or Terminal Window”), type:

run_builder

2.     Login to the Display Builder. By default, the Display Builder does not require a login. “Login” can be enabled at setup to support “Role-based Security”. The default user name and password are:

User Name: admin

Password: admin 

Note: It is possible that your system administrator may have configured another user name and password. In this case, you may also need to select a role. See “Role-based Security” for more information.

You are now ready to create a display using the SQL data source.

Create a Display

At this point you have:

§        Registered for a license key. See “Registration” for more information.

§        Logged on to the Display Builder

§        Setup the SQL sample database.

§        Completed the “Quick Start Tutorial”.

In this tutorial you use the sample SQL database as a data source to create a Production Table that displays production numbers per plant, as seen below.

As you saw in the “Quick Start Tutorial”, the data structure of tables and graphs (tabular data) enables RTView to automatically create several data source specific, built-in Substitutions for you. You will see these built-in Substitutions used in the target display when you create the drill down. For more information on Substitutions, see “Substitutions”.

In this exercise, you create a drill down using the previously created display, sql_dd_qs.rtv, as the target display. First you will set the Production Table to display production status per plant. Then you will create a drill down that opens a bar graph that shows more detailed data for each plant.

Add the SQL Database To List of Available Databases

Adding the sample SQL database to the Database List makes it available for animating graphic objects in your display.

1.     In the Display Builder, select Tools>Options to open the Application Options dialog.

2.     Select the SQL tab and click on Add Database to open the Add Database dialog.

3.     In the Add Database dialog:

Database Name - Enter SampleDB 

4.     Click OK to close Add Database dialog.

5.     Click OK to apply and close the Application Options dialog.

The sample SQL database is now available for animating graphic objects in your display.

Display Data in a Table

In this exercise you add a table and then display data in the table by attaching it to the data source.

1.     Click on the Add Table button  and click again in the Working Area to place the table.

2.     In the Object Properties dialog:

autoResizeFlag  (category: Column) - Click to select the check box.

label (category: Label) - Change the name of the label to Production Table. Press <Enter>.

valueTable (category: Data) - Right-click in the Property Name field and select Attach to Data>SQL.

3.     In the “Attach to SQL Data” dialog:

Database Name - SampleDB should already be selected.

Table Name - Select production_table.

Column(s) - Select the  button to open the Select Columns dialog.

4.     In the Select Columns dialog:

Select Plant in the Available Columns list and click Add.

Select Status in the Available Columns list and click Add.

Click OK to close the Select Columns dialog.

5.     In the “Attach to SQL Data” dialog leave the Filter field blank.

6.     Click OK to apply these values and close the Attach to SQL Data dialog.

The table populates with values from production_table in the sample SQL database.

7.     In the Display Builder, select File>Save.

You are now ready to create the drill down.

Create a Drill Down Target in the Table

In this exercise, you create the drill down using the previously created display, sql_dd_qs.rtv, as the target.   

1.     In the Object Properties dialog:

drillDownTarget (category: Interaction) - Double-click in the Property Name field to bring up the Drill Down Properties dialog.

2.     In the “Drill Down Properties” dialog:

Apply Drill Down To - Select Named Window from the drop down menu. This option lets you re-use the window when you drill down multiple times.

Window Name - Enter sql. This name should be unique unless the display is to open in an existing window.   

Drill Down Display Name - Select dstutorial\sql_dd_qs.rtv from the drop down menu.

3.     Click OK to set the drill down target and close the Drill Down Properties dialog.

View the Drill Down Display

 In this exercise, you drill down to the target display.

1.     Double-click on any cell in the table to drill down to detailed data. The target display opens.

2.     Double-click on another row in the table and the same display is used to show different data based on the row you select.

3.     Close the drill down window.

4.     In the Display Builder select File>Save.

Go to the “Quick Start Tutorial”

SQL Database Connection Setup

RTView communicates with your database using either a direct JDBC connection. Connections require some setup before RTView can communicate with your database.

Once you have setup your database connection, you will need to add your database in the Display Builder from the Application Options dialog on the “SQL Tab”. RTView will attempt to connect to your database. If RTView is unable to connect to your database, this means that either the driver is not setup correctly or that you do not have permission to access the database.

Note: Databases that have been setup to Use Client Credentials will not connect unless you are logged in and you have objects in your display that are using that connection. See Application Options>“SQL Tab”.

If the connection is successful, and the Get Tables and Columns from Database check box is selected in the Application Options dialog, RTView will use information from this database to populate drop down menus in the “Attach to SQL 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 check box, RTView 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.

Direct JDBC Connection

In order for RTView to communicate with your database using a straight JDBC connection, you must have a JDBC driver for your database. JDBC drivers are available from most database vendors. To make your database driver available to RTView, locate the driver on your machine and define an environment variable named RTV_USERPATH so that it includes the path to the driver class or the jar that contains the driver class. The RTV_USERPATH variable is included in the classpath for RTView. It may contain paths to multiple driver classes. You must know the fully qualified class name for the driver class and the database URL required to connect to your database when you add the database in the Application Options dialog. 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.

Database Repository File

A Database Repository file may be used to populate the initial values of drop down menus in the “Attach to SQL Data” dialog. See “Application Options - SQL” for information on how to create a Database Repository file.

It is possible to edit an existing Database Repository file, however the file name sqlrepository.xml cannot be modified. If sqlrepository.xml is not found in the specified directory or your current working directory, RTView will look in the lib directory. If the Database Repository file is not found, drop down menus will remain empty until databases are added from the Application Options dialog or typed directly into the Attach to SQL Data dialog. See “RTV_JAVAOPTS” for more information.

To edit an existing Database Repository file, supported tags and attributes are as follows:

An example Database Repository file:

<?xml version="1.0"?>

<sqlrepository xmlns="www.sl.com" version="3.0">

       <db name="SampleDB">

               <table name="production_table">

                       <col>Plant</col>

                       <col>Units in Production</col>

                       <col>Units Completed</col>

                       <col>Status</col>

                       <col>On Schedule</col>

               </table>

               <table name="system_table">

                       <col>System</col>

                       <col>Status</col>

                       <col>%Free Space</col>

                       <col>CPU Usage</col>

                      <col>On Site</col>

               </table>

               <table name="trade_table">

                       <col>Customer</col>

                       <col>Symbol</col>

                       <col>Shares</col>

                       <col>Purchase Price</col>

                       <col>Current</col>

                       <col>High</col>

                       <col>Low</col>

               </table>

       </db>

</sqlrepository>

Excluding Tables From The Attach To SQL Data Dialog

To exclude tables from the Attach to SQL Data dialog, copy the sqlrepository.xml file to a new sqlexcludedtables.xml file and remove the table references that you want to include in the Attach to SQL Data dialog drop down menus. See “Database Repository File” for more information. For example:

<?xml version="1.0"?>

<sqlrepository xmlns="www.sl.com" version="3.0">

       <db name="SampleDB">

               <table name="production_table">

                       <col>Plant</col>

                       <col>Units in Production</col>

                       <col>Units Completed</col>

                       <col>Status</col>

                       <col>On Schedule</col>

               </table>

       </db>

</sqlrepository>

Save the sqlexcludedtables.xml file to your RTV_HOME\lib directory. Table information stored in sqlexcludedtables.xml will now be excluded from the initial values of Table Name drop down menus in the Attach to SQL Data dialog.

Note: It is not necessary to create a Database Repository file in order to use sqlexcludedtables.xml. RTView will still use the sqlexcludedtables.xml file to exclude tables from the Attach to SQL Data dialog. If you have an sqlexcludedtables.xml file and you click the Save Database Repository button, the new sqlrepository.xml file will not contain any of the tables listed in your sqlexcludedtables.xml file.

To create your own sqlexcludedtables.xml file without creating a Database Repository File, supported tags and attributes are as follows:

Note: The file name sqlexcludedtables.xml cannot be modified.

SQL Data Source - Command Line Options

In addition to General Options, the following command line arguments are enabled with the SQL data source when you run RTView applications from a Windows Command Prompt or UNIX terminal window. See “Command Line Options: Display Builder and Display Viewer” for more information.

Note: If a command line argument contains a space or a semicolon, then the entire argument must be enclosed in quotes (e.g.: "-sub:$data:my Data").