SQL

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

SQL

Control Commands

There are a number of ancillary commands which execute procedures, connect databases and tabfiles, etc. Most of these operations can be carried out more easily from the menus. The commands are as follows:
CALL

Executes commands stored as a procedure CONNECT DATABASE

Connects databases CONNECT TABFILE

Connects tabfiles CREATE ATTRIBUTE

Creates a short internal name to represent a filename CREATE SYNONYM

Defines a short word to stand for longer text DISCONNECT

Disconnects a database or tabfile DROP

Deletes a tabfile or elements such as paths or views which have been created END

terminates session GET

Restores the workspace from a file SAVE

Saves the workspace to a file

CALL

CALL [database.] [family.] member [(parameter, ...)]

CALL executes a procedure from a SIR/XS procedure file. The procedure file must be connected. A procedure (or member) may be edited, amended and added to the procedure file from the SQL menus.

A procedure that is called in SQL should only contain SQL commands.

Procedures can be created which have parameters to specify particular conditions. Parameters are positional; that is, the stored procedure references parameters by number and these numbers are assigned in the order they are specified. A procedure parameter can be any sequence of text. Parameters are enclosed in parentheses and separated by commas. Null parameters are specified by a comma immediately following the previous comma.

Example: Suppose the following were in the text of procedure REP1:

SELECT ID NAME <1> <2> FROM EMPLOYEE <3>

Call this procedure with:

CALL REP1 ( SALARY , GENDER , WHERE ( SALARY GT 2250 ) )

which results in the full command:

SELECT ID NAME SALARY GENDER FROM EMPLOYEE -
 WHERE ( SALARY GT 2250 )

CONNECT DATABASE

CONNECT DATABASE database_name
         [ PASSWORD  database_password ]
         [ PREFIX 'file_prefix' ]
         [ SECURITY  read_security,write_security ]
         [ SUPPRESS PATH ]
         [ SUPPRESS QPROFILE]

Abbreviation: CON DB Connects the named database. Supply the appropriate password and security passwords. If the database is not in the default directory, supply a prefix. Enclose the prefix in single quotes.

This database is made the default database. If other databases are connected, the SET DATABASE command alters the default. When using multiple databases, prefix non-unique record names with the database name. For example, in a database called COMPANY with a record named EMPLOYEE:

SELECT ....  FROM   COMPANY.EMPLOYEE

PASSWORD

PW

PREFIX

P

PREFIX 'C:\MYFILES\'

SUPPRESS PATH

CLEAR PATH

SUPPRESS QPROFILE

SYSTEM.QPROFILE

SECURITY

Specifies the read and write security passwords for the database. Specify the read password first, then a comma, then the write password. If there is a write password but no read password, precede the write password with a comma.

CONNECT DATABASE ......  SECURITY HIGH,HIGH

CONNECT TABFILE

CONNECT TABFILE  name
    [ AUTO | READ | WRITE ]
    [ FILENAME  filename ]
    [ IDENT BY grpname/grppass.username/userpass ]

Abbreviation: CON TF

Connects the named, existing tabfile. The name used must be the name specified when the tabfile was created. This is an internal name and is independent of, and unaffected by, the operating system filename. All references to the tabfile are by this name. Tabfiles with the same name cannot be connected at the same time.

AUTO

Specifies that the tabfile is opened and closed every time an SQL operation, such as a SELECT, is executed. This locks the tabfile for exclusive write for the minimum period of time. AUTO is the default. Specify WRITE for exclusive updating or READ if only doing queries.

READ

Specifies that the tabfile is opened and remains open for shared read-only access until DISCONNECTed.

WRITE

DISCONNECT

FILENAME

Specifies the operating system filename for the tabfile. If a filename is not specified, the internal name of the tabfile plus ".tbf" is used as the operating system filename.

IDENT BY

Specifies group name, group password, user name, and user password for access to this tabfile. If security controls were not defined when the tabfile was created, these clauses are unnecessary. Depending on the access controls, you may need to specify a password for the group, a username and a password for the username.

CREATE ATTRIBUTE

CREATE ATTRIBUTE  name  FILENAME  'external filename'

Abbreviation: CRE ATTRIB

Associates an SQL internal name with an operating system filename specified in quotes. The SQL name can be used in subsequent commands wherever you need to specify a filename.

CREATE SYNONYM

CREATE SYNONYM  name text

Abbreviation : CRE SYN Creates a synonym. A synonym is a text replacement mechanism, typically used for long, repeated sets of names. Create the synonym and then use it wherever applicable. A synonym can be used at any point in any SQL command.

Do not enclose the text in quotes. The synonym name is a standard SQL name up to 32 characters. Do not use reserved words as synonym names.

CREATE SYNONYM MYSELECT SELECT ID NAME SALARY FROM EMPLOYEE

DISCONNECT

DISCONNECT DATABASE  database_name
DISCONNECT TABFILE   tablefile_name

Abbreviation: DISCON DB, DIS DB, DISCON TF, DIS TF Disconnects a database or tabfile. The database or tabfile is closed and all schema information is released.

DROP

DROP ATTRIB  attribute_name
DROP INDEX   index_name
DROP JOURNAL file_name
DROP PATH    path_name
DROP SYNONYM synonym_name
DROP TABFILE tabfile_name
DROP TABLE   table_name
DROP VIEW    view_name

Deletes the named entity. The entity no longer exists and must be re-created if required again.

END

END  [ CLEAR | SAVE [ workspace_filename ] ]
Synonyms: BYE, EXIT, QUIT, STOP, .

Terminates the SQL session from SQL.

If the workspace has been modified, you are prompted to save it. It will be saved as the default workspace file which is either the workspace file that has been restored (with a GET, the workspace file most recently saved or the default SirSQL.wsp. If the file is new, you are prompted for a password. If you specify a password, a user must specify the password when restoring the workspace. If you do not specify a password, you will not be prompted for it again.

GET

GET  [ filename ] [ PASSWORD  password ]

Restores a workspace from the specified file name. When starting an SQL session, the default workspace (SirSQL.wsp) is loaded unless the

WORK
=

execution parameter is specified.

Use GET to load a previously saved workspace. The default workspace (set by SET WORK name), is loaded if a name is not specified on the GET command.

Specify the keyword PASSWORD and the required password if one is needed. If the password is not specified and one is required, you are prompted for it.

`SAVE`

SAVE workspace_filename  [ PASSWORD workspace_password ]

SAVE saves the current workspace on the specified file. If the file name is omitted, the workspace is saved on the default workspace file. The default for this is SirSQL.wsp.

Execution Statement

The parameters can be specified on the SQL execution statement. The parameters may be specified on the execution statement in any order and separated by a space. The parameters determine:

Batch Mode

Whether to run SQL in batch mode Database

Specifies a database to connect automatically Tabfile

Specifies a tabfile to connect automatically Environment

Specifies the workspace to restore.

Batch Parameters

ABORT
BA
IN  = filename
OUT = filename

Use these to run SQL in batch mode.

ABORT

BA

IN

SELECT

DISPLAY

END

OUT

Specifies an output file for error and status messages.

Format File

SQL messages are held in a format file sirsql.fmt. This is a machine readable file produced by a batch run of SQL. This reads a text input file of messages and either creates a new format file or adds messages to a pre-existing file. This facility might be used to produce messages in a different language. Please contact SIR support if you require this facility.

PREPARE
FMT = format filename (output)
IN = input format text filename
OUT = output filename (messages)

PREPARE

Specifies that this is a batch run to prepare a format file. FMT

Specifies the format file to create or added to if it already exists. IN

Specifies the input text of the messages. This is in a predetermined format available from SIR. OUT

Specifies the output file for any messages or errors.

Database Parameters

DB = database
P =  'prefix'
PW =  password
RS =  password
WS =  password
EX =  membername
SUPQ

Specify the following parameters to connect a default database.

DB

Specifies the name of a database to connect and use as the default. P

P='C:\MYFILES\'

PW

Specifies the database password. If a database name is not specified, any passwords are ignored. RS

Specifies the database read security password. WS

Specifies the database write security password. EX

SYSTEM

QPROFILE

SUPQ

QPROFILE

Tabfile Parameters

TBL  (or TFL)     =        name
TBFN (or TFFN)    =        filename
GRP               =        name
GPW               =        password
USER              =        name
UPW               =        password

Specify the following parameters to connect a default tabfile.

TFL

Specifies a tabfile connected when SQL is executed. TBFN

TBL

.tbf

GRP

Specifies the group name to access the tabfile.

GPW

Specifies the password for the group.

USER

Specifies the user name to access the tabfile.

UPW

Specifies the password for the user.

Environment Parameters

CENY    =        nnnn
WORK    =        workspace
WORKPW  =        password

Specify the following parameters as necessary to alter the default environment settings:

CENY

Specifies the four digit year used for century calculations when converting dates with only two digit years. Specify the year below which dates are in the next century. The system setting is 1920. This means that years below 20 are assigned a century of 20xx. Years above this are assigned a century of 19xx. Valid dates run from 1582 to 2900.

[NO]WORK

Specifies the name of the workspace file automatically loaded when SQL starts. If a workspace is not specified on the execution parameter, if SirSQL.wsp exists, it is automatically loaded unless NOWORK is specified. For example:

SQL/WORK='SQLMYWS.wsp'

WORKPW

Specifies the password for the workspace