HP OpenVMS Systems

DCL symbol substitution and SQL?

» close window

The Question is:

 
 
My problem is this, I am writing a .COM file which passes a value to a .SQL
file which is then run.  My problem is how do I pass a value to an .SQL file
which is then executed.
 
 
e.g
 
MYCOMFILE.COM
 
$ INQUIRE DZ "Please input a destination"
$ SQL @MYSQLFILE.SQL
$ EXIT
 
 
MYSQLFILE.SQL
 
 
SET TRANSACTION READ ONLY RESERVING table-name FOR SHARED READ;
SELECT * FROM table-name WHERE field-name=DZ;
ROLLBACK;
 
 
Could someone please tell me if this is possible, and if so what is the
correct syntax of the variable DZ.
 
 
 
 
Jefferson Humber
 
P.S I am running this on a VAX with Open VMS v.7.1 and Oracle Rdb 7.0-1 (Or
something like that!!)
 

The Answer is :

 
  The Wizard assumes you mean you want the token "DZ" in the file
  MYSQLFILE.SQL replaced by the text read by the INQUIRE command.
  (Use of the READ command is recommended over INQUIRE, too, as
  INQUIRE has various documented but potentially rather unexpected
  behaviours.)
 
  SQL may have a syntax to specify symbol substitution, but the
  Wizard doubts it -- DCL symbol substitution occurs only when the
  command input is read in and processed by DCL, not (as in this
  case) by the SQL utility.  In any case, you can achieve the same
  effect by generating your SQL file and substituting the text in
  yourself. For example:
 
  $ READ/PROMPT="Please input a destination " SYS$COMMAND DZ
  $ tmpname="SYS$SCRATCH:SQL"+F$GETJPI("","PID")+"TMP.SQL"
  $ CLOSE/NOLOG TMP
  $ OPEN/WRITE TMP 'tmpname'
  $ WRITE TMP "SET TRANSACTION READ ONLY RESERVING table-name FOR SHARED READ;"
  $ WRITE TMP "SELECT * FROM table-name WHERE field-name=''DZ';"
  $ WRITE TMP "ROLLBACK;"
  $ CLOSE TMP
  $ SQL @'tmpname'
  $ DELETE 'tmpname';
  $ EXIT
 
  Information on writing command procedures on OpenVMS, as well as
  on symbol substitution, is available in the OpenVMS User's Guide,
  and in the Digital Press (an imprint of Butterworth Heinneman; URL
  http://www.bh.com) book _Writing Real Programs in DCL_.

  

     

     
answer written or last revised on ( 8-FEB-1999 )
     
» close window