The JdbcClient program provides a command-line interface (inspired by
mclient) for interactive SQL query processing.
It is written entirely in Java using the JDBC API and the MonetDB JDBC driver.
The program is available as a single small jar file
jdbcclient.jre8.jar downloadable from MonetDB Java Download Area.
The JAR file already includes the MonetDB JDBC driver, which is used to communicate with a MonetDB server, so no additional JAR files (or
classpath settings) are needed.
The JdbcClient program supports startup options. To view them, type in a shell the command:
java -jar jdbcclient.jre8.jar --help
This command will show the following usage information:
Usage java -jar jdbcclient.jre8.jar [-h host[:port]] [-p port] [-f file] [-u user] [-l language] [-d database] [-e] [-D [table]] [--csvdir /path/to/csvfiles]] [-X<opt>] | [--help] | [--version] or using long option equivalents --host --port --file --user --language --dump --echo --database. Arguments may be written directly after the option like -p50000. If no host and port are given, localhost and 50000 are assumed. An .monetdb file may exist in the user's home directory. This file can contain preferences to use each time JdbcClient is started. Options given on the command line override the preferences file. The .monetdb file syntax is <option>=<value> where option is one of the options host, port, file, mode debug, or password. Note that the last one is perilous and therefore not available as command line option. If no input file is given using the -f flag, an interactive session is started on the terminal. OPTIONS -h --host The hostname of the host that runs the MonetDB database. A port number can be supplied by use of a colon, i.e. -h somehost:12345. -p --port The port number to connect to. -f --file A file name to use either for reading or writing. The file will be used for writing when dump mode is used (-D --dump). In read mode, the file can also be an URL pointing to a plain text file that is optionally gzip compressed. -u --user The username to use when connecting to the database. -d --database Try to connect to the given database (only makes sense if connecting to monetdbd). -l --language Use the given language, defaults to 'sql'. --csvdir The directory path where csv data files are read or written when using ON CLIENT clause of COPY command. --help This help screen. --version Display driver version and exit. -e --echo Also outputs the contents of the input file, if any. -q --quiet Suppress printing the welcome header. -D --dump Dumps the given table(s), or the complete database if none given. -Xoutput The output mode when dumping. Default is sql, xml may be used for an experimental XML output. -Xhash Use the given hash algorithm during challenge response. Supported algorithm names: SHA512, SHA384, SHA256 and SHA1. -Xdebug Writes a transmission log to disk for debugging purposes. If a file name is given, it is used, otherwise a file called monet<timestamp>.log is created. A given file never be overwritten; instead a unique variation of the file is used. -Xbatching Indicates that a batch should be used instead of direct communication with the server for each statement. If a number is given, it is used as batch size. i.e. 8000 would execute the contents on the batch after each 8000 statements read. Batching can greatly speedup the process of restoring a database dump.
It is easy to start a JdbcClient program (assuming you have a MonetDB/SQL server running) from a shell, for example:
% java -jar jdbcclient.jre8.jar -p50000 -ddemo -umonetdb password: Welcome to the MonetDB interactive JDBC terminal! JDBC Driver: MonetDB Native Driver v3.2 (Liberica 20220127 based on MCL v1.21) Database Server: MonetDB v11.43.9 Current Schema: sys Type \q to quit (you can also use: quit or exit), \? or \h for a list of available commands auto commit mode: on sql>
As the password cannot be provided as a startup option, you will be asked to enter it after the
If you do not want to enter the password each time, use a
.monetdb file, which contains the
If the authentication or connection fails, observe the printed error messages from JdbcClient (or the merovingian logs) for clues.
After a successful connection, the
sql> prompt is shown, allowing you to enter any SQL query or backslash command and execute it using the enter-key.
You can use multiple lines to enter the SQL query. To execute it, enter the
; character after the SQL query and press the enter-key. For example:
sql> select * from table_types more> order by 2; +---------------+------------------------+ | table_type_id | table_type_name | +===============+========================+ | 20 | GLOBAL TEMPORARY TABLE | | 30 | LOCAL TEMPORARY TABLE | | 3 | MERGE TABLE | | 5 | REMOTE TABLE | | 6 | REPLICA TABLE | | 10 | SYSTEM TABLE | | 11 | SYSTEM VIEW | | 0 | TABLE | | 1 | VIEW | +---------------+------------------------+ 9 rows Elapsed Time: 2 ms sql>
To view a list of available backslash commands, enter
\? after the
sql> prompt and press the enter-key.
sql> \? Available commands: \q quits this program (you can also use: quit or exit) \d list available user tables and views in current schema \dt list available user tables in current schema \dv list available user views in current schema \df list available user functions in current schema \dp list available user procedures in current schema \ds list available user sequences in current schema \dn list available user schemas \dS list available system tables and views in sys/tmp schema \dSt list available system tables in sys/tmp schema \dSv list available system views in sys/logging schema \dSf list available system functions in current schema \dSp list available system procedures in current schema \dSn list available system schemas \d <obj> describes the given table or view \l<uri> executes the contents of the given file or URL \i<uri> batch executes the inserts from the given file or URL \vsci validate sql system catalog integrity \vsi <schema> validate integrity of data in the given schema \vdbi validate integrity of data in all user schemas in the database \? or \h this help screen sql>
\q to quit the program. Alternatively, you can use
\v commands allow you to quickly validate the data integrity of: system catalogue tables, tables in one schema, or all user schemas in the database:
\vsci \vsi schema_name \vdbi
The data integrity validations include:
NOT NULLconstraint (currently only for
It is advised to run
\vsci command before and after an upgrade of the MonetDB server to check if the system catalogue tables are consistent.
\vsi my_schema to validate data for all tables of a specific schema.
\vdbi to validate the integrity of user data in all user schemas in the database.
The data integrity validation can take a long time to complete, depending on the number of user schemas, tables, columns and rows in the tables in a database.
The integrity validation functionality is still beta, so you may get false violations reported. If you encounter any, first check if you are using the latest available JdbcClient program. If so, report the issue via github.com/MonetDB/monetdb-java/issues.
The JdbcClient program does not support the SQL
DEBUG <query>; command. Use the
mclient program for those instead.
The JdbcClient program is provided as a test tool for performing connectivity and interactive tests using the integrated MonetDB JDBC driver.
It is NOT recommended for production environments; the
mclient program should be used instead.