Link to home
Start Free TrialLog in
Avatar of rbhargaw
rbhargawFlag for United States of America

asked on

Rollback in Unix Script

Hello Experts,

Can any one tell me how to handle errors in the below script? In the below code, I want to do an rollback if
"insert into <Table2> select * from <Table1>" command fails and commit if no errors. Let me know the syntax to do.

Thanks
isql -U${ID} -S${SERVERNAME} -P${PASSWD} << EOF > ${OUTPUTHOME}/${filename}
set nocount on
use database 
go
truncate table <Table2>
go
begin transaction
go
insert into <Table2> select * from <Table1>
go
commit transaction
go
exit
EOF

Open in new window

Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

This is not a "scripting" issue nor it can be solved using bash commands.
You would be better off reading the SQL reference of whatever rdbms you are using.
 
Avatar of rbhargaw

ASKER

Normally in SQL we will put

IF @@error!=0
                        BEGIN
                              --echo 'failed'
                              ROLLBACK TRAN
            
                        END

Can't this be written via Unix?

Then you have the solution:


isql -U${ID} -S${SERVERNAME} -P${PASSWD} << EOF > ${OUTPUTHOME}/${filename}
set nocount on
use database 
go
truncate table <Table2>
go
begin transaction
go
insert into <Table2> select * from <Table1>
go
IF @@error!=0
  BEGIN
    --echo 'failed'
    ROLLBACK TRAN
  ELSE
    commit transaction            
  END
go
exit
EOF

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of MikeOM_DBA
MikeOM_DBA
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Mike,

If I try this , it does not work..basically @@error will not work in a shell script right?

if @@error != 0
then
echo "hello"
fi

if [ $? = 0 ]
then
echo "correct"
else
echo "false"
fi
go


I tried this too but it is saying "Incorrect syntax near 'then'.
Thanks Mike..You were correct the first time itself :P