Hi,

I'd like to run a SQL quey on the ingres database on openvms through a command script. So that I can un this command script as a job weekly and email the results file to specific users?

Is there any way to do this?
Please let me know.

thanks,
geek.

i don't know specifically Ingres, but there should be something similar than in Oracle:
The "start somefile" or "@somefile" in Oracle SqlPlus executes sql statements in the file.
Together with a "spool filename" it produces an output file to be mailed then.

Probably Ingres Sql reads its input from sys$input, so in DCL commandfiles simply put all sql statements behind the sql command:

$ sql user/password
spool mylisting.lis
start somesqlfile
$ if f$search("mylisting.lis").nes.""
$ then
$ mail mylisying.lis addressee

I usually run sql queries by going to isql in vax.
so now i want these queries to run via a job(i.e. a command script).
so how do i go about it.

also do you mean that mylisting.lis has the sql query?

As I told You, I have only experience with Oracle sql, the corresponding Ingres isql You should lookup in Ingres docu.
"spool file" in Oracle sql directs the standard output (i.e. query results) to the file.
In principle scripting sql should work by putting all sql statements behind the invoking isql command:

$ isql user/password
sql statement
select from ...
...
$! dollar next DCL command=EOF for isql

If it doesn't work (if isql still reads input from the terminal), put a
$ define/user sys$command sys$input
before the $ isql .

This is how Oracle sql works, I would wonder if Ingres does not work this way.

Hi,

i tried to create a job, but this is what i got in the log after running it :

$ isql databasename
E_TD001C Cannot determine user's terminal type.
Either TERM_INGRES is not defined or it is defined to nothing. Please
check that TERM_INGRES is defined correctly and restart.

E_FI1FA4 Could not initialize terminal.

%DCL-W-SKPDAT, image data (records not beginning with "$") ignored

any suggestions??

Well then my assumption was wrong, apparently isql requires a terminal for I/O.

Did You consider to put Your question on a CA support forum ?
http://www.ingres.com

thanks for ur help though.

i'll try that forum as well.