[ 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.
