Starting with version 9.1.3, DbVisualizer verifies that the connection has not been dropped by executing a simple query (e.g. SELECT 1 FROM DUAL for an Oracle database) for most databases, or by calling a JDBC method that does something similar if the Database Type is set to Generic or to another database type that is not one of the databases with database-specific support.


If the JDBC driver you use implement this method by trying to execute a query your database does not understand, you get a Database connection is not open message when you try to execute any statement.


For DbVisualizer 9.2 or later

Modify the validation query for the connection to one that the database supports:

  1. Open the Object View tab for the connection,
  2. Open the Properties tab,
  3. Select the Physical Connection category,
  4. Enter a valid query in the Validation SQL or Validation and Keep-Alive SQL field,
  5. Click Apply.

For most databases the any of the following SQLs should work as the Validation SQL: 

SELECT 1;
SELECT 1 FROM aSmallTable WHERE 1 = 0;

  Note: You will need to change the name of the table (aSmallTable) to a table that exists.

For DbVisualizer versions earlier than 9.2

First make sure you set the Database Type for the connection to Generic. Then open the file named DBVIS-HOME/resources/dbvis-custom.prefs (located in the DbVisualizer installation folder) in a text editor and add the following on a new row:

dbvis.generic.-ConnectionValidationSelect=<simple-query>

where <simple-query> is any query that is fast to execute successfully and returns one or no rows. For many databases any of the SQLs listed above should work. Save the file and re-start DbVisualizer.


If you use Database Type set to Generic and you run into this problem, you can set the validation query to the string disabled instead, but that means things may not always work right if a connection is dropped by the server.

 

If you must use a Database Type other than Generic, e.g. a specific database type that does not have database-specific features in DbVisualizer like Teradata or Firebird, you can keep that Database Type setting and add the corresponding property for that database type in DBVIS-HOME/resources/dbvis-custom.prefs instead, e.g. 

dbvis.netezza.-ConnectionValidationSelect=<simple-query>

In most cases, the property name is as in this example, i.e. with the database type name in lowercase instead of generic.


Handle Lost Connection for DbVisualizer versions 14 or later

You can decide how to handle a lost connection during the execution of a script by changing the settings in the Handling loss of Connection section in the Connection Properties tab and the SQL Commander category as described in the Users Guide section Executing SQL Statements.