sqlline Overview

<< Click to Display Table of Contents >>

Navigation:  Using sqlline >

sqlline Overview

Previous pageReturn to chapter overviewNext page

SQLLine is an open source utility modified by SQLstream to handle streaming data. SQLLine works similarly to other command-line database access utilities, such as sqlplus for Oracle, mysql for MySQL, isql for Sybase/SQL Server, and psql for PosgeSQL. SQLLine can also connect to other relational database drivers and execute SQL commands on those databases. More information about SQLLine can be found at: http://sqlline.sourceforge.net/

It is supplied either as part of the s-Server installation package or as part of the Client Tools download from the SQLstream website (via SQLstream-5.1.0-clienttools-linux.run or SQLstream-client-tools-5.1.0.-windows.exe).

Using SQLstream's SQLLine, you can do the following :

Connect to SQLstream, databases, and servers, local or remote

Execute SQL commands

Define and create streams

Run streaming queries

Three SQLstream scripts provide SQLLine functionality, and each one, once launched, can be used to connect to local or remote databases using a !connect command or a !properties command.

Script name & use





Automatically connects the user to a local SQLstream s-Server.

You can then connect to other databases using a !connect command.

On the SQLstream server, in the SQLSTREAM_HOME/bin directory



Requires additional user-supplied instructions to connect to either SQLstream or another local or remote database.

You can then connect to other databases using a !connect command.

On the SQLstream server, in the SQLSTREAM_HOME/bin directory


(local or remote)

Can connect from a remote server to databases either on a SQLstream s-Server or on other remote servers

On a remote server in the ClientTools/bin directory.

Connecting to SQLstream with sqllineClient

To connect to the local SQLstream s-Server using sqllineClient (on the same machine containing the script), use one of the following two methods:

Go to the SQLSTREAM_HOME/bin directory and use the following command:

o./sqllineClient (Linux)

osqlline.cmd (Windows)


Double-click the 'Run sqlline' icon on the desktop (either platform). A terminal window appears, showing the following command being executed:



When that command completes, the terminal window shows the following:

    Connecting to jdbc:sqlstream:sdp://bento;sessionName='sqllineClient@/dev/pts/1:demo@bento'

         log4j:INFO Using URL [jar:file:/home/demo/SQLstream-sServer-3.0.0/

         lib/GroboUtils-5-core.jar!/log4j.properties] for automatic log4j

         configuration of repository named [default].

    Connected to: SQLstream (version 3.0.0)

    Driver: SQLstreamJdbcDriver (version 3.0-distrib)

    Autocommit status: true

    Transaction isolation: TRANSACTION_REPEATABLE_READ

    sqlline version 1.0.8-eb by Marc Prud'hommeaux

    0: jdbc:sqlstream:sdp://bento>


Once you have started SQLLine using one of the scripts, you can connect to database servers by using a command (the !connect command or the !properties command) or by supplying the connection parameters on the use sqllineRemote as shown in the next section.

Command-Line Arguments

Default connection values can be set for all the tools in the

clienttools/default.conn.properties file. The properties are specified there as


Each of those default values can be overwritten by passing a command-line argument to the script as follows:








-u jdbc:sqlstream:sdp://example.com:5571



Driver class

-d com.example.jdbc.Driver



User name

n sqlstream




-v mypassword

SQLLine Functionality

Once connected to SQLstream or a database, you can use SQLLine in either of the following ways:

Begin using SQL commands against the current connection


Enter SQLLine commands.

Using SQLLine Commands

All SQLLine commands start with an exclamation point (!).

You can get a list of all SQLLine commands by typing !help , or you can get specific help information on a known command by typing

  !help <command name>


You also get specific help information when you enter a command without the parameters it requires.

For example, when you are running sqlline client, if you type !connect without supplying the required parameters, the response is as follows:

    0: jdbc:sqlstream:sdp:> !connect

    Usage: connect <url> <username> <password> [driver]


To minimize typing, SQLLine will complete a partial command you've typed if you then press the tab key.

You can reissue commands you have already entered by pressing the Up arrow or Down arrow keys until you see the one you want. You can then edit that command, or simply execute it by pressing Enter. (See also the !history command.)

SQLLine Configuration Preference settings

SQLstream has found the following settings to be best practices:

Command Name


!set color

true or false

!set incremental

(Modified by SQLstream to enable working with streaming data)

true or false. Defaults to false.

For streaming data, this setting must be set to true, because the entire result set is never really fetched. With incremental set to true, result rows are displayed as soon as they are fetched.

!set force

true or false; if false, any subsequent execution error causes all subsequent commands to abort.

!set outputformat

Sets the output format for displaying results, to one of the following terms:

table, vertical, csv, tsv, xmlattrs, xmlelements

!set showheader

Displays the names of the columns when displaying results. When this is set to false, the headers are only displayed at the beginning and not repeated. This is convenient when you are using !record to save the file.

Frequently-Used Commands

Command Name


!run <filename>

Executes the specified commands and SQL in a file.

Ignores lines beginning with #.

Aborts if errors occur, unless "force" is set to true.

(See the configuration preferences settings section.)


Lists commands recently used, which are saved in a file.

In Windows, the file is in HOME/sqlline/history.

On all other platforms, the file is in HOME/.sqlline/history.


Exits SQLLineClient


Reconnects to a server that was brought down while connected to SQLstream using SQLLine.

!record <filename>

Saves all session output to the named file.

To turn this off, use !record alone, with no filename.


Saves configuration preferences settings

(See the configuration preferences settings section.)

!set <command>

Sets a configuration/preference setting.

(See the configuration preferences settings section.)


Lists all the tables in the database of the current connection. (See also !describe.)

Complete SQLLine Command Set

The following alphabetic list provides brief explanations for all SQLLine commands. Some commands interrelate.

Command Name



Executes the specified SQL against all the current connections.


Sets autocommit mode on or off (See !commit and !rollback.) Note: autocommit is set to on by default. This differs from the SQLstream JDBC driver. See the topic the SQLstream JDBC driver in the Integration Guide for more details.


Starts or execute a batch of statements


Sets verbose mode off


Executes a callable statement


Closes the current connection to the database


Closes all currently open connections


Lists all columns in the specified table


Commits the current transaction (if !autocommit is off)


Opens a new connection to the database. See the example command above and the description of properties files.


Gives metadata information about the database


Describes a table


Drops all tables in the current database


Lists all the exported keys for the specified table


Selects the current connection


Prints a summary of command usage


Displays the command history


Lists all the imported keys for the specified table


List all the indexes for the specified table


Sets the transaction isolation for this connection


Lists the current connections


Displays the SQLLine manual


Obtains metadata information


Shows the native SQL for the specified statement


Sets the output format for displaying results to one of the following:

table, vertical, csv, tsv, xmlattrs, xmlelements


Lists all the primary keys for the specified table


Lists all the procedures


Connects to the database specified in the properties file(s)


Exits the program


Reconnects to the database


Records all output to the specified file


Fetches table and column names for command completion


Rolls back the current transaction (if !autocommit is off)


Runs a script from the specified file. (See the section on Running Scripts.


Saves the current variabes and aliases


Scans for installed JDBC drivers


Starts saving a script to a file


Sets a sqlline variable


Executes a SQL command


Lists all the tables in the database


Displays the type map for the current connection


Sets verbose mode on Running Support Scripts

Launching sqlline on Linux

If you are running .sql files, it will be easier if you run sqlline from the directory that contains the .sql files, as in the following:



There is also an icon that the installation process places on the desktop, labeled SQLLine. Double-clicking that icon launches a local SQLLine version, using a built-in launch command of the form /home/demo/SQLstream-2.5.0/bin/sqllineClient.

Once you use a script to launch SQLLine, you can choose to connect to a local or remote database by using a !connect or !properties command, as described in Using SQLLine Commands.

Properties files

Once you launch SQLline client using one of the scripts, you can optionally connect to SQLstream (already done if you used sqllineClient script) or another database. To do this, you can use any one of the following three methods:

Use the !connect command with all parameters specified on the command line, as shown by the sample !connect[****]command in the Commands Most Often Used section.

Use the !connect command with a properties file in which you have specified the needed parameters.

Use a !properties command with such a properties file.

Using a properties file with the !connect command

You can connect to SQLstream if you use the command !connect myserver after you create a file named "myserver," containing the following lines specifying server connection properties:






Using a properties file with the !properties command

You can also use a !properties mydatabase command to connect to a database after you create a file named "mydatabase," containing the needed database connection properties. That file's contents would look similar to the following lines specifying those properties:






using, of course, the database-user-specific password.

Scripts with extension .sql are used in a variety of places in a SQLstream system. You can find many examples in the demo subdirectories of the SQLSTREAM_HOME/demo directory.

Support sql scripts residing in the SQLSTREAM_HOME/support/sql directory enable you to query your database schemas and configurations.

You can use the !run command to execute all such scripts, as for example:

    !run <support script name>.sql

Most script names describe what they do. For example, the support scripts include the following:
















Connecting to databases using SQLLine scripts

You can run sqlline as a client to any database server that supports JDBC (or to a local database). In other words, the SQLLine scripts enable command-line connection to a relational database to execute SQL commands.

This section illustrates how to establish such connections, using sqllineRemote as the example script. (The example assumes you have navigated to the directory $SQLSTREAM_HOME/bin, where this script resides.)

SqllineRemote uses Aspen runtime Jars for access to drivers.

On Linux, you can pass one or more connection-properties files to connect to the remote server(s):

  ./sqllineRemote file1 file2 ...

As a convenience, connection-properties file names of the form


can be referenced as simply "myserver":

  ./sqllineRemote myserver

To find files like myserver.conn.properties, sqllineRemote must execute in the directory in which it was started. Only then can !run find the properties file in that directory.

Create a <database>.conn.properties file with the following entries (supply your own password):






To connect to a particular database, append it to the URL, for example, jdbc:<database>://myhostname/farrago. Then run

  ./sqllineRemote <database>

Test it by using sqllineRemote for table access.

1. !tables

2. Select * from sales.emps

3. Insert a row into sales.emps

or whatever tables you know are there.

Errors and Troubleshooting

Why am I getting the following prompt:

"................>" ?

oThe "greater than" sign (>) is a continuation prompt, enabling you to continue the statement you just entered without providing a final semicolon.


To continue and complete that statement, type the rest of the statement after that prompt, and then press Enter.

To cancel the statement instead, type ; (semicolon) and press Enter to get the usual prompt back.

Why am I not getting results when I try to view a streaming table?

oYou may have the incremental parameter set to false, the default.

This parameter needs to be set to true: !set incremental true;