18. Properties in the .settings file

18.1. Database Identifier
18.2. DBID
18.3. GUI related settings
18.4. Editor related settings
18.5. DbExplorer Settings
18.6. Controlling sorting of data
18.7. Database related settings
18.8. SQL Execution related settings
18.9. Default settings for Export/Import
18.10. Controlling the log file
18.11. Settings related to SQL statement generation
18.12. Filter settings

This section describes the additional options for SQL Workbench/J which are not (yet) available in the options dialog.

The name of the setting refers to the entry in the file workbench.settings which is located in the configuration directory. Not all listed properties will be present in workbench.settings. In this case, simply create a new line with the property name and the value as described here. The position where you add this entry does not matter.

18.1. Database Identifier

Some parameters are used such that a list of "Database Identifiers" is expected. The identifier that needs to be put there can be obtained by hovering the mouse over the connection URL information in the main window, or from the log file. After a successful connect to a database, there will be an entry in the log file similar to this:

INFO  15.08.2004 10:24:42 Connected to: [HSQL Database Engine]

If the description for a property in this chapter refers to a "Database Identifier", the text between (but not including) the square brackets has to be used.

18.2. DBID

For some settings, where the ID is part of the property's key, a "clean" version of the Database Identifer, called the DBID, is used. This DBID is also reported in the log file:

INFO  15.08.2004 10:24:42 Using DBID=hsql_database_engine

If the description for a property in this chapter refers to the "DBID", then this value has to be used. If the DBID is part of the property key this will be referred to as [dbid] in the key.

18.3. GUI related settings

18.3.1. Showing accelerator in menu

Property: workbench.gui.showmnemonics

Usually the mnemonic (aka. Accelerator) for a menu item is not shown under Windows 2000 or later. It will only be shown, when you press the ALT key. With this settings, this JDK behaviour can be controlled.

Default: true

18.3.2. Save workspaces automatically

workbench.workspace.autosave

If this is enabled, the current workspace will be saved automatically, when a SQL statement is executed.

Default: false

18.3.3. Controlling workspace backups

Property: workbench.workspace.createbackup

If this is enabled, a backup file will be created before saving the workspace. This is done by renaming the current file (overwriting any existing backup) and then saving the workspace.

Default: false

18.3.4. Controlling the number of macros in the main menu

Property: workbench.gui.macro.maxmenuitems

With this setting the number of macros displayed in the Macro menu can be controlled. Note that only the first 9 macros will be accessible with a menu accelerator.

Default value: 15

18.3.5. Controlling the type of print dialog

Property: workbench.print.nativepagedialog

When printing the contents of a table, this settings controls the type of print dialog to be used. The default setting will open the native print dialog of the operating system. If you experience problems when trying to print, set this property to false. SQL Workbench/J will then open a cross-platform print dialog.

Default value: true

18.4. Editor related settings

18.4.1. Include Oracle public synonyms in auto-completion of tables

Property: workbench.editor.autocompletion.oracle.public_synonyms

When using auto completion for table columns and table names, Oracle's public synonyms are not included by default. This has two reasons: first, the author believes that public synonyms shouldn't be used (it's just as bad as global variables in programming) and second, Oracle defines a huge number of public synonyms that would make the popup with all available tables very long and hard to use. Setting this property to true, will include public synonyms in the popup. Please refer to filtering synonyms for details on how to filter out unwanted synonyms from this list.

Default value: false

18.4.2. Empty line to terminate SQL statements

Property: workbench.editor.autocompletion.sql.emptylineseparator

When analysing statements in the editor, it is assumed that individual statements separated with a semicolon. When using auto completion, SQL Workbench/J can be configured to accept an empty line as the separator between two statements. This does not influence the behaviour when running scripts or for the "execute current" command.

Default value: false

18.4.3. Customizing the colors used for syntax highlighting

Property: workbench.editor.color.[type]

These properties control the various colors used for syntax highlighting in the editor. Each entry defines a color with the three RGB values that make up the color.

Available values for [type] and their defaults:
comment1 - The color used for multi-line comments (128,128,128)
comment2 - The color used for single-line comments (128,128,128)
keyword1 - The color standard SQL keywords (0,0,255)
keyword2 - The color used for WB specific commands (255,0,255)
keyword3 - The color used for SQL functions (0,150,0)
literal1 - The color used for literals (101,0,153)
operator - The color used for operators (0,0,0)

18.4.4. Set the modifier key for rectangular selections in the edior

Property: workbench.editor.rectselection.modifier

These properties control the modifier key that needs to be pressed to enable rectangular selections in the editor. Possible values are alt for setting the Alt key as the modifier, or ctrl for setting the Ctrl key as the modifier.

Default value: alt

18.4.5. Default file encoding

Property: workbench.file.encoding

Several internal commands use an encoding when writing external text files (e.g. WbExport). If no encoding is specified for those commands, the default platform encoding as reported by the Java runtime system is used. You can overwrite the default encoding that Java assumes by setting this property.

Default value: empty, the Java runtime default is used

18.4.6. Limitting size of the text put into the history

Property: workbench.sql.history.maxtextlength

When you execute a SQL statement in the editor, the current content of the editor is put into the history buffer. If you are editing large scripts, this can lead to memory problems. This property controls the max. size of the editor text that is put into the history.

If the current editor text is bigger than the size defined in this property the text is not put into the history.

Default value: 10485760 (10MB)

18.4.7. Controlling newlines in code snippets

Property: workbench.clipcreate.includenewline

When creating a Java code snippet, the newlines inside the editor are preserved by putting a \n character into the String declaration. Setting this property to false, will tell SQL Workbench/J not put any \n characters into the Java string.

Default: true

18.4.8. Controlling the concatenation character for code snippets

Property: workbench.clipcreate.concat

When creating a Java code snippet, each line is concatenated using the standard + operator. If your programming language uses a different concatenation character (e.g. &), this can be changed with this property.

Default: +

18.4.9. Controlling the prefix for code snippets

Property: workbench.clipcreate.codeprefix

When creating a Java code snippet, this is prefixed with String sql = . With this property you can adjust this prefix.

Default: String sql =

18.5. DbExplorer Settings

18.5.1. Controlling data display in the DbExplorer

Property: workbench.db.objecttype.selectable.[dbid]=value1,value2,...

The DbExplorer makes the "data" tab available based on the type of the selected object in the object list (second column). If the type returned by the JDBC driver is one of the types listed in this property, SQL Workbench/J assumes that it can issue a SELECT * FROM to retrieve data from that object.

Default values:
.defaultt=view,table,system view,system table
.postgres=view,table,system view,system table,sequence
.rdb=view,table,system,system view
The values in this property are not case-sensitiv (TABLE is the same as table)

18.5.2. Showing the currently focused control in the DbExplorer

Property: workbench.gui.dbobjects.showfocus

To indicate the table that has currently the focus in the DbExplorer, set this property to true. The currently focused table (or editor) will be enclosed with a yellow border to indicate this. To change the color of the border, define the color using the property workbench.gui.focusindicator.bordercolor. The value of that property are the three RGB values for the color.

Default value: false

18.6. Controlling sorting of data

When you sort data in the result set (by clicking on the column header) this is performed using Java built-in comparisons. For performance reasons the sorting of character values (Strings) is done based on the ASCII value of the characters which results in a case-sensitive sorting. Another disadvantage of this method is, that non-ascii characters might not be sorted correctly either.

The sorting of character values can be influenced with three different settings in workbench.settings that control the collation to be used. The first property enables the usage of a country and language aware collation sequence, the other two properties control the language and the country to be used for the collator.

18.6.1. Using language aware collations for sorting

Property: workbench.sort.usecollator

When you sort the result set, characters values will be sorted case-sensitiv by default. This is caused by the compareTo() method available in the Java environment which puts lower case characters in front of upper case characters when sorting. When setting workbench.sort.usecollator to true a language sensitive comparison is used to sort character values, that will treat lowercase and uppercase letters the same during sorting. As the policy on how special characters are sorted is different for each language, you can also define the language and country that should be used when initializing these sorting rules.

Default value: false

18.6.2. Sort language

Property: workbench.sort.language

If you want to use a language/country specific collation sequence, this property defines the sort language to be used.

Default value: en

18.6.3. Sort country

Property: workbench.sort.country

If you want to use a language/country specific collation sequence, this property defines the sort country to be used when setting up the collation sequence.

Default value: The country defined by your operating system

18.7. Database related settings

18.7.1. Verifying connection URLs

Property: workbench.db.verifydriverurl

Usually before connecting to the DBMS, SQL Workbench/J will call the acceptsUrl() method of the JDBC driver, to verify if the entered URL is correct and will be accepted by the driver. Some drivers have been reported to indicate an error in the URL even if they could connect successfully.

If you are seeing an error indicating that the URL is not accepted by the driver, but you are sure that the driver accepts the URL, then set this property to false

Default: true

18.7.2. Automatically connect the DataPumper

Property: workbench.datapumper.autoconnect

When opening the DataPumper it will connect to the current profile as the source connection. If you do not want the DataPumper to connect automatically set this property to false

Default: true

18.7.3. Controlling COMMIT for DDL statements

Property workbench.db.ddlneedscommit

A list of Database Identifiers that require a COMMIT after issuing DDL statements (such as CREATE TABLE

Default: PostgreSQL,Firebird,Cloudscape,Apache Derby,DB2/NT,Frontbase,Microsoft SQL Server

18.7.4. COMMIT/ROLLBACK behaviour

Property: workbench.db.usejdbccommit

Some DBMS return an error when COMMIT or ROLLBACK is sent as a regular command through the JDBC interface. If the DBMS is listed here, the JDBC functions commit() or rollback() will be used instead.

This is a comma separated list of Database Identifiers

Default: Firebird,Cloudscape

18.7.5. Generating constraints for SQL source

Property: workbench.db.inlineconstraints

This setting controls the generation of the CREATE TABLE source in the DbExplorer. This is a comma separated list of Database Identifiers that only support defining primary and foreign keys inside the CREATE TABLE statement.

Default: FirstSQL/J

18.7.6. Case sensitivity when comparing values

Property workbench.db.casesensitive

The search panel of the DbExplorer highlights matching values in the result tables. The highlighter needs to know whether string comparisons in the database are case sensitive in order to highlight the correct values. This is a comma separated list of Database Identifiers

Default: Oracle

18.7.7. Definining additional SQL keywords

Property: workbench.db.keywordlist.[dbid]=

For a DBID you can define a list of additional SQL keywords. This can be used if the JDBC driver does not implement getSQLKeywords() correctly.

No default

18.7.8. Definining SQL commands that may change the database

Property: workbench.db.updatingcommands for general SQL statements

Property: workbench.db.[dbid].updatingcommands for DBMS specific update statements

When enabling the read only or confirm update option in a connection profile, SQL Workbench/J assumes a default set of SQL commands that will change the database. With this property you can add additional keywords that should be considered as "updating commands". This is a comma separated list of keywords. The keywords may not contain whitespace.

No default

18.7.9. Database switch in DbExplorer

Property: workbench.dbexplorer.switchcatalog

When connected to a DBMS that supports multiple databases (catalogs) for the same connection, the DbExplorer displays a dropdown list with the available databases. Switching the selected catalog in the dropdown will trigger a switch of the current catalog/database if the DbExplorer uses its own connection. If you do not want to switch the database, but merely apply the new selection as a filter (which is always done, if the DbExplorer shares the connection with the other SQL panels) set this property to false.

Default: true

18.7.10. Filtering tables

Property: workbench.db.[dbid].exclude.tables

Whenever SQL Workbench/J retrieves a list of tables (e.g. the DbExplorer, auto completion, WbSchemaReport) certain tables can be filtered out by supplying a regular expression in this property. The default setting will filter Oracle tables that reside in the "Recycle bin". This setting can be applied on a per DBMS basis

Default value: workbench.db.oracle.exclude.tables=^BIN\\$.*

Note that you need to use two backslashes in the RegeEx.

18.7.11. Filtering synonyms

Property: workbench.db.[dbid].exclude.synonyms

The database explorer and the auto completion can display (Oracle public) synonyms. Some of these are usually not of interest to the end user. Therefor the list of displayed synonyms can be controlled. This property defines a regular expression. Each synonym that matches this regular expression, will be excluded from the list presented in the GUI.

Default value (for Oracle): ^AQ\\$.*|^MGMT\\$.*|^GV\\$.*|^EXF\\$.*|^KU\\$_.*|^WM\\$.*|^MRV_.*|^CWM_.*|^CWM2_.*|^WK\\$_.*|^CTX_.*

Note that you need to use two backslashes in the RegeEx.

18.7.12. Support for Oracle materialized views (snapshots)

Property: workbench.db.oracle.detectsnapshots

When displaying the list of tables in the database explorer Oracle materialized views (snapshots) are identified as tables by the Oracle JDBC driver. To identify a specific "table" as a materialized view, a second request to the database is necessary (accessing the system view ALL_MVIEWS). As this request can slow down the retrieval performance, this feature can be turned off. If for any reason the ALL_MVIEWS view cannot be accessed, this feature will be turned off until you re-connect to the database.

Default value: true

18.7.13. Fix type display for VARCHAR columns in Oracle

Property: workbench.db.oracle.fixcharsemantics

The Oracle driver does not report the size of VARCHAR2 columns correctly if the character semantic has been set to "char". The JDBC driver always returns the length in bytes. When this property is set to true, the length for those columns will be displayed correctly in the DbExplorer. As this means SQL Workbench/J is using it's own query to retrieve the table definition, this might not always yield the same results as the original statement from the Oracle driver. If your table definitions are not displayed correcly, set this value to false so that the original driver methods are used. The statement used by SQL Workbench/J is a bit faster then then original Oracle statement, as it does not use a LIKE predicate (which is required to comply with the JDBC specs).

Default value: true

18.7.14. Fix type display for NVARCHAR2 columns in Oracle

Property: workbench.db.oracle.fixnvarchartype

The Oracle driver does not report the type of NVARCHAR2 columns correctly. They are returned as Types.OTHER. If this property is enabled, than SQL Workbench/J is also using it's own SELECT statement to retrieve the table definition.

Default value: true

18.7.15. Defining a base directory for JDBC libraries

Property: workbench.libdir

A directory that contains the .jar files for the JDBC drivers. The value of this property can be referenced using %LibDir% in the driver's definition. The value for this can also be specified on the commandline.

No default

18.7.16. Defining keywords for date or timestamp input

Property: workbench.db.keyword.current_date

The "literals" that are accepted for DATE columns to identify the current date. Default values are current_date, today

Property: workbench.db.keyword.current_timestamp

The "literals" that are accepted for TIMESTAMP columns to identify the current date/time. Default values are current_timestamp,sysdate,systimestamp

Property: workbench.db.keyword.current_time

The "literals" that are accepted for TIME columns to identify the current time. Default values are current_time, now

18.7.17. Use Savepoints to guard DDL statement execution

Property: workbench.db.[dbid].ddl.usesavepoint

Some DBMS (such as PostgreSQL) cannot continue inside a transaction when an error occurs. A script with multiple DDL statements can therefor not run completely if one statement fails, even if you choose to ignore the error. If this property is set to true, SQL Workbench/J will set a savepoint before executing a DDL statement. In case of an error the savepoint will be rolledback and the transaction can continue.

Default value: true for PostgreSQL, false for others

18.7.18. Use Savepoints to update/insert mode for WbImport

Property: workbench.db.[dbid].import.usesavepoint

Some DBMS (such as PostgreSQL) cannot continue inside a transaction when an error occurs. When running WbImport in update,insert or insert,update mode, the first of the two statements needs to be rolled back in order to be able to continue the import. If this property is set to true, SQL Workbench/J will set a savepoint before executing the first (insert or update) statement. In case of an error the savepoint will be rolledback and WbImport will try to execute the second statement.

Default value: true for PostgreSQL, false for others

18.7.19. Ignore errors during data retrieval

Property: workbench.db.ignore.readerror

When retrieving data (e.g. using a SELECT statement) errors that are reported by the driver will be displayed to the user. The retrieval will be terminated. If you want to ignore errors and replace the data that could not be retrieved with a NULL value, set this property to true.

Using this parameter is not recommended as it might produce results that do not reflect the data as it is stored in the database.

Default value: false

18.8. SQL Execution related settings

18.8.1. Maximum script size for in-memory script execution

Property: workbench.sql.script.inmemory.maxsize

This setting controls the size up to which files that are executed in batch mode or via the WbInclude command are read into memory. Files exceeding this size are not read into memory but processed statement by statement. When a file is not read into memory the automatic detection of the alternate delimiter does not work any longer. The size is given in bytes.

Default: 1048576

18.8.2. Ignoring certain SQL commands

Property: workbench.db.ignore.[dbid]=

For a DBMS identifier you can define a list of commands that are simply ignored by SQL Workbench/J. This is useful e.g. for Oracle, when you want to run scripts that are intended for SQL*Plus. If those scripts contain special SQL*Plus commands (that are not understood by the Oracle server as SQL*Plus executes these commands directly) they would fail in SQL Workbench/J. If those commands are simply ignored and not send to the server, the scripts can run without modification.

The DBID is the lower case DBMS identifier (see above) with all special characters like \/,.$[]() and spaces removed. The actual ID that is used to retrieve this list is reported as an informational message in the log file (Using DBID=)

Default: workbench.db.ignore.oracle=prompt,exit,whenever

18.8.3. Enabling short WbInclude

Property: workbench.db.supportshortinclude

By default the WbInclude command can be shortened using the @ sign. This behaviour is disabled for MS SQL to avoid conflicts with parameter definitions in stored procedures. This property contains a list of DBIDs for which this should be enabled. To enable this for all DBMS, simply use * as the value for this property.

Default: oracle, rdb, hsqldb, postgresql, mysql, adaptive_server_anywhere, cloudscape, apache_derby

18.8.4. Check for single line commands without delimiter

Property: workbench.db.checksinglelinecmd

When parsing a SQL script, SQL Workbench/J supports statements that are put into a single line without a delimiter. This is primarily intended for compatibility with Oracle's SQL*Plus and is not enabled for other database systems (starting with build 86).

Default: oracle

18.9. Default settings for Export/Import

For some switches of the WbExport and WbImport command, you can override the default values used by SQL Workbench/J in case you do not provide the parameter. The default values mentioned in this chapter apply, if no property is defined in the workbench.settings file. The current default for these properties is displayed in the help message when you run the corresponding command without any parameters.

18.9.1. Controlling header lines in text exports

Property: workbench.export.text.default.header

This property controls whether default value for the -header parameter of the WbExport command.

Default: false

18.9.2. Controlling XML export format

Property: workbench.export.xml.default.verbose

This property controls whether XML exports are done using verbose XML or short tags and only basic formatting. This property sets the default value of the -verbosexml parameter for the WbExport command.

Default: true

18.9.3. Setting default for WbImport's -continueOnError parameter

Property: workbench.import.default.continue

This property controls the default value for the parameter -continueOnError of the WbImport command.

18.9.4. Setting default for WbImport's -header parameter

Property: workbench.import.default.header

This property controls the default value for the parameter -header of the WbImport command.

Default: true

18.9.5. Setting default for WbImport's -multiLine parameter

Property: workbench.import.default.multilinerecord

This property controls the default value for the parameter -multiLine of the WbImport command.

Default: false

18.9.6. Setting default for WbImport's -trimValues parameter

Property: workbench.import.default.trimvalues

This property controls the default value for the parameter -trimValues of the WbImport command.

Default: false

18.10. Controlling the log file

18.10.1. Log level

Property: workbench.log.level

Set the log level for the log file. Valid values are

  • DEBUG

  • INFO

  • WARN

  • ERROR

Default: INFO

18.10.2. Log format

Property: workbench.log.format

Define the format of the log messages. The following placeholders are supported:

  • {type}

  • {timestamp}

  • {message}

  • {error}

  • {source}

  • {stacktrace}

The order of the placeholders defines the order in the log file, except for the stacktrace which will always be printed after the message. If the log level is set to debug, the stacktrace will always be displayed even if it is not included in the format string.

Default: {type} {timestamp} {message} {error}

18.10.3. Logging to the console

Property: workbench.log.console

Defines whether SQL Workbench/J logs messages additionally to the standard error output

Default: false

18.10.4. Logging SQL used for retrieving metadata

Property: workbench.dbmetadata.logsql

If this is set to truethe SQL queries used to retrieve DBMS specific meta data (such as view/procedure/trigger source, defined triggers/views) will be logged with level INFO. This can be used to debug customized SQL statements for DBMS's which are not (yet) preconfigured.

Default: false

18.11. Settings related to SQL statement generation

18.11.1. Controlling schema usage in generated SQL statements

Property: workbench.sql.ignoreschema.[dbid]=schema1,...

Define a list of schemas that should be ignored for the DB ID When SQL Workbench/J creates DML statements and the current table is reported to belong to any of the schemas listed in this property, the schema will not be used to qualify the table. To ignore all schemas use a *, e.g. workbench.sql.ignoreschema.rdb=*. In this case, table names will never be prefixed with the schema name reported by the JDBC driver. The values specified in this property are case sensitiv.

Note that for Oracle, tables that are owned by the current user will never be prefixed with the owner.

Default values:
.oracle=PUBLIC
.postgres=public
.rdb=*

18.11.2. System generated names for contraints

Property: workbench.db.[dbid].constraints.systemname

Defines a regular expression to identify system generated constraint names. If a constraint name is identified as beeing system generated, it is treated as if no name was defined, when e.g. creating the SQL for a table. Whether or not SQL Workbench/J then generates a name for the constraint can be controlled in the options for the DbExplorer.

Default values:
oracle: ^SYS_.*
mysql: PRIMARY

18.11.3. Controlling the chunk size for WbDataDiff

Property: workbench.sql.sync.chunksize

Controls the number of rows that are retrieved from the target table when running WbDataDiff or WbCopy with the -syncDelete=true parameter.

Default value: 25

18.12. Filter settings

18.12.1. Controlling the number of items in the pick list

Property: workbench.gui.filter.mru.maxsize

When saving a filter to an external file, the pick list next to the filter icon will offer a drop down that contains the most recently used filter definitions. This setting will control the maximum size of that dropdown.

Default value: 15