|
|
|
|
|
|
BSQL Commands
Command
Function
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';READ COMMAND INPUT FROM 'filename'; READ ALL INPUT FROM 'filename'; READ INPUT FROM 'filename'; READ 'filename';CLOSE
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
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
EXIT
Syntax
EXIT;Description
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
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.
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.
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
Description
Displays the current values for all set options, i.e.
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
Transaction Control
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 |
|
|
|
|
|
|