Lx SQL Console User’s Guide

1. Usage

To run the console use this command on a installed system

unix$ lx sql
lx%

To exit, type control-D on UNIX or control-Z on Windows, or the !quit command.

This is the command usage:

usage: lx/sql  [options] [command...]
  -a                              enable automatic transaction commit
  -c <number>                     show header every number lines (0 for default)
  -d <driver class>               the driver class to use
  -D                              debug diags
  -E                              display nested errors and connection warnings
  -e                              display nested errors
  -F <number>                     the maximum field width to use when displaying columns
  -f <file>                       use file as input
  -h                              display this message
  -i                              ignore errors
  -N <pattern>                    format numbers using DecimalFormat pattern
  -n <username>                   the username to connect as
  -o table|vertical|csv|tsv       set output format
  -p <password>                   the password to connect as
  -q                              be quiet
  -t LEVEL                        set the transaction isolation level
  -u <database url>               the JDBC URL to connect to
  -v                              show verbose error messages and debug info
  -W <number>                     the maximum width of the terminal
  -w                              increase the width used for output

The installed lxsql knows the address to dial the database, and it will try to connect when called.

The user and password used to connect can be given using flags -n and -p. When -p is not used, if the environment variable LXPASS is set, its value is used as the password.

It is also possible to run this command using the lxsql.SqlLine program from the lxsql JAR file. To do this, both the lxsql JAR and the leanXcale JDBC JAR files are required. Put both files in your CLASSPATH and run it as in

unix$  java lxsql.SqlLine ...

In this case, you have to supply the URL using the -u flag or use a !connect command as shown later.

2. Connections

This is only relevant when using the JAR file or opening multiple connections. The lx sql command connects to the installed system on its own when called.

To connect to a database, supply the URL using flag -u, or issue a !connect command.

The URL starts with "jdbc:", and usually has the machine name of the database and the name of the database itself as part of the string.

For example, this is a command to dial a database:

unix$ lx sql
lx% !connect jdbc:leanxcale://blade105:14420/db lxadmin foobar
Connecting to jdbc:leanxcale://blade105:14420/db
Connected to: LeanXcale (version 2.0)
Driver: LeanXcale JDBC Driver (version 2.0)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
lx1%

Multiple connections can be used at the same time. In fact, in this example, a second connection has been open (the first one was open to the installed system used).

The prompt shows the connection number, and the set of connections is shown by the !list command:

lx1% !list
2 active connections:
 #0  open     jdbc:leanxcale://blade105:14420/db
 #1  open     jdbc:leanxcale://blade105:14420/db

The connection used to issue statements can be set using the command go.

lx1% !go 0
lx0% !list
2 active connections:
 #0  open     jdbc:leanxcale://blade105:14420/db
 #1  open     jdbc:leanxcale://blade105:14420/db

3. Commands

Once connected, you can type SQL commands. For example,

lx% SELECT * FROM lxsys.CONNECTIONS;
              connectionId                          kiviConnectionId
  65ead86e-a4bb-4bb5-8025-d5c550b6d19c    b8087e0d-6be1-41f0-b811-78e187e66e58
  5038c023-7c33-4c88-8a86-15e8482dcf2f    ca8fa047-954d-42b8-8384-7ec3a25321b5
2 rows selected (2.282 seconds)

Commands that start with ! are not SQL commands, but console commands described next. Commands that start with !! are shell scapes, and are executed (without keyboard input) by a system shell.

SQL commands must end with a ; character. When they do not, the console will read further lines until they do.

Interrupting with Control-c while typing commands discard the command typed so far, but it might require typing Enter after interrupting.

4. 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.

Example:

lx% !autocommit off
Autocommit status: false
lx% !!date
Sun Dec 17 09:58:42 UTC 2023
lx%

5. 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.

Example:

lx% !batch
...inserts...
lx% !batch

6. close

Close the active connection

!close

Close the current connection and remove it from the connection list.

Example:

lx% !list
1 active connection:
 #0  open     jdbc:leanxcale://blade105:14420/db
lx% !close
Closing: com.leanxcale.client.multi.MultiClientConnection
lx% !list
No active connections

7. columns

Display columns of a table

!columns tablename

8. commit

Commit the current transaction

!commit

9. dbinfo

Provide information about the current database

!dbinfo

10. describe

Describe a table

!describe tablename

11. dropall

Drop all tables in the database

!dropall

Example:

lx% !dropall
Really drop every table in the database? (y/n) y

This command will destroy all data in the database (subject to access restrictions). Use with extreme caution.

12. exportedkeys

List exported foreign keys for a database

!exportedkeys tablename

List all the foreign keys that are exported by the specified table.

13. help

Display information about allowed commands.

!help

14. importedkeys

List imported foreign keys for a database

!importedkeys tablename

15. indexes

List all the indexes on the specified table name.

!indexes tablename

16. isolation

Set the transaction isolation mode for the active connection

!isolation level

17. metadata

Run metadata methods.

!metadata method [args...]

Example

lx% !metadata supportsSelectForUpdate
false
lx% !metadata getTypeInfo
             TYPE_NAME              DATA_TYPE    PRECISION             LITERAL
  BOOLEAN                           16           1
  TINYINT                           -6           3
...

Refer to the Java sql.DatabaseMetaData class for documentation on methods.

18. outputformat

Change the method for displaying SQL results

!outputformat table|vertical|csv|tsv|xmlattr|xmlelements

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.

19. primarykeys

Display the primary key columns for a table

!primarykeys tablename

20. procedures

List stored procedures

!procedures [pattern]

Display a list of all the stored procedures that are exposed by the current database.

21. quit

Exit the console.

!quit

22. reconnect

Reconnect to the current database

!reconnect

23. record

Record output from SQL commands

!record [filepath]

Save all output from the session to the specified file when the file name is given, and stop recording when no file name is given.

24. 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.

25. rollback

Rollback the current transaction, if autocommit is off.

!rollback

26. run

Execute a command script

!run filename

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 flag is set.

27. 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.

Example:

lx% !scan
scan complete in 2ms
1 driver class found
Compliant Version Driver Class
yes       2.0     com.leanxcale.client.Driver

28. script

Save executed commands to a file

!script [filename]

Start saving all commands entered to the specified file when a file name is given. Stop saving when no file name is given.

29. set

Set a preference or flag

!set [flag value]

Set the specified preference to the specified value. Without arguments, list preferences and their values.

Example:

lx% !set
autocommit          true
autosave            false
color               false
fastconnect         true
force               false

30. sql

Execute a SQL against a database.

!sql stmt

The same effect is achieved by just typing the statement without a command.

31. tables

List all the tables in the database

!tables

32. Flags

This section describes flags that can be listed or set with the set command. Some of them may be set through command line flags, as shown early in this document.

32.1. autocommit

If true, then new connections will have autocommit set, otherwise, transactions will need to be explicitely committed or rolled back. Defaults to true. <sect1 id="setting_autos

32.2. fastconnect

When false, any new connection will access information about the available tables and columns. Table and column information can always be explicitely retrieved using the rehash command.

32.3. force

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. Otherwise, errors will be displayed but otherwise ignored. Defaults to false.

32.4. headerinterval

The interval between which the column headers will be redisplayed when using the table outputformat. Defaults to 100.

32.5. incremental

When set to false, the entire result set is fetched and buffered before being displayed, yielding optimal display column sizing. Otherwise 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.

32.6. isolation

The default transaction isolation that will be used for new connections.

32.7. maxcolumnwidth

The maximum column width to display for each colummn before truncating data when using the table Defaults to 15.

32.8. maxwidth

The maximum width to display before truncating data when using the table output format. Defaults to 80.

32.9. numberformat

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 text.DecimalFormat.

For example, the setting 0.###E0 yields scientific notation with up to three fractional digits.

32.10. outputformat

The format for how results are displayed.

32.11. showheader

If true, display the names of the columns when displaying results.

32.12. shownestederrs

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.

32.13. showwarnings

If true, display any warnings that are reported on the connection after issuing any database commands. Defaults to false.

32.14. silent

If true, then reduce the amount of informational messages displayed. Useful for redirecting a command to a file for later parsing. Defaults to false.

32.15. verbose

If true, then print out the entire java stack trace whenever an error occurs, as well as displaying debugging information. Defaults to false.