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

rbhargawFounderAsked:
Who is Participating?
 
MikeOM_DBAConnect With a Mentor Commented:

Ooops, typo:

PS: Check for correct syntax!


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
    ROLLBACK TRAN
  END
ELSE
  BEGIN
    commit transaction            
  END
go
exit
EOF

Open in new window

0
 
MikeOM_DBACommented:
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.
 
0
 
rbhargawFounderAuthor Commented:
Normally in SQL we will put

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

Can't this be written via Unix?
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
MikeOM_DBACommented:

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

0
 
rbhargawFounderAuthor Commented:
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

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


I tried this too but it is saying "Incorrect syntax near 'then'.
0
 
rbhargawFounderAuthor Commented:
Thanks Mike..You were correct the first time itself :P
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.