Data Preparation    Modules    Interfaces    Tutorials    Tips and Tricks    Glossary   
FRAMES    NO FRAMES   

Accessing Relational Databases

Content

The JDBC data access interface
Supported database management systems (DBMS)
Adding a new supported DBMS
Testing your JDBC connection

The JDBC data access interface

The JDBC application programming interface is the industry standard for database-independent connectivity between Java programs and a wide range of databases. Synop Analyzer uses this standard for reading data directly from database tables. Each database management system (DBMS) requires a specific JDBC driver in the form of a Java library (jar-file) for providing JDBC connectivity.

For a couple of widely used DBMS, a suitable JDBC driver comes with the Synop Analyzer install package. These java libraries are not part of Synop Analyzer and not covered by your Synop Analyzer license and support agreement. They are free software which has been placed into the public domain by their authors under the GNU Lesser Public License (GLPL).

The license conditions of other widely used DBMS do only permit the distribution of JDBC drivers together with a license of the underlying DBMS. For these databases, Synop Analyzer does not install the JDBC driver but relies on a preexisting JDBC driver installation on the database server. Nontheless, Synop Analyzer is preconfigured for using these JDBC drivers. Both groups of 'known' DBMS are described in section Supported DBMS

If you are working with a DBMS which is not part of the list of 'known' DBMS, you can manually configure Synop Analyzer for reading data from this new DBMS by editing the preferences file IA_preferences.xml. A step-by-step instruction for declaring a new DBMS can be found in section Adding a new supported DBMS.

If you want to test whether a given table in a given database of a given DBMS can be accessed from Synop Analyzer using given user and password credentials, you can use a separate JDBC connection tester program called JDBCTest.bat which comes with Synop Analyzer. The usage of this program is described in section Testing your JDBC connection.

Supported database management systems (DBMS)

Adding JDBC connectivity for a new DBMS

If your database management system (DBMS) provides a JDBC interface and driver library but does not figure in the list of 'known' DBMS, you can manually add your DBMS' JDBC driver to the list of supported JDBC connections.

For declaring a new DBMS-JDBC driver combination, you need to have the following information at hand:

  1. The name under which the new data source will appear in the list of all available data sources
  2. The name of the Java class which implements the JDBC driver (such as oracle.jdbc.driver.OracleDriver for Oracle)
  3. The first part of the JDBC connection string which precedes the host name (such as jdbc:oracle:thin: for Oracle)
  4. The hostname prefix within the JDBC connection string. This is // for most JDBC drivers and @ for Oracle.
  5. Each JDBC driver has a different default port number via which it communicates with the database. If your database user must use another port, you must know that port number.
  6. The middle part of the JDBC connection string which follows the host name or port number and precedes the database name (for example : for Oracle, / for many other JDBC drivers or ;databaseName= for Progress/Openedge)
  7. The SQL statement for detecting the column names and types in a given table. The statement must return the column names in the first column and the table types in the second column of its result set.
    Examples: SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLS WHERE TABLE_NAME='<tablename>' AND OWNER='<schema>' for Oracle or SHOW COLUMNS FROM <schema>.<tablename> for MySQL.
  8. The SQL statement for detecting the occupied disk space of table in bytes.
    Examples: SELECT AVG_ROW_LEN*NUM_ROWS "data_length" FROM DBA_TABLES WHERE TABLE_NAME='<tablename>' AND OWNER='<schema>' for Oracle or SHOW TABLE STATUS LIKE '<schema>.<tablename>' for MySQL.
  9. The column name containing the table size information within the result set of the above statement for detecting the table size. Example: data_length.

Once you have collected this information from the JDBC driver documentation of your JDBC driver, you can test the correctness of the settings by running a little test program called JDBCTest.bat which can be found in the subdirectory JDBCTest of the Synop Analyzer installation directory. The usage of this file and its accompanying parameter file JDBCTest_params.txt is described in more detail in section Testing your JDBC connection.

When you are sure your settings describe a working JDBC connection to a DMBS which does not figure on Synop Analyzer's list of automatically supported DBMS, you can declare this user-defined JDBC data source to Synop Analyzer. Open the preferences file IA_preferences.xml in an arbitrary XML text editor (for example in the freely available general purpose editor Notepad++) and search the following setting:

  <Setting name="userDefinedDBMSName"

This setting is the first out of a series of 9 settings which all start with the prefix "userDefined, the last setting within the series is

  <Setting name="userDefinedTableSizeColumnName".

Copy the 9 pieces of information which you have collected in the list shown above between the double quotes following the 9 value= attributes of the 9 settings parameters, then save the modified file IA_preferences.xml. When you start Synop Analyzer the next time, you will find your newly defined JDBC data access in the pop-up list of available DBMS in the 'database connect' panel.

Testing your JDBC connection

If you encounter problems when accessing data residing in a DBMS (database management system), or if you want to define and test a new JDBC data source, you can test your JDBC connection using the test program JDBCTest.bat which resides in subdirectory JDBCTest of the Synop Analyzer installation directory.

The test program JDBCTest.bat is an executable MS-Windows batch file which can be started by double-clicking on it. The main program comes with a couple of auxiliary and source code files. Legally, the entire JDBCTest package is not a part of Synop Analyzer but has been placed into the public domain under the BSD License, which means that you can do almost anything with it, use it, modify the source code and distribute it - as far as you maintain the original copyright and warranty disclaimer note in the source code and as far as you do not sue the author for any damages which result from using it.

If you want to test whether your database management system and your JDBC driver is suitable for working with Synop Analyzer, do the following:

  1. copy all java libararies of your JDBC driver (for example ojdbc6.jar for Oracle or db2jcc4.jar for DB2) into the Synop Analyzer installation directory
  2. Open the batch file JDBCTest/JDBCTest.bat in a text editor, for example Notepad or Notepad++ and make sure all libraries of your JDBC driver appear after the -cp option. Use ; as separator character and don't forget the relative directory path prefix. For example, for adding the Oracle JDBC library you could write -cp ../ojdbc6.jar.
  3. Edit the parameter file JDBCTest_params.txt. Note that all lines starting with '#' are comment lines which will be ignored by the program JDBCTest.bat. If you are working with one of the DBMS for which JDBCTest_params.bat already contains some (commented-out) settings, activate these settings by removing the '#' and edit them so that your host name, port number, user name, database name and password are correctly inserted. If your DBMS does not occur in jdbctest_params.txt, create a new section with settings for your DBMS.
  4. Start JDBCTest.bat and look at the diagnostic output; refine your parameter settings until the test protocol tells you that your configuration is suitable for Synop Analyzer.

If you found the test package JDBCTest helpful and if you have added a new DBMS toJDBCTest_params.txt or if you have performed a bugfix or an improvement, your feedback to the Synop Analyzer team is appreciated (make sure you anonymize your IP addresses, user names and passwords when you paste in snippets from your parameter file!):

: name
: email
comments