Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


BSQL Commands


Command
Function
 CLOSE
Closes active log files, see CLOSE.
 DESCRIBE 
Describes a specified object, see DESCRIBE.
 EXIT
Leaves BSQL, see EXIT.
 LIST 
Lists information on a specified object, see LIST.
 LOAD
Loads data into a table, see LOAD.
 LOG 
Logs input, output or both on a sequential file, see LOG.
 READ INPUT
Reads commands from a sequential file, see READ INPUT.
 SET ECHO
Specifies whether lines are echoed to the terminal during READ INPUT, see SET ECHO.
 SET LINECOUNT
Sets the terminal page size, see SET LINECOUNT.
 SET LINESPACE
Sets the number of blank lines between each output record, see SET LINESPACE.
 SET LINEWIDTH
Sets the terminal page width, see SET LINEWIDTH.
 SET LOG
Stops or resumes logging input, output or both, see SET LOG.
 SET MESSAGE
Specifies whether messages are displayed on the terminal, see SET MESSAGE.
 SET OUTPUT
Specifies whether output should be written to the terminal, see SET OUTPUT.
 SET PAGELENGTH
Defines the page length of output file, see SET PAGELENGTH.
 SET PAGEWIDTH
Defines the page width of output file, see SET PAGEWIDTH.
 SHOW SETTINGS
Displays current values of all set options, SHOW SETTINGS.
 UNLOAD
Unloads data from a table, see UNLOAD.
 WHENEVER
Sets action to be taken in response to an error or warning, see WHENEVER.

BSQL commands are not case sensitive.

About BSQL Syntax Descriptions

In the syntax descriptions, items in square brackets ([ ]) are optional. Items separated by a vertical bar ( | ) are alternatives.

For example:
 READ [COMMAND | ALL] [INPUT FROM] 'filename';
 

allows the following forms

 READ COMMAND INPUT FROM 'filename';
 READ ALL INPUT FROM 'filename';
 READ INPUT FROM 'filename';
 READ 'filename';
 

CLOSE

Closes log files.

Syntax

 CLOSE [INPUT|OUTPUT|INPUT,OUTPUT] log;

Description

The command closes the specified log file. If no log file is specified, all active log files are closed.

DESCRIBE

Describes a specified object.

Syntax

 DESCRIBE [object-type [object-name]];

Description

The DESCRIBE command presents the following menu:

Choosing an item presents a submenu for choosing between different DESCRIBE functions - see the table that follows for details.

Entering an exclamation mark (!) in the Select field returns to the previous menu level. Entering a double exclamation mark (!!) terminates the DESCRIBE session.

Specifying an object type and name in the command executes the first menu choice for that object. If no object name is given, the user is prompted for a name.

Selection numbers can be provided in a batch file for unattended operation. However, DESCRIBE is most useful in interactive mode from a terminal.

DESCRIBE Options

DESCRIBE
OPTION
RESULT
DATABANK
BRIEF
Lists the following information on the specified databank:
creator
file space used
allocated size
physical file name
option
tables.

BY TABLE PRIVILEGE
Lists the following information on the specified databank:
idents with table privilege.

FULL
Lists the following information on the specified databank:
creator
file space used
allocated size
physical file name
option
tables
idents with table privilege
comment
creation date.
DOMAIN
BRIEF
Lists the following information on the specified domain:
data type
default value
check constraints.

BY REFERENCES
Lists the following information on the specified domain:
referenced objects
referencing objects.

BY ACCESS
Lists the following information on the specified domain:
idents with usage privilege.

FULL
Lists the following information on the specified domain:
data type
default value
check constraints
referenced objects
referencing objects
idents with usage privilege
comment
creation date.
IDENT
BRIEF
Lists the following information on the specified ident:
creator
ident type
privileges held by ident.

BY ACCESS
Lists the following information on the specified ident:
accessible objects.

BY OWNERSHIP
Lists the following information on the specified ident:
created objects.

FULL
Lists the following information on the specified ident:
creator
ident type
accessible objects
created objects
comment
creation date.
INDEX
BRIEF
Lists the following information on the specified index:
table name and columns on which the index is defined
sort order
uniqueness
comment
creation date.
SYNONYM
BRIEF
Lists the following information on the specified synonym:
schema and name of referenced table/view
comment
creation date.
TABLE
VERY BRIEF
Lists the following information on the specified table or view:
column names and types.

BRIEF
Lists the following information on the specified table or view:
column names and types
default values
constraints
referenced domains
indexes
triggers.

BY ACCESS
Lists the following information on the specified table or view:
idents with access.

BY REFERENCES
Lists the following information on the specified table or view:
referencing objects
referenced objects.

FULL
Lists the following information on the specified table or view:
column names and types
default values
constraints
referencing objects
referenced objects
indexes
triggers
idents with access
comment
creation date
date when statistics were generated.
VIEW
BRIEF
Lists the following information on the specified view:
view definition
comment
creation date.
MODULE
BRIEF
List the following information on the specified module:
module definition
comment
creation date.
PROCEDURE
BRIEF
Lists the following information on the specified procedure:
parameters
result items
procedure attributes
specific name.

BY ACCESS
Lists the following information on the specified procedure:
idents with execute privilege.

BY REFERENCES
Lists the following information on the specified procedure:
referencing objects
referenced objects.

FULL
Lists the following information on the specified procedure:
parameters
result items
procedure attributes
idents with execute privilege
referencing objects
referenced objects
source definition
module name
specific name
comment
creation date.
FUNCTION
BRIEF
Lists the following information on the specified function:
parameters
result data type
function attributes
specific name.

BY ACCESS
Lists the following information on the specified function:
idents with execute privilege.

BY REFERENCES
Lists the following information on the specified procedure:
referencing objects
referenced objects.

FULL
Lists the following information on the specified function:
parameters
result data type
function attributes
specific name
idents with execute privilege
referencing objects
referenced objects
source definition
module name
comment
creation date.
TRIGGER
BRIEF
Lists the following information on the specified trigger:
table name on which trigger is defined
trigger event
trigger type
event time.

BY REFERENCES
Lists the following information on the specified trigger:
referenced objects.

FULL
Lists the following information on the specified trigger:
table name on which trigger is defined
trigger event
trigger type
event time
referenced objects
source definition
comment
creation date.
SEQUENCE
BRIEF
List the following information about the specified sequence:
initial value
increment value
maximum value.

BY ACCESS
List the following information on the specified sequence:
idents with usage privilege.

BY REFERENCES
List the following information on the specified sequence:
referencing objects.

FULL
List the following information about the specified sequence:
initial value
increment value
maximum value
referencing objects
idents with usage privilege
comment
creation date.
SCHEMA
BRIEF
List the following information about the specified schema:
schema owner
contained objects
comment
creation date.
SHADOW
BRIEF
Lists the following information about the specified shadow:
shadow owner
shadow name
name of shadowed databank
filename for shadow
comment
creation date.

EXIT

Leave BSQL.

Syntax

 EXIT;

Description

Terminates the BSQL session.

LIST

Lists information on a specified object.

Syntax

 LIST [object-type];

Description

The LIST command presents the following menu:

Choosing an item presents a submenu for choosing between different LIST functions - see the table that follows for details.

Entering an exclamation mark (!) in the Select field returns to the previous menu level. Entering a double exclamation mark (!!) returns two levels.

Giving an object type in the command executes the first menu choice for that type.

Selection numbers can be provided in a batch file for unattended operation. However, LIST is most useful in interactive mode from a terminal.

LIST Options

LIST
OPTION
RESULT
DATABANKS
ALL
Lists all databanks in the database.

CREATED BY
Lists databanks created by a specified ident.

ALL SHADOWS
Lists all shadows in the database.
DOMAINS
ALL
Lists all domains in the database.

CREATED BY
Lists domains created by a specified ident.
IDENTS
ALL
Lists all idents in the database.

CREATED BY
Lists idents created by a specified ident.
INDEXES
ALL
Lists the secondary indexes in the database.

CREATED BY
Lists secondary indexes created by a specified ident.
OBJECTS
ALL
Lists objects in the database.

CREATED BY
Lists objects created by a specified ident.

BY TYPE
Lists objects of a specified type.
SYNONYMS
ALL
Lists synonyms in the database.

CREATED BY
Lists synonyms created by a specified ident.
TABLES
ALL
Lists tables in the database.

CREATED BY
Lists tables created by a specified ident.
VIEWS
ALL
Lists views in the database.

BY CREATOR
Lists views created by a specified ident.
MODULES
ALL
Lists all the modules in the database that are visible to (i.e. created by) the current ident.
PROCEDURES
ALL
Lists all the procedures the current ident has execute privilege on.

CREATED BY
Lists procedures created by the specified ident.
FUNCTIONS
ALL
Lists all the functions the current ident has execute privilege on.

CREATED BY
Lists functions created by the specified ident.
TRIGGERS
ALL
List triggers defined on tables accessible to current user.

CREATED BY
Lists procedures created by the specified ident.
SEQUENCES
ALL
Lists all the sequences the current ident has usage privilege on.

CREATED BY
Lists sequences created by the specified ident.
SCHEMATA
ALL
Lists schemata created by the current ident.
SHADOW
ALL

Lists shadows created by the current user.

A user with SHADOW privilege may see all shadows defined on the server.

LOAD

The LOAD command can be used to load data from a sequential file into a target table.

Syntax

 LOAD FROM 'file-name' INTO table-name <NULL | NONULL,>
 <DUPLICATES | NODUPLICATES>  <LOGFILE 'file-name'>
 <(col-name POS(s:e), ..., col-name POS(s:e)) | DELIMITER 'character' >;

Description

NULL (default) specifies that the first byte for each column value in the input file is used to indicate whether the value is NULL or not. An ampersand (&) in this byte indicates NULL, all other values indicate NOT NULL.

NONULL specifies that the values in the input file are entered into the columns exactly as read (i.e. NULL values can not be entered).

DUPLICATE (default) specifies that the number of duplicates found during the load operation will be reported. NODUPLICATES means that number of duplicates will not be reported or logged.

The number of rows not loaded because of a conversion error will be reported. It will also be logged if LOGFILE has been specified.

LOGFILE specifies a sequential file, where duplicate rows and rows not loaded because of a conversion error may be logged.

If column-specifications are given, only values for the columns which are given will be read from the input file. For each column, the sequential position for the start and the end byte of the value to assign should be specified in POS(s:e).

If a delimiter character is specified, the values for the columns which are read from the input file are expected to be delimited by the specified character.

If neither column-specifications nor a delimiter character are specified, default values for positions to read from are determined from the table definition. All columns will be given values.

The LOAD command may not be used if a transaction is active. For further information on transactions, see Managing Transactions on page 93.

Examples:
 LOAD FROM 'rooms.dat' INTO rooms NULL,DUPLICATES LOGFILE 'rooms.dup';
 
 LOAD FROM 'rooms2' INTO rooms NONULL (roomno POS(1:5), roomtype POS(8:18));
 
 LOAD FROM 'rooms.txt' INTO rooms NONULL DELIMITER ',';

LOG

Logs input, output or both to a specified sequential file.

Syntax

 LOG INPUT|OUTPUT| INPUT,OUTPUT ON|APPEND 'filename';

Description

All input, output or both will be logged in the specified sequential file. If ON is specified a new file will always be created, otherwise the log data is appended to the file.

Logging is stopped with the SET LOG OFF command and is resumed with the SET LOG ON command.

READ INPUT

Reads commands from a sequential file.

Syntax

 READ [COMMAND|ALL] [INPUT FROM] 'filename';

Description

Commands and SQL statements are read from the specified file.

When READ COMMAND INPUT is specified, commands are read from the file while prompt answers are taken from the terminal (batch job, command procedure).

When READ ALL INPUT or READ INPUT is specified, both commands and prompt answers are read from the sequential file.

SET ECHO

Controls whether or not lines read during READ INPUT are echoed.

Syntax

 SET ECHO ON|OFF;

Description

When echo is set to ON, lines read during READ INPUT are echoed to the terminal or batch log file. When echo is set to OFF, these lines are not echoed. The default value is ON.

The setting has no effect on the output of responses to BSQL commands and statements.

SET LINECOUNT

Sets the length of the terminal page.

Syntax

 SET LINECOUNT|LC value;

Description

The LINECOUNT value defines the length of the terminal page.

If LINECOUNT has a value greater than zero, terminal output will temporarily be stopped after the number of lines defined for the value.

After the 'Continue'-prompt, the user will have the choice of either continuing with the display or terminating the output.

Answering 'Y' (default) implies that the output will continue until the number of lines is reached again.

Answering 'N' terminates the output. Answering 'G' will ignore the linecount and the output will continue until all data are displayed.

If LINECOUNT is zero, the output will continue until all data is displayed.

The value of LINECOUNT must either be zero or >= 10.

Default

If BSQL is run from a batch job, LINECOUNT is zero by default. For interactive operation, the default value is machine- and terminal-dependent.

SET LINESPACE

Sets the number of blank lines between each output record.

Syntax

 SET LINESPACE|LS value;

Description

The LINESPACE value defines the number of blank lines to be written between each output record. This value is only used when printing the result of a SELECT statement.

The maximum value for LINESPACE is 9. The default value is 0.

SET LINEWIDTH

Specifies the width of the output.

Syntax

 SET LINEWIDTH|LW value;

Description

The LINEWIDTH value defines the maximum line width for output to the terminal or batch log file.

The value for LINEWIDTH cannot be set to a value less than 20.

SET LOG

Stops or resumes logging input, output or both.

Syntax

 SET [INPUT|OUTPUT|INPUT, OUTPUT]  LOG  OFF|ON;

Description

When SET LOG is set to OFF, logging of input, output or both in a sequential file is temporarily stopped.

Resume logging with the SET LOG ON command.

If no input/output log is specified, all active logs are stopped or resumed.

SET MESSAGE

Specifies whether or not messages should be displayed.

Syntax

 SET MESSAGE|MSG ON|OFF;

Description

Specifies whether or not result messages such as 'One row found' etc. are written to the terminal screen or batch log file.

The default setting is ON.

SET OUTPUT

Specifies whether or not output should be displayed.

Syntax

 SET OUTPUT ON|OFF;

Description

When OUTPUT is set to ON, the output from BSQL is written to the terminal or batch log file. When it is set to OFF, the output does not appear.

The default value is ON.

SET PAGELENGTH

Specifies the page size of the output log file.

Syntax

 SET PAGELENGTH|PL value;

Description

The PAGELENGTH value defines the page size of the file on which output is logged, i.e. at what interval a page break will be performed. A value of zero will result in no page breaks.

The PAGELENGTH value can either be set to zero or >= 10. The default value is machine-dependent.

SET PAGEWIDTH

Specifies the page width of the output log file.

Syntax

 SET PAGEWIDTH|PW value;

Description

The PAGEWIDTH value defines the page width of the output file. The value should be >= 20. The default value is machine-dependent.

SHOW SETTINGS

Displays the current values of all set options.

Syntax

SHOW SETTINGS;

Description

Displays the current values for all set options, i.e.

ECHO

LINEWIDTH

OUTPUT

TRANSACTION START

LINECOUNT

LOG

PAGELENGTH

TRANSACTION ISOLATION LEVEL

LINESPACE

MESSAGE

PAGEWIDTH

TRANSACTION MODE (read only or read write)

Current server and connection names are also displayed.

UNLOAD

The UNLOAD command can be used to unload data from a table into a sequential file.

Syntax

 UNLOAD TO 'file-name' FROM table-name <NULL|NONULL>
 < (col-name POS(s:e), ..., col-name POS(s:e))  | DELIMITER 'character' >;

Description

NULL (default) specifies that the first byte for each column value in the output file is used to indicate whether the value is NULL or not. This byte is assigned an ampersand (&) if the column from which the field is derived contains NULL, the rest of the field is filled with periods (...). Otherwise the byte is blank.

NONULL specifies that the first byte for each column value in the output file is the first data byte of the value.

If column-specifications are given, the output file will only hold values for the columns which are given. For each column the sequential position of the start and the end byte of the column value should be specified in POS(s:e). Overlapping is not controlled.

If a delimiter character is given, column data will be written to the output file delimited by the specified character. All columns will be included.

If neither column-specifications nor a delimiter character are given, default values for positions are determined by the table definition. All columns will be included.

The UNLOAD command may not be used if a transaction is active. For further information on transactions, see Managing Transactions on page 93.

Examples:
 UNLOAD TO 'rooms.dat' FROM rooms;
 
 UNLOAD TO 'rooms2' FROM rooms NONULL
   (roomno POS(1:5), roomtype POS(8:18));
 
 UNLOAD TO 'rooms.txt' FROM rooms NONULL DELIMITER ',';

WHENEVER

Determines which actions should be taken in the event of an error or warning.

Syntax

 WHENEVER ERROR|WARNING action<,action>;

Description

If an error or warning should occur in a file being run in batch, there are several 'action' options that may be chosen to determine what should happen.

The actions can be broken down into two groups:

Execution Flow

EXIT
Leaves BSQL in batch mode. Returns to prompt if interactive mode. I.e. if interactive mode and file input mode, the remaining file input is ignored and a new prompt is received.
CONTINUE
Continues execution.

Transaction Control

ROLLBACK
Abandons the transaction; no changes are made to the database.
COMMIT
Requests that the operations are executed against the database, and the changes in the database are made permanent.

The transaction control action can only be used if the execution flow is specified as EXIT. If execution flow is CONTINUE any ongoing transaction will not be affected by an error.

Default

The default value for warning is CONTINUE.

The default values for errors are EXIT, ROLLBACK in batch mode or file input mode and CONTINUE in interactive mode.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX