Products Support Documentation Download
Command Line SQL Scripting Tool (rdm-sql)
Synopsis
rdm-sql [OPTIONS]... [input_file [arg]... ] 
Description

rdm-sql is a 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 server-uri

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

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
Command Description
-h, --help Display this usage information
--version Display the version information
-q, --quiet Quiet mode. No information will be displayed
--docroot=path Document root under which to place database files. (See Document Root (docroot))
-L, --locale=locale The string collation locale the database will use (See locale)
-C, --connect=server-uri Set up the initial connection to a remote RDM SQL server
--continue Continue onto the prompt after processing the input file script
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. This option is off by default.
.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. This option is off by default.
.e [on | off] Set or toggle the 'statement echo' mode. This option is on by default.
.h | .? Display help for rdm-sql interface commands
.k n Set rowset size to n. The default rowset size is 50 rows.
.l n Set output page length to n lines. There is no page break by default.
.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. This option is off by default.
.p [passcode]Register a passcode with the current connection for use of opening encrypted databases. No passcode will remove all passcodes from the current connection.
.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. This option is on by default.
.v [on | off] Set or toggle the 'warning display' mode. When this option is enabled, diagnostic messages will be printed out when an operation returns a non-error status. This 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. The default page width is 4096 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. This option is off by default.
.line [file] Set the logical line number and file for the following line of input. This can be used by preprocessors and input passed in from scripts for more accurate error reporting.
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