• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1314
  • Last Modified:

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

0
rbhargaw
Asked:
rbhargaw
  • 4
  • 3
1 Solution
 
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
 
rbhargawAuthor Commented:
Normally in SQL we will put

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

Can't this be written via Unix?
0
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
MikeOM_DBACommented:

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
 
rbhargawAuthor 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
 
rbhargawAuthor Commented:
if [ $? = 0 ]
then
echo "correct"
else
echo "false"
fi
go


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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now