Due to a bug in Oracle's JDBC driver, you cannot retrieve columns with the LONG or LONG RAW data type if the DBMS_OUTPUT package is enabled. In order to be able to display these columns, the support for DBMS_OUTPUT has to be switched off using the DISABLEOUT command before runnnig a SELECT statement that returns LONG or LONG RAW columns.

SQL Workbench/J supports reading and writing BLOB data in various ways. The implementation relies on standard JDBC API calls to work properly in the driver. If you experience problems when updating BLOB columns (e.g. using the enhanced UPDATE, INSERT syntax or the DataPumper) then please check the version of your Oracle JDBC driver. Only 10.x drivers implement the necessary JDBC functions properly. The version of your driver is reported in the log file when you make a connection to your Oracle server.

By default Oracle's JDBC driver does not return comments made on columns or tables (COMMENT ON ..). Thus your comments will not be shown in the database explorer.

To enable the display of column comments, you need to pass the property remarksReporting to the driver.

In the profile dialog, click on the Extended Properties button. Add a new property in the following window with the name remarksReporting and the value true. Now close the dialog by clicking on the OK button.

Turning on this features slows down the retrieval of table information e.g. in the Database Explorer.

When you have comments defined in your Oracle database and use the WbReport command, then you have to enable the remrks reporting, otherwise the comments will not show up in the report.

It seems that the necessary API calls to list the tables of the INFORMATION_SCHEMA database (which is a database, not a schema - contrary to its name) are not implemented correctly in earlier JDBC drivers of MySQL. Only the driver with the version 5.1.7 returns the list of tables of the INFORMATION_SCHEMA database.

In case you receive an error message "Operation not allowed after ResultSet closed" please upgrade your JDBC driver to a more recent version. This problem was fixed with the MySQL JDBC driver version 3.1. So upgrading to that or any later version will fix this problem.

MySQL allows the user to store invalid dates in the database (0000-00-00). Since version 3.1 of the JDBC driver, the driver will throw an exception when trying to retrieve such an invalid date. This behaviour can be controlled by adding an extended property to the connection profile. The property should be named zeroDateTimeBehavior. You can set this value to either convertToNull or to round. For details see the MySQL site

This error usually occurs in the DbExplorer if an older Microsoft JDBC Driver is used and the connection does not use autocommit mode. There are three ways to fix this problem:

The possible parameters for the SQL Server 2005 driver are listed here

Microsoft SQL Server (at least up to 2000) does not support concurrent reads and writes to the database very well. Especially when using DDL statements, this can lead to database locks that can freeze the application. This affects e.g. the display of the tables in the DbExplorer. As the JDBC driver needs to issue a SELECT statement to retrieve the table information, this can be blocked by e.g. a non-committed CREATE ... statement as that will lock the system table(s) that store the meta information about tables and views.

Unfortunately there is no real solution to blocking transactions e.g. between a SQL tab and the DbExplorer. One (highly discouraged) solution is to run in autocommit mode, the other to have only one connection for all tabs (thus all of them share the same transaction an the DbExplorer cannot be blocked by a different SQL tab).

The Microsoft JDBC Driver supports a connection property called lockTimeout. It is recommended to set that to 0 (zero) (or a similar low value). If that is done, calls to the driver's API will through an error if they encounter a lock rather than waiting until the lock is released. The jTDS driver does not support such a property. If you are using the jTDS driver, you can define a post-connect script that runs SET LOCK_TIMEOUT 0.

The jTDS driver and the Microsoft JDBC driver read the complete result set into memory before returning it to the calling application. This means that when retrieving data, SQL Workbench/J uses (for a short amount of time) twice as much memory as really needed. This also means that WbExport will effectively read the entire result into memory before writing it into the output file. For large exports this us usually not wanted.

This behaviour of the drivers can be changed by adding an additional parameter to the JDBC URL that is used to connect to the database. For the jTDS driver append useCursors=true to the URL, e.g. jdbc:jtds:sqlserver://localhost:2068;useCursors=true.

For the Microsoft driver, use the parameter selectMethod=cursor to switch to a cursor based retrieval that does not buffer all rows within the driver, e.g. jdbc:sqlserver://localhost:2068;selectMethod=cursor

When using the DB2 JDBC drivers it is important that the charsets.jar is part of the used JDK (or JRE). Apparently the DB2 JDBC driver needs this library in order to correctly convert the EBCDIC characterset (used in the database) into the Unicode encoding that is used by Java. The library charsets.jar is usually included in all multi-language JDK/JRE installations.

If you experience intermittent "Connection closed" errors when running SQL statements, please verify that charsets.jar is part of your JDK/JRE installation. This file is usually installed in jre\lib\charsets.jar.

The Postgres JDBC driver defaults to buffer the results obtained from the database in memory before returning them to the application. This means that when retrieving data, SQL Workbench/J uses (for a short amount of time) twice as much memory as really needed. This also means that WbExport will effectively read the entire result into memory before writing it into the output file. For large exports this us usually not wanted.

This behaviour of the driver can be changed so that the driver uses cursor based retrieval. To do this, the connection profile must disable the "Autocommit" option, and must define a default fetch size that is greater than zero. A recommended value is e.g. 10, it might be that higher numbers give a better performance. The number defined for the fetch size, defines the number of rows the driver keeps in its internal buffer before requesting more rows from the backend.

More details can be found in the driver's manual: http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor

Postgres - unlike other DBMS - marks a complete transaction as failed if a single statement fails. In such a case the transaction cannot be committed, e.g. consider the following script:

INSERT INTO person (id, firstname, lastname) VALUES (1, 'Arthur', 'Dent');
INSERT INTO person (id, firstname, lastname) VALUES (2, 'Zaphod', 'Beeblebrox');
INSERT INTO person (id, firstname, lastname) VALUES (2, 'Ford', 'Prefect');
COMMIT;

As the ID column is the primary key, the third insert will fail with a unique key violation. In Postgres you cannot commit anyway and thus persist the first two INSERTs.

This problem can only be solved by using a SAVEPOINT before and after each statement. In case that statement fails, the transaction can be rolled back to the state before the statement and the reminder of the script can execute.

Doing this manually is quite tedious, so you can tell SQL Workbench/J to do this automatically for you by setting the properties:

workbench.db.postgresql.ddl.usesavepoint=true
workbench.db.postgresql.sql.usesavepoint=true

in the file workbench.settings. If this is enabled, SQL Workbench/J will issue a SET SAVEPOINT before running each statement and will release the savepoint after the statement. If the statement failed, a rollback to the savepoint will be issued that will mark the transaction as "clean" again. So in the above example (with sql.usesavepoint set to true), the last statement would be rolled back automatically but the first two INSERTs can be committed (this will also required to turn on the "Ignore errors" option is enabled).

If you want to use the modes update/insert or insert/update for WbImport, you should also add the property:

workbench.db.postgresql.import.usesavepoint=true

to enable the usage of savepoints during imports. This setting also affects the WbCopy command.

You can also use the parameter -useSavepoint for the WbImport and WbCopy commands to control the use of savepoints for each import.

Using savepoints can slow down the import substantially.