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
SQL>

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.

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.

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

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

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

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

SELECT *
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
SQL>

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
SQL>

This will limit the number of rows retrieved to 42.

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

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
SQL>

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.

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.

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

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

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

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
SQL> 

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:

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.