| ||
| SQL | | Control Commands |
CALL
CALL [database.] [family.] member [(parameter, ...)]
CALL executes a
procedure from a SIR2002 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.
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 )
This database is made the default database. If
other databases are connected, the
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.
Associates an SQL internal name with an operating system filename specified in
quotes. The SQL name can be used in subsequent SQL commands wherever you need
to specify a filename. The SQL name specified is an internal file name, up to
eight characters long, with no special characters.
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.
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
Use
Specify the keyword
Batch Mode
CONNECT DATABASE
CONNECT DATABASE database_name
[ AUTO | READ | WRITE ]
[ 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.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
AUTO
Specifies that the database is opened and closed every time an SQL
operation, such as a
SELECT, is executed. This locks the
database for exclusive write for the minimum period of time.
AUTO is the default. Specify WRITE (if you
are updating the database) or READ (if you are only doing
queries). These open the database once, and it remains open as long as it is
connected. If WRITE is specified, no other users can access
the database. If READ is specified, other users may also
read, but no-one may write.
When concurrent SQL is used (the MST= parameter is specified on
the execution statement), the database is connected through
MASTER.
The
AUTO, READ and
WRITE clauses still apply. MASTER only controls a database as
long as someone has it open. AUTO mode opens and closes the
database and thus the MASTER can release the database between operations.
READ does not affect the way MASTER opens the database;
however, it disables any SQL update operations and gives protection against
accidental updating. WRITE, with concurrent SQL, gives concurrent
update capabilities.READ
Specifies that the database is opened and remains open for shared
read-only access until it is disconnected.
WRITE
Specifies that the database is opened and remains open for exclusive
write operations until disconnected.
PASSWORD
Specifies the database password.
Abbreviation: PWPREFIX
Specifies a directory other than the current default directory for
database access. The directory used for any other files is unaffected. Specify
the prefix in single quotes.
Abbreviation: P
For
example:PREFIX 'C:\MYFILES\'
SUPPRESS PATH
Suppresses the generation of paths. Paths are automatically generated
when a SIR2002 database is connected. Turn off the use of paths with the
CLEAR PATH command. Disconnect and reconnect without
suppressing paths to generate the paths.SUPPRESS QPROFILE
Suppresses automatic execution of
SYSTEM.QPROFILE.
When a database is connected, SQL automatically executes this procedure
on that database. The procedure can contain any SQL commands
executed each time this database is connected. If the procedure
does not exist, no member is executed.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 TFAUTO
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
Specifies that the tabfile is opened and remains open for exclusive
write operations until
DISCONNECTed.
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
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.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.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.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.PASSWORD and the required password if one
is needed. If the password is not specified and one is required, you are
prompted for it.SAVESAVE 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:
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
Specifies that batch processing is stopped if an error is detected. If
ABORT is not specified, processing continues after an error.
Specify ABORT if commands in the input file depend on the
results of an earlier command.BA
Specifies Batch Mode. SQL commands are read from an input file
specified by the
IN parameter.IN
Specifies the input file containing SQL commands. Any SQL command
can be included in the file. In batch mode
SELECT does not
automatically cause a display. To display the output, include a
DISPLAY statement and an END command.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 special batch run of SQL. This special run
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.
PREPARE
FMT = format filename (output)
IN = input format text filename
OUT = output filename (messages)
PREPARE
Specifies that this is a special batch run to prepare a format file.
FMT
Specifies the format file to be created or to be added to if it already exists.
IN
Specifies the input text of the messages. This must be in a predetermined format.
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
Specifies a directory other than the current directory is the
location of the database. This parameter has no affect on the directory used for
any other files. For example:
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
Specifies the procedure executed when the database is connected.
The procedure is a member in the family
SYSTEM. If this member does
not exist, no member is executed. If this is not specified, the member
QPROFILE (if it exists) is executed.SUPQ
Specifies that
QPROFILE is not executed when the
database is connected.
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
Specifies the operating system filename of the tabfile specified on
the
TBL parameter. If this is not specified, the filename is
assumed to be the same as the tabfile name plus a prefix of .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