SAND CDBMS Tools Reference Guide
Interactive SQL Utility (nisqlm)

 

x
Chapter Index
Next Topic:
nisqlm System Commands

 

Using nisqlm


Invoking nisqlm

The SAND CDBMS interactive SQL utility invocation is entered from the operating system command prompt and has the following form:

nisqlm [ flags ] [ connection-name instance-name user-name[/user-password ] ]


nisqlm Invocation Arguments:

connection-name

The name of the connection specified when instance-name was started with the nserv program. The connection-name argument is optional, but must be entered in order to connect automatically to a database instance.
If nisqlm is being used to connect to a database instance running on a remote server, the client-side nucleus.ini file must contain a [CONNECTION connection-name] section that specifies the port number and host name used to connect to the database instance, and these must match the values specified for the same connection in the server-side nucleus.ini file. The connection name is not case-sensitive.

Note:
By default, the maximum number of client programs that may use a particular connection is 32 (this limit may be changed by including a MaxUsers entry in the appropriate CONNECTION section of the server-side nucleus.ini file
).


instance-name


The name of the database instance to which to connect. The instance-name argument is optional. It must be entered, however, if a connection-name argument has been specified.
Instance-name must have already been started using nserv with the specified connection name. The instance name is not case-sensitive.


user-name


The user-name argument is optional. It is required, however, if connection-name and instance-name arguments are entered. The user-name argument must specify a valid user authorization in the specified database.
The user name is not case-sensitive.
 

/user-password

The user-password argument is required only if a user-name argument is entered, and if the specified user authorization has an associated password. The user-password argument must be separated from the user-name argument only by a slash character ( / ); no "white space" may appear between the two arguments. The password must precisely match the password associated with the specified user authorization.

Note:
User passwords are case-sensitive.

 

flags

The optional flags described below may be specified individually, or in combination (if multiple flags are specified, they must be separated by spaces). When the flag includes an argument, these elements cannot be separated by any "white space".

-d{0 | 1}
The -d flag switches the display of fetched data off (0) or on (1). When switched off (-d0), nisqlm performs query fetches but does not show the retrieved data. Only the number of rows returned and the column headers are displayed. By default, fetched data is displayed (-d1).

-e{0 | 1}
The -e flag disables (0) or enables (1) the return of exit codes. By default, exit codes are disabled (-e0), which means that 0 is always returned when nisqlm exits, whether or not nisqlm encountered any errors. If exit codes are enabled (-e1) and an error is encountered, the returned code will indicate the type of error. Note that when multiple errors occur, nisqlm returns only the latest one when it exits, although it can also be configured (using the -q1 option) to quit when it encounters its first error.

The nisqlm exit codes can be viewed in the Nisqlm Exit Codes section of the SAND CDBMS Error Messages Guide.

-f file-name
The -f flag lets the user specify a name and location for the session log file created through the .OUTPUT system command. By default, the log file is called inter.out and is created in the current working directory. If the full path to the log file contains any spaces, put quotation marks around the path. If only the file name is specified (that is, the path location is omitted), the file is created in the current directory.

-h or -?
The -h (or -?) flag displays a help screen which describes the nisqlm invocation flags and arguments.

-l
The -l flag displays the list of databases from the nucleus.ini file that have the Octopus parameter ODDBPublic set to TRUE. These are databases that can be started as "public" ODDB instances. Refer to the Octopus On-Demand Database (ODDB) Operations chapter of the SAND CDBMS Octopus Administrator's Guide for more information.

-o{0 | 1}
The -o flag turns the session logging off (0) or on (1). The default setting is "off" (that is, -o0). The -o1 flag turns session logging on, and creates a file with the name inter.out, in which all nisqlm input and output will be recorded. The inter.out file is always created in the current directory and overwrites any existing inter.out file.

-p{0 | 1}
The -p flag turns the nisqlm command prompt off (0) or on (1). The default setting is "on", that is, -p1. Include the -p0 flag in the invocation in order to turn off the prompt display.

-q{0 | 1}
The -q flag forces nisqlm to quit upon encountering an error, when enabled (-q1). By default (-q0), nisqlm will not exit on the first error, unless it is an error that normally causes termination.

-t{0 | 1}
The -t flag turns the title list display off (0) or on (1). The default setting is "on", that is, -t1. Include the -t0 flag in the invocation in order to suppress the display of the column headings returned by SQL SELECT statements.

-w{0 | 1}
The -w flag turns the SQL warning message display off (0) or on (1). The default setting is "on", that is, -w1. Include the -w0 flag in the invocation in order to suppress the display of SQL warning messages.


nisqlm Usage Notes

When the nisqlm invocation connects to a running database instance successfully, the user is automatically placed in SQL mode. If the nisqlm invocation does not connect successfully, either because the invocation arguments are invalid or are not included, the user is automatically placed in Session (SESS) mode.

The standard redirection character (<) may be used in conjunction with the nisqlm invocation to execute SQL commands contained in a batch file, as in the following example:

nisqlm server1_vip dba <batch.sql

This nisqlm invocation connects to the database instance called vip, using the connection server1_vip, as the user DBA. The SQL statements contained in the ASCII file batch.sql are executed automatically. To return control to the operating system shell when command execution is finished, include the .EXIT system command at the end of the batch file.


nisqlm Invocation Example

The nisqlm invocation in this example connects to a database instance called vip as user1, using a connection definition called server1_vip. The user authorization password is PaSs1. Session logging is turned on with the -o1 flag. Following a successful connection, user1 is automatically placed in SQL mode (in the default schema associated with that user authorization).

nisqlm -o1 server1_vip vip user1/PaSs1


nisqlm Operating Modes

nisqlm has two modes of operation: SQL and Session (SESS). Each mode has its own set of commands. Additionally, there is a set of system-level commands. Session mode enables users to connect and disconnect database sessions. SQL mode enables users to execute SQL statements interactively against a database. The system-level commands include commands for switching between the operating modes.

The nisqlm prompt always reflects the mode currently in use. When in Session mode, nisqlm displays a prompt of the form SESS:n>; when in SQL mode, the prompt is SQL:n> (where n is a number reflecting the command number for each mode). Each time a command is issued within a particular mode, the command number displayed in the prompt increases by one.


Command Syntax

nisqlm system commands are prefixed with a period ( . ) in order to distinguish them from the command set for the current mode. Any command that is not prefixed by a period is executed within the context of the current mode; nisqlm issues an error message if the command does not belong to the current mode's command set. nisqlm commands are not case-sensitive.

In order to execute a command belonging to the current operating mode, the command must not be prefixed by a period, and must be terminated by a semicolon (;).

To execute a command in another mode without leaving the current mode, precede the command with the system command normally used to change to the desired mode, as in the following example:

SQL:3> .sess disconnect 3;

Here, the DISCONNECT statement is executed from SQL mode by prefixing it with the system command normally used to change to SESS mode, that is, .SESS. Note that the statement is terminated with a semicolon ( ; ) — in this case, however, it is not necessary to include the semicolon, since the command is executed with the mode prefix.

 

x
Chapter Index
Next Topic:
nisqlm System Commands