Calling an Informix Stored Proc that Returns a value from a UNIX script
Lisa asked how a Unix shell script could detect whether an Informix stored procedure returned a value or failed. Richard Harnden suggested capturing dbaccess output with awk or just checking $?; Doug Lawry showed an exit-status test. Art Kagel proposed unloading the procedure's result to a file with UNLOAD and testing whether that file was empty. No confirmation of which worked.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi,
I need to call an informix stored procedure from my unix script. My
informix stored proc returns a value if the stored proc did not fail.
How do I get the return value from the informix stored proc?
Here is my unix script:
#!/bin/sh
echo Start Time $today >daily.log
date >> daily.log
dbaccess 2>>daily.log <<SQLSTMT
database xxx;
execute procedure test();SQLSTMT
if (the stored procedure returns a failure) then
rmail lisalisa@lisa.com <<endmsg2
>From Lisa 10:00am remote from servername
The Stored Proc Failed.
endmsg2
Thanks...Any help is appreciated!
~
~
On 12 Mar, 14:51, lisa_0...@yahoo.com wrote:
> Hi,
>
> I need to call an informix stored procedure from my unix script. My
> informix stored proc returns a value if the stored proc did not fail.
> How do I get the return value from the informix stored proc?
>
> Here is my unix script:
>
> #!/bin/sh
> echo Start Time $today >daily.log
> date >> daily.log
>
> dbaccess 2>>daily.log <<SQLSTMT
> database xxx;
> execute procedure test();> SQLSTMT
You don't do anything with stdout.
Maybe something like:
let R=`dbaccess fss <<X 2>/dev/null | \\
awk '/[0-9]/ {print $1;}'
select tabid from systables where tabname = "systables"X`
would work. If so, then get sqlcmd.
Or, maybe just checking $? would be enough.
>
> if (the stored procedure returns a failure) then
> rmail lisal...@lisa.com <<endmsg2
>
> >From Lisa 10:00am remote from servername
>
> The Stored Proc Failed.
>
> endmsg2
>
> Thanks...Any help is appreciated!
>
> ~
> ~
if [ $? != 0 ] ; then # Check exit status of last command
--
Regards,
Doug Lawry
www.douglawry.webhop.org
<lisa_0801@yahoo.com> wrote in message
news:1173711067.473526.25270@s48g2000cws.googlegroups.com...
> Hi,
>
> I need to call an informix stored procedure from my unix script. My
> informix stored proc returns a value if the stored proc did not fail.
> How do I get the return value from the informix stored proc?
>
> Here is my unix script:
>
> #!/bin/sh
> echo Start Time $today >daily.log
> date >> daily.log
>
>
> dbaccess 2>>daily.log <<SQLSTMT
> database xxx;
> execute procedure test();> SQLSTMT
>
> if (the stored procedure returns a failure) then
> rmail lisalisa@lisa.com <<endmsg2
>>From Lisa 10:00am remote from servername
>
> The Stored Proc Failed.
>
> endmsg2
>
> Thanks...Any help is appreciated!
lisa_0801@yahoo.com wrote:
> Hi,
>
> I need to call an informix stored procedure from my unix script. My
> informix stored proc returns a value if the stored proc did not fail.
> How do I get the return value from the informix stored proc?
>
> Here is my unix script:
>
> #!/bin/sh
> echo Start Time $today >daily.log
> date >> daily.log
>
>
> dbaccess 2>>daily.log <<SQLSTMT
> database xxx;
> execute procedure test();> SQLSTMT
>
> if (the stored procedure returns a failure) then
> rmail lisalisa@lisa.com <<endmsg2
>>From Lisa 10:00am remote from servername
>
> The Stored Proc Failed.
>
> endmsg2
Try this:
#!/usr/bin/ksh
echo Start Time $today >daily.log
date >> daily.log
touch results.fil
dbaccess databasename 2>>daily.log <<SQLSTMT
unload to 'results.fil' delimiter ' '
execute procedure test();SQLSTMT
if [[ -z results.fil ]]; then
rmail lisalisa@lisa.com <<-endmsg2
From Lisa 10:00am remote from servername
The Stored Proc Failed.
endmsg2
else
cat results.fil
fi
rm results.fil
# EOF
Art S. Kagel