Load JDBC Driver and Get Connected

• Introduction 
• What is a JDBC Driver?
• Get the JDBC driver file(s)
• Connection Wizard
• Driver Manager
  • JDBC Driver Finder
  • Loading and Configuring Drivers Manually
    • Setup a JDBC driver
    • JDBC drivers that requires several JAR or ZIP files
    • The JDBC-ODBC bridge
  • Loading JNDI Initial Contexts
  • Errors (why are some paths red?)
  • Several versions of the same driver
• Setup a database connection
  • Setup using JDBC driver
  • Setup using JNDI lookup
  • Connection Properties
    • Database Profile
    • Driver Properties
      • Driver Properties for JDBC Driver
      • Driver Properties for JNDI Lookup
  • Always ask for userid and/or password
  • Using variables in the Connection details
• Connect to the Database
• Connections Overview

Introduction 

This document describes the way JDBC drivers are managed in DbVisualizer and all aspects about getting connected to your database(s).

If you are impatient, please go ahead and read the Connection Wizard section. It is the recommended way to create database connections in DbVisualizer.

What is a JDBC Driver?

DbVisualizer is, as you know, a generic tool for administration and exploration of databases. DbVisualizer is in fact quite simple, since it does not deal directly with how to communicate with each database type. The hard job is done by a JDBC driver, which is a set of Java classes that are either organized in a directory structure or collected into a JAR or ZIP file. The magic of these JDBC drivers is that they all match the JDBC specification and the standardized Java interfaces. This is what DbVisualizer relies on. A JDBC driver implements all details for how to communicate with a specific database and database version, and there are a range of drivers from the database vendors themselves and 3:rd party authors. To establish a connection with a database, DbVisualizer loads the driver and then get connected to the database through the driver.

It is also possible to obtain a database connection using the Java Naming and Directory Interface (JNDI). This technique is widely used in enterprise infrastructures, such as application server systems. It does not replace JDBC drivers but rather adds an alternative way to get a handle to an already established database connection. To enable database "lookup's" using JNDI, an Initial Context implementation must be loaded into the Driver Manager. This context is then used to lookup a database connection. The following sections describe the steps for getting connected using a JDBC Driver, and also how to use JNDI to obtain a database connection.

A complete JDBC driver typically consists of a number of Java classes, located in a JAR, ZIP or a folder, that need to be loaded into the DbVisualizer driver manager. DbVisualizer automatically recognizes the classes that are used to initiate the connection with the database and presents them in the Driver Class list. You must select the correct class in this list to make sure DbVisualizer successfully can initiate the connection. Consult the driver documentation for information of which class to select, or if the number of classes found are low, figure it out by trying each of them. More about this in the following sections.

Get the JDBC driver file(s)

DbVisualizer comes bundled with all commonly used JDBC drivers that have a license that allows for distribution with a third party product. Currently, drivers for DB2, JavaDB/Derby, Mimer, MySQL, and PostgreSQL, as well the jTDS driver for SQL Server and Sybase, are included with DbVisualizer.  If you only need to connect to databases of these types, you can skip the rest of this section and jump straight to the Connection Wizard section, because by default, DbVisualizer configures all these drivers automatically the first time you start DbVisualizer.

If you need to connect to a database that is not supported by a bundled JDBC driver, you must get a JDBC driver that works with your database type and version. The following online web page contains an up-to-date listing of the database/driver combinations we have tested:

http://www.dbvis.com/products/dbvis/doc/supports.jsp

Information about almost all drivers that are available is maintained by Sun Microsystems on this page:

http://industry.java.sun.com/products/jdbc/drivers

Download the driver to an appropriate directory. Make sure to read the installation instructions provided with the driver. Some drivers are delivered in ZIP or JAR format but need to be unpacked to make the driver files visible to the Driver Manager. The Databases and JDBC Drivers web page describes where you can download each driver and also what additional steps may be needed to install and load the driver in DbVisualizer.

(Drivers are categorized into 4 types. We're not going to explain the differences here, just give you the hint that the "type 4," aka "thin," drivers are the easiest to maintain, since they are pure Java drivers and do not depend on any external DLL's or dynamic libraries. Even though DbVisualizer works with any type of driver, we recommend that you get a type 4 driver if there is one for your database).

When you have downloaded the JDBC driver into a local folder (and unpacked it, if needed), you can go ahead and create a connect with the Connection Wizard, as described in the next section. You will then asked to load the driver files when the wizard needs them. Alternatively, you can move (or copy) the JDBC driver files to the DBVIS_HOME/jdbc folder, where they will be picked up and loaded automatically by the JDBC Driver Finder the next time you start DbVisualizer. You can read more about this option in the JDBC Driver Finder section.

Connection Wizard

The Connection Wizard greatly simplifies the steps needed to load the JDBC driver and create a new database connection. You just enter information about the driver file(s) and the connection data on a few wizard pages, and the wizard handles all the details. Once the new database connection has been created, it appear in the database objects tree.

The wizard cannot be used to define database connections via JNDI data sources.

The first wizard screen look like this.

Figure: Connection Wizard - Page 1

In the connection alias field, enter the name of the new database connection. This is the name that will be used for the connection in DbVisualizer, e.g., in the object tree.

Press Next to go to the next page.

On this page, select the driver you are going to use from the list. A red icon in front of the driver name indicates that the driver is not yet ready to use, while a green icon indicates that it has been properly configured (simply press Next to continue).

If the driver you select is not yet configured, a Load Driver File(s) button is displayed. When you click the Load Driver File(s) button, a file chooser is opened. You should select the JAR or ZIP file(s) that contain the driver implementation.

Figure: Connection Wizard - Page 2

In the file chooser, locate the files containing the JDBC driver files. (Select multiple files by pressing the SHIFT key while clicking).

Figure: Connection Wizard - Page 3

Once the driver has been properly loaded, a green icon appears in front of the driver name. Press Next to continue to the last page.

Figure: Connection Wizard - Page 4

On the last wizard pane, enter details for the new database connection. The information that must be provided varies depending on the database type. Please consult the database documentation if you are unsure about how to find the requested information.

Figure: Connection Wizard - Page 5

Press Ping Server to verify that a network connection can be established to the specified host and port. If the test passes, press Finish to create the new database connection and connect to the database.

Some databases support different types of URLs. For instance, Oracle supports URLs that contain the host, port and service name or SID, but also a TNS URL type where you just include a TNS alias and get all the details from a tnsnames.ora file. For cases like this, the last wizard page has a URL Type list at the top where you select the type of URL to use.

Figure: Connection Wizard - Page 5 for a database with multiple URL types

The other fields then depend on the selected URL type. For the Oracle TNS format, for example, you can pick the TNS alias from a list. If you like to use TNS URLs with an Oracle database, the tnsnames.ora file must be located either in the ORACLE_HOME/network/admin directory or in a directory identified by the TNS_ADMIN environment variable. For more about TNS, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net Services Reference.

We recommend that you skip the rest of this document, unless you:

• want to learn how the driver manager in DbVisualizer works
• need to have several versions of the same JDBC driver loaded simultaneously
• need to establish a connection via the JNDI interfaces (Java Naming and Directory Interface)
• need to add a Driver that do not exist in the wizard list of drivers

Driver Manager

The Driver Manager in DbVisualizer is used to define the drivers that will be used to communicate with the databases.  You can manually locate the JDBC driver files and configure the driver, or you can use the JDBC Driver Finder to do most of the work for you, either on demand or automatically.

JDBC Driver Finder

The JDBC Driver Finder is a very powerful part of the Driver Manager that automates most of the driver management work. Given the folders where JDBC drivers are located, it loads and configures new drivers (if any) every time you start DbVisualizer. You can configure the JDBC Driver Finder in Tools Properties, in the General->Driver Manager category.

Figure: JDBC Driver Finder properties

Use the following properties to specify the finder behavior:


You can also specify the folders the JDBC Driver Finder will search. By default, it will search folders named jdbc in the DbVisualizer installation directory (${dbvis.home}) and the DbVisualizer preferences folder (${dbvis.prefs.home}). These folder paths are shown under the list of Driver Finder Paths.

Finally, you can specify regular expression patterns for filenames that the finder should ignore. This can be useful if you need to store other files besides driver files in the designated folders.

If you let the JDBC Driver Finder load all drivers for you, all you need to do to install a new driver is to put the driver files in one of the folders specified for the finder in Tool Properties and then restart DbVisualizer.

Loading and Configuring Drivers Manually

You can also load and configure JDBC drivers manually using the Driver Manager. If you use JNDI to provide access to the database, you must use this option, since the JDBC Driver Finder does not handle JNDI. Start the Driver Manager dialog using the Tools->Driver Manager menu choice.

The left part of the driver manager dialog contains a list of driver names with a symbol indicating whether the driver has been configured or not. The right part displays the driver configuration for the selected driver in terms of the following:

• Name
  A driver name in the scope of DbVisualizer is a logical name for either a JDBC driver or an Initial Context in JNDI. This is the name shown in the Connection tab setup when selecting which driver to use for a database connection
  
• URL Format
  The URL format specifies the pattern for the JDBC URL or a JNDI Lookup name. Its purpose is to assist the user in the Connection tab when entering the URL or lookup name
  
• Driver Class
  Defines the main class for the JDBC driver, used for connecting to the database.
  
• Web Site
  Link to the DbVisualizer web site, where you can get up-to-date information about how to download the driver.
  
• Driver File Paths
  Defines all paths to search for JDBC drivers or Initial Contexts when connecting to the database. The Driver File Paths area is composed of two tabs: the paths in the User Specified tab are used for dynamically loaded JDBC drivers or Initial Context classes, and the System Classpath tab lists all paths that are part of the Java system classpath.
  

The System Classpath tab is only of interest for the JDBC-ODBC driver.

Figure: Driver Manager dialog

Initially, the driver list contains a collection of default drivers. They are not fully configured, as the paths to search for the classes need to be identified. You can edit the list, i.e., create, copy, remove and rename drivers. A driver is ready to use once a driver class has been identified, which is indicated with a green check icon in the list. Drivers that are not ready for use are indicated with a red cross icon.

Only ready (configured) drivers appear in the Connection tab driver list.

The figure shows seven drivers that are ready: DB2 UDB, Informix, JDBC/ODBC Bridge, Mimer, MySQL, Oracle Thin and PostgreSQL.

Setup a JDBC driver

The recommended way to setup a driver is to pick a matching driver name from the list and then simply load the JAR, ZIP or directory that keeps the driver class(es). For instances, if you are going to load the JDBC driver for Oracle, select the Oracle driver in the list . You can also create a new driver or copy an existing one.

Check the following online web page with the most current information about the tested databases and drivers:

http://www.dbvis.com/products/dbvis/doc/supports.jsp

• It lists which databases and drivers we have tested
• Download links to JDBC drivers
• Information of which files to load in the driver manager for each JDBC driver
  
• Information of which Driver Class to choose

When you have selected the driver to configure, you need to load the driver files. Click the Load button to the right of the User Specified paths tree to show the file chooser and load the driver JAR, ZIP or individual files.

Figure: File Chooser dialog

A JDBC Driver implementation typically consists of several Java classes. If they are packaged in a JAR or a ZIP file, you don't have to worry about the details; just select and load the JAR or ZIP file. For instance, in the example above, use the ojdbc5.jar file.

If the driver classes are not packaged, it is important to select and load the root folder for the JDBC Driver. Java classes are typically organized using a package name structure. Example:

oracle.jdbc.driver.OracleDriver

Each package part in the name above (separated by ".") is represented by a folder in the file system. The root folder for the driver is the folder named by the first part, i.e., the oracle directory in this example. The class files are stored in the oracle/jdbc/driver sub folder. When the driver classes are located in a folder structure like this, you must select and load the root folder, so that the Driver Manager gets the complete package structure.

When a connection is established in the Connection tab, DbVisualizer searches the selected drivers path tree's in the following order:

1. User Specified
   
2. System Classpath

The paths are searched from the top of the tree, i.e., if there are several identical classes in, for example, the dynamic tree, the topmost class will be used. Loading several paths containing different versions of the same driver in one driver definition is not recommended, even though it works (if you do this, you must move the driver you are going to use to the top of the tree). The preferred method for handling multiple versions of a driver is to create several driver definitions.

When you load files in the User Specified paths list, DbVisualizer analyzes each file to find the classes that represent main driver classes. Each such class is listed under the path where it was found in the User Specified paths lists, and it is also added to the Driver Class list in the Driver Settings area above. If there is more than one class in the list, make sure you select the correct Driver Class from the list. Consult the driver documentation (or the Databases and JDBC Drivers page) for information about which class to select. 

JDBC drivers that requires several JAR or ZIP files

Some drivers depend on several ZIP or JAR files, or directories. An example is if you want XML support for an Oracle database. In addition to the standard JAR file for the driver, you then also need to load two additional JAR files. These are not JDBC driver files but adds functionality the driver needs to fully support XML. 

Simply select all JARs at once and press Open in the file chooser dialog. The Driver Manager will then automatically analyze each of the loaded files and present any JDBC driver classes or JNDI initial context classes it finds.

Figure: File Chooser dialog

The JDBC-ODBC bridge

The JDBC-ODBC driver is bundled with most Java installations, but not all (e.g., it is not included with Java for Max OS X). The JdbcOdbcDriver class is included in a JAR file that is commonly named rt.jar, stored somewhere in the Java directory structure. DbVisualizer automatically identifies this JAR file in the System Classpath tree. To locate the JdbcOdbcDriver, simply press the Find Drivers button to the right of the System Classpath tree. When it is found, make sure the sun.jdbc.odbc.JdbcOdbcDriver is selected as the Driver Class in the Driver Settings area.

Loading JNDI Initial Contexts

Initial Context classes are needed to get a handle to a database connection that is registered with a JNDI lookup service. In DbVisualizer, these context classes are similar to JDBC driver classes in that an Initial Context implementation for a specific environment is required.

Remember that the appropriate JDBC driver classes must be loaded into the Driver Manager even if the database connection is obtained using JNDI.

To load Initial Context classes into the Driver Manager, simply follow the steps outlined for loading JDBC drivers. The difference is that you will instead load paths containing Initial Context classes instead of JDBC drivers. When you load a path, DbVisualizer locates all Initial Context classes in the path and lists them in the User Specified paths list.

Figure: Driver Manager List with Initial Context classes

Visually, the difference between the identified JDBC drivers and Initial Context classes is the icon in the tree.

The figure shows the JAR files required to first obtain the JNDI handle, and then also the JDBC driver for the database. Check with the application server vendor or similar for more information about what files you need to load to get connected via JNDI.

Errors (why are some paths red?)

A path in red color indicates that the path is invalid. This may happen if the path has been removed or moved after it was loaded into the driver manager. Simply remove the erroneous path and locate the correct one.

Several versions of the same driver

The Driver Manager supports loading and using several versions of the same driver concurrently. We recommend that you create a unique driver definition per version of the driver and name the driver definitions properly, e.g., Oracle 9.2.0.1, Oracle 10.2.1.0.1, etc.

Setup a database connection

This section explains how to setup a Database Connection in the Connection tab.

Setup using JDBC driver

A Database Connection in DbVisualizer is the root of all communication with a specific database. It requires at a minimum that a driver is selected and that a Database URL is specified. A new Database Connection is created using the Database->Add Database Connection menu choice in the main window:

Figure: New Database Connection using JDBC driver

The Connection tab is the only tab that is enabled if you are not connected to the database. Database connection objects appear throughout the application and are by default listed by their URL. A URL can be, and often is, quite complex and long. You can use the Database Alias to set a more readable name for the database connection.

The Database Type list shows all database types that have a set of separate properties, which you can adjust in the Tool Properties dialog. Select the database type you are creating a connection for, or select Generic if you cannot find a matching type.

The Driver list shows all defined drivers that have been defined properly in the Driver Manager. Just open the list and select the appropriate driver. Clicking the button to the right of the field opens the Driver Manager dialog with the settings for the selected driver.

Enter the JDBC URL for the connection in the Database URL field. The drop-down menu to the right of the field provides two options for entering or editing the URL. Edit URL opens a multirow editor, in case your URL is extremely long. Launch URL Builder opens a dialog where you fill out a form with information about the connection, used to generate the URL for you when you close the dialog.

There is also a URL Format field under the URL field that shows the URL format that the driver supports. You can click on the format string to copy the format template into the URL field. Terms between < and > characters are placeholders that need to be replaced with appropriate values, e.g.:

jdbc:oracle:thin:@proddb:1521:bookstore
jdbc:sybase:Tds:localhost:2638
jdbc:db2://localhost/crm
jdbc:microsoft:sqlserver://localhost;DatabaseName=customers

Userid and Password are optional but most databases require that they are specified.

Some drivers accept additional proprietary parameters described in the Connection Properties section.

Setup using JNDI lookup

The information needed to obtain a database connection using JNDI lookup is similar to what is needed for connecting using a JDBC driver.

Figure: New Database Connection using JNDI lookup

The figure above shows parameters to connect with a lookup service via the MySQL RefFS driver. The /tmp/jnditest4975.tmp/test lookup name specifies a logical name for the database connection. This example is in its simplest form, since userid and password are not specified, nor where the database connection is finally fetched from. Any errors during the process of getting a handle to the database connection appears in the Connection Message area.

Connection Properties

In addition to the standard connection parameters (URL, Driver, Userid, Password, etc.), there are also a collection of connection properties. Which properties are available depends on the database type. Some database types have more properties than others. Which edition of DbVisualizer you use also affects which connection properties are available.

All supported database types (Oracle, Informix, Mimer, DB2, MySQL, etc.)  are listed in the Database tab in the Tool Properties window. For each database type, there are a number of properties that are applied to any database connection of that type. This means, for instance, that a database connection defined as being a PostgreSQL database type will use the PostgreSQL properties defined in Tool Properties. The Connection Properties can then be used to override some settings specifically for one database connection. The advantage with this inheritance model is that property changes that apply to all connections can be made in one place, instead of having to apply a common setting for every database connection of a specific database type.

The following summarize the organization of the properties:

• Tool Properties (Database)
  These apply to all database connections of the specific database type.
  
• Connection Properties
  These apply for a specific database connection only.
  

-"Okay, so there are two places to change the value of a property. Which shall I use?"

This depends on whether the change should be applied to all database connections for a specific database type or just a single one. If the majority of your database connections should use the new property, it is recommended to set it in Tool Properties.  Any overridden properties in the Connection Properties tab are indicated with an icon in the Properties tab label.

Figure: Connection Properties

The Connection Properties tab is organized in the same way as the Tool Properties window. The difference is that the list only includes the categories that are applicable for the selected database connection. Briefly, the categories are:

• Database Profile
  
• Driver Properties
• MySQL (The current Database Type)
• Authentication
• Delimited Identifiers
• Qualifiers
• Physical Connection
• Transaction
• SQL Statements
• Connection Hooks
• Objects Tree
• SQL Editor
• Query Builder

The Database Profile and Driver Properties categories are only available in the Connection Properties tab and not in Tool Properties. The next section explains the Database Profile and Driver Properties categories, while the other categories are described in the Tool Properties document.

Additional categories may appear in the connection properties depending on the type of database. An example is the category for Explain Plan for Oracle, DB2 and SQL Server.

Database Profile

Please read in the Database Objects Explorer document for detailed information about database profiles.

The Database Profile category is used to select whether a profile should be automatically detected and loaded by DbVisualizer, or if a specific one should be used for the database connection. The default strategy is to Auto Detect a database profile.

Figure: Database Profile category for a database connection

The way DbVisualizer auto detects a profile is based on the setting of Database Type in the connection details.

If you manually choose a database profile, this choice will be saved between invocations of DbVisualizer.

Driver Properties

The Driver Properties category is used to fine tune a driver or Initial Context before the database connection is established.

Driver Properties for JDBC Driver

Some JDBC drivers support driver specific properties that are not covered in the JDBC specification. 

Figure: Driver Properties for JDBC Driver

The list of parameters, their default values and parameter descriptions are determined by the JDBC driver used for the connection. Not all drivers supports additional driver properties. To change a value, just modify it in the list. The first column in the list indicates whether the property has been modified or not, and so, whether DbVisualizer will pass that parameter and value onto the driver at connect time.
New parameters can be added using the buttons at the bottom of the dialog. Be aware that additional parameters do not necessarily mean that the driver will do anything with them.

Driver Properties for JNDI Lookup

The Driver Properties category for a JNDI Lookup connection always contains the same parameters.

Figure: Driver Properties for JNDI lookup

The list of options for JNDI lookup is determined by the constants in the javax.naming.Context class. To change a value, just modify the value of the parameter. The first column in the list indicates whether the property has been modified or not, and so, whether DbVisualizer will pass that parameter and value onto the driver at connect time. New parameters can be added using the buttons at the bottom of the dialog. Be aware that additional parameters do not necessarily mean that the InitialContext class will do anything with them.

Always ask for userid and/or password

Userid and password information is generally information that should be handled with great care. By default, DbVisualizer saves both userid and password (encrypted) for each database connection. Userid is always saved while password saving can be disabled in the connection properties, in the Authentication category.

The Require Userid and Require Password connection properties in the Authentication category can be enabled to tell DbVisualizer to automatically prompt for userid and/or password when a connection is to be established. Enabling either one or both of these while leaving the Userid and Password fields blank for a database connection ensures that DbVisualizer will not keep this vital information between sessions. The following dialog is displayed if requiring both userid and password.

Figure: Dialog asking for Userid and Password as a result of having Require Userid and Password settings enabled

Using variables in the Connection details

Variables can be used in any of the fields in the Connection tab. This can be useful alternative to having a lot of similar database connection objects. Several variables can be in a single field, and default values can be set for each variable. The following figure shows an example with variables, i.e., variable named delimited by dollar characters, $$...$$.

Figure: Connection tab with variables

The following variables appear in the figure:

• $$Alias$$
• $$Database Host||dbhost2||||choices=[dbhost1,dbhost2,dbhost3] $$
• $$Port||1521$$
  
• $$SID||ORCL$$

All of these variables define a default value after the "||" delimiter, except for the $$Alias$$ variable, which have no default value. The default values appear in the connect dialog when you ask for a connection to be established. The $$Database Host$$ variable includes the choices option, with a comma separated list of choices that should appear in a drop-down list. The drop-down list is editable, so the user is not locked into the choices from the list.

The following figure shows the connect dialog based on the connection definition shown above.

Using variables in conjunction with the Require Userid and/or Require Password settings is also supported.

Figure: Connection tab with variables

Enter the appropriate information in the fields and then press the Connect button to establish the connection. When the connection is established, DbVisualizer automatically substitutes the variables in the Connection tab with the values entered in the connect dialog. At disconnect from the database, they revert back to the original variable definitions.

Connect to the Database

Press Connect when all information has been specified. DbVisualizer passes all information you entered on to the selected driver, and when the connection is established, the following appears.

Figure: A freshly initiated database connection using JDBC driver

The Connection Message box now lists the name and version of the database as well as the name and version of the JDBC driver. The database connection node in the tree indicates that it is connected. The connection properties cannot be edited while a database connection is established. 

The figure above also shows that the database connection node in the tree has been expanded to show its child objects.

If the connection is unsuccessful, it is indicated by an error icon in the tree. The error message as reported by the database or the driver appears in the Connection Message area. Use this information to track down the actual problem. Since these conditions are specific for the combination of driver and database, you should check the driver and database documentation to find out more. Below are a few common problem situations:

Connections Overview

The Connections overview is displayed by selecting the Connections object in the Database Objects Tree. This overview displays all database connections in a list and is handy to get a quick overview of all connections. In addition to the Alias, Profile, URL, driver, etc. there are a few symbols describing the state of each connection. Double clicking on a connection changes the display to show that specific connection.

Figure: The Connections Overview

Information for each symbol is provided in the description area below the list. The fifth check symbol is the only editable symbol and is used to set the state of the Connect when Connect All property, i.e., whether the database connection should be connected when selecting the Database->Connect All menu choice.

Click the Type column for an entry to modify its Database Type.

---