Usage

Modes

SQL-DK works in several modes. The mode is determined by the combination of the CLI options.

Note: the SQL query can be specified at command line as parameter of the --sql option or passed on standard input using --sql-in option.

Query now

SQL command is immediately executed on given database. Formatted result set or updates result is printed on standard output.

$ sql-dk --db "MyDatabase" --sql "SELECT 'hello world'"

Prepare batch

SQL command (including parameters) is encoded in binary format and fushed on standard output. Typical use case is to redirect this output to a file for executing later.

$ sql-dk --batch --sql "SELECT 'hello world'" >> my.batch

If the file already exists it is not overwritten – new SQL commands are appended to the file – added to the batch. You can also simply concatenate several batches into bigger one:

$ cat first.batch second.batch third.batch > the-big.batch

Execute batch

Batch is loaded from the standard input and executed on given database. Formatted result set or updates result is printed on standard output.

$ sql-dk --db "MyDatabase" --batch < my.batch

CLI options

--db

Name of configured database.

--db-property

Key and value pair – additional parameter for the JDBC driver. Can be also configured persistently.

$ sql-dk … --db-property "useCompression" "true" …

What properties can be set? It depends on particular JDBC driver. SQL-DK is self-documenting, so you can list them easily – use option --list-jdbc-properties. Because there are usually long descriptions, you might tune your formatter or have really wide terminal:

$ sql-dk --list-jdbc-properties "MyDatabase" \
	--formatter tabular --formatter-property "trim" "true"

sql-dk --list-jdbc-properties "MyDatabase" \
	--formatter xhtml > driver-documentation.xhtml

You can generate yourself up-to-date documentation for exact version of the JDBC driver you have installed in your system. See sample output for PostgreSQL and MySQL drivers.

optional

--sql

SQL query. Can contain questionmarks (?) for ordinal parameters or placeholders with given prefix/suffix (see --name-refix and --name-suffix) e.g. :param1 for named parameters

--sql-in

Same as --sql but the query is not specified as command line parameter – is read from STDIN.

--formatter

Name of built-in or configured formatter. The formatter is responsible for printing the result sets and/or updates result (count of inserted/updated rows). See Built-in formatters and Custom formatters

List of available formatters can be printed by --list-formatters option.

optional (default formatter will be used)

--formatter-property

Key and value pair – additional parameter for the formatter. Can be also configured persistently.

$ sql-dk … --formatter-property "color" "true" …

optional

--data

This option must be the last – the rest of arguments is parsed as ordinal parameters. Its count must be the same as number of questionmarks (outside literals, of course) in the SQL query.

--data "first value" "second value" "third value" …

optional (if the query has no parameters or has named ones)

--data-named

This option must be the last – the rest of arguments is parsed as name/value pairs – the named parameters.

--data-named "paramName1" "some value 1" "paramName2" "some value 2" …

In the SQL query you can then use placeholders like this:

SELECT * FROM students WHERE name = :hisName AND surname = :hisSurname

--data-named "hisName" "Robert'); DROP TABLE students; --" "hisSurname" "Nobody"

Parameters in SQL (not in --data-named arguments) are surrounded by prefix (default is colon) and suffix (default is nothing followed by some non-word character).

SQL-DK internally replaces these placeholders with question marks to make standard parametrized query and then parametrizes it. A parameter can be used several times and can also be unused.

n.b. SQL-DK does not parse the SQL thus you should not use parameter-like looking strings inside SQL literals – it will broke your SQL command if you define such parameter. If you need to have literal like this:

SELECT 'Look, this :omg is not a named parameter';

in your query, you should not define parameter named omg. If you really need :omg inside your SQL literal (a string or even a table name) and at the same time the omg named parameter, you should specify different name prefix/suffix.

optional (if the query has no parameters or has ordinal ones)

--name-prefix and --name-suffix

These options allows you to specify regular expressions which describes prefix/suffix for named parameters. So you can use e.g. shell-styled variables in your SQL:

sql-dk --db blog --sql 'SELECT ${message}'  \
	--name-prefix '\$\{' --name-suffix '\}' \
	--data-named "message" "hello world"

Don't forget to use 'apostrophes' or escaping to avoid interpreting these ones by your shell.

--types

We have much more datatypes in SQL than just mere text strings. You can specify types of your ordinal parameters:

--types "INTEGER,BOOLEAN,VARCHAR" --data "123" "true" "hello"

Or for your named parameters:

--types "p1:INTEGER,p2:BOOLEAN,p3:VARCHAR" \
	--data-named "p1" "123" "p2" "true" "p3" "hello"

List of available types can be printed by --list-types option. Type names are case insensitive.

optional (default type is varchar)

--batch

This option has no arguments. If used together with --sql it means prepare batch mode. If used together with --db it means execute batch mode.

Prepare a batch consisting of several queries:

$ sql-dk --batch --sql "SELECT ?" --data "123" >> first.batch
$ sql-dk --batch --sql "SELECT ?" --data "456" >> first.batch
$ sql-dk --batch --sql "SELECT ?" --data "789" >> first.batch

And execute whole batch at once:

$ sql-dk --batch --db "MyDatabase" < first.batch

optional (not used in the query now mode)

Informational options

Exit codes

SQL-DK returns with one of these exit codes:

0 = success
1 = unexpected error (probably bug)
3 = SQL error
4 = CLI options parse error
5 = CLI options validation error
6 = configuration error
7 = formatting error
8 = batch error