pass sybase return code to unix shell

[hok1man]

Hi guys,

I've got the script which contain sybase sql as below

#!/usr/bin/ksh

isql -S.. -U.. -P.. << SQL
INSERT INTO TABLE VALUES (...)
SQL

if INSERT in sybase successful
    echo "success"
else
    echo "fail"

The only way I can tell whether insert successful or not is via sybase return code.

if I do via sybase, I can simply do select @@ERROR,

but when it go out to unix.. the return code is 0, I understand that.

So, is there anyway to pass the value "select @@ERROR" OUT TO UNIX ?

Thanks guys,

 

[feherke]

Hi

I know nothing about Sybase, but I would try something like this :

#!/usr/bin/ksh

output="$(
isql -S.. -U.. -P.. << SQL
INSERT INTO TABLE VALUES (...);
select @@ERROR;
SQL
)"

if echo "$output" | grep 'whatever indicates success' > /dev/null
then
    echo "success"
else
    echo "fail"
fi

Tell us how the [tt]select @@ERROR[/tt]'s output looks like and maybe we will find something better.

Feherke.

http://rootshell.be/~feherke/

 

[hok1man]

Hi feherke,

here's the output if successful or not in sybase:

if successful

(1 row affected)

off course the number of row is various.


if failed

Msg 2601, Level 14, State 6: Server 'PDS1_TEST', Line 1: Attempt to insert duplicate key row in object 'EXCPT_OUTGOING' with unique index 'idx_EXCPT_OUTGOING_0' Command has been aborted. (0 rows affected)

Thanks for your help.

 

[feherke]

Hi

Well, that error message is not very helpful. I expected something like PostgreSQL does : all error messages start with 'ERROR: ', so is easy to find them. And including the number of affected rows in the error message too, sounds even worse.

But anchoring the expression to the beginning of string hopefully will work correctly in all situations :

echo "$output" | grep '^([0-9]* rows\? affected)'

If you have no statement which could affect 0 rows even if is successful ( or you consider such situations as errors even if there is no failure ), you could restrict the expression to not accept 0s :

echo "$output" | grep '^([1-9][0-9]* rows\? affected)'

Feherke.

http://rootshell.be/~feherke/

 

[PDreyer]

do a grep for 'Msg'
else do e.g.
select '===err===',@@error
in the sql
then grep for '===err===' to see the error code