SAND CDBMS Tools Reference Guide
Interactive SQL Utility (nisqlm)


Previous Topic:
Nisqlm Session Mode
Chapter Index
Next Topic:
Date and Time Display Formats


Nisqlm SQL Mode

nisqlm SQL mode enables users to execute SQL statements interactively against a particular database. Any legal SAND CDBMS SQL statement may be executed in SQL mode once a database session has been established. All SQL statements must be terminated by a semicolon (;). There can be a single SQL statement per line. If multiple SQL statements, separated by semicolons, are included on the same line, only the first statement will be processed; the rest will be ignored.

When a connection name, database name, and user name are included in the nisqlm invocation, the user is automatically placed in SQL mode (in the default schema associated with the specified user) upon successful connection to the database instance. If the user switches to Session mode once a database session has been established, the .SQL system command can be used to switch back to SQL mode.

In SQL mode, pressing <Ctrl+C> while a query is executing will halt the query. Pressing <Ctrl+C> twice in this situation will cause nisqlm to shut down.

If a user connects to the database instance while ndlm is performing a load operation on it, attempting to execute any SQL command that would change the database will produce the following error message:

Error state 0A000. Feature not supported
Application not supported while loading

This restriction serves to protect the integrity of the database while data is being imported. Once the load operation has completed, the user can execute a COMMIT [WORK] or ROLLBACK [WORK] command to start a new transaction that will then allow the full range of SQL commands.

For information about the syntax and usage of SAND CDBMS SQL commands, refer to the SAND CDBMS SQL Reference Guide.

By default, the maximum number of concurrently executing SQL commands for a single client process is 128. This limit may be changed for the connection by including a MaxQueries entry in the appropriate CONNECTION section of the server-side nucleus.ini file.

Limiting the Display of Results from a SELECT Statement

The display of results returned by a query can be limited to a specified number of records by including the LIMIT option with the SELECT statement. When the LIMIT keyword and a numeric parameter are appended to a SELECT statement, at most that number of records will be displayed from the actual result set.

Note that, like the .MAXROWS system command, the LIMIT option does not affect the query results returned from the server; it serves only to limit what is displayed on the client side. Therefore, using the LIMIT option with CREATE TABLE...SELECT or INSERT...SELECT will not filter the data in any way.

The LIMIT syntax is the following:

<SELECT statement> LIMIT n ;

where n is a positive integer.

For example:

SELECT t1 FROM salestab LIMIT 10;

If the above query returns more than 10 records, only the first 10 records from the result set will be displayed. Otherwise, if the query returns 10 records or less, the LIMIT option is essentially ignored and the full result set is displayed.