Command line SQL scripting tool
rdm-sql [OPTIONS]... [input_file [arg]... ]
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:
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
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
|-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|
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.
The following interface commands are supported.
|;||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
|.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|
|.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,
|.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.|
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
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.
fib function can be called as follows.
rdm-sql: SELECT fib(6); fib(6) ----------- 8 *** 1 row(s) returned