sql: Sculptor SQL engine client ¶

Since Sculptor version

6.0.0

Contents

sql: Sculptor SQL engine client

SQL (Structured Query Language) is a powerful but simple method of extracting data from a Sculptor database. It is a command line program that executes queries on a Sculptor file or files though kfserver/kfservnt (Sculptor SQL engine), enabling data to be obtained by end users without the need for a Sculptor development system or the ability to write a Sculptor program. The user specifies selection criteria and the fields for inclusion, which are displayed in columnar form. Sculptor SQL can also be used to update files.

This client command implements remote database access as defined in SQL-92.

Because the new SQL follows the SQL-92 standard it is not fully compatible with the Sculptor 2 SQL program. This can still be used if needed but modifying queries to use the new SQL should be the preferred option.

Syntax ¶

C:\>sql -h
Usage: sql [options] [script_filename]
Options:
-o filename Output filename (standard output if not specified)
-q Quiet, no prompt output (implied with scipt_filename)
-s server Default Kfserver servername (localhost if not specified)
-v Print program version
-h This message

C:\>sql
* > help

Supported commands ¶

HELP	Show this help.
EXIT	Close all connections and exit sql.
CONNECT TO DEFAULT	Connect to the default server (localhost).
CONNECT TO server_name [AS connect_name] [USER user_name[/password]]	Connect to specified server optionally naming the connection and/or using credentials
SET CONNECTION DEFAULT	Set the current connection to the default.
SET CONNECTION connect_name	Set the current connection to the named connection.
SHOW CONNECTION	Show all the connections including the current one.
DISCONNECT {ALL\|CURRENT}	Disconnect all connections or the current one.
DISCONNECT connect_name	Disconnect the specified connection.
SET OPTION name = value	Set the option ‘name’ to ‘value’. See options: CSV = [ON\|OFF] Comma Separated Values output CSV_SEPARATOR = ‘#’ Comma Separated Values output separator AUTO_COMMIT = [ON\|OFF] Set the Auto Commit behaviour. ON by default (Since Sculptor version 6.0.3) LOGIN_TIMEOUT = # The timeout in seconds used to connect to the server (0 means forever) PROCESS_PRIORITY = SPP* Set the current connection process priority: SPP_IDLE, SPP_BELOW_NORMAL, SPP_NORMAL, SPP_ABOVE_NORMAL, SPP_HIGH, SPP_REALTIME THREAD_PRIORITY = STP* Set the current connection thread priority: STP_IDLE, STP_LOW, STP_BELOW_NORMAL, STP_NORMAL, STP_ABOVE_NORMAL, STP_HIGH, STP_TIME_CRITICAL
SHOW OPTION	Show all the option values
RUN file_name	Run the sql commands inside the file_name
COMMIT [WORK]	Commit the current connection transaction (Since Sculptor version 6.0.3)
ROLLBACK [WORK]	Rollback the current connection transaction (Since Sculptor version 6.0.3)
sql_statement ;	Any valid SQL statement (semicolon terminated).

Note: When using the sql command, when issuing a sql statement and no connection was previously made, a CONNECT TO DEFAULT automatic command is executed.

Using the sql command ¶

A query can be executed in one of the following ways.

Type the query in at the command line.

This method is obviously only suitable for brief queries that are unlikely to be repeated.

Type sql at the DOS prompt to load Sculptor SQL. Header information such as Version No is displayed, followed by an asterisk. Start typing the query after the asterisk.

The query may be single-line or multi-line. A semi-colon indicates that the query is complete. If a line does not end with a semi-colon, SQL begins a new line and the prompt changes as follows:
```
C:\>sql
* > select * from employees;
```
Continue to type the query text until complete, ending with a semi-colon (which may be the only character of the last line). The query is then executed and the selected data displayed. If the query was invalid for any reason an error message is issued.

The UP and DOWN arrow keys can be used to cycle through previously entered lines.

Type exit to quit Sculptor SQL.
Use a saved query file.

This method is more suitable for complex queries and also facilitates repeated use of the same query:
```
C:\> sql "*filename*"
```
The query text can be created with any text editor. Note that a semi-colon at the end of the file is required. The file does not need to have any particular suffix, though Sculptor example query files follow the convention of using “.sql”.