Getting Started

Jaqy is a Java based console application. It runs a simple script language that mixes SQL with commands.

A command is simply a name token with a dot prefix. For instance, .open, .close, etc.

Jaqy also supports SQL line comments which starts with --. It should be noted that Jaqy does not recognize comments /* */. Comments recognized by Jaqy is not sent to the database, but sometimes such behavior may not be wanted since it may be used to contain some metadata for logging purposes. Thus, /* */ is not recognized by Jaqy.

Launching Jaqy

Once downloaded jaqy-1.2.0.jar, it can be run using the following command.

java -jar jaqy-1.2.0.jar

If you need to load / export large amount of data though, it can be necessary to allocate a bit more memory.

java -Xmx256m -jar jaqy-1.2.0.jar

If you are going to have Unicode outputs, it may be necessary to specify the encoding for the standard output.

java -Dfile.encoding=UTF-8 -Xmx256m -jar jaqy-1.2.0.jar

It is recommended to have the above script in an alias

alias jq='java -Dfile.encoding=UTF-8 -Xmx256m -jar jaqy-1.2.0.jar'

or a batch file.

Opening a Connection

To connect a database, you will need to the JDBC driver for your database. Here I use Teradata JDBC driver as an example.

.protocol teradata com.teradata.jdbc.TeraDriver
.classpath teradata lib/terajdbc4.jar
.open -u dbc -p dbc teradata://127.0.0.1

The first command .protocol specifies the driver information for teradata protocol. The second command .classpath specifies the class path to dynamically load the driver. The last command .open actually opens a JDBC connection to 127.0.0.1 with user dbc and password dbc.

The .protocol command is actually not necessary here since Jaqy actual knows the driver name of several databases. You can look at the existing list of driver names by simply run .protocol command by itself.

The .classpath command if used frequently, can be put in .jqrc in your home directory, so that one does not need to type it. The driver is not loaded until the corresponding JDBC protocol is used.

Note

In Jaqy, all relative paths are relative to the current script file (which can be nested), or in case of interactive mode, the current working directory.

The .open command is used to initiate a database connection via JDBC driver. -u option is used to specify the user name dbc and -p option is used to specify the password dbc. The connection URL teradata://127.0.0.1 is basically a JDBC connection URL without jdbc: prefix.

Tip

Use .@open instead of .open in scripts to hide the command which contains the password.

Exeuting a SQL Query

Jaqy recognizes any text that are not started with . as SQL. The SQL block is ended with a semicolon ; as the last character on a line.

SELECT *
FROM MyTable
ORDER BY a;

It is possible to enter chain multiple queries in a single statement or enter a slightly complex statement by avoiding having the first ; as the last character on a line. Here are two examples.

CREATE MACRO myquery AS
(
        SELECT * FROM MyTable
        ORDER BY a
;);

SELECT * FROM MyTable ORDER BY a
; SELECT * FROM MyTable ORDER BY a;

To enter even more complex SQL as a single statement, as in the case of a stored procedure, one can use .exec to do so.

.exec
CREATE MACRO myquery AS
(
        SELECT * FROM MyTable
        ORDER BY a
;);
.end exec

Or simply use .exec to execute an external SQL file.

.exec mysp.sql

Closing a Connection

The command is simply

.close

Exiting Jaqy

You can either run .quit or .exit command to exit Jaqy.

Running a Jaqy Script

You can enter commands or SQL interactively, or use put everything in a script and pipe in the input.

.open -u dbc -p dbc teradata://127.0.0.1

/* switch to test database */
DATABASE test;

CREATE TABLE MyTable
(
        a INTEGER,
        b INTEGER
);

-- Populate with data
INSERT INTO MyTable VALUES (1, 1);
INSERT INTO MyTable VALUES (2, 2);

-- Testing macro creation with ; handling
CREATE MACRO myquery AS
(
        SELECT * FROM MyTable
        ORDER BY a
;);

EXEC myquery;

-- Cleaning up
DROP MACRO myquery;
DROP TABLE MyTable;

.quit

jq < input.sql

The output of a script is generally to the standard output. However, some JDBC drivers may print debugging or error information to the standard error.