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