Running SQL Queries

To use the database with the standard SQL client, you can use this command supplying the user name and secret:

unix$ lx sql -n lxadmin -p ****
lx% !tables
...

It is suggested not to give the password in the command line, but to open the connection later within the sql prompt, to prevent listing the password in the process list for the underlying password. This was just an example.

The isolation model can be set using the -M argument, as in:

unix$ lx sql -Msession -n lxadmin -p ****
lx% !tables
...

LeanXcale isolation levels are (from strongest to weakest):

  • session_consistency or session: the transaction has as snapshot then one incorporating the changes from the last update transaction in the same session.

  • snapshot_isolation or si: standard snapshot isolation, that is, when the transaction starts gets the current snapshot and all reads are performed over that snapshot.

  • read_committed or rc: each SQL statement gets the current snapshot and reads are performed from that snapshot.

  • raw_read_committed or rawrc: each read performed by an SQL statement get the latest committed value.

  • loader: Special isolation level to accelerate database loads. It is like read committed but it does not perform conflict detection nor logging. This can only be done without any other transactions accessing the database.

Communication between the SQL client and the query engine(s) is not encrypted by default. Install using the tls property to ask for encrypted communications.

The lx sql command does this on its own, but, when using a connection URL on a standard JDBC client, add the

tls=yes

property to the connection property set. This tells the leanXcale client driver to use TLS.