lxClient
1. Introduction
lxClient is an SQL client that comes bundled with every LeanXcale install, and it’s
preconfigured so you can just use it without any setup. It’s based on the sqlline
SQL client and it’s a command line utility that uses our JDBC Connector to connect
to LeanXcale.
lxClient allows tab completion while you’re typing your commands, so usually you can just start typing the name of a table or column, press the tab key, and lxClient will complete the rest of the name automatically or provide you with a list of options for you to choose from.
You’ll need to connect to one of the nodes where you have LeanXcale installed to use it.
To run it, you just run the lxClient command:
user@host:~/lxs$ lxClient
sqlline version 1.8.0
lx>Once you get the lx> prompt, you can start typing commands, including the
one to connect to your database. Give a look to our
Examples section.
2. Parameters
These are the parameters you can use pass on the command line. If you want to know what variables you can set or what keyboard keys you can press to do important stuff, give a look to the Get Help section.
-u <database url> the JDBC URL to connect to
-n <username> the username to connect as
-p <password> the password to connect as
-d <driver class> the driver class to use
-e <command> the command to execute
-nn <nickname> nickname for the connection
-ch <command handler>[,<command handler>]* a custom command handler to use
-f <file> script file to execute (same as --run)
-log <file> file to write output
-ac <class name> application configuration class name
-ph <class name> prompt handler class name
--color=[true/false] control whether color is used for display
--colorScheme=[chester/dark/dracula/light/obsidian/solarized/vs2010] Syntax highlight schema
--confirm=[true/false] confirm before executing commands specified in confirmPattern
--confirmPattern=[pattern] pattern of commands to prompt confirmation
--csvDelimiter=[delimiter] Delimiter in csv outputFormat
--csvQuoteCharacter=[char] Quote character in csv outputFormat
--escapeOutput=[true/false] escape control symbols in output
--showHeader=[true/false] show column names in query results
--headerInterval=ROWS the interval between which headers are displayed
--fastConnect=[true/false] skip building table/column list for tab-completion
--autoCommit=[true/false] enable/disable automatic transaction commit
--verbose=[true/false] show verbose error messages and debug info
--showTime=[true/false] display execution time when verbose
--showWarnings=[true/false] display connection warnings
--showNestedErrs=[true/false] display nested errors
--strictJdbc=[true/false] use strict JDBC
--nullValue=[string] use string in place of NULL values
--numberFormat=[pattern] format numbers using DecimalFormat pattern
--dateFormat=[pattern] format dates using SimpleDateFormat pattern
--timeFormat=[pattern] format times using SimpleDateFormat pattern
--timestampFormat=[pattern] format timestamps using SimpleDateFormat pattern
--force=[true/false] continue running script even after errors
--maxWidth=MAXWIDTH the maximum width of the terminal
--maxColumnWidth=MAXCOLWIDTH the maximum width to use when displaying columns
--maxHistoryFileRows=ROWS the maximum number of history rows to store in history file
--maxHistoryRows=ROWS the maximum number of history rows to store in memory
--mode=[emacs/vi] the editing mode
--silent=[true/false] be more silent
--autosave=[true/false] automatically save preferences
--outputformat=[table/vertical/csv/tsv/xmlattrs/xmlelements/json]
format mode for result display
--isolation=LEVEL set the transaction isolation level
--run=/path/to/file run one script and then exit
--historyfile=/path/to/file use or create history file in specified path
--useLineContinuation=[true/false] Use line continuation
--incremental=[true/false] display result rows immediately as they are
fetched, yielding lower latency and memory
usage at the price of extra display column padding
--incrementalBufferRows integer threshold at which to switch to incremental mode
--help display this message
3. Usage Examples
3.1. Connecting to LeanXcale
If you’re connected by SSH directly to one of the Compute nodes of the instance,
you can use localhost to connect to it. In any other case, you should use the
IP or hostname of the Compute node:
user@host:~/lxs$ lxClient
sqlline version 1.8.0
lx> !connect jdbc:leanxcale://localhost:1522/test_db user1 pass4user1
lx>3.2. Connecting in one step
You can connect directly from the command line with lxClient:
user@host:~/lxs$ lxClient --color=true --autoCommit=true -u jdbc:leanxcale://localhost:1522/test_db -n user1 -p pass4user1
lx>In case you are using LeanXcale with Security and you want to connect from outside your cluster, connection string would look like this
user@host:~/lxs$ lxClient --color=true --autoCommit=true -u jdbc:leanxcale://localhost:1522/test_db;secure=true -n user1 -p pass4user1
lx>Note: Keep in mind that you need to have previously added your TLS certificate to your trusted ca certificates store. Check how to do it here.
3.3. Creating tables
lx> CREATE TABLE persons (
.)> PersonID int,
.)> LastName varchar(255),
.)> FirstName varchar(255),
.)> Address varchar(255),
.)> City varchar(255)
.)> );
No rows affected (0.552 seconds)3.4. Inserting rows
lx> INSERT INTO persons VALUES (7, 'Smith', 'John', 'Calle Pez, 8', 'Madrid');
1 row affected (0.116 seconds)3.5. Inserting multiple rows
lx> INSERT INTO persons VALUES
(8, 'Lopez', 'Juan', 'Paseo de Gracia, 45', 'Barcelona'),
(9, 'García', 'Manuel Alberto', 'Agua Street, 45', 'Sevilla'),
(10, 'Gomez', 'Israel', 'City of the Arts, 291', 'Valencia');
3 rows affected (0.182 seconds)3.6. Listing tables
lx> !tables
+-----------+-------------+--------------+--------------+---------+----------+------------+-----------+---------------------------+----------------+
| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_CAT | TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION |
+-----------+-------------+--------------+--------------+---------+----------+------------+-----------+---------------------------+----------------+
| | LXSYSMETA | CONNECTIONS | SYSTEM TABLE | | | | | | |
| | LXSYSMETA | FOREIGN_KEYS | SYSTEM TABLE | | | | | | |
| | LXSYSMETA | INDEXES | SYSTEM TABLE | | | | | | |
| | LXSYSMETA | PARTITIONS | SYSTEM TABLE | | | | | | |
| | LXSYSMETA | PRIMARY_KEYS | SYSTEM TABLE | | | | | | |
| | LXSYSMETA | TABLE_CHECKS | SYSTEM TABLE | | | | | | |
| | LXSYSMETA | TRANSACTIONS | SYSTEM TABLE | | | | | | |
| | SYSMETA | COLUMNS | SYSTEM TABLE | | | | | | |
| | SYSMETA | TABLES | SYSTEM TABLE | | | | | | |
| | USER1 | PERSONS | TABLE | | | | | | |
+-----------+-------------+--------------+--------------+---------+----------+------------+-----------+---------------------------+----------------+3.7. Querying a table
lx> SELECT * FROM persons;
+----------+----------+----------------+-----------------------+-----------+
| PERSONID | LASTNAME | FIRSTNAME | ADDRESS | CITY |
+----------+----------+----------------+-----------------------+-----------+
| 8 | Lopez | Juan | Paseo de Gracia, 45 | Barcelona |
| 10 | Gomez | Israel | City of the Arts, 291 | Valencia |
| 9 | García | Manuel Alberto | Agua Street, 45 | Sevilla |
| 7 | Smith | John | Calle Pez, 8 | Madrid |
+----------+----------+----------------+-----------------------+-----------+
4 rows selected (0.213 seconds)3.8. Querying a table in descendent order
lx> SELECT * FROM persons ORDER BY personid DESC;
+----------+----------+----------------+-----------------------+-----------+
| PERSONID | LASTNAME | FIRSTNAME | ADDRESS | CITY |
+----------+----------+----------------+-----------------------+-----------+
| 10 | Gomez | Israel | City of the Arts, 291 | Valencia |
| 9 | García | Manuel Alberto | Agua Street, 45 | Sevilla |
| 8 | Lopez | Juan | Paseo de Gracia, 45 | Barcelona |
| 7 | Smith | John | Calle Pez, 8 | Madrid |
+----------+----------+----------------+-----------------------+-----------+
4 rows selected (0.182 seconds)3.9. Get help
lx> !help
!all Execute the specified SQL against all the current
connections
!appconfig Set custom application configuration class name
!autocommit Set autocommit mode on or off
!batch Start or execute a batch of statements
!brief Set verbose mode off
!call Execute a callable statement
!close Close the current connection to the database
!closeall Close all current open connections
!columns List all the columns for the specified table
!commandhandler Add a command handler
!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
!manual Display the SQLLine manual
!metadata Obtain metadata information
!nativesql Show the native SQL for the specified statement
!nickname Create a friendly name for the connection (updates command
prompt)
!outputformat Set the output format for displaying results (table,
vertical, csv, tsv, xmlattrs, xmlelements, json)
!primarykeys List all the primary keys for the specified table
!procedures List all the procedures
!prompthandler Set custom prompt handler class name
!properties Connect to the database specified in the properties file(s)
!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
!rerun Execute previous command from the history file
!reset Reset a sqlline variable
!rollback Roll back the current transaction (if autocommit is off)
!run Run a script from the specified file
!save Save the current variables and aliases
!scan Scan for installed JDBC drivers
!script Start saving a script to a file
!set List / set a sqlline variable
!sql Execute a SQL command
!tables List all the tables in the database
!typeinfo Display the type map for the current connection
!verbose Set verbose mode on
Variables:
Variable Value Description
=============== ========== ==================================================
autoCommit true/false Enable/disable automatic transaction commit
autoSave true/false Automatically save preferences
color true/false Control whether color is used for display
colorScheme chester/dark/dracula/light/obsidian/solarized/vs2010
Syntax highlight schema
confirm true/false Whether to prompt for confirmation before running
commands specified in confirmPattern (default:
false)
confirmPattern pattern A regexp that defines the dangerous commands for
which to prompt Are you sure? before execution;
(default: DELETE and DROP)
csvDelimiter String Delimiter in csv outputFormat
csvQuoteCharacter char Quote character in csv outputFormat
dateFormat pattern Format dates using SimpleDateFormat pattern
escapeOutput true/false Escape control symbols in output
fastConnect true/false Skip building table/column list for tab-completion
force true/false Continue running script even after errors
headerInterval integer The interval between which headers are displayed
historyFile path File in which to save command history. Default is
$HOME/.sqlline/history (UNIX, Linux, Mac OS),
$HOME/sqlline/history (Windows)
incremental true/false Do not receive all rows from server before
printing the first row; uses fewer resources,
especially for long-running queries, but column
widths may be incorrect
incrementalBufferRows integer Threshold at which to switch to incremental
mode; query starts in non-incremental mode, but
after the this many rows, switches to incremental
isolation LEVEL Set transaction isolation level
maxColumnWidth integer The maximum width to use when displaying columns
maxHeight integer The maximum height of the terminal
maxWidth integer The maximum width of the terminal
maxHistoryFileRows integer The maximum number of history rows
to store in history file
maxHistoryRows integer The maximum number of history rows
to store in memory
mode emacs/vi The editing mode
nullValue String Use String in place of NULL values
numberFormat pattern Format numbers using DecimalFormat pattern
outputFormat table/vertical/csv/tsv/xmlattrs/xmlelements/json
Format mode for result display
prompt pattern Format prompt
promptScript String Script code to execute to generate a prompt
propertiesFile path File from which SQLLine reads properties on
startup; default is
$HOME/.sqlline/sqlline.properties (UNIX, Linux,
macOS), $HOME/sqlline/sqlline.properties (Windows)
rightPrompt pattern Format right prompt
rowLimit integer Maximum number of rows returned from a query; zero
means no limit
showElapsedTime true/false Display execution time when verbose
showHeader true/false Show column names in query results
showNestedErrs true/false Display nested errors
showWarnings true/false Display connection warnings
silent true/false Be more silent
strictJdbc true/false Use strict JDBC
timeFormat pattern Format times using SimpleDateFormat pattern
timeout integer Query timeout in seconds; less than zero means no
timeout
timestampFormat pattern Format timestamps using SimpleDateFormat pattern
trimScripts true/false Remove trailing spaces from lines read from script
files
useLineContinuation true/false Use line continuation
verbose true/false Show verbose error messages and debug info
version version Show the current sqlline version.
The property is read only.
Key-strokes:
alt-b Backward word
alt-c Capitalize word
alt-d Kill word
alt-f Forward word
alt-h Next color scheme
alt-l Lowercase word
alt-n History search forward
alt-p History search backward
alt-t Transpose words
alt-u Uppercase word
ctrl-a To the beginning of line
ctrl-b Backward char
ctrl-d Delete char
ctrl-e To the end of line
ctrl-f Forward char
ctrl-h Backward delete char
ctrl-i Complete
ctrl-j Enter
ctrl-k Kill the line
ctrl-m Enter
ctrl-n Down line or history
ctrl-p Up line or history
ctrl-r History incremental search backward
ctrl-s History incremental search forward
ctrl-t Transpose chars
ctrl-u Kill the whole line
ctrl-w Backward kill the line