19. Using SQL Workbench/J in console mode

19.1. Entering statements
19.2. Exiting console mode
19.3. Setting or changing the connection
19.4. Displaying result sets
19.5. Running SQL scripts that produce a result
19.6. Controlling the number of rows displayed
19.7. Controlling the query timeout
19.8. Managing connection profiles
19.9. Using an external pager
19.10. PostgreSQL psql commands

SQL Workbench/J can also be used from the command line without starting the GUI, e.g. when you only have a console window (Putty, SSH) to access the database. In that case you can either run scripts using the batch mode, or start SQL Workbench/J in console mode, where you can run statements interactively, similar to the GUI mode (but of course with less comfortable editing possibilities).

When using SQL Workbench/J in console mode, you cannot use the Windows® launcher. Please use the supplied scripts sqlwbconsole.cmd (Windows® batch file) or sqlwbconsole.sh (Unix shell script) to start the console mode.

When starting SQL Workbench/J in console mode, you can define the connection using a profile name or specifying the connection properties directly . Additionally you can specify all parameters that can be used in batch mode.

The following batch mode parameters will be ignored in console mode:

script - you cannot specify a script to be run during startup. If you want to run a script in console mode, use the command WbInclude.
encoding - as you cannot specify a script, the encoding parameter is ignored as well
displayResult - always true in console mode
cleanupSuccess and cleanupError- as no script is run, there is no "end of script" after which a "cleanup" is necessary

19.1. Entering statements

After starting the console mode, SQL Workbench/J displays the prompt SQL> where you can enter SQL statements. The statement will not be sent to the database until it is either terminated with the standard semicolon, or with the alternate delimiter (that can be specified either in the used connection profile or on the commandline when starting the console mode).

As long as a statement is not complete, the prompt will change to ..>. Once a delimiter is identified the statement(s) are sent to the database.

SQL> SELECT * [enter]
..>FROM person;

A delimiter is only recognized at the end of the input line, thus you can enter more than one statement on a line (or multiple lines) if the intermediate delimiter is not at the end of one of the input lines:

SQL> DELETE FROM person; rollback;
DELETE executed successfully
4 row(s) affected.

ROLLBACK executed successfully

19.2. Exiting console mode

To exit the application in console mode, enter exit when the default prompt is displayed. If the "continuation prompt" (..>) is displayed, this will not terminate the application. The keyword exit must not be terminated with a semicolon.

19.3. Setting or changing the connection

If you did not specify a connection on the command line when starting the console, you can set or change the current connection in console mode using the WbConnect command. Using WbConnect in console mode will automatically close the current connection, before establishing the new connection.

To disconnect the current connection in console mode, run the statement WbDisconnect. Note that this statement is only available in console mode.

19.4. Displaying result sets

If you are running SELECT statements in console mode, the result is displayed on the screen in "tabular" format. Note that SQL Workbench/J reads the whole result into memory in order to be to adjust the column widths to the displayed data.

You can disable the buffering of the results using the command line parameter bufferResults=false. In that case, the width of the displayed columns will not be adjusted properly. The column widths are taken from the information returned by the driver which typically results is a much larger display than needed.

The output in tabular format (if results are buffered) looks like this:

SQL> select id, firstname, lastname, comment from person;
id | firstname | lastname   | comment
1  | Arthur    | Dent       | this is a comment
2  | Zaphod    | Beeblebrox |
4  | Mary      | Moviestar  | comment
3  | Tricia    | McMillian  | test1

(4 Rows)

If the size of the column values exceed the console's width the display will be wrapped, which makes it hard to read. In that case, you can switch the output so that each column is printed on a single line.

This is done by running the statement: WbDisplay record

SQL> WbDisplay record;
Display changed to single record format
Execution time: 0.0s
SQL> select id, firstname, lastname, comment from person;
---- [Row 1] -------------------------------
id        : 1
firstname : Arthur
lastname  : Dent
comment   : this is a very long comment that would not fit onto the screen when printed as the last column
---- [Row 2] -------------------------------
id        : 2
firstname : Zaphod
lastname  : Beeblebrox
comment   :
---- [Row 3] -------------------------------
id        : 4
firstname : Mary
lastname  : Moviestar
comment   :
---- [Row 4] -------------------------------
id        : 3
firstname : Tricia
lastname  : McMillian
comment   :

(4 Rows)

To switch back to the "tabular" display, use: WbDisplay tab.

19.5. Running SQL scripts that produce a result

Normally when executing a SQL script using WbInclude, the result of such a script (e.g. when it contains a SELECT statement) is not displayed on the console.

To run such a script, use the command WbRun instead of WbInclude. If you have the following SQL script (named select_person.sql):

FROM person;

and execute that using the WbInclude command:

SQL> WbInclude -file=select_person.sql;
SQL> Execution time: 0.063s

If you execute this script using WbRun the result of the script is displayed:

SQL> WbRun select_people.sql;
select *
from person;

id | firstname | lastname
1  | Arthur    | Dent
4  | Mary      | Moviestar
2  | Zaphod    | Beeblebrox
3  | Tricia    | McMillian

(4 Rows)
Execution time: 0.078s

19.6. Controlling the number of rows displayed

In the SQL Workbench/J GUI window, you can limit the reusult of a query by entering a value in the "Max. Rows" field. If you want to limit the number of rows in console mode you can do this by running the statement

SQL> set maxrows 42;
MAXROWS set to 42
Execution time: 0.0s

This will limit the number of rows retrieved to 42.

SET MAXROWS has no effect when run as a post-connect script.

19.7. Controlling the query timeout

To set the query timeout in console mode, you can run the following statement

SQL> set timeout 42;
TIMEOUT set to 42
Execution time: 0.0s

This will set a query timeout of 42 seconds. Note that not all JDBC drivers support a query timout.

SET TIMEOUT has no effect when run as a post-connect script.

19.8. Managing connection profiles

Connection profiles can be managed through several SQL Workbench/J specific commands. They are primarily intended to be used in console mode, but can also be used when running in GUI mode.

19.8.1. List available profiles - WbListProfiles

The command WbListProfiles will display a list of all displayed profiles

19.8.2. Delete a profile - WbDeleteProfile

You can delete an existing profile using the command WbDeleteProfile. The command takes one argument, which is the name of the profile. If the name is unique across all profile groups you don't have to specify a group name. If the name is not unique, you need to include the group name, e.g.

SQL> WbDeleteProfile {MyGroup}/SQL Server
Do you really want to delete the profile '{MyGroup}/SQL Server'? (Yes/No) yes
Profile '{MyGroup}/SQL Server' deleted

As the profile name is the only parameter to this command, no quoting is necessary. Everything after the keyword WbDeleteProfile will be assumed to be the profile's name

All profiles are automatically saved after executing WbDeleteProfile.

19.8.3. Save the current profile - WbStoreProfile

Saves the currently active connection as a new connection profile. This can be used when SQL Workbench/J if the connection information was passsed using individual parameters (-url, -username and so on) either on the commandline or through WbConnect.

SQL> WbStoreProfile {MyGroup}/PostgreSQL Production
Profile '{MyGroup}/PostgreSQL Production' added

If no parameter switch is given, everything after the keyword WbDeleteProfile will be assumed to be the profile's name. By default the password is not saved.

Alternatively the command supports the parameters name and savePassword. If you want to store the password in the profile, the version using parameters must be used:

SQL> WbStoreProfile -name="{MyGroup}/DevelopmentServer" -savePassword=true
Profile '{MyGroup}/DevelopmentServer' added

If the current connection references a JDBC driver that is not already defined, a new entry for the driver defintions is created referencing the library that was passed on the commandline.

All profiles are automatically saved after executing WbStoreProfile.

19.8.4. Create a new connection profile - WbCreateProfile

WbCreateProfile can be used to create a new profile without an existing connection. It accepts the same parameters as WbConnect plus an additional parameter to define the name of the new profile.

SQL> WbCreateProfile -name="Postgres" -profileGroup=DBA -savePassword=true -username=postgres -password=secret
..> -url=jdbc:postgresql://localhost/postgres
..> -driver=org.postgresql.Driver
..> -driverJar=c:/etc/libs/postgres/postgresql-9.4-1206-jdbc42.jar;
Profile '{DBA}/Postgres' added

19.9. Using an external pager

By default all rows of the result of a query will be printed to the console without pausing. To scroll through the result, configure the console to enable scrolling back to previous rows.

Alternatively, it's possible to enable the use of an external pager tool, that controls the output of the result. Popular tools for this are the command line tools more, or less

To enable the use of an external pager, set the property workbench.console.pager to the full path of the pager's executable. This can also be done through WbSetConfig. pager is recognized as an abbreviation of workbench.console.pager.

SQL> WbSetConfig pager=/usr/bin/more;

If the specified executable does not exist, the definition of the pager will be ignored.

19.10. PostgreSQL psql commands

Some of the SQL Workbench/J specific commands can be abbreviated using the command syntax from PostgreSQL's command line client psql. This is only implemented for very few commands and most of them don't work exactly the same way as the PostgreSQL command.

The following commands are available:

CommandDescription / SQL Workbench/J command
\qQuit console mode (equivalent to exit
\s WbHistory - display the statement history
\iWbRun - Run a SQL script
\dWbList - Show the list of available tables
\lWbListCat - Show the list of databases
\dnWbListSchemas - Show the list of schemas
\dtDESCRIBE - Show the structure of a table
\dfWbListProcs - Show the list of stored procedures
\sfWbProcSource - Show the source code of a stored procedure or function
\gRun the last entered statement again
\!WbSysExec - Run a commandline program

Even though those commands look like the psql commands, they don't work exactly like them. Most importantly they don't accept the parameters that psql supports. Parameters need to be passed as if the regular SQL Workbench/J command had been used.