Using sqlline

<< Click to Display Table of Contents >>

Navigation:  »No topics above this level«

Using sqlline

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

Capabilities

Resides

sqllineClient

(local)

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

sqllineRemote

(local)

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

sqlline

(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)
or
Double-click the 'Run sqlline' icon on the desktop (either platform). A terminal window appears, showing the following command being executed:

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

 

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

DRIVER, SERVER, NAME, and PASSWORD.

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

Property

Switch

Argument

Example

SERVER

-u

URL

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

DRIVER

-d

Driver class

-d com.example.jdbc.Driver

NAME      

-n

User name

n sqlstream

PASSWORD

-v

Password

-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

or

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

Explanation

!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

Explanation

!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.)

!history

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.

!quit

Exits SQLLineClient

!reconnect

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.

!save

Saves configuration preferences settings

(See the configuration preferences settings section.)

!set <command>

Sets a configuration/preference setting.

(See the configuration preferences settings section.)

!tables

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

Explanation

!all

Executes the specified SQL against all the current connections.

!autocommit

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.

!batch

Starts or execute a batch of statements

!brief

Sets verbose mode off

!call

Executes a callable statement

!close

Closes the current connection to the database

!closeall

Closes all currently open connections

!columns

Lists all columns in the specified table

!commit

Commits the current transaction (if !autocommit is off)

!connect

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

!dbinfo

Gives metadata information about the database

!describe

Describes a table

!dropall

Drops all tables in the current database

!exportedkeys

Lists all the exported keys for the specified table

!go

Selects the current connection

!help

Prints a summary of command usage

!history

Displays the command history

!importedkeys

Lists all the imported keys for the specified table

!indexes

List all the indexes for the specified table

!isolation

Sets the transaction isolation for this connection

!list

Lists the current connections

!manual

Displays the SQLLine manual

!metadata

Obtains metadata information

!nativesql

Shows the native SQL for the specified statement

!outputformat

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

table, vertical, csv, tsv, xmlattrs, xmlelements

!primarykeys

Lists all the primary keys for the specified table

!procedures

Lists all the procedures

!properties

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

!quit

Exits the program

!reconnect

Reconnects to the database

!record

Records all output to the specified file

!rehash

Fetches table and column names for command completion

!rollback

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

!run

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

!save

Saves the current variabes and aliases

!scan

Scans for installed JDBC drivers

!script

Starts saving a script to a file

!set

Sets a sqlline variable

!sql

Executes a SQL command

!tables

Lists all the tables in the database

!typeinfo

Displays the type map for the current connection

!verbose

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:

$SQLSTREAM_HOME/bin/sqlline

 

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:

  url=jdbc:sqlstream:sdp;sessionName=sqllineClient:sqlstreamuser@localhost

  driver=com.sqlstream.jdbc.Driver

  user=myusername

  password=mypassword

 

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:

  url=jdbc:mysql://sevilla:3306/

  driver=com.mysql.jdbc.Driver

  user=sqlstreamusername

  password=s-serverbpwd

 

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:

showFennelConfig.sql

showFarragoConfig.sql

showForeignServers.sql

showForeignStreams.sql

showProcedureColumns.sql

showProcedures.sql

showPumps.sql

showSchemas.sql

showSessionParams.sql

showSessions.sql

showStatements.sql

showSystemInfo.sql

showTableColumns.sql

showTableColumnsAll.sql

showUsers.sql

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

  myserver.conn.properties

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

url=jdbc:<database>://myhostname

driver=org.<database>.Driver

user=<database>

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;