sql: Sculptor SQL engine client

Since Sculptor version

6.0.0

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.

  1. 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.

  2. 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”.

NOTES

  • Sculptor SQL is not to be confused with the sql command, which is a built-in command only available for use with a database that has an ODBC driver.


RELATED TOPICS

Sculptor SQL engine introduction

Getting started with Sculptor SQL engine

Sculptor SQL engine features

Sculptor SQL engine examples