[Info-Ingres] Running Ingres SQL commands from a shell script?

[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-----
> From: info-ingres-bounces at kettleriverconsulting.com [mailto:info-ingres-
> bounces at kettleriverconsulting.com] On Behalf Of Dan Stromberg
> Sent: 26 April 2007 03:33
> To: info-ingres at kettleriverconsulting.com
> 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
> Info-Ingres at kettleriverconsulting.com
> http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres