Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

Running BSQL

BSQL can be run by a script or interactively. Interactive operation can be used to execute statements entered directly or read from sequential files.

About Complex SQL Statements - @

Use the @ character to delimit a complex SQL statement where the normal end-of-statement character `;' appears before the end of the statement (e.g. when creating functions, procedures, methods and triggers.)

 create function capitalize(str nvarchar(1000)) returns nvarchar(1000)
 begin atomic
   declare outstr nvarchar(1000);
   declare strlen integer;
   declare n integer default 2;
   set strlen = length(str);
   set outstr = upper(substring(str from 1 for 1));
   while n <= strlen do
     set outstr = outstr || case when substring(str from n - 1 for 1) = ' ' then
                                   upper(substring(str from n for 1))
                                   lower(substring(str from n for 1))
     set n = n + 1;
   end while;
   return outstr;

Running BSQL From a Script

Create a script file with the following contents:

Unicode Pipe Support in Console Programs on Windows

All Mimer SQL console programs such as BSQL, can pipe Unicode files. The files can be any of the Unicode formats supported by Mimer SQL such as UTF16 big and little endian, and UTF8.

When output is piped to a file, the input decides the type of the output file. If the input file is ASCII, the output will also be ASCII. If the input file is UTF16, the output will also be UTF16. If the input is from the keyboard, the output will be an UTF16 file on Windows. For example:


Security and Script Jobs

For unattended operation, a script file must either include the Mimer SQL ident user name and password in explicit form or connect using an OS_USER login.

For security reasons, make sure that your script files are well protected and/or remove your password from the file after execution.

Alternatively, SQL statements and BSQL commands may be written in a sequential file without user name and password, and executed with the READ command from an interactive BSQL session.

Running BSQL

How you start BSQL depends on your operating system.

BSQL Command-line Arguments

Windows & Unix-style
 -u username
Ident name to be used in connect. If the switch is not given the user is prompted for a username.
To connect using OS_USER, give -u "", --username="", or /USERNAME="".
 -p password
Password for ident. If the switch is omitted the user is prompted for a password, unless OS_USER is specified with the username switch, as described above.
VMS: Note that in a Open VMS environment it might be necessary to enclose the password in quotation marks as the value otherwise is translated to upper case.
Connects to the database in multi-user mode.
Connects to the database in single-user mode.
 -q query
query can be any BSQL command or SQL statement. If a query is supplied, BSQL will terminate immediately after the query has been processed.
The switch can be used together with the --query switch to indicate that the BSQL program is not terminated after the execution of the query.
Specifies the name of the database to access. If a database name is not specified, the default database will be accessed, see Mimer SQL System Management Handbook, The Default Database.
Show help text.

If a database name is not specified, the default database will be accessed.

If neither --single nor --multi is specified for the optional mode flag, the way the database is accessed will be determined by the setting of the MIMER_MODE variable, see Mimer SQL Engine System Management Handbook, Appendix A, Specifying Single-user Mode Access.

If this is not set, it will be accessed in multi-user mode.

If multiple instances of a qualifier is given, the last one is used. For example

 bsql --single --multi --username=t1 --username=t2

is valid and means connect user t2 in multi-user mode.

Unix examples

Start BSQL and connect user cosmo with password Kramer:

 bsql --username=cosmo --password=Kramer

Start BSQL, connect using OS_USER, execute a query, and then leave:

 bsql --user="" --query="select * from \"SomeTable\" where user = 'COSMO'"
VMS examples

Start BSQL and connect user cosmo with password Kramer:

 bsql /USERNAME="cosmo" /PASSWORD="Kramer"

Start BSQL, connect using OS_USER, execute a query, and then leave:

 bsql /USERNAME="" /QUERY="select * from ""SomeTable"" where user = 'COSMO'"
Note: You can also use the Unix-style syntax in OpenVMS.
Windows examples

Start BSQL and connect user cosmo with password Kramer:

 bsql --username=cosmo --password=Kramer

Start BSQL, connect using OS_USER, execute a query, and then leave:

 bsql --user="" --query="select * from \"SomeTable\" where user = 'COSMO'"
To start Mimer BSQL from the Windows Start button:

Click Start, navigate to your Mimer SQL program group and select Batch SQL which is found in the Utilities sub-group.

Logging IN

Starting BSQL displays the following:

 $ bsql
 Mimer SQL Command Line Utility, version 10.1.1A
 Copyright (C) Mimer Information Technology AB. All rights reserved.

After you have entered a user name and a correct password, the BSQL prompt is displayed:


You can now enter BSQL specific commands and general SQL statements.

BSQL Command Line Editing - Unix

Command line editing is available in the BSQL program, which uses a line-oriented interface.

The following functions are available:

Move to beginning of command
Move backwards in command
Delete current character
Move to end of command
Move forwards in command
Delete previous character
Delete after current position in command
Next command
Execute retrieved command and get next from history list
Previous command
Retrieve command by search condition
Change place for the previous two characters
Delete command
Delete before current position in command
Set mark in command (or `esc <space>')
 ctrl-x ctrl-x
Go to mark set by `ctrl <space>'
 ctrl-x ctrl-h
Show the history list
 ctrl-x ctrl-r
Retrieve command by history list number
 esc h
Delete previous word
 esc d
Delete next word
 esc b
Move to previous word
 esc f
Move to next word

You can use the arrow keys for command retrieval and for positioning the cursor within a line, i.e. the same function as for ctrl-b, ctrl-f, ctrl-n and ctrl-p.

To change the number of commands that can be held in the history list, the environment variable MIMER_HISTLINES can be used (the default is 23).

Note: The operating system may have control sequences set that, if they overlap, override those described above. E.g. the settings can be listed using the Unix stty -a command.

Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
Mimer SQL Documentation TOC PREV NEXT INDEX