Table of Contents
Table of Contents
SQLLine is a pure-Java console based utility for connecting
to relational databases and executing SQL commands. It is
similiar 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 independant, and will
run on any platform that can run Java 1.3 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 is hosted on SourceForge, and is located at http://sqlline.sf.net. The latest release can be downloaded from http://sqlline.sf.net/download.html.
SQLLine depends on the following software:
Java™ Virtual Machine - SQLLine is a pure java program, and requires Java version 1.3 or higher in order to run. The latest JVM can be downloaded from http://java.sun.com.
JLine - SQLLine uses the JLine console reader for command line editing, tab-completion, and command history. It can be downloaded from http://jline.sf.net. Version 0.8.1 or higher is required.
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 independant 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.jar from
http://jline.sf.net into
sqllinedir.
Download your database's JDBC driver files into
sqllinedir. Note that some JDBC
drivers require some installation, such as uncompressing
or unzipping.
SQLLine can be run with the following command:
java {-Djava.ext.dirs=sqllinedir} {sqllinedir/sqlline.jar} [options...] [properties files...]
sqllinedir is
/usr/share/java/ (this is typical for
Debian Linux), then you might run:
java -Djava.ext.dirs=/usr/share/java/ /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 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. 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:
On UNIX systems, 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.
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
!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
!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
!outputformat Set the output format for displaying results
(table,vertical,csv,tsv,xmlattrs,xmlelements)
!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)
!run Run a script from the specified file
!save Save the current variabes and aliases
!scan Scan for installed JDBC drivers
!script Start saving a script to a file
!set Set a sqlline variable
!sql Execute a SQL command
!tables List all the tables in the database
!verbose Set verbose mode on
Comments, bug reports, and patches go to mwp1@cornell.edu
sqlline>
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 http://java.sun.com/j2se/1.3/docs/api/java/sql/Connection.html#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 agains the current connection. refpurpose are separated by spaces. Use "" for a blank String, and null for a null parameter. For information on available metadata methods, see http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html.
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 parseable format.
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: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>
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 unspecifed, SQLLine with 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: 2005/09/19 19:17:14 $, $Revision: 1.9 $ )) 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}
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.
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.
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>
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.
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>
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>
If true, then new connections will have autocommit
set, otherwise, transactions will need to be explicitely
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 explicitely 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.
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 explicitely 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.
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 colummn before truncating data when using the "table" outputformat. Defaults to 15.
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 format for how results are displayed. For details, see the information on the outputformat command.
If true, display any warnings
that are reported on the connection after issuing
any database commands. 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.