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.
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.
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.
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
workbench.workspace.autosave
If this is enabled, the current workspace will be saved automatically, when a SQL statement is executed.
Default: false
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
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
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
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
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
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) |
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
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
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)
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
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: +
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 =
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 |
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
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.
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
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
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
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
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
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
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
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
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
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
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
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.
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.
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
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
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
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
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
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
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
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
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
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
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 DBID
s 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
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
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.
Property: workbench.export.text.default.header
This property controls whether default value for the -header
parameter of the WbExport command.
Default: false
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
Property: workbench.import.default.continue
This property controls the default value for the parameter -continueOnError
of the WbImport command.
Property: workbench.import.default.header
This property controls the default value for the parameter -header
of the WbImport command.
Default: true
Property: workbench.import.default.multilinerecord
This property controls the default value for the parameter -multiLine
of the WbImport command.
Default: false
Property: workbench.import.default.trimvalues
This property controls the default value for the parameter -trimValues
of the WbImport command.
Default: false
Property: workbench.log.level
Set the log level for the log file. Valid values are
DEBUG
INFO
WARN
ERROR
Default: INFO
Property: workbench.log.format
Define the format of the log messages. The following placeholders are supported:
{type}
{timestamp}
{message}
{error}
{source}
{stacktrace}
Default: {type} {timestamp} {message} {error}
Property: workbench.log.console
Defines whether SQL Workbench/J logs messages additionally to the standard error output
Default: false
Property: workbench.dbmetadata.logsql
If this is set to true
the 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
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=* |
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 |
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
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