Running Mimer BSQL Scripts

This chapter describes how to run BSQL scripts in the Mimer SQL environment.

Running a BSQL Script

BSQL can be used to run SQL commands from within a script. There are several ways to use BSQL and some examples are given here.

You can use the READ command from within BSQL to read a file containing SQL statements:

$ CREATE Q.SQL

SELECT 1+1 FROM SYSTEM.ONEROW;

$ BSQL/USER=SYSADM/PASSW=SYSADM

READ 'Q.SQL';

 

To use BSQL from within a command procedure (.COM file), you do like this:

$ BSQL

SYSADM

SYSADM

SELECT 1+1 FROM SYSTEM.ONEROW;

$ ! next DCL command beginning with dollar ends program input.

 

To execute a single SQL command, the /QUERY switch can be used with BSQL:

$ BSQL/USER=SYSADM/PASSW=SYSADM/QUERY="SELECT 1+1 FROM SYSTEM.ONEROW"

 

The VMS command PIPE can be used to create Unix-like pipes with Unix-like redirection. This command reads SQL statements from the file Q.SQL:

$ PIPE BSQL/USER=SYSADM/PASSW=SYSADM < Q.SQL > RESULT.TXT

 

Old-time VMS users would achieve the same thing by redefining SYS$INPUT and SYS$OUTPUT. By defining them in USER mode, the definitions are dropped automatically at image (program) exit:

$ DEFINE/USER SYS$INPUT Q.SQL

$ DEFINE/USER SYS$OUTPUT RESULT.TXT

$ BSQL/USER=SYSADM/PASSW=SYSADM

 

A problem with all the examples above is that the password for the Mimer ident used is stored in a file. This should generally be avoided unless the security of the file can be guaranteed.

The problem can be solved by adding an OS_USER login to the user. A user with an OS_USER login can log in without providing a password. (This does not work over TCP/IP.)

Example - same username for VMS and Mimer:

$ BSQL /USER=SYSADM /PASSW="SYSADMPASSW"

SQL> CREATE IDENT PER AS USER;

SQL> ALTER IDENT PER ADD OS_USER 'PER';

SQL> EXIT;

 

Now VMS user PER can do:

$ BSQL /USER="" /QUERY="SELECT 1+1 FROM SYSTEM.ONEROW"

Example - different usernames for VMS and Mimer:

$ BSQL /USER=SYSADM /PASSW="SYSADMPASSW"

SQL> CREATE IDENT DANIEL AS USER;

SQL> ALTER IDENT DANIEL ADD OS_USER 'DAN';

SQL> EXIT;

 

Now VMS user DAN can connect as Mimer user DANIEL with:

$ BSQL /USER="DANIEL" /PASSW=""