[ Master
documentation index ]
What is a JDBC
Driver?
DbVisualizer is as you know a generic tool to administrate and
explore databases. DbVisualizer is in fact quite simple with respect to how much
it knows about specific databases. 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 ZIP or JAR 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.
From DbVisualizer 3.2 it is
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.
Get a JDBC driver
First you
must grab a JDBC driver that works with the actual database and the version of
it. DbVisualizer has been verified with a collection of databases and JDBC
drivers. The following page lists the currently supported
combinations:
Databases and JDBC
Drivers
Information about almost all drivers that are available is
maintained by Sun Microsystems:
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.
(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 your hands on a type 4 driver even though
DbVisualizer works with any type of driver).
Driver Manager
(The Driver Manager is used to define the path (aka
CLASSPATH) that will be searched when locating drivers. The list of locations is
searched in order from the top of the list when DbVisualizer connects to a
database).
The Driver
Manager in DbVisualizer is used to load drivers from the file system.
Start the driver manager dialog using the Database->Driver Manager menu choice.
The main area of the driver manager dialog consist of a tree with paths
as root elements and any driver classes identified by class name as
children.
Figure: Driver Manager dialog
The figure shows
six locations. To load a driver select the File->Add Location menu choice or the tool
bar button. The Driver Manager automatically searches all locations that are
empty for JDBC Driver classes.
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 "9.2.0.3" directory so the Driver Manager must
load this directory in order to find the driver class. If the driver is packaged
in a zip or jar file then point the driver manager to that file in order for the
driver manager to locate the driver class.
Drivers with external
dependencies
Some drivers depend on several zip/jar files or directories. An
example is the JDBC driver for Microsoft SQL Server that requires 3 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 in one of them.
Errors
A path in red color
indicates that the location has been removed from the file system but
information about it is still in the driver manager. The Edit->Remove Invalid menu choice simply
removes all these locations.
Several versions of the same
driver
The list of drivers is searched from the top of the list when
DbVisualizer connects to a database. If there are several versions of the same
driver in the driver manager then use the Edit->Move Up or Edit->Move Down menu choices to re-arrange
the order of the list. Once the connection has been established the version of
the actual driver is listed in the Connection tab message area.
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.
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.
Entries that are not needed in
the list can be removed to make the list less cluttered.
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 least a JDBC driver and a URL that identifies
the database to establish connection with. 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 JDBC Driver list when
opened shows all identified JDBC drivers that have been exposed in the JDBC
Driver Manager. Just open the list and select the appropriate driver. The
Database URL list contains the standard URL's for the supported
drivers:
jdbc:oracle:thin:@<host>:<port>:<sid>
jdbc:sybase:Tds:<host>:<port>/<database>
jdbc:db2://<host>:<port>/<database>
jdbc:microsoft:sqlserver://<host>:<port>;DatabaseName=<database>
jdbc:mysql://<host>/<database>
jdbc:postgresql:net://<host>/<database>
jdbc:postgresql://<host>:<port>/<database>
jdbc:daffodilDB://<host>:<port>/<database>
jdbc:mckoi://<host>/
jdbc:hsqldb:<database>
jdbc:hsqldb:hsql://<host>:<port>
jdbc:Cache://<host>:<port>/<namespace>
jdbc:informix-sqli://<host>:<port>/<database>:informixserver=<dbservername>
jdbc:pointbase:server://<host>:<port>/<database>
jdbc:borland:dslocal:<file>
jdbc:sapdb://<host>:<port>/<database>
jdbc:mimer://<host>:<port>/<database>
jdbc:FrontBase://<host>:<port>/<database>
jdbc:pervasive://<host>:<port>/<database>
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
that is obtained using the weblogic.jndi.WLInitialContextFactory class.
The mypointbase lookup name specifies a
logical name for the database connection that wll 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
The
connection properties are primarly used to override some of the generic
properties available in the Tool Properties window. There are two ways to change
the property for a database connection:
- Tool Properties
These changes
will be applied to all database connections that have not overridden the
actual properties in its Connection Properties.
- Connection Properties
These
changes apply for the actual 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 or not. 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
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:
- Driver Properties
- Database Connection
- SQL Statements
- Transaction
The Driver Properties category is only available in
the Connection Properties tab and not in Tool Properties. The next section
explains the Driver Properties while the other categories are described in the
Tool
Properties document.
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. New properties can be added for testing purposes
(driver tests).
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.
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:
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. |
DbVisualizer and 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. 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.
Copyright © 2004 Minq Software AB. All rights
reserved.
