Running Ingres SQL commands from a shell script?

[Dan Stromberg]

Hi folks.

Is there any good way, from a shell script, to run SQL commands against
Ingres (perhaps via ingres' terminal monitor), and to get a useful exit
status from the SQL statements back to the calling shell?

I realize I could use something like python with an ODBC interface, but 1)
that seems kind of heavyweight for what I want to do and 2) I don't think
I'll be able to get permission to put an ODBC interface -on- the
machines in question. It's pretty much whatever comes with CentOS 4.4 or
not at all for this project.

Ideal might be to have any exit stati and'd or or'd together (dare I say,
at the caller's discretion?), or to have an array of exit stati kind of
like bash's $PIPESTATUS array, except for a chronology of statements, not
concurrent-but-chained-together statements.

If I must, I could run the SQL statements (the ones with side effects,
at least) one a time though.

Any suggestions?

Thanks!

[OldSchool]

Post by Dan Stromberg
Hi folks.
Is there any good way, from a shell script, to run SQL commands against
Ingres (perhaps via ingres' terminal monitor), and to get a useful exit
status from the SQL statements back to the calling shell?
I realize I could use something like python with an ODBC interface, but 1)
that seems kind of heavyweight for what I want to do and 2) I don't think
I'll be able to get permission to put an ODBC interface -on- the
machines in question. It's pretty much whatever comes with CentOS 4.4 or
not at all for this project.
Ideal might be to have any exit stati and'd or or'd together (dare I say,
at the caller's discretion?), or to have an array of exit stati kind of
like bash's $PIPESTATUS array, except for a chronology of statements, not
concurrent-but-chained-together statements.
If I must, I could run the SQL statements (the ones with side effects,
at least) one a time though.
Any suggestions?
Thanks!

Another option to pursue is the shells "here-doc" ability. For
example

Search_val=Jim
sql <<eof
select * from table1 where col1='$Search_val';

eof


note that the "eof"s above can be any string you define. if you use
"<<" operator the string marking the end of input *must* be in column
1, if you use "<<-", the ending mark may be preceded by tab.

check the man page for your shell

[Dan Stromberg]

Post by OldSchool

Post by Dan Stromberg
Hi folks.
Is there any good way, from a shell script, to run SQL commands against
Ingres (perhaps via ingres' terminal monitor), and to get a useful exit
status from the SQL statements back to the calling shell?
I realize I could use something like python with an ODBC interface, but 1)
that seems kind of heavyweight for what I want to do and 2) I don't think
I'll be able to get permission to put an ODBC interface -on- the
machines in question. It's pretty much whatever comes with CentOS 4.4 or
not at all for this project.
Ideal might be to have any exit stati and'd or or'd together (dare I say,
at the caller's discretion?), or to have an array of exit stati kind of
like bash's $PIPESTATUS array, except for a chronology of statements, not
concurrent-but-chained-together statements.
If I must, I could run the SQL statements (the ones with side effects,
at least) one a time though.
Any suggestions?
Thanks!

Another option to pursue is the shells "here-doc" ability. For
example
Search_val=Jim
sql <<eof
select * from table1 where col1='$Search_val';
eof
note that the "eof"s above can be any string you define. if you use
"<<" operator the string marking the end of input *must* be in column
1, if you use "<<-", the ending mark may be preceded by tab.
check the man page for your shell

I can handle getting stuff into terminal monitor OK (although I hadn't
seen <<- before - that's pretty cool), it's more getting a useful
"pass/fail" result back from the terminal monitor to bash for some SQL
statements that I'm wondering about.

[Dan Stromberg]

If I egrep the output of the terminal monitor (sql shell command) for
"^E_", should that get all errors and only errors (iff there's an error)?

[Peter Gale]

Hi,

Here is a small script that hopefully illustrates what can be done in a
shell. It uses named pipes to communicate with an sql session that is
launched in the background.
By opening the pipes with file descriptors (exec) the parent is able to
freely read and write to each pipe without having to have a permanently
attached command. Thus the script can prompt for the SQL,
run it and process the output and then prompt for more SQL.

#!/bin/ksh
#
# Start SQL process

set -f # Suppress name generation

mkfifo IN OUT

sql ub1prod < IN > OUT &

# Open File descriptors for the fifo's
exec 3> IN
exec 4< OUT

while true
do

echo Enter SQL

# Prompt for the SQL. Ctrl+d to terminate the input
while read SQL
do
[ "$SQL" = "QUIT" ] && exit
print -u3 $SQL
done

# add the necessary directives and a marker to show the end of the
output.
print -u3 \\p\\g
print -u3 SQL_END\\p\\r

# Read the output until the end marker is seen and process errors
while read -u4 OUTPUT
[ "$OUTPUT" != "* SQL_END" ]
do
[ "`echo "$OUTPUT" | cut -c1-2`" = "E_" ] && echo "ERROR OCCURRED"
echo $OUTPUT
done
done

The loop that grabs the SQL could easily read from another file of commands
which could have the

SQL_END\p\r

emebedded in it. The script would be modified thus

while read SQL
do
print -u3 $SQL
done < sql_file

and sql_file would look like this

SELECT....
\p\g
SQL_END\p\r
SELECT ....
\p\g
SQL_END\p\r

etc.
Of course dont forget to do some COMMITs here and there :)

HTH

Peter Gale

-----Original Message-----
Sent: 26 April 2007 03:33
Subject: [Info-Ingres] Running Ingres SQL commands from a shell script?
Hi folks.
Is there any good way, from a shell script, to run SQL commands against
Ingres (perhaps via ingres' terminal monitor), and to get a useful exit
status from the SQL statements back to the calling shell?
I realize I could use something like python with an ODBC interface, but 1)
that seems kind of heavyweight for what I want to do and 2) I don't think
I'll be able to get permission to put an ODBC interface -on- the
machines in question. It's pretty much whatever comes with CentOS 4.4 or
not at all for this project.
Ideal might be to have any exit stati and'd or or'd together (dare I say,
at the caller's discretion?), or to have an array of exit stati kind of
like bash's $PIPESTATUS array, except for a chronology of statements, not
concurrent-but-chained-together statements.
If I must, I could run the SQL statements (the ones with side effects,
at least) one a time though.
Any suggestions?
Thanks!
_______________________________________________
Info-Ingres mailing list
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Dan Stromberg]

This is cool - but what about error checking?

DATAllegro Support
1 (877) 470-DATA (3282)
***@datallegro.com <mailto:***@datallegro.com>
www.datallegro.com <http://www.datallegro.com/>

85 Enterprise, 2nd Floor, Aliso Viejo, CA 92656

The information transmitted in this email is intended only for the
person(s) or entity to which it is addressed and may contain
proprietary, confidential and/or privileged material. If you have
received this email in error please contact the sender by replying and
delete this email so that it is not recoverable. If you are not the
intended recipient(s), any retention, review, disclosure, distribution,
copying, printing, dissemination, or other use of, or the taking of any
action in reliance upon, this information is strictly prohibited and
without liability on our part.

Post by Peter Gale
Hi,
Here is a small script that hopefully illustrates what can be done in a
shell. It uses named pipes to communicate with an sql session that is
launched in the background.
By opening the pipes with file descriptors (exec) the parent is able to
freely read and write to each pipe without having to have a permanently
attached command. Thus the script can prompt for the SQL,
run it and process the output and then prompt for more SQL.
#!/bin/ksh
#
# Start SQL process
set -f # Suppress name generation
mkfifo IN OUT
sql ub1prod < IN > OUT &
# Open File descriptors for the fifo's
exec 3> IN
exec 4< OUT
while true
do
echo Enter SQL
# Prompt for the SQL. Ctrl+d to terminate the input
while read SQL
do
[ "$SQL" = "QUIT" ] && exit
print -u3 $SQL
done
# add the necessary directives and a marker to show the end of the
output.
print -u3 \\p\\g
print -u3 SQL_END\\p\\r
# Read the output until the end marker is seen and process errors
while read -u4 OUTPUT
[ "$OUTPUT" != "* SQL_END" ]
do
[ "`echo "$OUTPUT" | cut -c1-2`" = "E_" ] && echo "ERROR OCCURRED"
echo $OUTPUT
done
done
The loop that grabs the SQL could easily read from another file of commands
which could have the
SQL_END\p\r
emebedded in it. The script would be modified thus
while read SQL
do
print -u3 $SQL
done < sql_file
and sql_file would look like this
SELECT....
\p\g
SQL_END\p\r
SELECT ....
\p\g
SQL_END\p\r
etc.
Of course dont forget to do some COMMITs here and there :)
HTH
Peter Gale

-----Original Message-----
Sent: 26 April 2007 03:33
Subject: [Info-Ingres] Running Ingres SQL commands from a shell script?
Hi folks.
Is there any good way, from a shell script, to run SQL commands against
Ingres (perhaps via ingres' terminal monitor), and to get a useful exit
status from the SQL statements back to the calling shell?
I realize I could use something like python with an ODBC interface, but 1)
that seems kind of heavyweight for what I want to do and 2) I don't think
I'll be able to get permission to put an ODBC interface -on- the
machines in question. It's pretty much whatever comes with CentOS 4.4 or
not at all for this project.
Ideal might be to have any exit stati and'd or or'd together (dare I say,
at the caller's discretion?), or to have an array of exit stati kind of
like bash's $PIPESTATUS array, except for a chronology of statements, not
concurrent-but-chained-together statements.
If I must, I could run the SQL statements (the ones with side effects,
at least) one a time though.
Any suggestions?
Thanks!
_______________________________________________
Info-Ingres mailing list
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres

[Peter Gale]

Hi Dan,

That goes in the second loop. I included something very basic

while read -u4 OUTPUT
[ "$OUTPUT" != "* SQL_END" ]
do
[ "`echo "$OUTPUT" | cut -c1-2`" = "E_" ] && echo "ERROR OCCURRED" #
Error checking
echo $OUTPUT
done

All you can look for when running the terminal monitor is E_ error messages.
You could it in an array and even have 2 arrays, one to hold the SQL and one
for the errors (if any).
If you want more info on arrays in ksh let me know.

Peter

Post by Dan Stromberg
This is cool - but what about error checking?
DATAllegro Support
1 (877) 470-DATA (3282)
www.datallegro.com <http://www.datallegro.com/>
85 Enterprise, 2nd Floor, Aliso Viejo, CA 92656
The information transmitted in this email is intended only for the
person(s) or entity to which it is addressed and may contain
proprietary, confidential and/or privileged material. If you have
received this email in error please contact the sender by replying and
delete this email so that it is not recoverable. If you are not the
intended recipient(s), any retention, review, disclosure, distribution,
copying, printing, dissemination, or other use of, or the taking of any
action in reliance upon, this information is strictly prohibited and
without liability on our part.

Post by Peter Gale
Hi,
Here is a small script that hopefully illustrates what can be done in a
shell. It uses named pipes to communicate with an sql session that is
launched in the background.
By opening the pipes with file descriptors (exec) the parent is able to
freely read and write to each pipe without having to have a permanently
attached command. Thus the script can prompt for the SQL,
run it and process the output and then prompt for more SQL.
#!/bin/ksh
#
# Start SQL process
set -f # Suppress name generation
mkfifo IN OUT
sql ub1prod < IN > OUT &
# Open File descriptors for the fifo's
exec 3> IN
exec 4< OUT
while true
do
echo Enter SQL
# Prompt for the SQL. Ctrl+d to terminate the input
while read SQL
do
[ "$SQL" = "QUIT" ] && exit
print -u3 $SQL
done
# add the necessary directives and a marker to show the end of the
output.
print -u3 \\p\\g
print -u3 SQL_END\\p\\r
# Read the output until the end marker is seen and process errors
while read -u4 OUTPUT
[ "$OUTPUT" != "* SQL_END" ]
do
[ "`echo "$OUTPUT" | cut -c1-2`" = "E_" ] && echo "ERROR OCCURRED"
echo $OUTPUT
done
done
The loop that grabs the SQL could easily read from another file of

commands

Post by Peter Gale
which could have the
SQL_END\p\r
emebedded in it. The script would be modified thus
while read SQL
do
print -u3 $SQL
done < sql_file
and sql_file would look like this
SELECT....
\p\g
SQL_END\p\r
SELECT ....
\p\g
SQL_END\p\r
etc.
Of course dont forget to do some COMMITs here and there :)
HTH
Peter Gale

-----Original Message-----

info-ingres-

Post by Peter Gale

Sent: 26 April 2007 03:33
Subject: [Info-Ingres] Running Ingres SQL commands from a shell script?
Hi folks.
Is there any good way, from a shell script, to run SQL commands against
Ingres (perhaps via ingres' terminal monitor), and to get a useful exit
status from the SQL statements back to the calling shell?
I realize I could use something like python with an ODBC interface, but

1)

Post by Peter Gale

that seems kind of heavyweight for what I want to do and 2) I don't

think

Post by Peter Gale

I'll be able to get permission to put an ODBC interface -on- the
machines in question. It's pretty much whatever comes with CentOS 4.4or
not at all for this project.
Ideal might be to have any exit stati and'd or or'd together (dare I

say,

Post by Peter Gale

at the caller's discretion?), or to have an array of exit stati kind of
like bash's $PIPESTATUS array, except for a chronology of statements,

not

Post by Peter Gale

concurrent-but-chained-together statements.
If I must, I could run the SQL statements (the ones with side effects,
at least) one a time though.
Any suggestions?
Thanks!
_______________________________________________
Info-Ingres mailing list
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres