Lx SQL Console User’s Guide
lx sqlis a console based utility for connecting to leanXcale databases and executing SQL commands. It is similar to other command-line database access utilities and derives from Marc Prud’hommeaux’s sqlline.
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 -P <verifier> the MFA verifier to use -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.
If MFA (and thus PAM) is in use, the -P flag gives the current authenticator value
for the user for the given service.
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.2) Driver: LeanXcale JDBC Driver (version 2.2) 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).
If MFA is in use, the current authenticator value can be added to the !connect command:
!connect jdbc:leanxcale://blade105:14420/db lxadmin foobar "" 123456
Note the empty string before the value (123456 here).
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
To connect using multiple QE addresses, you can supply extra addresses using the
attribute xaddr.
For example, provided there are JDBC servers at blade105:14420,
blade107:14420, and blade110:14420, this URL with extra addresses can be used:
jdbc:leanxcale://blade105:14420/db;xaddr=blade107:14420,blade110:14420
In this case, a random QE is selected to connect, and the connection will fail-over to other available servers when the one selected is not available.
If you supply an isolation mode in the URL, it will be overwritten by the default
isolation mode or the mode given to the -t argument.
That is, use -t to specify the isolation mode when using the lx sql tool.
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.
Commands can be given in the command line too. For example,
unix$ export LXPASS=mypassword unix$ history -c # to remove the password from the history file unix$ lx sql -n lxadmin 'select * from lxsys.CONNECTIONS'
Or, to save the MYTABLE table contents in CSV format into out.csv, just
unix$ lx sql -n lxadmin 'select * from MYTABLE' >out.csv
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
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.
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.
20. procedures
List stored procedures
!procedures [pattern]
Display a list of all the stored procedures that are exposed by the current database.
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.
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.2 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.
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.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.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.