Supply password to HSQLDB SqlTool in a script

2020-05-17 hsqldb

I am running an HSQLDB instance in server mode as a systemd service. To shut it down, I issue the following command:

java -cp $CLASSPATH:/usr/share/java/hsqldbutil.jar:/usr/share/java/hsqldb.jar "org.hsqldb.cmdline.SqlTool" --inlineRc=url=jdbc:hsqldb:hsql://localhost/$DB_NAME,user=SA,password=`cat ~/SA.pwd` --sql="SHUTDOWN;"

As one can see in the command, I connect as user SA with a password read from a file (which only that particular user can read), and specify both in the JDBC URL.

This works as long as SA has an empty password and I just supply password= in the command.

However, if SA has a real password and I supply it here, this fails with the error message:

'password' element must have empty value. For non-empty password, give no password element and you will be prompted for the value.

Is there any way to supply the password in a non-interactive way?

Answers

It works if you use a sqltool.rc file instead of --inline-rc. Place a file with the following contents in the home folder of the account which will be issuing the stop command:

urlid my-server
url jdbc:hsqldb:hsql://localhost/db_name
username SA
password CorrectHorseBatteryStaple

Then modify the command line as follows:

java -cp $CLASSPATH:/usr/share/java/hsqldbutil.jar:/usr/share/java/hsqldb.jar "org.hsqldb.cmdline.SqlTool" --sql="SHUTDOWN;" my-server

Where:

  • my-server is an arbitrary identifier that you define in sqltool.rc and quote in the command line invocation of SqlTool
  • db_name is the database name assigned at startup
  • CorrectHorseBatteryStaple is the SA password (as currently stored in ~/SA.pwd).

Change these as appropriate for your system. As sqltool.rc contains credentials, be sure to lock down its permissions as you would for ~/SA.pwd.

Related