Before you configure an SQL data source, configure any JDBC driver you may require. For more information about completing this task, refer to the section JDBC Drivers.
In the Administration application, you can configure an SQL data source by opening the Relational folder and selecting the SQL node. The Administration application displays the following fields:
|
Property |
Description |
|
Logical Name |
Specifies the name for the data source. This name need not be the actual name of the relational database you are connecting to. |
|
Driver |
Specifies the type of driver to display the URL syntax and example URL information for. NOTE: If you want to configure the SQL data source to use Windows Authentication rather than SQL Server Authentication, you must choose the jTDS driver. |
|
URL Syntax |
Displays the syntax used for the driver's URL. This is specified in the JDBC Configuration utility for each driver. The syntax is intended to help you enter the database URL on this window. |
|
Example URL |
Displays an example of the driver's database URL. This is specified in the JDBC Configuration utility for each driver. The example is intended to help you enter the Database URL on this window. |
|
User Name |
Specifies the login name to use when connecting to the relational database. |
|
Password |
Specifies the password to use when connecting to the database. |
|
Database URL |
Specifies he URL of the database. Use the the text in the URL Syntax and Example URL fields to help you enter the exact URL you require. For example, if you are configuring an Oracle 9i database, the URL syntax is jdbc:oracle:thin:@servername:portnumber:servername. Copy this line from the URL syntax field and replace the text after the at-sign (@) with the database's server name and port number, followed by server name again. |
|
Fetch row count at run-time |
Select to display of the count of rows returned by each query to the data source. |
|
Max Rows for Stored Procedures |
Specifies the maximum number of rows to return for stored procedures. The default value -1 means 1E6 (one million rows). You may need to change this number for some drivers to function correctly. |
In the Proficy Portal client application, click the Administration button on the main toolbar. The Administration application appears.
Click the Connectors & Data Sources button. A directory tree appears.
Expand the Relational folder and click the SQL node in this folder. The Add New SQL Data Source window appears.
In the Logical Name field, enter the name of the data source.
From the Driver list, select the driver name. The URL Syntax and Example URL fields are filled in automatically (from the JDBC Configuration Utility).
In the User Name field, enter the user name you will connect to the database with.
In the Password field, enter the password you will connect to the database with.
In the Database URL field, enter the URL of the database you are connecting to. You can use the URL Syntax and Example URL fields to help you determine the URL.
If you want the total number of rows that are retrieved to be displayed at run-time, select the Fetch row count at run-time check box.
NOTE: Refer to your driver's documentation to ensure that `fetch row count' is supported.
If required, in the Max Rows for Stored Procedures box, enter the maximum number of rows to return during run-time. The default is -1 (return all rows).
Click the Apply button to save your changes. Test, Copy, and Remove buttons appear.
Click the Test button to see if your connection is functioning properly. You will receive a message that the data source test passed or that it failed. If the test passed, you are ready to retrieve data. If the test failed, refer to Verifying Relational Database Connections.
