Setting up a Database Connection

Setting up a Database Connection
DbVisualizer 8.0	http://www.dbvis.com support@dbvis.com

---

Master documentation index 

• Introduction 
• Using the Connection Wizard
• Create and Setup a Database Connection Manually
  • Setup using JDBC driver
    • Connection section
    • Authentication section
    • Use SSH Tunnel section
    • Options section
    • Testing Database Server access
  • Setup using JNDI lookup
  • Connection Properties
    • Database Profile
    • Driver Properties
      • Driver Properties for JDBC Driver
      • Driver Properties for JNDI Lookup
  • Always ask for userid and/or password
  • Using variables in the Connection fields
• Connect to the Database
• Connections Overview

Introduction 

To access a database with DbVisualizer, you must first create and setup a Database Connection. A Database Connection describes what type of database it is, which JDBC driver to use, where it is running, which account to use and more. The easiest, and recommended, way to do this is to use the Connection Wizard but you can also create a Database Connection node in the database objects tree without the wizard and fill out the information there. This chapter describes both approaches.

Using the Connection Wizard

The Connection Wizard greatly simplifies the steps needed to create a new database connection, and load the JDBC driver files if needed. 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 appears in the database objects tree.

The first wizard screen looks 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 green icon in front of the driver name indicates that the driver is ready to use, while the absence of an icon indicates that it has not yet been properly configured.

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 contains 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 in the Connection section varies depending on the database type. Please consult the database documentation if you are unsure about how to find the requested information. The  Create and Setup a Database Connection Manually describes the Authentication, Use SSH Tunnel and Options sections in more detail.

Figure: Connection Wizard - Page 5

Press  Ping Server to verify that a network connection can be established to the specified host and port. If the test passes, press  Finish to create the new database connection and connect to the database.

Some databases support different ways to identify which database instance to use (through different URL formats). For instance, Oracle supports formats that contain the host, port and service name or SID, but also a format where you just include a TNS alias and get all the details from a  tnsnames.ora file. For cases like this, the last wizard page has a Connection Type list at the top where you select the format to use. 

Figure: Connection Wizard - Page 5 for a database with multiple URL types

The other fields then depend on the selected Connection Type. For the Oracle TNS format, for example, you can pick the TNS alias from a list. If you like to use TNS aliases with an Oracle database, the  tnsnames.ora file must be located either in the  ORACLE_HOME/network/admin directory or in a directory identified by the TNS_ADMIN environment variable. For more about TNS, see the "Local Naming Parameters (tnsnames.ora)" chapter in Oracle Database Net Services Reference.

Create and Setup a Database Connection Manually

This section explains how manually create a Database Connection node in the objects tree and configure it in the Connection tab.

Create the Database Connection node by selecting  Database -> Create Database Connection in the main window menu bar and then click on  No Wizard in the dialog that pops up.

Setup using JDBC driver

A Database Connection in DbVisualizer is the root of all communication with a specific database. The  Connection tab is where you enter all the information needed to access the database and where you connect to and disconnect from the database.

Figure: New Database Connection using JDBC driver

The Connection tab contains a number of sections. 

Connection section

The Connection section holds the primary information about the database connection.  Enter the name you want to be used for the connection in the objects tree and other places in the DbVisualizer GUI in the  Name field. 

In the  Database Type list, select the type of database you are creating a connection for. If your database type is not listed, select Generic. The Database Type determines which database dependent Tool Properties to use and also which feature set (profile) to provide for the connection. 

The  Driver list shows all drivers that have been defined in the Driver Manager. A green icon in front of the driver name indicates that the driver is ready to use, while the absence of an icon indicates that it has not yet been properly configured. Select the appropriate driver for the connection. If it has not yet been configured, a dialog is shown where you can configure the driver by loading the driver files.

You can also open the Driver Manager dialog with the settings for the selected driver by right-clicking on the Driver label or field.

The remaining fields depend on two things: the selected Driver and the  Settings Format selection at the top of the Connection tab. For most drivers, you can use the Server Info settings format. When this format is selected, a number of driver dependent fields, such as Database Server, Database Port, Service, etc. are shown. Simply enter the requested information. 

If you have defined your own custom driver or if you are using JNDI, only the  Database URL choice may be available. With this setting format, a single Database URL field is shown where you have to enter a valid URL for the database connection. Right-clicking on the field gives you a list of recently used URLs as well as a URL format string with placeholders for the information you need to provide, such as server and port. Select an entry in the list to copy it into the field and edit it as needed. You can use a multi-line editor by selecting the field and clicking on the button to the right in the field.

Authentication section

This section contains fields for information about the database account you are using. Both the  Database Userid and  Database Password are optional but most databases require that they are specified. See  Always ask for userid and/or password for more information about how to set your own preferences for how to deal with authentication.

Use SSH Tunnel section

This section is only shown when the Server Info settings format is selected, and only for databases identified by at least a Database Server and a Database Port (i.e. not for embedded databases).

A database that sits behind a firewall cannot be accessed directly from a client on the other side of the firewall, but it can often be accessed through an SSH tunnel. The firewall must be configured to accept SSH connections and you also need to have an account on the SSH host for this to work.

Enable SSH tunneling by clicking on the checkbox. When it is enabled, five additional fields are shown.

Figure: Database Connection Using an SSH Tunnel

The  SSH Host is the name or IP address for the host accepting SSH connections. The SSH Host is typically the same as the Database Server. Enter the port for SSH connections in the  SSH Port field. The default value is 22. 

You may also enter the userid and password for your SSH host account in the  SSH Userid and  SSH Password fields, but see  Always ask for userid and/or passwordfor other options. Alternatively, you can enter the path to a private key file (using either the RSA or DSA algorithms) in the  Private Key File field. The SSH Password field is then replaced by a  Key Passphrase field where you can enter the passphrase if the private key is protected with one.

When SSH tunneling is enabled, a tunnel is established when you connect to the database and the connection is then made through the tunnel by constructing a JDBC URL that uses information from both the Connection and Use SSH Tunnel sections. 

If you're familiar with using the  ssh command to set up a tunnel manually, you may be interested in more details. The tunnel corresponds to the tunnel you would set up with the  ssh command like this:

ssh -p <SSHPort> -L<LocalPort>:<DatabaseServer>:<DatabasePort> <SSHUserid>@<SSHHost>

where the placeholders correspond to the fields in the Connect and Use SSH Tunnel sections, except for <LocalPort> which is any available port, determined at connect time. 

Note that when using an SSH tunnel, the Database Server is evaluated on the SSH host. If the database server is running on the SSH host, you can therefore set Database Server to localhost in case the database only accepts local connections.

The JDBC URL is constructed using 127.0.0.1 as the Database Server portion and <LocalPort> as the Database Port portion, e.g. like this for the Oracle Thin driver when <LocalPort> is 50538:

jdbc:oracle:thin@127.0.0.1:50538/XE

In other words, the JDBC driver connects to the SSH tunnel's local port, which then forwards all communication to the database server.

The URL that is used for the connection is shown at the top of the Object View for the Database Connection when a connection is established, along with a certificate icon if the connection is made through an SSH tunnel.

Figure: URL and SSH Certificate in the Object View for a Database Connection

Options section

The Options section contains fields for important properties that can also be made in the Connection Properties for the Database Connection, to make it easier to access them. Setting an option in one place changes it in the other, but one option may result in changes to multiple Connection Properties.

This section always contains three fields. When  Auto Commit is enabled, changes resulting from an SQL Statement are committed automatically after executing the statement. Disable Auto Commit if you want to manually commit or rollback the result of a number of SQL statements instead. 

Enable  Save Database Password to save the Database Password between DbVisualizer sessions in an encrypted form. See  Always ask for userid and/or passwordfor more information about this option.

Connection Mode allows you to designate how the connection is used: Development, Test or Production. For the Test and Production modes, DbVisualizer displays a border around areas where database content can be edited, to bring your attention to the fact that you are connected to a database where others may be affected by your changes. You can also define what kind of action to take for different database operations based on the Connection Mode, see  Permissions for details.

For some drivers, there are additional fields in the Options section. Let the mouse hover over a field to get a description of these options.

Testing Database Server access

When you use the Server Info settings format and have entered values in the Database Server and Database Port fields, you can ensure that the port on the server can be accessed by clicking the  Ping Server button. A dialog will tell you if there are any problems accessing that network address. Note that this only tests access at the network level. No attempt is made to actually connect to the database.

Ping Server is not available for connections that use an SSH tunnel.

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

For a JNDI lookup connection, you must use the Database URL settings format and, hence, cannot make connections through an SSH tunnel or test if the database can be accessed using the Ping Server button.

The figure above shows parameters to connect with a lookup service via a RefFS driver. The  /tmp/jnditest4975.tmp/test lookup name specifies a logical name for the database connection. This example is in its simplest form, and additional information may need to be specified as driver properties, see  Driver Properties for JNDI Lookup for more information. 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 basic connection information in the Connection tab, there is also a collection of connection properties. Which properties are available depends on the Database Type selected for the Database Connection in the Connection tab. Some database types have more properties than others. Which edition of DbVisualizer you use also affects which connection properties are available.

Properties for a connection can be defined at two different levels:

• Tool Properties (Database)
  These apply to all database connections of the specific database type.
  
• Connection Properties
  These apply to a specific database connection only.

All supported database types (Oracle, Informix, Mimer SQL, 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 Connection Properties are available in the  Properties tab for the selected Database Connection.

Figure: Connection Properties

The Properties tab is organized basically the same way as the Tool Properties window. The main difference is that the list contains only the categories that are applicable to the selected database connection. Briefly, the categories are:

• Database Profile
  
• Driver Properties
• Oracle (The current Database Type)
  • Authentication
  • Delimited Identifiers
  • Qualifiers
  • Physical Connection
    • Transaction
  • SQL Statements
  • Connection Hooks
  • Objects Tree Labels
  • SQL Editor
  • Query Builder
  • Data Types
  • Explain Plan
  • Objects Tree

The  Database Profile and  Driver Properties categories are available only in the 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 see 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

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 JNDI 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 the Database Userid and Database Password (encrypted) for each database connection. The default for SSH is to save the SSH Userid but not the SSH Password (or Key Passphrase). You can change this behavior to fit your preferences. You specify how to handle the Database Userid and Password in the Authentication category of the Properties tab. The same options are available for the SSH Userid and Password in the Database Connection->SSH Settings category in the General tab of the Tool Properties window.

The  Require Userid and  Require Password properties can be enabled to tell DbVisualizer to automatically prompt for userid and/or password when a connection is to be established if they are not specified for the connection. 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

Since a password may need to be handled with great care, you can also specify for how long it should be saved, if at all. If  Clear Password at Disconnect is selected, DbVisualizer ensures that the Password field is cleared as soon as the connection is terminated. With  Save During a Session, it is cleared when you close DbVisualizer. To keep the password between sessions, select  Save Between Sessions.

Using variables in the Connection fields

Variables can be used in some of the Connection tab fields. You can use variables in the Name, Userid and Password (both Database and SSH) fields with the Server Info settings format, or in the Database URL field when using this settings format. This can be a 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, described in more detail in the  Variables section of the SQL Commander chapter.

Figure: Connection section with variables

The following variables appear in the figure:

• ${Name}$
• ${Database Host||dbhost2||||choices=[dbhost1,dbhost2,dbhost3]}$
• ${Port||1521}$
  
• ${SID||ORCL}$

All of these variables define a default value after the "||" delimiter, except for the  ${Name}$ variable, which has 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  choices option, 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. 

Using variables in conjunction with the Require Userid and/or Require Password settings is also supported.

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 box 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 can be edited while a database connection is established, but some changes (e.g. changing Driver Properties) will not have any effect until you reconnect.  

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 cause of the 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
The selected Driver cannot handle the specified Database URL. The most common reason for this error is that the database URL contains a syntax error preventing the driver from accepting it. The error also occurs when trying to connect to a database with the wrong driver. Correct this and try again.	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 use the Server Info settings format and let DbVisualizer build a correct URL for you. If this is not an option for the selected driver, please 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 format templates that are available in the Database URL list uses the "<" and ">" characters around placeholders in the template. These placeholders must be replaced with an appropriate value and 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 Alias, Profile, 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 about 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. 

You can click the  Type column for an entry to modify its  Database Type.