Copyright © 2002, 2003, 2004, 2005 Marc Prud'hommeaux
Copyright © 2006-2019 Julian Hyde
Table of Contents
List of Examples
Table of Contents
SQLLine is a pure-Java console based utility for connecting
to relational databases and executing SQL commands. It is
similar to other command-line database access utilities like
sqlplus for Oracle, mysql
for MySQL, and isql for Sybase/SQL Server.
Since it is pure Java, it is platform independent, and will
run on any platform that can run Java 8 or higher.
SQLLine is distributed under the BSD License, meaning that you are free to redistribute, modify, or sell the software with almost no restrictions. For the text of the license, see the license text.
For information on obtaining the software under another license, contact the copyright holder: mwp1@cornell.edu.
SQLLine could be found at Maven Central. SQLLine's source could be found at SQLLine source. SQLLine's releases could be found at SQLLine releases.
Table of Contents
SQLLine depends on the following software:
Java™ Virtual Machine - SQLLine is a pure java program, and requires Java version 8 or higher in order to run. The latest JVM can be downloaded from http://java.sun.com.
JLine3 - SQLLine uses the JLine3 console reader for command line editing, tab-completion, command history, highlighting and prompting. It can be downloaded from Maven Central. Version 3.9.0 or higher is required. Jline3's source could be found at JLine3 source. JLine3's releases could be found at maven central JLine3 releases.
JDBC™ Driver(s) - Since SQLLine uses the Java Database Connectivity package to connect to your database, you need to obtain the correct JDBC driver libraries for your database. Any JDBC compliant driver can be used for this purpose. Drivers can be located by referring to the section on known JDBC drivers, or else by referring to your database vendor's web site.
There are currently no set standards for installing Java software on different platforms. The simplest method for installing SQLLine in a platform independent way is as follows:
Create a new directory/folder where you like. This
will be referred to as sqllinedir.
Download sqlline.jar into
sqllinedir.
Download the latest jline-terminal.jar,
jline-reader.jar,
jline-builtins.jar from
Maven Central into
sqllinedir.
For the case of Windows OS additionally it is required to download
into sqllinedir
the latest jline-terminal-jna.jar,
jline-terminal-jansi.jar,
jansi.jar,
and version 4.2.2 jna.jar
Download your database's JDBC driver files into
sqllinedir. Note that some JDBC
drivers require some installation, such as uncompressing
or unzipping.
Table of Contents
SQLLine can be run with the following command:
java
{-cp "sqllinedir/*"}
{-jar sqllinedir/sqlline.jar}
[options...]
[properties files...]
For example, if your sqllinedir is
/usr/share/java/ (this is typical for
Debian Linux), then you might run:
java -cp "/usr/share/java/*" -jar /usr/share/java/sqlline.jar
Once you have started SQLLine, you will a prompt that reads "sqlline> ". From here, you can enter either SQLLine commands (beginning with a "!" character), or a SQL statement (if you are currently connected to a database). For example, entering "!help" will display a list of all the available commands.
SQLLine is only useful once you are connected to a database. In order to do so, you need to use the "!connect" command, with an optional list of properties definition with the JDBC URL of the database you are going to connect to, followed by the username and password (followed optionally by the driver class name). The JDBC URL is specific to the JDBC driver that you are using for your database, but will always start with "jdbc:", and usually has the machine name of the database and the name of the database itself as part of the string. Quotes (both single and double) could be used in case of url, username, password contain spaces. Consult your driver's documentation for details on the URL to use.
For example, to connect to an Oracle database, you might enter:
sqlline> !connect jdbc:oracle:thin:@localhost:1521:mydb scott tiger oracle.jdbc.driver.OracleDriver
Connected to: Oracle (version Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production)
Driver: Oracle JDBC driver (version 9.0.1.0.0)
Autocommit status: true
0: jdbc:oracle:thin:@localhost:1521:mydb>
Once you are connected, the prompt will change the reflect URL of the current connection.
SQLLine can also accept command line options to connect to a database. If a file is specified on the command line, it will be treated as a properties file, and try to connect to the database specified therein. For details, see the properties command documentation.
SQLLine allows you to have multiple connections to databases (either the same database, or a different database) open at the same time. Issuing the connect command multiple times will add the new connection to the list of open connections. The "current" connection (against which all SQL statements will be executed) can be switched with the go command. The currently open connections can be listed with the list command.
Once you are connected to a database, you can begin SQL statements against the current connection. For example:
Example 5.1. Issuing SQL commands
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM MYTABLE;
+-----+
| ID |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
+-----+
4 rows selected (0.012 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb>
A query or command need not be given all on a single line, so lengthy queries that require several lines are not a problem. SQLLine determines where the statement ends by looking for the terminating semicolon (SQL command) or newline (SQLLine command), not by looking for the end of the input line. (In other words, SQLLine accepts free-format input: it collects input lines but does not execute them until it sees the terminating symbol.)
Example 5.2. Multiline SQL
0: jdbc:calcite:model=example/csv/target/test> select 'multiline ;
. . . . . . . . . . . . . . . . . . . . quote> value'
. . . . . . . . . . . . . . . . . . semicolon> /*
. . . . . . . . . . . . . . . . . . . . . .*/> "just 'a'
. . . . . . . . . . . . . . . . . . . . . .*/> comment";
. . . . . . . . . . . . . . . . . . . . . .*/> */
. . . . . . . . . . . . . . . . . . semicolon> ;
+-------------------+
| EXPR$0 |
+-------------------+
| multiline ;
value |
+-------------------+
1 row selected (0.035 seconds)
0: jdbc:calcite:model=example/csv/target/test>
The prompt provides valuable feedback showing what SQLLine is waiting for.
The following table shows each of the prompts one may see
and summarizes what they mean about the state that SQLLine is in
Table 5.1.
| Prompt | Meaning |
|---|---|
| sqlline> | Ready for a new query |
| semicolon> | Waiting for next line of multiple-line query, waiting for completion of query with semicolon (;) |
| quote> | Waiting for next line, waiting for completion of a string that began with a single quote (') |
| dquote> | Waiting for next line, waiting for completion of a string that began with a double quote (") |
| `> | Waiting for next line, waiting for completion of a string that began with (`) |
| */> | Waiting for next line, waiting for completion of a multi-line comment that began with "/*" |
| (> | Waiting for next line, waiting for completion of a string that began with a round bracket, "(" |
| [> | Waiting for next line, waiting for completion of a string that began with a square bracket, "[" |
| extra ')'> | There is an extra round bracket ")", that is not opened with "(" |
| extra ']'> | There is an extra square bracket "]", that is not opened with "[" |
SQLLine allows command-line completion using the tab key. This will be familiar for users of such popular shells as bash and tcsh. In general, hitting the TAB key when part of the way through typing a command will fill in the rest of the command, or else will display a selection of appropriate possibilities.
-u
{<database url>}
The JDBC URL to connect to. In case of having spaces, semicolumns inside url please use quotes (double or single) e.g.
./sqlline -u "jdbc:calcite:schemaFactory=org.apache.calcite.adapter.druid.DruidSchemaFactory; schema.url=http://localhost:8082; schema.coordinatorUrl=http://localhost:8081"
-d
{<driver class>}
The driver class to use. For instance
./sqlline -u jdbc:mysql://localhost:3306/scott -n user -p password -d com.mysql.jdbc.Driver
-ch
{<command handler>[,<command handler>]*}
A custom command handler to use. In case of several command handlers use comma as separator, for instance
./sqlline -ch sqlline.commandhandler.HelloWorldCommandHandler,org.apache.calcite.HelloWorld2CommandHandler
-ac
{<class name>}
Application configuration class name, for instance
./sqlline -ac sqlline.extensions.CustomApplication
--<property_name>=[value]
Set the specified value for property_name. For instance
./sqlline --verbose=true --mode=vi --nullValue=123
For details, see the properties command documentation.
Table of Contents
all — Execute SQL against all active connections
!all
{SQL statement}
1: jdbc:mysql://localhost/mydb> !list
2 active connections:
#0 open jdbc:oracle:thin:@localhost:1521:mydb
#1 open jdbc:mysql://localhost/mydb
1: jdbc:mysql://localhost/mydb> !all DELETE FROM COMPANY;
Executing SQL against: jdbc:oracle:thin:@localhost:1521:mydb
4 rows affected (0.004 seconds)
Executing SQL against: jdbc:mysql://localhost/mydb
1 row affected (3.187 seconds)
1: jdbc:mysql://localhost/mydb>
autocommit — Enable or disable automatic transaction commit mode
!autocommit
{on/off}
Set autocommit mode on or off. When autocommit is on, every individual SQL statement will be committed after it is issued. Otherwise, the commit command will need to be issued in order to commit any changes to the database.
batch — Start or execute a batch of SQL statements
!batch
Start or execute a batch of SQL statements. The first time the statement is issued, subsequent SQL statements will be deferred until the batch command is issued again. Databases can frequently optimize multiple batched statements (e.g., for bulk data loading) in order to speed up execution.
0: jdbc:oracle:thin:@localhost:1521:mydb> !batch
Batching SQL statements. Run "batch" again to execute the batch.
0: jdbc:oracle:thin:@localhost:1521:mydb> INSERT INTO MYTABLE (ID) VALUES (1);
0: jdbc:oracle:thin:@localhost:1521:mydb> INSERT INTO MYTABLE (ID) VALUES (2);
0: jdbc:oracle:thin:@localhost:1521:mydb> INSERT INTO MYTABLE (ID) VALUES (3);
0: jdbc:oracle:thin:@localhost:1521:mydb> INSERT INTO MYTABLE (ID) VALUES (4);
0: jdbc:oracle:thin:@localhost:1521:mydb> !batch
Running batched SQL statements...
COUNT STATEMENT
1 INSERT INTO MYTABLE (ID) VALUES (1)
1 INSERT INTO MYTABLE (ID) VALUES (2)
1 INSERT INTO MYTABLE (ID) VALUES (3)
1 INSERT INTO MYTABLE (ID) VALUES (4)
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM MYTABLE;
+-----+
| ID |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
+-----+
4 rows selected (0.012 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb>
brief — Enable terse output mode
!brief
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM BOGUS_TABLE;
Error: ORA-00942: table or view does not exist (state=42000,code=942)
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2110)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:909)
at sqlline.SqlLine$Commands.sql(SqlLine.java:3183)
at sqlline.SqlLine.dispatch(SqlLine.java:771)
at sqlline.SqlLine.begin(SqlLine.java:634)
at sqlline.SqlLine.main(SqlLine.java:332)
0: jdbc:oracle:thin:@localhost:1521:mydb> !brief
verbose: off
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM BOGUS_TABLE;
Error: ORA-00942: table or view does not exist (state=42000,code=942)
0: jdbc:oracle:thin:@localhost:1521:mydb>
columns — Display columns of a table
!columns
{table name}
0: jdbc:oracle:thin:@localhost:1521:mydb> !columns EMPLOYEE
+-------------+--------------+------------+------------+
| TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME |
+-------------+--------------+------------+------------+
| EMPLOYEE | ID | 3 | NUMBER |
| EMPLOYEE | FIRST_NAME | 12 | VARCHAR2 |
| EMPLOYEE | LAST_NAME | 12 | VARCHAR2 |
+-------------+--------------+------------+------------+
0: jdbc:oracle:thin:@localhost:1521:mydb>
commit — Commit the current transaction
!commit
0: jdbc:oracle:thin:@localhost:1521:mydb> !autocommit off
Autocommit status: false
0: jdbc:oracle:thin:@localhost:1521:mydb> INSERT INTO EMPLOYEE (ID, FIRST_NAME, LAST_NAME) VALUES (1, 'Bob', 'Smith');
1 row affected (1.254 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !commit
Commit complete (0.661 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM EMPLOYEE;
+--------------+-------------+------------+
| ID | FIRST_NAME | LAST_NAME |
+--------------+-------------+------------+
| 1 | Bob | Smith |
+--------------+-------------+------------+
1 row selected (0.309 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb>
connect — Connect to a database
!connect
{url}
{username}
{password}
[driver class]
sqlline> !connect jdbc:oracle:thin:@localhost:1521:mydb scott tiger oracle.jdbc.driver.OracleDriver
Connected to: Oracle (version Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production)
Driver: Oracle JDBC driver (version 9.0.1.0.0)
Autocommit status: true
0: jdbc:oracle:thin:@localhost:1521:mydb>
dbinfo — Provide information about the current database
!dbinfo
0: jdbc:oracle:thin:@localhost:1521:mydb> !dbinfo
allProceduresAreCallable false
allTablesAreSelectable false
dataDefinitionCausesTransactionCommit true
dataDefinitionIgnoredInTransactions false
doesMaxRowSizeIncludeBlobs true
getCatalogSeparator
getCatalogTerm
getDatabaseProductName Oracle
getDatabaseProductVersion Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
getDefaultTransactionIsolation 2
getDriverMajorVersion 9
getDriverMinorVersion 0
getDriverName Oracle JDBC driver
getDriverVersion 9.0.1.0.0
getExtraNameCharacters $#
getIdentifierQuoteString "
getMaxBinaryLiteralLength 1000
getMaxCatalogNameLength 0
getMaxCharLiteralLength 2000
getMaxColumnNameLength 30
getMaxColumnsInGroupBy 0
getMaxColumnsInIndex 32
getMaxColumnsInOrderBy 0
getMaxColumnsInSelect 0
getMaxColumnsInTable 1000
getMaxConnections 0
getMaxCursorNameLength 0
getMaxIndexLength 0
getMaxProcedureNameLength 30
getMaxRowSize 2000
getMaxSchemaNameLength 30
getMaxStatementLength 65535
getMaxStatements 0
getMaxTableNameLength 30
getMaxTablesInSelect 0
getMaxUserNameLength 30
getNumericFunctions ABS,CEIL,COS,COSH,EXP,FLOOR,
LN,LOG,MOD,POWER,ROUND,SIGN,
SIN,SINH,SQRT,TAN,TANH,TRUNC,
AVG,COUNT,GLB,LUB,MAX,MIN,
STDDEV,SUM,VARIANCE
getProcedureTerm procedure
getSchemaTerm schema
getSearchStringEscape //
getSQLKeywords ACCESS, ADD, ALTER, AUDIT,
CLUSTER, COLUMN, COMMENT,
COMPRESS, CONNECT, DATE, DROP,
EXCLUSIVE, FILE, IDENTIFIED,
IMMEDIATE, INCREMENT, INDEX,
INITIAL, INTERSECT,
LEVEL, LOCK, LONG, MAXEXTENTS,
MINUS, MODE, NOAUDIT,
NOCOMPRESS, NOWAIT, NUMBER,
OFFLINE, ONLINE, PCTFREE,
PRIOR,
all_PL_SQL_reserved_ words
getStringFunctions CHR, INITCAP, LOWER, LPAD,
LTRIM, NLS,_INITCAP,
NLS,_LOWER, NLS,_UPPER,
REPLACE, RPAD, RTRIM, SOUNDEX,
SUBSTR, SUBSTRB, TRANSLATE,
UPPER, ASCII, INSTR, INSTRB,
LENGTH, LENGTHB, NLSSORT,
CHARTOROWID, CONVERT,
HEXTORAW, RAWTOHEX,
ROWIDTOCHAR,
TO_CHAR, TO_DATE, TO_LABEL,
TO_MULTI_BYTE, TO_NUMBER,
TO_SINGLE_BYTE
getSystemFunctions DUMP, GREATEST, GREATEST_LB,
LEAST, LEAST_UB, NVL, UID,
USER, USERENV, VSIZE
getTimeDateFunctions ADD_MONTHS, LAST_DAY,
MONTHS_BETWEEN, NEW_TIME,
NEXT_DAY, ROUND, SYSDATE,
TRUNC
getURL jdbc:oracle:thin:@localhost:1521:mydb
getUserName MYDB
isCatalogAtStart false
isReadOnly false
nullPlusNonNullIsNull true
nullsAreSortedAtEnd false
nullsAreSortedAtStart false
nullsAreSortedHigh false
nullsAreSortedLow true
storesLowerCaseIdentifiers false
storesLowerCaseQuotedIdentifiers false
storesMixedCaseIdentifiers false
storesMixedCaseQuotedIdentifiers true
storesUpperCaseIdentifiers true
storesUpperCaseQuotedIdentifiers false
supportsAlterTableWithAddColumn true
supportsAlterTableWithDropColumn false
supportsANSI92EntryLevelSQL true
supportsANSI92FullSQL false
supportsANSI92IntermediateSQL false
supportsBatchUpdates true
supportsCatalogsInDataManipulation false
supportsCatalogsInIndexDefinitions false
supportsCatalogsInPrivilegeDefinitions false
supportsCatalogsInProcedureCalls false
supportsCatalogsInTableDefinitions false
supportsColumnAliasing true
supportsConvert true
supportsCoreSQLGrammar true
supportsCorrelatedSubqueries true
supportsDataDefinitionAndDataManipulationTransactionstrue
supportsDataManipulationTransactionsOnly true
supportsDifferentTableCorrelationNames true
supportsExpressionsInOrderBy true
supportsExtendedSQLGrammar true
supportsFullOuterJoins true
supportsGroupBy true
supportsGroupByBeyondSelect true
supportsGroupByUnrelated true
supportsIntegrityEnhancementFacility true
supportsLikeEscapeClause true
supportsLimitedOuterJoins true
supportsMinimumSQLGrammar true
supportsMixedCaseIdentifiers false
supportsMixedCaseQuotedIdentifiers true
supportsMultipleResultSets false
supportsMultipleTransactions true
supportsNonNullableColumns true
supportsOpenCursorsAcrossCommit false
supportsOpenCursorsAcrossRollback false
supportsOpenStatementsAcrossCommit false
supportsOpenStatementsAcrossRollback false
supportsOrderByUnrelated true
supportsOuterJoins true
supportsPositionedDelete true
supportsPositionedUpdate true
supportsSchemasInDataManipulation true
supportsSchemasInIndexDefinitions true
supportsSchemasInPrivilegeDefinitions true
supportsSchemasInProcedureCalls true
supportsSchemasInTableDefinitions true
supportsSelectForUpdate true
supportsStoredProcedures true
supportsSubqueriesInComparisons true
supportsSubqueriesInExists true
supportsSubqueriesInIns true
supportsSubqueriesInQuantifieds true
supportsTableCorrelationNames true
supportsTransactions true
supportsUnion true
supportsUnionAll true
usesLocalFilePerTable false
usesLocalFiles false
0: jdbc:oracle:thin:@localhost:1521:mydb>
describe — Describe a table
!describe
[table name]
Synonym for columns (if an argument is specified) or tables (if no argument is specified).
0: jdbc:oracle:thin:@localhost:1521:mydb> !describe EMPLOYEE
+--------------+------------+-------------+-----------+------------+
| TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | DATA_TYPE | TYPE_NAME |
+--------------+------------+-------------+-----------+------------+
| MYDB | EMPLOYEE | ID | 3 | NUMBER |
| MYDB | EMPLOYEE | FIRST_NAME | 12 | VARCHAR2 |
| MYDB | EMPLOYEE | LAST_NAME | 12 | VARCHAR2 |
+--------------+------------+-------------+-----------+------------+
0: jdbc:oracle:thin:@localhost:1521:mydb>
dropall — Drop all tables in the database
!dropall
0: jdbc:oracle:thin:@localhost:1521:mydb> !dropall
Really drop every table in the database? (y/n) y
1/5 DROP TABLE COMPANY;
2/5 DROP TABLE EMPLOYEE;
3/5 DROP TABLE PROJECTS;
4/5 DROP TABLE EMPLOYEE_PROJECTS;
5/5 DROP TABLE COMPANYEMPLOYEES;
0: jdbc:oracle:thin:@localhost:1521:mydb>
exportedkeys — List exported foreign keys for a database
!exportedkeys
{table name}
0: jdbc:hsqldb:db-hypersonic> !exportedkeys COMPANY
+----------------+---------------+----------------+-------------+
| PKTABLE_SCHEM | PKTABLE_NAME | PKCOLUMN_NAME | FKTABLE_CAT |
+----------------+---------------+----------------+-------------+
| | COMPANY | COMPANY_ID | |
+----------------+---------------+----------------+-------------+
0: jdbc:hsqldb:db-hypersonic>
go — Change to a different active connection
!go
{connection number}
0: jdbc:oracle:thin:@localhost:1521:mydb> !list
2 active connections:
#0 open jdbc:oracle:thin:@localhost:1521:mydb
#1 open jdbc:mysql://localhost/mydb
0: jdbc:oracle:thin:@localhost:1521:mydb> !go 1
1: jdbc:mysql://localhost/mydb> !go 0
0: jdbc:oracle:thin:@localhost:1521:mydb>
help — Display help information
!help
sqlline> !help
!all Execute the specified SQL against all the current connections
!appconfig Sets application configuration class name
!autocommit Set autocommit mode on or off
!batch Start or execute a batch of statements
!brief Set verbose mode off
!close Close the current connection to the database
!columns List all the columns for the specified table
!commit Commit the current transaction (if autocommit is off)
!connect Open a new connection to the database.
!dbinfo Give metadata information about the database
!describe Describe a table
!dropall Drop all tables in the current database
!exportedkeys List all the exported keys for the specified table
!go Select the current connection
!help Print a summary of command usage
!history Display the command history
-d Print timestamps for each event (default for !history)
-f Print full time date stamps in the US format
-E Print full time date stamps in the European format
-i Print full time date stamps in ISO8601 format
-n Suppresses command numbers
-r Reverses the order of the commands
!importedkeys List all the imported keys for the specified table
!indexes List all the indexes for the specified table
!isolation Set the transaction isolation for this connection
!list List the current connections
!metadata Obtain metadata information
!nickname Create a friendly name for the connection (updates command prompt)
!outputformat Set the output format for displaying results
(table, vertical, csv, tsv, xmlattrs, xmlelements, json, ansiconsole)
!prompthandler Set custom prompt handler class name
!properties Connect to the database specified in the properties file(s)
!primarykeys List all the primary keys for the specified table
!procedures List all the procedures
!quit Exits the program
!reconnect Reconnect to the database
!record Record all output to the specified file
!rehash Fetch table and column names for command completion
!rollback Roll back the current transaction (if autocommit is off)
!reset Reset a sqlline variable
!run Run a script from the specified file
!save Save the current variables and aliases
!scan Scan for installed JDBC drivers
!schemas List all the schemas in the database
!script Start saving a script to a file
!set Set a sqlline variable, or show its value
!sql Execute a SQL command
!tables List all the tables in the database
!verbose Set verbose mode on
Key-strokes:
alt-b Backward word
alt-c Capitalize word
alt-d Kill word
alt-f Forward word
alt-h Next color scheme
alt-l Lowercase word
alt-n History search forward
alt-p History search backward
alt-t Transpose words
alt-u Uppercase word
ctrl-a To the beginning of line
ctrl-b Backward char
ctrl-d Delete char
ctrl-e To the end of line
ctrl-f Forward char
ctrl-h Backward delete char
ctrl-i Complete
ctrl-j Enter
ctrl-k Kill the line
ctrl-m Enter
ctrl-n Down line or history
ctrl-p Up line or history
ctrl-r History incremental search backward
ctrl-s History incremental search forward
ctrl-t Transpose chars
ctrl-u Kill the whole line
ctrl-w Backward kill the line
Comments, bug reports, and patches go to mwp1@cornell.edu
sqlline>
history — Display the command history -d Print timestamps for each event (default for !history) -f Print full time date stamps in the US format -E Print full time date stamps in the European format -i Print full time date stamps in ISO8601 format -n Suppresses command numbers -r Reverses the order of the commands
!history
importedkeys — List imported foreign keys for a database
!importedkeys
{table name}
0: jdbc:hsqldb:db-hypersonic> !importedkeys EMPLOYEE
+----------------+---------------+----------------+-------------+
| PKTABLE_SCHEM | PKTABLE_NAME | PKCOLUMN_NAME | FKTABLE_CAT |
+----------------+---------------+----------------+-------------+
| | COMPANY | COMPANY_ID | |
+----------------+---------------+----------------+-------------+
0: jdbc:hsqldb:db-hypersonic>
indexes — Display indexes for a table
!indexes
{table name}
0: jdbc:oracle:thin:@localhost:1521:mydb> !indexes EMPLOYEE
+-------------+-------------+-----------------+-------+--------------+
| TABLE_NAME | NON_UNIQUE | INDEX_NAME | TYPE | COLUMN_NAME |
+-------------+-------------+-----------------+-------+--------------+
| EMPLOYEE | 0 | | 0 | |
| EMPLOYEE | 0 | SYS_C003115849 | 1 | ID |
| EMPLOYEE | 1 | FIRST_NAME | 1 | FIRST_NAME |
| EMPLOYEE | 1 | LAST_NAME | 1 | LAST_NAME |
+-------------+-------------+-----------------+-------+--------------+
0: jdbc:oracle:thin:@localhost:1521:mydb>
isolation — Set the transaction isolation mode for the active connection
!isolation
{isolation level}
Set the isolation level for the current transaction. For a description of the different isolation levels, see java.sql.Connection#field_detail.
0: jdbc:oracle:thin:@localhost:1521:mydb> !isolation TRANSACTION_READ_UNCOMMITTED
Error: READ_COMMITTED and SERIALIZABLE are the only valid transaction levels (state=,code=17030)
0: jdbc:oracle:thin:@localhost:1521:mydb> !isolation TRANSACTION_SERIALIZABLE
Transaction isolation: TRANSACTION_SERIALIZABLE
0: jdbc:oracle:thin:@localhost:1521:mydb>
metadata — Invoke arbitrary metadata commands
!metadata
{methodname}
{params...}
Execute an arbitrary metadata method against the current connection. Parameters are separated by spaces. Use "" for a blank String, and null for a null parameter. For information on available metadata methods, see java.sql.DatabaseMetaData.
0: jdbc:oracle:thin:@localhost:1521:mydb> !metadata supportsSelectForUpdate
true
0: jdbc:oracle:thin:@localhost:1521:mydb> !metadata getTypeInfo
+------------+------------+-------------+-----------------+-----------------+
| TYPE_NAME | DATA_TYPE | PRECISION | LITERAL_PREFIX | LITERAL_SUFFIX |
+------------+------------+-------------+-----------------+-----------------+
| NUMBER | -7 | 1 | | |
| NUMBER | -6 | 3 | | |
| NUMBER | -5 | 38 | | |
| LONG RAW | -4 | 2147483647 | ' | ' |
| RAW | -3 | 2000 | ' | ' |
| LONG | -1 | 2147483647 | ' | ' |
| CHAR | 1 | 2000 | ' | ' |
| NUMBER | 2 | 38 | | |
| NUMBER | 4 | 10 | | |
| NUMBER | 5 | 5 | | |
| FLOAT | 6 | 63 | | |
| REAL | 7 | 63 | | |
| VARCHAR2 | 12 | 4000 | ' | ' |
| DATE | 93 | 7 | | |
| STRUCT | 2002 | 0 | ' | ' |
| ARRAY | 2003 | 0 | ' | ' |
| BLOB | 2004 | 4294967295 | | |
| CLOB | 2005 | 4294967295 | ' | ' |
| REF | 2006 | 0 | ' | ' |
+------------+------------+-------------+-----------------+-----------------+
0: jdbc:oracle:thin:@localhost:1521:mydb> !meta getProcedureColumns null % %JAVA% %
+----------------+------------------+------------------------+--------------+
| PROCEDURE_CAT | PROCEDURE_SCHEM | PROCEDURE_NAME | COLUMN_NAME |
+----------------+------------------+------------------------+--------------+
| DBMS_JAVA | SYS | DROPJAVA | OPTIONS |
| XMLPARSER | SYS | GETJAVARELEASEVERSION | |
| DBMS_JAVA | SYS | LOADJAVA | OPTIONS |
| DBMS_JAVA | SYS | LOADJAVA | OPTIONS |
| DBMS_JAVA | SYS | LOADJAVA | RESOLVER |
+----------------+------------------+------------------------+--------------+
0: jdbc:oracle:thin:@localhost:1521:mydb>
outputformat — Change the method for displaying SQL results
!outputformat
{format name}
Set the mode for displaying results from statements. This is useful for saving output from SQL statements to either a visually pleasing format or an easily parsable format. In the case of csv or table outputformat there could be specified additional parameters like csv delimiter, csv quote character, max column width. Such settings will change corresponding properties' values. If the parameters not specified then properties are left as is.
0: jdbc:oracle:thin:@localhost:1521:mydb> !outputformat table
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
+-------------+------------+
| COMPANY_ID | NAME |
+-------------+------------+
| 1 | Apple |
| 2 | Sun |
| 3 | IBM |
| 4 | Microsoft |
+-------------+------------+
4 rows selected (0.012 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !outputformat vertical
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
COMPANY_ID 1
NAME Apple
COMPANY_ID 2
NAME Sun
COMPANY_ID 3
NAME IBM
COMPANY_ID 4
NAME Microsoft
4 rows selected (0.011 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !outputformat csv
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
'COMPANY_ID','NAME'
'1','Apple'
'2','Sun'
'3','IBM'
'4','Microsoft'
4 rows selected (0.012 seconds)
0: jdbc:calcite:model=target/test-classes/mod> !set outputFormat csv ### @
0: jdbc:calcite:model=target/test-classes/mod> SELECT * FROM COMPANY;
@COMPANY_ID@###@NAME@
@1@###@Apple@
@2@###@Sun@
@3@###@IBM@
@4@###@Microsoft@
4 rows selected (0.014 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !outputformat tsv
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
'COMPANY_ID' 'NAME'
'1' 'Apple'
'2' 'Sun'
'3' 'IBM'
'4' 'Microsoft'
4 rows selected (0.013 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !outputformat xmlattr
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
<resultset>
<result COMPANY_ID="1" NAME="Apple"/>
<result COMPANY_ID="2" NAME="Sun"/>
<result COMPANY_ID="3" NAME="IBM"/>
<result COMPANY_ID="4" NAME="Microsoft"/>
</resultset>
4 rows selected (0.012 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !outputformat xmlelements
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
<resultset>
<result>
<COMPANY_ID>1</COMPANY_ID>
<NAME>Apple</NAME>
</result>
<result>
<COMPANY_ID>2</COMPANY_ID>
<NAME>Sun</NAME>
</result>
<result>
<COMPANY_ID>3</COMPANY_ID>
<NAME>IBM</NAME>
</result>
<result>
<COMPANY_ID>4</COMPANY_ID>
<NAME>Microsoft</NAME>
</result>
</resultset>
4 rows selected (0.02 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb>
0: jdbc:calcite:model=target/test-classes/mod> !outputformat json
0: jdbc:calcite:model=target/test-classes/mod> SELECT * FROM COMPANY;
{"resultset":[
{"COMPANY_ID":1,"NAME":"Apple"},
{"COMPANY_ID":2,"NAME":"Sun"},
{"COMPANY_ID":3,"NAME":"IBM"},
{"COMPANY_ID":4,"NAME":"Microsoft"}
]}
4 rows selected (0.03 seconds)
0: jdbc:calcite:model=target/test-classes/mod>
0: jdbc:calcite:model=target/test-classes/mod> !outputformat ansiconsole
0: jdbc:calcite:model=target/test-classes/mod> SELECT * FROM COMPANY;
COMPANY_ID NAME
1 Apple
2 Sun
3 IBM
4 Microsoft
4 rows selected (0.03 seconds)
0: jdbc:calcite:model=target/test-classes/mod>
primarykeys — Display the primary key columns for a table
!primarykeys
{table name}
0: jdbc:hsqldb:db-hypersonic> !primarykeys COMPANY
+--------------+-------------+--------------+----------+----------+
| TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | KEY_SEQ | PK_N |
+--------------+-------------+--------------+----------+----------+
| | COMPANY | COMPANY_ID | 1 | SYS_PK_C |
+--------------+-------------+--------------+----------+----------+
0: jdbc:hsqldb:db-hypersonic>
procedures — List stored procedures
!procedures
[procedure name pattern]
0: jdbc:oracle:thin:@localhost:1521:mydb> !procedures %JDBC%
+----------------+------------------+------------------+
| PROCEDURE_CAT | PROCEDURE_SCHEM | PROCEDURE_NAME |
+----------------+------------------+------------------+
| WK_ADM | WKSYS | GET_JDBC_DRIVER |
| WK_ADM | WKSYS | SET_JDBC_DRIVER |
+----------------+------------------+------------------+
0: jdbc:oracle:thin:@localhost:1521:mydb>
properties — Connect to the database defined in the specified properties file.
!properties
{file}
Connect to the database defined in the specified properties file. The properties file is expected to have the following values:
url: the database URL to which to connect
driver: the driver class that handles the URL. If unspecified, SQLLine will automatically scan the CLASSPATH for an appropriate driver
user: the username to use to connect to the database. If unspecified, SQLLine will prompt for it.
password: the password to use to connect to the database. If unspecified, SQLLine will prompt for it.
The key names in the properties file can also use JDO semantics for the properties. These properties are "javax.jdo.option.ConnectionURL", "javax.jdo.option.ConnectionDriverName", "javax.jdo.option.ConnectionUserName", and "javax.jdo.option.ConnectionPassword". Furthermore, any properties that end with "ConnectionURL", "ConnectionDriverName", "ConnectionUserName", and "ConnectionPassword" will be used for the connection.
sqlline> !properties test.properties
Connecting to jdbc:mysql://localhost/mydb
Enter password for jdbc:mysql://localhost/mydb: *****
Connected to: MySQL (version 3.23.52-log)
Driver: MySQL-AB JDBC Driver (version 3.0.8-stable ( $Date: 2008/12/10 $, $Revision: #3 $ ))
Autocommit status: true
Transaction isolation: TRANSACTION_READ_COMMITTED
0: jdbc:mysql://localhost/mydb>
reconnect — Reconnect to the current database
!reconnect
0: jdbc:oracle:thin:@localhost:1521:mydb> !reconnect
Reconnecting to "jdbc:oracle:thin:@localhost:1521:mydb"...
Closing: oracle.jdbc.driver.OracleConnection@4428d3
Connected to: Oracle (version Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production)
Driver: Oracle JDBC driver (version 9.0.1.0.0)
Autocommit status: true
0: jdbc:oracle:thin:@localhost:1521:mydb>
record — Begin recording all output from SQL commands
!record
{file name}
Save all output from the session to the specified file.
If the file name contains spaces, you may enclose it in double-quotes.
0: jdbc:oracle:thin:@localhost:1521:mydb> !record /tmp/mysession.out
Saving all output to "/tmp/mysession.out". Enter "record" with no arguments to stop it.
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
+-------------+------------+
| COMPANY_ID | NAME |
+-------------+------------+
| 3 | IBM |
| 4 | Microsoft |
| 1 | Apple |
| 2 | Sun |
+-------------+------------+
4 rows selected (0.011 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !record
Recording stopped.
0: jdbc:oracle:thin:@localhost:1521:mydb>
rehash — Obtain a list of all tables and columns from the database
!rehash
Get a list of all tables and columns from the database in order to include them in the list for tab-completion of SQL statements. This is done automatically on connect when the fastconnect option is enabled.
reset — Reset a preference
!reset
{all}
!reset
{preference}
rollback — Roll back the current transaction
!rollback
0: jdbc:oracle:thin:@localhost:1521:mydb> !autocommit off
Autocommit status: false
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
+-------------+------------+
| COMPANY_ID | NAME |
+-------------+------------+
| 3 | IBM |
| 4 | Microsoft |
| 1 | Apple |
| 2 | Sun |
+-------------+------------+
4 rows selected (0.011 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> DELETE FROM COMPANY;
4 rows affected (0.004 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
+-------------+-------+
| COMPANY_ID | NAME |
+-------------+-------+
+-------------+-------+
No rows selected (0.01 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> !rollback
Rollback complete (0.016 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM COMPANY;
+-------------+------------+
| COMPANY_ID | NAME |
+-------------+------------+
| 3 | IBM |
| 4 | Microsoft |
| 1 | Apple |
| 2 | Sun |
+-------------+------------+
4 rows selected (0.01 seconds)
0: jdbc:oracle:thin:@localhost:1521:mydb>
run — Execute a command script
!run
{file name}
Run the individual commands specified in the file name.
The file should consist of individual SQL
statements or SQLLine commands. Lines beginning with
"#" are interpreted as comments and ignored. If any
errors occur while running the script, the script will
be aborted, unless the
force preference is
set to true.
If the file name contains spaces, you may enclose it in double-quotes.
0: jdbc:hsqldb:db-hypersonic> !run example.sql
1/11 CREATE TABLE COMPANY (COMPANY_ID INT, NAME VARCHAR(255));
No rows affected (0.001 seconds)
2/11 INSERT INTO COMPANY VALUES (1, 'Apple');
1 row affected (0 seconds)
3/11 INSERT INTO COMPANY VALUES (2, 'Sun');
1 row affected (0 seconds)
4/11 INSERT INTO COMPANY VALUES (3, 'IBM');
1 row affected (0.001 seconds)
5/11 INSERT INTO COMPANY VALUES (4, 'Microsoft');
1 row affected (0.015 seconds)
6/11 CREATE TABLE EMPLOYEE (ID INT, FIRST_NAME VARCHAR(255), LAST_NAME VARCHAR(255), COMPANY INT);
No rows affected (0.004 seconds)
7/11 CREATE INDEX FIRST_NAME ON EMPLOYEE(FIRST_NAME);
No rows affected (0 seconds)
8/11 CREATE INDEX LAST_NAME ON EMPLOYEE(LAST_NAME);
No rows affected (0.001 seconds)
9/11 ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_COMPANY FOREIGN KEY (COMPANY) REFERENCES COMPANY (COMPANY_ID);
No rows affected (0 seconds)
10/11 INSERT INTO EMPLOYEE (ID, FIRST_NAME, LAST_NAME, COMPANY) VALUES (234, 'William', 'Gates', 4);
1 row affected (0.001 seconds)
11/11 SELECT * FROM COMPANY,EMPLOYEE WHERE EMPLOYEE.COMPANY = COMPANY.COMPANY_ID;
+------------+-----------+--------------+-------------+------------+---------+
| COMPANY_ID | NAME | ID | FIRST_NAME | LAST_NAME | COMPANY |
+------------+-----------+--------------+-------------+------------+---------+
| 4 | Microsoft | 234 | William | Gates | 4 |
+------------+-----------+--------------+-------------+------------+---------+
1 row selected (0.001 seconds)
0: jdbc:hsqldb:db-hypersonic>
scan — Scan class path for JDBC drivers
!scan
Scans all the jar files in the CLASSPATH for any JDBC drivers, and prints the class names of the drivers.
sqlline> !scan
35 driver classes found
Compliant Version Driver Class
no 4.0 COM.cloudscape.core.JDBCDriver
no 1.0 COM.cloudscape.core.RmiJdbcDriver
yes 7.1 COM.ibm.db2.jdbc.net.DB2Driver
yes 3.2 com.ddtek.jdbc.db2.DB2Driver
yes 3.2 com.ddtek.jdbc.informix.InformixDriver
yes 3.2 com.ddtek.jdbc.oracle.OracleDriver
yes 3.2 com.ddtek.jdbc.sqlserver.SQLServerDriver
yes 3.2 com.ddtek.jdbc.sybase.SybaseDriver
yes 1.0 com.ibm.db2.jcc.DB2Driver
yes 2.21 com.informix.jdbc.IfxDriver
no 0.2 com.internetcds.jdbc.tds.Driver
no 0.2 com.internetcds.jdbc.tds.SybaseDriver
yes 2.2715 com.jnetdirect.jsql.JSQLDriver
yes 3.2 com.merant.datadirect.jdbc.db2.DB2Driver
yes 3.2 com.merant.datadirect.jdbc.informix.InformixDriver
yes 3.2 com.merant.datadirect.jdbc.oracle.OracleDriver
yes 3.2 com.merant.datadirect.jdbc.sqlserver.SQLServerDriver
yes 3.2 com.merant.datadirect.jdbc.sybase.SybaseDriver
yes 2.2 com.microsoft.jdbc.sqlserver.SQLServerDriver
no 3.0 com.mysql.jdbc.Driver
no 3.0 com.mysql.jdbc.NonRegisteringDriver
yes 4.4 com.pointbase.jdbc.jdbcDriver
yes 4.4 com.pointbase.jdbc.jdbcEmbeddedDriver
yes 4.4 com.pointbase.jdbc.jdbcUniversalDriver
no 5.5 com.sybase.jdbc2.jdbc.SybDriver
yes 1.0 in.co.daffodil.db.jdbc.DaffodilDBDriver
no 2.0 interbase.interclient.Driver
no 0.5 net.sourceforge.jtds.jdbc.Driver
yes 1.0 oracle.jdbc.OracleDriver
yes 1.0 oracle.jdbc.driver.OracleDriver
no 3.26 org.enhydra.instantdb.jdbc.idbDriver
no 3.0 org.gjt.mm.mysql.Driver
no 1.7 org.hsqldb.jdbcDriver
no 7.3 org.postgresql.Driver
yes 0.9 org.sourceforge.jxdbcon.JXDBConDriver
sqlline>
schemas — List all the schemas in the database
!schemas
0: jdbc:postgresql:thin:@localhost:5432:mydb> !schemas
+--------------------+---------------+
| table_schem | table_catalog |
+--------------------+---------------+
| information_schema | |
| pg_catalog | |
| public | |
+--------------------+---------------+
0: jdbc:postgresql:thin:@localhost:5432:mydb>
script — Save executed commands to a file
!script
{filename}
Start saving all commands entered to the specified file. Once scripting on, it can be finished by entering the script command again with no arguments. Saved scripts can be re-run using the run command.
If the file name contains spaces, you may enclose it in double-quotes.
0: jdbc:hsqldb:db-hypersonic> !script /tmp/mysession.script
Saving command script to "/tmp/mysession.script". Enter "script" with no arguments to stop it.
0: jdbc:hsqldb:db-hypersonic> !autocommit off
Autocommit status: false
0: jdbc:hsqldb:db-hypersonic> SELECT * FROM COMPANY;
+-------------+------------+
| COMPANY_ID | NAME |
+-------------+------------+
| 1 | Apple |
| 2 | Sun |
| 3 | IBM |
| 4 | Microsoft |
+-------------+------------+
4 rows selected (0.001 seconds)
0: jdbc:hsqldb:db-hypersonic> UPDATE COMPANY SET NAME='Apple, Inc.' WHERE NAME='Apple';
1 row affected (0.005 seconds)
0: jdbc:hsqldb:db-hypersonic> !commit
Commit complete (0 seconds)
0: jdbc:hsqldb:db-hypersonic> !script
Script closed. Enter "run /tmp/mysession.script" to replay it.
0: jdbc:hsqldb:db-hypersonic>
set — Set a preference, or show its value
!set
!set
{all}
!set
{preference}
!set
{preference}
{value}
Set the specified preference to the specified value. If autosave is on, then this will cause preferences to be immediately saved. Otherwise, changed settings can be saved for future SQLLine sessions with the "save" command. The forms of the command without a "{value}" argument show the current value of the specified preference, or all preferences. "!set" and "!set all" commands are equivalent and list all preferences. save command.
tables — List all the tables in the database
!tables
0: jdbc:oracle:thin:@localhost:1521:mydb> !tables
+------------+--------------+---------------------------------+-------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE |
+------------+--------------+---------------------------------+-------------+
| | MYDB | COMPANY | TABLE |
| | MYDB | EMPLOYEE | TABLE |
+------------+--------------+---------------------------------+-------------+
0: jdbc:oracle:thin:@localhost:1521:mydb>
verbose — Enable verbose output
!verbose
Enable verbose mode, which causes stack traces to be printed when errors occur, and enabled outputting of debug information.
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM BOGUS_TABLE;
Error: ORA-00942: table or view does not exist (state=42000,code=942)
0: jdbc:oracle:thin:@localhost:1521:mydb> !verbose
verbose: on
0: jdbc:oracle:thin:@localhost:1521:mydb> SELECT * FROM BOGUS_TABLE;
Error: ORA-00942: table or view does not exist (state=42000,code=942)
java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2110)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2324)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:909)
at sqlline.SqlLine$Commands.sql(SqlLine.java:3183)
at sqlline.SqlLine.dispatch(SqlLine.java:771)
at sqlline.SqlLine.begin(SqlLine.java:634)
at sqlline.SqlLine.main(SqlLine.java:332)
0: jdbc:oracle:thin:@localhost:1521:mydb>
Table of Contents
Sets application configuration class name in order to customize output formats, commands, application information message.
If true, then new connections will have autocommit
set, otherwise, transactions will need to be explicitly
committed or
rolled back.
Defaults to true.
To change the autocommit status for
a connection that is already open and active, use the
autocommit
command instead.
When set to true, any changes
to preferences using the
set command will cause
the preferences to be saved. Otherwise, preferences will
need to be explicitly saved using the
save command.
Defaults to false.
If true, then output to
the terminal will use color for
a more pleasing visual experience. Requires that the
terminal support ANSI control codes (most do).
Defaults to false.
If chester/dark
/dracula/light
/obsidian/solarized
/vs2010,
then this scheme will be used for command/sql syntax highlighting.
If default then there is no syntax highlighting.
If askCredentials then sqlline will ask for the
credentials if they are not specified as properties to make
a connection. If notAskCredentials the sqlline
will not ask for the credentials to make a connection. If
useNPTogetherOrEmpty then it will ask for the
credentials if there is only one specified, if there is no specified
then it will use empty credentials (sqlline 1.7 and earlier behavior).
The format for how date values are displayed. Setting
to default causes date values
to be fetched and rendered via ResultSet.getString.
Any other setting results in fetch via ResultSet.getObject
and rendering via
java.text.SimpleDateFormat. For example,
the setting "YYYY_MM_dd" yields values like "1970_01_01".
When set to true, control symbols in output will be escaped. Otherwise they will be printed as is. Default is false..
When false, any new connection
will cause SQLLine to
access information about the available tables and columns
in order to provide them as candidates for tab-completion.
This can be a very slow operation for some databases, do
by default it is off. Table and column information can
always be explicitly retrieved using the
rehash command.
When set to false,
any failures during the
execution of the run
or record commands
will cause execution to abort, preventing the execution
of subsequent commands. When set to
true, errors
will be displayed but otherwise ignored.
Defaults to false.
The interval between which the column headers will be redisplayed when using the "table" outputformat. Defaults to 100.
The file to which SQLLine will save a record of all the commands issued. Defaults to HOME/sqlline/history on Windows, and HOME/.sqlline/history on all other platforms.
When set to false,
the entire result set is fetched and buffered
before being displayed, yielding optimal display
column sizing. When set to true,
result rows are displayed immediately as they are
fetched, yielding lower latency and memory usage
at the price of extra display column padding.
Defaults to false.
When incremental set
to false and the entire result set
has more rows than value of
incrementalBufferRows
sqlline will enter incremental mode.
In that case result rows are displayed by batches
of incrementalbufferrows size.
Negative value means no entering
incremental mode.
Defaults to 1000.
The default transaction isolation that will be used for new connections. To change the isolation level of the currently active connection, use the isolation command instead.
The maximum column width to display for each column before truncating data when using the "table" outputformat. Defaults to 15.
The maximum number of history rows to store in history file. Defaults to 10000 (org.jline.reader.impl.history.DefaultHistory.DEFAULT_HISTORY_FILE_SIZE).
The maximum number of history rows to store in memory. Defaults to 500 (org.jline.reader.impl.history.DefaultHistory.DEFAULT_HISTORY_SIZE).
The maximum width to display before truncating data when using the "table" outputformat. Defaults to attempting to query the terminal for the current width, falls back to 80.
The value which will be used instead of null values. Setting to default causes 'null' for numbers/dates/times and empty strings for others. For example, the setting *** yields null values like '***'.
The format for how numeric values are displayed. Setting
to default causes numeric
values to be fetched and rendered via ResultSet.getString.
Any other setting results in fetch via ResultSet.getObject
and rendering via
java.text.DecimalFormat. For example,
the setting "0.###E0" yields scientific notation
with up to three fractional digits, such as "6.022E23".
The format for how results are displayed. For details, see the information on the outputformat command.
The tcsh-like format for how prompt is displayed.
For example, the setting
%[\033[1;33m%]sqlline%[\033[m%]> yields
yellow sqlline and normal angle bracket.
Defaults to sqlline>. If for the specific
database connection nickname is set, then nickname will be used.
For details, see the
Prompting documentation.
The tcsh-like format for how right prompt is displayed. The same patterns are applied as for prompt but it does not care if nickname is set or not. Defaults to ''. For details, see the Prompting documentation.
Sets a JavaScript program that is evaluated to
generate a prompt. If specified, prompt
is ignored. The following variables are pre-defined for use in the
JavaScript:
connectionIndex,
currentSchema,
databaseProductName,
url,
userName.
For instance
sqlline> !set promptscript ' databaseProductName.toLowerCase() + ":" + connectionIndex + "> " '
hsql database engine:0>
The maximum number of rows to fetch per query. Defaults to 0, which is interpreted as fetching all rows.
If true, display help description for each
completion candidate. Defaults to
true.
If true, display any nested errors
that are reported on the connection after issuing
any database commands. Defaults to
false, meaning only the topmost
error is displayed.
If true,
display execution time for
database commands when in
verbose mode. Defaults to
true.
If true, display any warnings
that are reported on the connection after issuing
any database commands. Defaults to
true.
If false, then use default values
defined by java.sql.DatabaseMetaData
in case an implementation of this interface fails with
e.g. "Method not supported", otherwise it also fails.
Defaults to false.
If true, then reduce the amount
of informational messages displayed. Useful for redirecting
a sqlline command to a file for later parsing. Defaults to
false.
The format for how time values are displayed. Setting
to default causes time values
to be fetched and rendered via ResultSet.getString.
Any other setting results in fetch via ResultSet.getObject
and rendering via
java.text.SimpleDateFormat. For example,
the setting HH:mm:ss yields values like 14:12:11.
The format for how date values are displayed. Setting
to default causes date values
to be fetched and rendered via ResultSet.getString.
Any other setting results in fetch via ResultSet.getObject
and rendering via
java.text.SimpleDateFormat. For example,
the setting dd/MM/YYYY'T'HH:mm:ss yields values like 01/01/1970T12:32:12.
If true, then trim leading and
trailing whitespace from lines as they are processed
in scripts; otherwise, preserve whitespace. Defaults to
true.
If true, then print out the
entire java stack trace whenever an error occurs,
as well as displaying debugging information.
Defaults to false.
sqlline>.
Right prompt - prompt on the right side with empty default value.
Secondary prompt - prompt which appears while multiline editing.
SQLLine supports customization of prompt and right prompt via
!set prompt and !set rightPrompt.
The value of the selected prompt variable is printed literally,
except where a percent sign (%) is encountered.
Depending on the next character,
certain other text is substituted instead. Defined substitutions are:
Current date in format yyyy-MM-dd HH:mm:ss.SSS
sqlline> !set prompt 'time: %D sqlline>'
time: 2019-01-07 13:11:13.221 sqlline>
The value of the specified sqlline property (case insensitive). For instance For details, see the properties command documentation.
For instance,
sqlline> !set prompt %:verbose::sqlline>
false:sqlline>!set verbose true
true:sqlline>
Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text. Both ansi and constants or its shortcuts for styles could be used here, e.g.
!set prompt %[f:m%]sqlline%[default%]>
!set prompt %[foreground:magenta%]sqlline%[default%]>
!set prompt %[35%]sqlline%[%]>
All three commands are identical.
SQLLine supports styles default,
bold, italic,
faint, underline,
blink, inverse,
inverse-neg (inverseneg),
conceal, crossed-out
(crossedout), hidden.
There are two styles for coloring foreground
(short options f and fg) and
background
(short options b and bg).
The short options for colors are available like
k for black,
r for red,
g for green,
y for yellow,
b for blue,
m for magenta,
c for cyan,
w for white.