Load JDBC Driver and Get Connected

 DbVisualizer Logo
DbVisualizer 4.3
January 2006 		http://www.dbvis.com
support@dbvis.com

[ Master documentation index ]

Introduction

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

Fast track: If you are impatient then please go ahead and read the Connection Wizard section. It is the recommended feature in DbVisualizer to create database connections.

What is a JDBC Driver?

DbVisualizer is as you know a generic tool to administrate and explore databases. DbVisualizer is in fact quite simple since it do not deal with how to communicate with each database. The hard job is done by the 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 is a database and database version specific implementation and there are a range of drivers from the database vendors themselves and 3:rd party authors. In order to establish a connection with a database using DbVisualizer it needs to load the driver and then get connected to the database through the driver.


Figure: The runtime environment with the JDBC interface, JDBC driver and sample databases

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. These are then used in the connection properties in order to lookup a database connection. The following information explains the steps of how to get connected using a JDBC Driver and also how to use JNDI to obtain a database connection.

A JAR, ZIP or directory that is loaded into the driver manager consists of a number of Java classes that forms the complete implementation of the JDBC driver. DbVisualizer automatically recognize 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 found classes are low, figure out by trying each of them.

Get the JDBC driver file(s)

DbVisualizer do not include any JDBC driver so first you must grab a JDBC driver file(s) that works with the actual database and the version of it. The following online web page lists an up to date listing of the tested combinations:

Databases and JDBC Drivers

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

JDBC Data Access API - 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 in order to make the driver files visible to the Driver Manager.
The Databases and JDBC Drivers web page lists from where to download each driver and also what steps is needed to eventually unpack, install and load the driver in DbVisualizer.

(Drivers are categorized into 4 types. We're not going to explain the differences here but just give a hint that the "type 4" aka "thin" drivers are easiest to maintain since they are pure Java drivers and do not depend on any external DLL's or dynamic libraries i.e try to get a type 4 driver even though DbVisualizer works with any type of driver).

Connection Wizard

The Connection Wizard greatly simplifies the steps needed to load the JDBC driver and create a new database connection. It is based on a few wizard pages in which information about the driver file(s) and connection data should provided. Once the new database connection has been created it will appear in the database objects tree.

Note: 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 in DbVisualizer.
Press Next to connect to the next page.

In this page select the driver from the list that you are going to use. The red icon indicates that the driver is not ready yet 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 the following will be displayed. Press the Load Driver File(s) button to open a file chooser in which you should select the JAR or ZIP file(s) that contain the driver implementation. (Press Ctrl button in the file chooser to select several files).


Figure: Connection Wizard - Page 2

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


Figure: Connection Wizard - Page 3

This page lists the data that should be entered for the selected driver. Supply the information and press Test Connection to check if the connection can be established. Press Finish to create the new database connection and connect it.

It is recommended that you skip reading the rest of this document if you not:

Driver Manager

The Driver Manager in DbVisualizer is used to define the drivers that will be used to communicate with the actual databases. Start the driver manager dialog using the Tools->Driver Manager menu choice.

The left part of the driver manager dialog lists a collection of driver names and a symbol indicating whether the driver has been configured or not. The right part displays the definition of the selected driver in terms of the following:
Note: Do not bother about the System Classpath tab unless you are using the JDBC-ODBC driver.


Figure: Driver Manager dialog

The driver list contains initially a collection of default drivers. These are not fully configured as the actual paths used to search for the classes need to be identified. The list can be edited as drivers can be created, copied, removed and renamed. A driver is ready once a default class has been identified and this state is indicated with a green check icon in the list. Not ready drivers are indicated with a red cross icon.

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

The figure shows four drivers that are ready, MySQL RefFS, Oracle Thin, SQL Server and Sybase ASE.

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 actual driver class(es) i.e if you are going to load the JDBC driver for Oracle then select the Oracle driver in the list. You can also create a new driver or copy an existing one.

Note: Check the following online web page with the most current information about the tested databases and drivers.
Databases and JDBC Drivers
 
To load jar file(s) then press the Load button to the right of the User Specified paths tree to show the file chooser and load the driver jar(s).


Figure: File Chooser dialog

It is important to load the root of the JDBC Driver i.e a JDBC Driver implementation consists in most cases of several Java classes. These are also in most cases organized using the package mechanism in Java. Example:

oracle.jdbc.driver.OracleDriver

Each package part in the name above (separated by ".") will be represented by a directory in the file system. These directories are either explicitly visible in the file system or implicitly if the driver is packaged in a ZIP or JAR file. The root of the driver is in this case where the oracle directory is located. In the Oracle example this is the ojdbc14.jar JAR file so the driver manager must load this path in order to find the driver class. If the driver is packaged in a ZIP file or a directory then point the driver manager to that path in order for the driver manager to locate the driver class.

Once a connection is established in the Connection tab will DbVisualizer search the selected drivers path tree's in the following order:
  1. User Specified
  2. System Classpath
These are searched from the top of the tree i.e if there are several identical classes in for example the dynamic tree then the topmost class will be used. Loading several paths with different versions of the same driver in one driver definition is not recommended even though it works (if you do this then you must move the driver you are going to use to the top of the tree). The preferred solution to handle multiple versions of a driver is to create several driver definitions.

Once one or several classes has been identified and listed in the Driver Class list then make sure you select the correct Driver Class from the list. See the table earlier for assistance.

JDBC drivers that requires several JAR or ZIP files

Some drivers depend on several ZIP, JAR files or directories. An example is the JDBC driver for Microsoft SQL Server that requires three different JAR files to be loaded. In this case simply load all JAR files even though the driver manager will only report the driver class (com.microsoft.jdbc.sqlserver.SQLServerDriver) in one of them.

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 in the tree.


Figure: File Chooser dialog

The JDBC-ODBC bridge

The JDBC-ODBC driver is by default part of most Java installations. The JdbcOdbcDriver class is included in a JAR file that is commonly named rt.jar and is 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. Once found then make sure the sun.jdbc.odbc.JdbcOdbcDriver is selected as the Default Class.

Loading JNDI Initial Contexts

Initial Context classes are needed in order to get a handle to a database connection that is registered in a JNDI lookup service. These classes are similar to JDBC driver classes since an Initial Context implementation is required.

Note: 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 locations that contain Initial Context classes instead of JDBC drivers. Once Initial Context classes have been found the following will appear in the Driver Manager list.


Figure: Driver Manager List with Initial Context classes

The visual difference between the identified JDBC drivers and Initial Context classes is the icon in the tree.

The figure shows the required JAR files in order to first obtain the JNDI handle and then also the actual JDBC driver that is needed to interface the database. Check with the application server vendor or similar for more information of what files that need to be loaded 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 then using several versions of the same driver concurrently. The recommendation is to create a unique driver definition per version of the driver and then name the drivers properly. Ex. 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 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 enabled tab if you are not already 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. The Database Alias is used to optionally set a more readable name of the database connection. The Driver list when opened shows all defined drivers that have been defined properly in the Driver Manager. Just open the list and select the appropriate driver. The URL Format lists the format  that the driver supports.

Tip: Put the mouse pointer on the URL Format and click with the mouse to copy the format template into the URL field.

The < and > characters indicates that they are the boundary for a placeholder and that they shall be replaced with appropriate values.  Ex.

jdbc:oracle:thin:@proddb:1521:bookstore
jdbc:sybase:Tds:localhost:2638
jdbc:db2://localhost/crm
jdbc:microsoft:sqlserver://localhost;DatabaseName=customers
Userid and Password is 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 in order to obtain a database connection using JNDI lookup is similar to getting connected 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 that will be used. This example is in its simplest form since userid and password is not specified, nor where the database connection is finally fetched from. Any errors during the process of getting a handle to the database connection will appear in the Connection Message area.

Connection Properties

Some of the connection properties are used to override the generic properties available in the Tool Properties window. There are two ways to change the property for a database connection:
-"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 or just a single one. If the majority of database connections should use the new value then 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 was as the tool properties window. The difference is that the list only includes the categories that are applicable for a database connection. The categories are briefly:
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.

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

Note: The way DbVisualizer auto detects a profile is based on mappings in the DBVIS-HOME/resources/database-mappings.xml file.

If you manually choose a database profile then 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 actual driver. 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 contain 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. DbVisualizer saves by default both userid and password (encrypted) for each database connection. Userid is always saved while password saving can be disabled in the connection properties.

The Require Userid and Require Password connection properties can be enabled to control that DbVisualizer automatically should prompt for userid and/or password once a connection is established. Enabling either one or both of these and leaving the Userid and Password fields blank for a database connection ensures that DbVisualizer will not keep this vital information between sessions. The following figure 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 instead of 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 of variables that are identified by the dollar characters, $$...$$.


Figure: Connection tab with variables

The following variables appear in the figure:
All of these variables defines a default value after the "||" delimiter except $$Alias$$ that have no default value. These default values will appear in the connect dialog once a connection is requested. The following figure shows the connect dialog based on the information above.

Note: Using variables in conjunction with the Require Userid and/or Require Password settings also works.


Figure: Connection tab with variables

Enter the appropriate information in the fields and then press the Connect button to establish the connection. Once connected will DbVisualizer automatically substitute the variables in the Connection tab with the values entered in the connect dialog. These will at disconnect from the database revert back to the original variable definitions.


Figure: Connection tab as it look once connected using variables

Connect to the Database

Press Connect when all information has been specified. DbVisualizer will pass all entered information onto the selected driver and if the connection is established the following will appear.


Figure: A freshly initiated database connection using JDBC driver

The Connection Message 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 once while a database connection is established. The Alias can be edited by selecting the database connection node in the tree and then clicking on the name.

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 will be indicated by an error icon in the tree. The error message as reported by the database or the driver will appear in the Connection Message area. Use this to track the actual problem. Since these conditions are specific for the combination of driver and database it is generally recommended to check the driver and database documentation to find out more. Below are a few common problem situations:

Error Explanation
No suitable driver.
There is no driver that can handle a connection for the specified URL. The most common reason is that the driver is not loaded in the Driver Manager. Also make sure the URL is correct spelled.		 The JDBC support in Java determines what driver to load based on the database URL. If the URL is malformed then there might be no driver that is able to handle the  database connection based on that URL. This error is produced when this situation occurs or when the driver is not loaded in the driver manager. The recommendation is to check the JDBC driver documentation for the correct syntax.
java.sql.SQLException: Io exception: Invalid number format for port number
Io exception: Invalid number format for port number
 The URL templates that are available in the Database URL list contains the "<" and ">" place holders. These are there to indicate that the value between them must be replaced with an appropriate value. The "<" and ">" characters must then be removed.

This example error message is produced by the Oracle driver when using the following URL:

jdbc:oracle:thin:@<qinda>:<1521>:<fuji>

Simply remove the "<" and ">" characters and try again.

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 URL, driver, etc there are a few symbols describing the state of each connection. Double clicking on a connection will change 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.

Copyright © 2006 Onseven Software AB. All rights reserved.