Trapping ISQL error in UNIX shell script
John S. Doody
stored procedures in succession and if one fails in the series, I don't
want the subsequent ones to execute.
John
Ed Heal
Ed
--
Ed Heal, Development programmer, NISS _/ mailto:E.H...@niss.ac.uk
National Information Services & Systems _/ Tel: +44 1225 323721
University of Bath, BATH BA2 7AY. UK. _/ Fax: +44 1225 826177
Mariano Corral
> I need to trap ISQL errors in UNIX shell scripts. I'm running several
> stored procedures in succession and if one fails in the series, I don't
> want the subsequent ones to execute.
I use this primitive approach: Whenever there is a chance of error in
a SQL stmt, I check its success or failure with some coding like
if @@error <> 0 set noexec on
or
if @@sqlstatus = 1 set noexec on /* fetch */
or
if @@rowcount <> my_expected_value set noexec on
Among the last SQL statements on the isql script I write
print "Ended OK"
This is only executed if neither of the above "if" statements where true,
so if I grep the last lines of output and found "Ended OK", my whole
isql script was executed correctly. I mean, correctly to the extent of
the validations performed by means of "if" statements.
A simpler approach is to grep on the output looking after lines with the
string "Msg" which don't contain also the string "Level 0,". This pertains
to warnings.
Regards,
Mariano Corral
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
hrd_syspro
an error in SYBASE.
Have fun.
"John S. Doody" wrote:
> I need to trap ISQL errors in UNIX shell scripts. I'm running several
> stored procedures in succession and if one fails in the series, I don't
> want the subsequent ones to execute.
>
> John
Phil Burchfield
is it cool to run isql seperately for each proc? if so, you can save the
following script as runproc:
#!/bin/sh
doproc(){
isql -Sserver -Uuid -Ppw << eof
$*
go
eof
}
exit $(doproc $* | sed -n 's/^.*return status =
\(-*[0-9][0-9]*\).*$/\1/p')
and run your procs with:
runproc spa && runproc spb && runproc spc
you'll probably want to make this a little more robust, i.e. doing
something intelligent with return statuses outside of 0 - 255
phil
Brian Ceccarelli
"John S. Doody" wrote:
> I need to trap ISQL errors in UNIX shell scripts. I'm running several
> stored procedures in succession and if one fails in the series, I don't
> want the subsequent ones to execute.
>
> John
The definitive program that does this is called "btSQL". It's part of the
Sybase tools package found on public ftp site ftp.cdrom.com.
Look in /pub/unixfreeware/database/btools-3.0.tar.Z.
This package works only on Sun Solaris platforms. Open Client 11.1 must be
installed on the Sun. The package works with Sybase servers versions 4.9
through 11.9.2.
There is a more up-to-date version in
/pub/unixfreeware/incoming/sybase-4.0.tar.Z. I recommend you install this
one. However, until the ftp site's administrator gets on the ball and
moves the file to ../database, you won't be able to read it. I just sent
an e-mail to the site admin. I hope he notices it.
I can also burn a CD for you. Send $25.00 to make it worth my while. Send
your check and address to
Brian Ceccarelli
106 Dutchess Drive
Cary, NC 27513
Since I wrote the program, I guarantee your satisfaction or your money
back. Really. As of today, I don't know of any bugs in any of these
programs. They are extremely stable and proven in the field.
Internally, btSQL is trapping all the server and client messages as your
SQL is executing. (I route all incoming messages through my client and
server message callbacks.) btSQL knows the difference between an error and
an informational message. btSQL also understands if its talking to a
Replication Server, a Backup Server or a SQL Server. Even though the
severity level of an informational message and an error message changes
between these kinds of servers. btSQL behaves accordingly.
If btSQL traps an error, btSQL exits immediately. No more SQL executes.
This is the behavior you are looking for. Also, when your code has a SQL
error, btSQL exits with a non-zero return code. You can check that with
UNIX. If your SQL script completed without an error, btSQL returns
zero. You can also make btSQL exit successfully and unconditionally if a
print "exit btSQL" in your SQL script executes.
The total sybase-4.0.tar.Z package consists of some 80 Sybase programs.
You get the executables and the source code. All programs are command line
driven so that you can put them in scripts. All programs return 0 when
successful, non-zero if any problem occurs. There are programs to list all
the table definitions, update statistics all tables in a database, dump all
the databases in a server, copy entire schemas from one user to another,
from database to database, or from server to server. You can even migrate
the data and place the tables and indexes on different segments as you
go. There is even a program which tells you exactly where every object in
your database resides.
You'll find everything a DBA longs to have. FYI, General Electric, Lucent,
NC State and Duke University Medical Center use these programs in
production.
Matlock
You must also check !? for an error in actually running the command.
Mariano Corral wrote in message <7bhcub$9rf$1...@nnrp1.dejanews.com>...
>john....@attws.com wrote:
>> I need to trap ISQL errors in UNIX shell scripts. I'm running several
>> stored procedures in succession and if one fails in the series, I don't
>> want the subsequent ones to execute.
>
Ross Mohan
I don't know Brian, but based on an offline discussion with him, I say his
knowledge is solid, and his tools likely quite useful.
25 smackers is probably a bargain.
IMHO, YMMV,AFAIK, etc etc etc
ross
Brian Ceccarelli wrote in message <36DDA91C...@icagen.com>...
<snip>