SQL Workbench/J uses the concept of database profiles to store connection information. A connection profile stores two different types of settings:
After the program is started, you are prompted to choose a connection profile to connect to a database. The dialog will display a list of available profiles on the left side. When selecting a profile, its details (JDBC and SQL Workbench/J settings) are displayed on the right side of the window.
To create a new profile click on the
New Profile
button ().
This will create a new profile with the name "New Profile". The new profile
will be created in the currently active group.
The other properties will be empty. To create a copy of the currently
selected profile click on the
Copy Profile
button
().
The copy will be created in the current group. If you want to place the copy
into a different group, you can either choose to Copy & Paste a copy of the profile
into that group, or move the copied profile, once it is created.
To delete an existing profile, select the profile in the list and
click on the Delete Profile
button
()
Profiles can be organized in groups, so you can group them by type (test, integration, production)
or customer or database system. When you start SQL Workbench/J for the first time,
no groups are created and the tree will only display the default group node.
To add a new group click on the Add profile group
()
button. The new group will be appended at the end of the tree. If you create a new profile, it will
be created in the currently selected group. If a profile is selected in the tree and not a group
node, the new profile will be created in the group of the currently selected profile.
![]() | |
Empty groups are discarded (i.e. not saved) when you restart SQL Workbench/J |
You can move profiles from one group to another but right clicking on the profile, then choose
. Then right-click on the target group and select from the popup menu. If you want to put the profile into a new group that is not yet created, you can choose . You will be prompted to enter the new group name.If you choose
instead of , a copy of the selected profile will be pasted into the target group. This is similar to copying the currently selected profile.To rename a group, select the node in the tree, then press the
key. You can now edit the group name.To delete a group, simply remove all profiles from that group. The group will then automatically be removed.
Property | Description |
---|---|
Driver | This is the classname for the JDBC driver. The exact name depends on the DBMS and driver combination. The documentation for your driver should contain this information. SQL Workbench/J has some drivers pre-configured. See JDBC drivers for details on how to configure your JDBC driver for SQL Workbench/J. |
URL | The connection URL for your DBMS. This value is highly DBMS specific. The pre-configured drivers from SQL Workbench/J contain a sample URL. If the sample URL (which gets filled into the text field when you select a driver class) contains words in brackets, then these words (including the brackets) are placeholders for the actual values. You have to replace them (including the brackets) with the appropriate values for your DBMS connection. |
Username | This is the name of the DBMS user account |
Password | This is the password for your DBMS user account. You can choose not to store the password in the connection profile. |
Fetch size |
This setting controls the default fetch size
for data retrieval. This parameter will directly be passed to the
setFetchSize()
method of the The JDBC driver for PostgreSQL controls the caching of ResultSets through this parameter. As the results are cached by SQL Workbench/J anyway, it is suggested to set this parameter to a value greater then zero to disable the caching in the driver. Especially when exporting large results using WbExport or WbCopy it is recommended to turn off the caching in the driver (e.g. by setting the value for this property to 1). |
Autocommit | This checkbox enables/disables the Autocommit property
for the connection. If Autocommit is enabled, then each SQL statement
is automatically committed on the DBMS. If this is disabled, any DML
statement ( |
Most JDBC drivers support additional connection properties where you can fine tune the behaviour of the driver or enable special features that are not switched on by default.
If you need to pass an additional paramter to your driver you can do that with the
button. After clicking that button, a dialog will appear with a table that has two columns. The first column is the name of the property, the second column the value that you want to pass to the driver.
To create a new property click on the new button. A new row will be inserted
into the table, where you can define the property. To edit an existing property,
simply doubleclick in the table cell that you want to edit. To delete an existing property
click on the Delete
button
().
If this option is enabled (i.e. checked) the password for the profile will also be stored in the profile file. If the global option Encrypt Passwords is selected, then the password will be stored encrypted, otherwise it will be stored in plain text!
If you choose not to store the password, you will be prompted for it each time you connect using the profile.
If this option is enabled, then each tab in the main window will open a separate connection to the database server. This is useful, if the JDBC driver is not multi-threaded and does not allow to execute two statements concurrently on the same connection.
The connection for each tab will not be opened until the tab is actually selected.
Enabling this option has impact on transaction handling as well. If
only one connection for all tabs (including the Database Explorer)
is used, then a transaction that is started in one tab is "visible" to all other tabs
(as they share the same connection). Changes done in one tab via UPDATE
are seen in all other tabs (including the Database Explorer).
If a separate connection is used for each tab, then each tab will have its own
transaction context. Changes done in one tab will not be visible in other
tabs until they are committed (depending on the isolation level of the database of
course)
If you intend to execute several statements in parallel then it's strongly recommended to use one connection for each tab. Most JDBC drivers are not multi-threaded and thus cannot run more then on statement on the same connection. SQL Workbench/J does try to detect conflicting usages of a single connection as far as possible, but it is still possible to lock the GUI when running multiple statements on the same connection
When you disable the use of separate connections per tab, you can still
create new a (physical) connection for the current tab later, by selecting
Separate connection per tab
is
disabled or you have already created a new connection for that tab.
If this option is enabled, any error reported by the database
server when issuing a statement that begins with DROP, will be
ignored. Only a warning will be printed into the message area. This
is useful when executing SQL scripts to build up a schema, where a
DROP TABLE
is executed before each CREATE TABLE
. If the table does
not exist the error which the DROP
statement will report, is not
considered as an error and the script execution continues.
When running SQL Workbench/J in batchmode this option can be defined using a separate command line parameter. See Section 8, “Using SQL Workbench/J in batch files” for details.
Some DBMS require that all open transactions are closed before actually
closing the connection to the server. If this option is enabled, SQL Workbench/J
will send a ROLLBACK
to the backend server before
closing the connection. This is e.g. required for Cloudscape/Derby because executing
a SELECT
query already starts a transaction. If you see
errors in your log file while disconnecting, you might need to enable this
for your database as well.
If this option is enabled, then SQL Workbench/J will ask you to confirm the execution of any SQL statement that is updating or changing the database in any way (e.g. UPDATE, DELETE, INSERT, DROP, CREATE, COMMIT, ...).
If you save changes from within the result list, you will be prompted even if Confirm result set updates is disabled.
This option cannot be selected together with the "Read only" option.
If this option is enabled, then SQL Workbench/J will never run any statements that might change the database. Changing of retrieved data is also disabled in this case. This option can be used to prevent accidental changes to important data (e.g. a production database)
SQL Workbench/J cannot detect all possible statements that may change the database. Especially when calling stored procedures SQL Workbench/J cannot know if they will change the database. But they might be needed to retrieve data, this cannot be disabled alltogether.
You can extend the list of keywords known to update the data in the
workbench.settings
file.
![]() | |
SQL Workbench/J will not guarantee that there is no way (accidentally or intended) to change data when this option is enabled. Please do not rely on this option when dealing with important data that must not be changed. If you really need to guarantee that no data is changed, you have to do this with the security mechanism of your DBMS, e.g. by creating a read-only user. |
This option cannot be selected together with the "Confirm updates" option.
When entering data into the result set (grid) this setting controls, how empty values (for character columns) should be treated. If this option is enabled, then a NULL value will be sent to the database for an empty (zero length) string. Empty values for other datatypes (dates, numbers etc) are always considered NULL. If this option is not enabled, then an empty string ('') will be sent to the database.
This setting controls whether columns where the value from the result grid
is null are included in INSERT statements. If this setting is enabled, then
columns for new rows that have a null value are listed in the column list
for the INSERT
statement (with the corresponding NULL
value passed in the VALUES
list).
If this property is un-checked, then those columns will not be listed in
INSERT
statements. This is useful if you have e.g.
auto-increment columns that only work if the columns are not listed in the
DML statement.
If this option is checked, then comments will be removed from the SQL statement before it is sent to the database. This covers single line comments using -- or multi-line comments using /* .. */ As an ANSI compliant SQL Lexer is used for this, this does not include comments for MySQL that are entered using the # character.
If this option is enabled, the currently selected schema in the DbExplorer will be stored in the workspace associated with the current connection profile. If this option is not enabled, the DbExplorer tries to pre-select the current schema when it's opened.
For columns defined with the CHAR datatype, some DBMS pad the values
to the length defined in the column definition (e.g. a CHAR(80) column
will always contain 80 characters).
If this option is enabled, SQL Workbench/J will remove trailing
spaces from the values retrieved from the database. When running SQL Workbench/J
in batch mode, this flag can be enabled using the -trimCharData
switch.
You can define a SQL script that is executed immediately after a connection for this profile has been established, and a script that is executed before a connection is about to be closed. To define the scripts, click on the button
. A new window will be opened that contains two editors. Enter the script that should be executed upon connecting into the upper editor, the script to be executed before disconnecting into the lower editor. You can put more than one statement into the scripts. The statements have to be separated by a semicolon.The statements that are executed will be logged in the message panel of the SQL panel where the connection is created. You will not see the log when a connection for the DbExplorer is created.
Execution of the script will stop at the first statement that throws an error. The error message will also be logged to the message panel. If the connection is made for a DbExplorer panel, the errors will only be visible in the log file.
Some DBMS are configured to disconnect an application that has been idle for some time. You can define an idle time and a SQL statement that is executed when the connection has been idle for the defined interval. This is also available when clicking on the
.
The keep alive statement can not be a script, it can only be a single
SQL statement (e.g. SELECT version()
or SELECT 42 FROM dual
).
You may not enter the trailing semicolon.
The idle time is defined im milliseconds, but you can also enter the
interval in seconds or minutes by appending the letter 's' (for seconds)
or 'm' (for minutes) to the value.
e.g.: 30000
(30 seconds), or 45s
(45 seconds), or
10m
(10 minutes).
When only one of the fields contains a value, You can disable the keep alive feature by deleting the entry for the interval but keeping the SQL statement. Thus you can quickly turn off the keep alive feature but keep the SQL statement for the next time.
Once a connection has been established, information about the connection are display
in the toolbar of the main window. You can select a color for the background of this
display to e.g. indicate "sensitive" connections. To use the default background,
click on the Reset
()
button. If no color is selected this is indicated with the text
(None)
next to the selection button. If you have selected a color, a preview of the color is
displayed.
If an alternate delimiter is defined, and the statement that is executed ends with the
defined delimiter, this one will be used instead of the standard semicolon. The profile
setting will overwrite the global setting for this connection. This way you can
define the GO
keyword for SQL Server connections, and use
the forward slash in Oracle connections. The delimiter can be defined as a "single line delimiter",
which means that it will only be recognized if put on a single line.
Please refer to using the alternate delimiter
for details on this property.
For each connection profile, a workspace file can (and should) be assigned. When you create a new connection, you can either leave this field empty or supply a name for a new profile.
If the profile that you specify does not exist, you will be prompted if you want to create a new file, load a different workspace or want to ignore the missing file. If you choose to ignore, the association with the workspace file will be cleared and the default workspace will be loaded.
If you choose to leave the workspace file empty, or ignore the missing file, you can later save your workspace to a new file. When you do that, you will be prompted if you want to assign the new workspace to the current connection profile.
To save you current workspace choose
→ to create a new workspace file.![]() | |
When specifying the location of the workspace file, you can use the placeholder
|
When you use the %ConfigDir%
placeholder, you can move the profiles
and workspaces to a different computer, without changing the location of the workspace files.
The placeholder will be put automatically into the filename
when you select the location of the profile using the file dialog. The file dialog
will be opened when you click the button with ...
to the right
of the input field.
![]() | |
As the workspace stores several settings that are related to the connection (e.g. the selected schema in the DbExplorer) it is recommended to create one workspace for each connection profile. |
Connecting to Oracle with SYSDBA privilege can be done by supplying an additional property to the driver when connecting.
In the profile dialog, click on the internal_logon
and the value sysdba
. Now close the dialog by clicking on the OK button.
This property will be passed on to the JDBC driver, which will enable the SYSDBA role when
connecting to the server.
The profile itself has to use an Oracle user account that is allowed to connect as SYSDBA (e.g. SYS).
On Microsoft Windows® you can use the ODBC bridge to connect to ODBC datasources. For some drivers you don't need to create an ODBC datasource in order to be able to use the ODBC driver. The following URLs can be used to connect to data files directly.
The class name of the driver is sun.jdbc.odbc.JdbcOdbcDriver
ODBC Connection | URL to be used |
---|---|
Excel | jdbc:odbc:DRIVER={Microsoft Excel Driver (*.xls)};DBQ=<filename> |
Access | jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=<filename> |
dBase | jdbc:odbc:DRIVER={Microsoft dBase Driver (*.dbf)};DefaultDir=<directory where the .dbf files are located> |