SAND CDBMS Tools Reference Guide
Interactive SQL Utility (nisqlm)

Nisqlm System Commands

nisqlm system commands may be executed from either operating mode. Unlike modal commands, system commands must fit entirely on the current prompt line. Any command prefixed by a period that is not recognized as a system command is passed to the operating system shell for execution.

.DATA

The .DATA command switches the display of fetched data off or on. When switched off, nisqlm performs query fetches but does not show the retrieved data. Only the number of rows returned and the column headers are displayed.

.EXIT

The .EXIT command returns the user to the operating system shell. nisqlm automatically disconnects all currently active sessions initiated from the client.

.EXPORT  [ BULK ]  { SQL-statement } INTO { |pipe | 'file' }
    ESCAPE 'escape-character'
                     'column-separator-character'
                     'row-separator-character'
                     'null-character'

The .EXPORT command exports the results of a query to a flat file or pipe. The full SELECT statement is included as a parameter for this command, enclosed by curly braces { }. A terminating semicolon is optional for the SELECT statement. If exporting to a file, the specified file path/name must be contained in single quotation marks. If exporting to a pipe, the pipe number (from 0 to 255) must be specified without quotes, preceded by a pipe symbol ( | ). Each of the special characters (escape, column separator, row separator, and null indicator) must be defined, represented by a single character in either ASCII or hexadecimal form, and enclosed by single quotation marks. These special characters are described below:

• escape-character is the character used to "escape" a special character in the exported data; immediately preceding the special character, it indicates that the character is to be interpreted literally, not as an escape, separator, or null character
• column-separator-character is the character that separates columns in the exported data
• row-separator-character is the character that specifies the end of a row in the exported data
• null-character is the character that represents null values in the exported data.

There is a "bulk fetch" option that allows data to be exported at a faster rate than with the standard .EXPORT. However, this feature should be used only when nisqlm is not running on the same machine as nserv, otherwise the I/O contention between the two will significantly degrade performance. To enable bulk fetching, simply include the BULK keyword after the .EXPORT keyword and before the SQL clause.

Example:

.EXPORT BULK { SELECT id, ddate, price, qty, sku
          FROM s1.t1 WHERE ddate > '2004-01-01' }
     INTO 'C:\exp.dat'
     ESCAPE '\x1b' '|' '\x0a' '‡'

In the above .EXPORT statement, the results of the embedded SELECT statement are to be written to the specified file (exp.dat), via bulk fetching from the server. Here, the escape character is \x1b (the hexadecimal representation of the ASCII escape character), the column separator is a pipe symbol ( | ), the row separator is \x0a (the hexadecimal representation of the linefeed character), and the null character is the ASCII "double dagger" symbol ( ‡ ). Note that there are no defaults for the special characters in the ESCAPE clause, so they must each be defined in the .EXPORT statement.

.HELP

The .HELP command displays a list of all system commands and their syntax.

.HEX

The .HEX command switches the hexadecimal display of binary data on or off. When turned off, unprintable characters are output as periods ( . ). This command is useful when querying BLOB columns.

.INDEX

The .INDEX command toggles the display of a row index. When turned on, returned rows will have an extra column called ID, which contains the numerical order in which the rows were fetched starting from 1. By default, row indexing is turned off.

.INTERVAL [ REPEAT | NO REPEAT ] [ n ] SQL-statement ;

The .INTERVAL command executes the specified SQL statement and displays n rows, sampled at regular intervals from the result set. If the n argument is not supplied, the value is assumed to be 100. When the REPEAT option is included (which is also the default), the number of rows specified is the number of rows displayed, even if it is greater than the total number of rows in the result set. If necessary, some or all of the displayed rows are duplicated. When the NO REPEAT option is specified, there is no row duplication, and the number of rows displayed can be less than n.

The SQL statement must end with a semicolon ( ; ).

.MAXLENGTH

The .MAXLENGTH command switches full display of character data on or off. By default, nisqlm limits the data display to 40 characters.

.MAXROWS n

The .MAXROWS command specifies the maximum number of rows (n) returned by a query. That is, if n is less than the number of rows normally returned by a query, an arbitrary subset of n rows will be returned instead. The default is 0 (unlimited).

Note:
While this system command is enabled, the true number of rows returned by a query will be obscured by the MAXROWS limitation. To disable the command, use .MAXROWS 0.

.MEASURE

The .MEASURE command switches the timing of query fetches on or off. When switched on, query output will also display the elapsed time for command execution, each fetch stage (512 KB of data per fetch stage), and the query as a whole.

.NULLS [ string ]

The .NULLS command establishes a display string for null values returned by SQL SELECT statements. nisqlm will display the character string provided in the string argument in place of any null values returned by SQL queries. Consider this example:

SQL:1> .NULLS N/A

After this command is executed, nisqlm will return the string 'N/A' in place of any nulls returned by SELECT statements. To clear the null string setting, execute the .NULLS command without an argument.

.OUTPUT

The .OUTPUT command switches session logging on or off. When session logging is on, all nisqlm input and output is recorded in a file named inter.out in the current working directory. If logging is turned off and then on again within a single nisqlm session, nisqlm continues to append to the output file without overwriting its contents. Turning on logging in a subsequent session, however, will overwrite any preexisting output file. A session can be started with logging turned on by including the -o flag in the nisqlm invocation.

Warning:
When session logging is enabled, executing queries that produce large result sets can cause the log file to grow quite large. Eventually, disk space can be exhausted, and the SAND CDBMS software will terminate abnormally. If possible, avoid logging large queries, or else use the .QUIET or .DATA command to hide the results of such queries.

.PROMPT

The .PROMPT command switches the SQL prompt display (that is, SQL:n>) on or off. A session can be started with the prompt display turned off by including the -p flag in the nisqlm invocation.

.QUIET

The .QUIET command switches SELECT statement output off or on. After execution of the .QUIET command, nisqlm returns only a count of the rows constituting the SELECT statement result, and not the actual data.

.RUN file

The .RUN command executes a batch of commands, either contained in a host file, or entered from the console. The file argument must be the name of an ASCII text file containing valid commands for the current mode, or system commands.

Consider the following example:

SQL:1> .RUN sql.txt

The contents of the file sql.txt must be valid SAND CDBMS SQL statements, since nisqlm is in SQL mode (unless system mode change commands are included in the file). nisqlm executes the statements in the order of their placement in the file. If a hyphen ( - ) is specified for the file argument, nisqlm accepts valid commands from the console until Ctrl+D is entered (indicating end of input).

.SESS [ command ]

The .SESS command, when executed in SQL mode, changes the current operating mode to Session mode. If the optional command argument is included, it must be a valid Session mode command. The Session mode command specified is executed without leaving the current nisqlm mode.

.SQL [ command ]

The .SQL command, when executed in Session mode, changes the current operating mode to SQL mode. If the optional command argument is included, it must be a valid SAND CDBMS SQL statement. The specified SQL command is executed without leaving the current nisqlm mode.

.STARTROW n
The .STARTROW command specifies the row in the result set from which to start fetching. For example, if n is 5, the first four rows in the result set are not displayed by nisqlm. By default, all rows are displayed (n = 1).

.STATS

The .STATS command displays the current values of nisqlm system variables as well as the database connection status for the client.

nisqlm STATS Display

The .STATS display has the following appearance:

SQL:3> .STATS
current status
   NULL string:
   OUTPUT flag: OFF
   PROMPT flag: ON
   TITLES flag: ON
   DATA flag: ON
   WARNINGS flag: OFF
   QUIET flag: OFF
   INDEX flag: OFF
   MEASURE flag: OFF
   MAXLENGTH flag: OFF
   HEX flag: OFF
   MAXROWS: DISABLED
   INTERVAL REPEAT: TRUE
   INTERVAL rows: 100
   STARTROW: 1
   CONNECTIONS:
   *   1: CN1@db01  (Nucleus 4.1.1994.0)  SERIALIZABLE
       2: CN1@db01  (Nucleus 4.1.1994.0)  SERIALIZABLE

NULL string:
Displays the NULL string specified using the .NULLS system command. Here, the string has been set to 'N/A.'

OUTPUT flag:
Displays OUTPUT flag status as set by the -o option flag or the .OUTPUT system command (ON or OFF).

PROMPT flag:
Displays PROMPT flag status as set by the -p option flag or the .PROMPT system command (ON or OFF).

TITLES flag:
Displays TITLES flag status as set by the -t option flag or the .TITLES system command (ON or OFF).

DATA flag:
Displays the DATA flag status as set by the .DATA system command (ON or OFF).

WARNINGS flag:
Displays WARNINGS flag status as set by the -w option flag or the .WARNINGS system command (ON or OFF).

QUIET flag:
Displays QUIET flag status as set by the .QUIET system command (ON or OFF).

INDEX flag:
Displays INDEX flag status as set by the .INDEX system command (ON or OFF).

MEASURE flag:
Displays the MEASURE flag status as set by the .MEASURE system command (ON or OFF).

MAXLENGTH flag:
Displays MAXLENGTH flag status as set by the .MAXLENGTH system command (ON or OFF).

HEX flag:
Displays HEX flag status as set by the .HEX system command (ON or OFF).

MAXROWS:
Displays the MAXROWS value specified using the .MAXROWS system command. Here, the maximum number of rows has not been set explicitly, so it defaults to unlimited.

INTERVAL REPEAT:
Displays whether the .INTERVAL option is set to REPEAT or not.

INTERVAL rows:
Displays the number of rows set by the .INTERVAL command (100 by default).

STARTROW:
Displays the starting row for fetches as set by the .STARTROW system command (1 by default).

CONNECTIONS:
Displays the database sessions (that is, connections) maintained by the current client. The server and database instance name are separated by the "at" symbol (@), and an asterisk ( * ) indicates the current session. As well, the server software version and the transaction mode (SERIALIZABLE or EXCLUSIVE) are displayed.
 

.SYSTEM [ command ]

The .SYSTEM command passes control to the operating system for execution of the command specified by the command argument. This is equivalent to typing .command, since all commands prefixed by a period that are unrecognized by nisqlm are passed to the operating system for execution.

.TIME

The .TIME command displays the current time. If executed subsequently during the same nisqlm session, it displays the time elapsed since the previous invocation of the command.

.TITLES

The .TITLES command switches the display of column names in the headings of query results on and off. A session can be started with the .TITLES display turned off by including the -t flag in the nisqlm invocation.

.WARNINGS

The .WARNINGS command switches the display of SQL warning messages on or off. A session can be started with SQL warning messages suppressed by including the -w flag in the nisqlm invocation.