6. Connecting to the database

6.1. Connection profiles
6.2. Managing profile groups
6.3. JDBC related profile settings
6.4. Extended properties for the JDBC driver
6.5. SQL Workbench/J specific settings
6.6. Connect to Oracle with SYSDBA privilege
6.7. ODBC connections without a data source

6.1. Connection profiles

SQL Workbench/J uses the concept of profiles to store connection information. A connection profile stores two different types of settings:

  • JDBC related properties such as the JDBC driver class, the connection URL, the username etc.
  • SQL Workbench/J related properties such as the profile name the associated workspace, etc.

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 ()

6.2. Managing profile groups

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.

[Note]

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 Cut. Then right-click on the target group and select Paste from the popup menu. If you want to put the profile into a new group that is not yet created, you can choose Paste to new folder. You will be prompted to enter the new group name.

If you choose Copy instead of Cut, 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 F2 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.

6.3. JDBC related profile settings

6.3.1. 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.

6.3.2. URL

The connection URL for your DBMS. This value is 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.

6.3.3. Username

This is the name of the DBMS user account

6.3.4. Password

This is the password for your DBMS user account. You can choose not to store the password in the connection profile.

6.3.5. Autocommit

This checkbox enables/disables the "auto commit" 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 (UPDATE, INSERT, DELETE, ...) has to be committed in order to make the change permanent. Some DBMS require a commit for DDL statements (CREATE TABLE, ...) as well. Please refer to the documentation of your DBMS.

6.3.6. Fetch size

This setting controls the default fetch size for data retrieval. This parameter will directly be passed to the setFetchSize() method of the Statement object. For some combinations of JDBC driver and DBMS, setting this parameter to a rather large number can improve retrieval performance because it saves network traffic.

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).

You can change the fetch size for the current connection manually by running the SQL Workbench/J specific command WbFetchSize

6.3.7. Timeout

This property defines a timeout in seconds that is applied when establishing the connection to the database server. If no connection is possible in that time, the attempt will be aborted. If this is empty, the default timeout defined by the JDBC driver is used.

6.4. Extended properties for the JDBC driver

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. Most drivers support passing properties as part of the URL, but sometimes they need to be passed to the driver using a different method called extended properties.

If you need to pass an additional paramter to your driver you can do that with the Extended Properties 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 ().

Some driver require those properties to be so called "System properties" (see the manual of your driver for details). If this is the case for your driver, check the option Copy to system properties before connecting.

6.5. SQL Workbench/J specific settings

6.5.1. Prompt for username

If this option is enabled (i.e. checked) you will be asked to enter the username and password each time you connect to the specified database server. If this is checked, the input fields for username and password are disabled (but the values will still be saved in the profile).

This is useful if you have many different usernames for the same DBMS server and don't want to create a connection profile for each user.

6.5.2. Save password

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.

6.5.3. Separate connection per tab

If this option is enabled, then each tab in the main window will open a separate (phyiscal) 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 FileNew Connection. That menu item will be disabled if Separate connection per tab is disabled or you have already created a new connection for that tab.

6.5.4. Ignore DROP errors

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 14, “Using SQL Workbench/J in batch files” for details.

6.5.5. Trim CHAR data

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.

6.5.6. Hide warnings

When a SQL statement returns warnings from the DBMS, these are usually displayed after the SQL statement has finished. By enabling this option, warnings that are returned from the DBMS are never displayed.

Note that for some DBMS (e.g. MS SQL Server) server messages (PRINT 'Hello, world') are also returned as a warning by the driver. If you disable this property, those messages will also not be displayed.

If you hide warnings when connected to a PostgreSQL server, you will also not see messages that are returned e.g. by the VACUUM command.

6.5.7. Remove comments

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 detecting comments, this does not work for non-standard MySQL comments using the # character.

6.5.8. Confirm updates

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.

The read only state of the connection can temporarily be changed (without modifying the profile) using the WbMode command.

6.5.9. Read only

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.

[Note]

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.

The read only state of the connection can temporarily be changed (without modifying the profile) using the WbMode command.

6.5.10. Rollback before disconnect

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.

6.5.11. Empty string is NULL

If this option is enabled, then a NULL value will be sent to the database for an empty (zero length) string. Everything else will be sent to the database as entered.

Empty values for non-character values (dates, numbers etc) are always treated as NULL.

If this option is disabled you can still set a column's value to NULL while editing a result set. Please see Editing data for details

6.5.12. Include NULL columns in INSERT

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.

6.5.13. Check for uncommitted changes

This option is (currently) only available for PostgreSQL, HSQLDB 2.x and Oracle

When closing the application (or a SQL panel) SQL Workbench/J will check if the current transaction has changes that were not committed and will issue a warning.

For more details see the description of DBMS specific features.

6.5.14. Remember DbExplorer Schema

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.

6.5.15. Store completion cache locally

If this option is enabled, the cache that is used for the code completion is stored locally when the connection is closed. When connecting to the database the next time the cache is pre-filled with the information from the local cache file.

The cache files will be created in a directory named .cache inside the configuration directory.

6.5.16. Info Background

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.

6.5.17. Alternate delimiter

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.

6.5.18. Workspace

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 WorkspaceSave Workspace as to create a new workspace file.

If you specify a filename that does not contain a directory or is a relative filename, it is assumed the workspace is stored in configuration directory.

[Note]

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.

6.5.19. Connect scripts

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 Connect scripts. 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.

Keep alive script

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 Connect scripts.

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).

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.

6.5.20. Schema and Catalog filters

If your database contains a lot of schema or catalogs that you don't want to be listed in the dropdown of the DbExplorer, you can define filter expressions to hide certain entries.

The filters are defined by clicking on the Schema/Catalog Filter button. The filter dialog contains two input fields, one to filter schema name and one to filter catalog names.

Each line of the filter definition defines a single regular expression of schema/catalog names to be excluded from the dropdown, i.e. if a schema/catalog matches the defined name, it will not be listed in the dropdown.

The filter items are treated as regular expressions, so the standard SQL wildcards will not work here. The basic expression is just a name (e.g. MDSYS). Comparison is always done case-insensitive. So mdsys and MDSYS will achieve the same thing.

If you want to filter all schemas that start with a certain value, the regular expression would be: ^pg_toast.*. Note the dot followed by a * at the end. In a regular expression the dot matches any character, and the * will allow any number of characters to follow. The ^ specifies that the whole string must occur at the beginning of the value.

The regular expression must match completely in order to exlude the value from the dropdown.

If you want to learn more about regular expressions, please have a look at http://www.regular-expressions.info/

6.5.21. Main window icon

You can assign an icon file for each connection profile. The icon will then be used for the main window instead of the default application icon.

The icon file can only be a png or gif file.

Do not use an animated GIF file because that will hang the application!

At least one file with an icon of 16x16 pixel should be selected. You can select multiple files with different icon sizes (e.g. a 16x16 and a 32x32 icon). Whether or not the additional sizes (i.e. bigger than 16x16) will be used depends on your operating system and desktop theme.

6.6. Connect to Oracle with SYSDBA privilege

Connecting to Oracle with SYSDBA privilege can be done by checking the option as SYSDBA next to the username. when using this option, you have to use an Oracle user account that is allowed to connect as SYSDBA (e.g. SYS).

6.7. ODBC connections without a data source

[Important]
The ODBC bridge has been removed from the Java with the release of Java 8.

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 (in Java7 and earlier) is sun.jdbc.odbc.JdbcOdbcDriver

ODBC ConnectionURL 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>