[ Master documentation index ]
Introduction
This document describes the way JDBC drivers are managed in
DbVisualizer and all aspects about getting connected to your
database(s).
Fast track: 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.
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. 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:
Databases
and JDBC Drivers
Information about almost all drivers that are available is maintained
by Sun Microsystems on 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 to
make
the driver files visible to the Driver Manager. The Databases
and JDBC Driversweb
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.
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 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 Test Connection to
check that the connection can be established. If the test passes, press Finish to
create the new database connection and connect to the database.
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:
Property
|
Description
|
| Run JDBC Driver Finder at Startup |
If enabled, the finder will run automatically every time
you start DbVisualizer. If it finds any new driver files, it will
automatically load and configure them. |
| Display When New Files |
If
enabled, the finder window pops-up if it finds any new files when you
start DbVisualizer. Otherwise, the finder runs invisibly in the
background.
|
Display on Error
|
If
enabled, the finder window pops-up if it encounters any errors loading and configuring new drivers.
Otherwise, it is silent about errors and you have to launch the Driver
Manager to see which drivers are not loaded successfully. Enabling this
property is only meaningful if you have disabled Display When New Files.
|
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.
Note: 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.
Note: 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.
Note: Check the following
online web page with the most current information about the tested
databases and drivers:
Databases
and JDBC Drivers
- 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 ojdbc14.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:
- User Specified
- 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.
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 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 then 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 of 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 Driver
list 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.
Terms between <
and > characters
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 was 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
- 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
Note: 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.
The Require Userid and Require Password
connection properties 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$$
- $$Userid||scott$$
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 choicesoption,
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.
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. 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 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 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 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:
| Error Message |
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 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.
Copyright © 2007 Onseven Software AB. All rights reserved.
