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

[Dan Stromberg]

This is cool - but what about error checking?

DATAllegro Support
1 (877) 470-DATA (3282)
Support at datallegro.com <mailto:Support at 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.



Peter Gale wrote:
> 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
>>     
>
>