topic Re: Batch Job in Operating System - OpenVMS

Batch Job

Himanshu_3 — Tue, 02 Nov 2004 05:51:44 GMT

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

Re: Batch Job

Bradford Hamilton_1 — Tue, 02 Nov 2004 06:21:51 GMT

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

Re: Batch Job

Bojan Nemec — Tue, 02 Nov 2004 06:23:14 GMT

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

Re: Batch Job

Ian Miller. — Tue, 02 Nov 2004 06:51:17 GMT

you may also be missing a ending quote before the ;

Re: Batch Job

Himanshu_3 — Tue, 02 Nov 2004 10:22:29 GMT

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

Re: Batch Job

Uwe Zessin — Tue, 02 Nov 2004 10:29:11 GMT

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
$

Re: Batch Job

Ian Miller. — Tue, 02 Nov 2004 11:06:13 GMT

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

Re: Batch Job

Antoniov. — Wed, 03 Nov 2004 02:36:22 GMT

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

Re: Batch Job

Jan van den Ende — Wed, 03 Nov 2004 09:19:25 GMT

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

Re: Batch Job

Uwe Zessin — Wed, 03 Nov 2004 09:35:54 GMT

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.

Re: Batch Job

Hein van den Heuvel — Wed, 03 Nov 2004 10:53:42 GMT

>>> 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.

Ditto. And a good reminder/hint it is.
It is not easy to get dcl to write out two single quote with nothing in between. One is free. Two is hard.
You basically have to build the target string with concatenations or multiple arguments.

$ write sys$output "insert into ... values ('A','"+"');"
--->
insert into ... values ('A','');

Sometimes I use a 'QQ' symbol to help:

$ qq= "'"+"'"
$ write sys$output "insert into ... values ('A',",qq,")"
--->
insert into ... values ('A','')

two more hints:
- Sometimes you need a "&" when working with quotes in symbols in order to 'postpone' substitution.

- Instead of generating SQl commands you may want to look in generating data, and using a dataloader tools instead of sql. By writing out the real data you'll drop one level of quoting quotes

fwiw,
Hein.

Re: Batch Job

Antoniov. — Thu, 04 Nov 2004 02:43:50 GMT

Jan,
my post is hard to read simply because it's not possible distinguish two single quote by double quote (may be this character font).
Two single quote, in SQL means a empty string and it's used to write an empty not null value.
Hein posted another interesting solution :-)

Antonio Vigliotti

Re: Batch Job

Jan van den Ende — Thu, 04 Nov 2004 03:09:45 GMT

Antonio,

You're right.
I totally forgot about empty string.
Guess it was just a bit too late when I wrote the previous entry :-(

Cheers.

Have one on me.

jpe