rdm-sql

Command line SQL scripting tool

Synopsis

rdm-sql [OPTIONS]... [input_file [arg]... ] 

Description

rdm-sql is an SQL utility with support for interactive and non-interactive use. When used interactively, rdm-sql allows you to execute SQL statements and view result sets. When used non-interactively (called “batch mode”), it processes the SQL statements as well as its interface commands (described later) stored in an input file or a redirection of standard input.

To use it interactively, simply invoke it from your command prompt as follows:

$ rdm-sql

You can connect to a remote server as follows:

$ rdm-sql --connect db-uri

The db-uri specifies the location of the remote server.

If you want to connect a remote TFS, you must first start the TFS Server. In the bin directory of the installation, run the rdm-tfs executable.

To use it in batch mode, specify the name of the input file following the tool name or redirect standard input:

$ rdm-sql input_file

Options

-h, --help Display this usage information
--version Display the version information
-q, --quiet Quiet mode. No information will be displayed
--docroot=pathspec The location of the docroot directory. (See Database Storage Location)
-C, --connect=db-uri Set up the initial connection to a remote RDM SQL server
input_file Use an input file to run it in batch mode
arg Arguments to the input file script

Interface Commands

rdm-sql has a set of options you can invoke inside the interactive shell. They are called "interface commands". To view the list of available interface commands, type ".h" in the interactive shell.

Enter ? for list of interface commands.

rdm-sql: ?

The following interface commands are supported.

Command Description
; A semicolon is used to mark the end of a single SQL statement. For compound SQL statements such as stored routine definitions, an extra semicolon must be added at the end of the SQL statement.
-- Comment marker. Characters following "--" will be ignored through the end of the line.
.b [on | off] Set or toggle the 'binary column' value mode
.d [on | off] Set or toggle the 'display SQLSTATE' mode. When this option is enabled, an error message will include the 5-digit SQLSTATE code and the native numeric error code in addition to the error message.
.e [on | off] Set or toggle the 'statement echo' mode
.h | .? Display help for rdm-sql interface commands
.k n Set rowset size to n
.l n Set output page length to n lines
.m message Display a message
.n Display the next row when the table mode is off. When the 'table display' mode is disabled, entering ".n" will display the next row in the result set.
.o [on | off] Set or ignore the 'ignore output' mode
.q Quit rdm-sql
.r file Read statement(s) from file. The file may contain SQL statements and interface commands.
.s [file] Set or toggle the 'save' mode. Specifying a file name will always set it on. The file argument can instead be 'on' or 'off' to explicitly turn it on or off.
.t [on | off] Set or toggle the 'table display' mode. When this option is enabled, an SQL query result set will be returned in a tabular mode, with each row on a new line. Turn this option off to view the result set one row at a time.
.v [on | off] Set or toggle the 'warning display' mode. When this option is enabled, rdm-sql will print out diagnostic messages when an operation returns a non-error status. The option is off by default, in which case only messages from errors will be printed out.
.w n Set output page width to n columns.
.x [on | off] Set or toggle the 'advanced command' mode. With this option enabled, Entering '.?' or '.h' will give you a full list of interface commands including the advanced ones. The details of the advanced commands may change for GA.

Usage Examples

Processing simple SQL statements

Simple SQL statements can be processed in rdm-sql by entering each statement with a single semicolon at the end. A statement can be split across multiple lines. The prompt changes to '>' to indicate that you are in the middle of a statement.

rdm-sql: CREATE DATABASE bookshop;
rdm-sql: CREATE TABLE author (
rdm-sql>     last_name CHAR(13) PRIMARY KEY,
rdm-sql>     full_name CHAR(25),
rdm-sql>     gender CHAR(1),
rdm-sql>     yr_born SMALLINT,
rdm-sql>     yr_died SMALLINT,
rdm-sql>     short_bio VARCHAR(216),
rdm-sql>     KEY yob_gender_key(yr_born, gender)
rdm-sql> );
rdm-sql: COMMIT;

Processing compound SQL statements

In addition to the simple SQL statements, rdm-sql can also process compound SQL statements. A compound SQL statement consists of one or more simple SQL statements. In RDM, the only type of compound SQL statements supported is stored routine definitions.

Since a compound SQL statement can contain multiple SQL statements, each ending with a semicolon, an extra semicolon must be added at the end of the statement to mark the completion of the routine. Below is an example, which defines a function named fib.

rdm-sql: USE bookshop;
rdm-sql: CREATE FUNCTION fib(n INTEGER) RETURNS INTEGER
rdm-sql> BEGIN
rdm-sql>     DECLARE gr DOUBLE;
rdm-sql>     SET gr = (1 + sqrt(5)) / 2;
rdm-sql>     RETURN round((power(gr, n) - power((1 - gr), n)) / sqrt(5));
rdm-sql> END;
rdm-sql> ;

The extra semicolon below the END statement indicates the end of the CREATE FUNCTION statement.

The fib function can be called as follows.

rdm-sql: SELECT fib(6);

     fib(6)
-----------
          8
*** 1 row(s) returned