Batch Job

[Himanshu_3]

Hi all,

Can you help regarding this batch job to go tp sql and then check/add entries.
The output is as below

$set on
$set verify
$sql
$attach 'filename vms_live;
%SYSTEM-W-NONEXPR, nonexistent process
$set trans read only;
%DCL-W-IVKEYW, unrecognized keyword - check validity and spelling
\TRANS\

Thanx & Regards

[Bradford Hamilton_1]

Hi,

Not sure what command "sql" translates to, but on our RDB VMS machines at work, SQL is invoked by the command, "MCR SQL$" (note the trailing dollar sign).

If you are going to contain SQL statements in a batch job, (attach and set trans) thos lines of code should _not_ contain leading "$", as the DCL interpreter may get confused.

--Brad

[Bojan Nemec · Accepted solution]

Hi HP,

I dont understand quite well what is your problem. But looking to yours output seems you want to run SQL and then give it some commands.
The wrong thing seems to be the $ at the beginning of $attach 'filename vms_live; and $set trans read only;. The procedure must be something like this:

$set on
$set verify
$sql
attach 'filename vms_live;
set trans read only;

without starting $.

Bojan

[Ian Miller.]

you may also be missing a ending quote before the ;

[Himanshu_3]

Now the second part,

How can I pass the parameters of VMS system into SQL ?

Example:If I want to pass the value of F$time() into sql

Script is......

$set noon
$set noverify
$sql
attach 'filename vms_live';
set trans read write;
set out callinglog.txt;
insert into ping_dtl values (
1,'22-OCT-2004 00:00:00.00', 'U', 'CLC_DFN',
'1,CLC_IDN,dC9281','Y','D', 'D', 'D', 'D', 'D', 'Y', 1);

Here I want to pass the parameters of current system time of lexical f$time() in place of '22-OCT-2004 00:00:00' .

How can that be done .

Regards,
HP

[Uwe Zessin]

Unfortunately you can't do that directly - you have to write the input for SQL to an intermediate file (input.tmp in this example).

$ open/write O input.tmp
$ write O "attach 'filename vms_live';"
...
$ write O "insert into ping_dtl values (1,'", F$TIME(), "', 'U',...
$ close O

then, run the whole thing:

$ set noon
$ set verify
$ define /user_mode SYS$INPUT input.tmp
$ sql
$

[Ian Miller.]

HP, you may also be interested in this thread
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=726103

[Antoniov.]

Adding more info to Uwe's post,
remember you cannot use twice single quote in your string because this means symbol translation.
For example this sql statement
insert into ... values ('A','');
cannot be written into
$ write o "insert into ... values ('A','');"
You must create a new symbol and use it
$ Q="'"
$ write o
"insert into ... values('A',",Q,Q,");"

Antonio Vigliotti

[Jan van den Ende]

Antonio,

where in the given example of the question did you find any sequence of two single quotes?
To my eyes, and as far as I remember SQL syntax, the single quote after a value is followed by a comma, before the single quote of the next value.
So, no problem there! (although, IF the issue should come to happen, then of course your solution is of course the way to go).
But as far as a sequence of values in an SQL statements is concerned, no need to make it any more complicated then needed.

fwiw,

Cheers.

Have one on me.

Jan

[Uwe Zessin]

I don't think Antonio wanted to correct me - it was rather a hint if H P needs to create an SQL command with two consecutive "'"s.