[ 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 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
implem/entation 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).
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:
- 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 name is later listed in the Connection tab setup when selecting what driver to use for a database connection
- URL Format
The URL format specifies the pattern for the JDBC URL or a JNDI Lookup
name. The purpose is to assist the user in the connection tab while
entering the URL or lookup name
- Default Class
Defines the default class to use when connecting
- Web Site
Link to the DbVisualizer web site containing up to date information how to download the driver.
- Driver File Paths
Defines all paths to search for JDBC drivers or Initial
Contexts during connect with the database. The Driver File Paths is composed of
two tabs, the User Specified tab is used to locate and identify dynamically loaded JDBC drivers or Initial Context classes. The System Classpath tab lists all paths that are part of the Java system classpath.
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.
- It lists what databases and drivers that has been tested
- Download links to JDBC drivers
- Information of what files to load in the driver manager for each JDBC driver
- Information of what Driver Class that should be choosen
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:
- User Specified
- 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/jnditest8056.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:
- 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 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:
- Database Profile
- Driver Properties
- Database Connection
- SQL
- Transaction
- Editor
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. 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.
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:
- $$Alias$$
- $$Database Host||localhost$$
- $$Port||1521$$
- $$SID||ORCL$$
- $$Userid||scott$$
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:
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.
Copyright © 2005 Minq Software AB. All rights reserved.
