Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 sqlit, transaction

Author  Topic 

whitepear
Starting Member

24 Posts

Posted - 2014-04-19 : 07:00:12
Hello!
Please, does sqlit understand "exception" in transactions ( if there is a ny error, then u can undo the transaction)??
many thanks!!!!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-19 : 12:44:31
Yes
Have a look at a try catch block, begin transaction, commit transaction, rollback transaction.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

whitepear
Starting Member

24 Posts

Posted - 2014-04-21 : 02:45:26
Hello!
Please, can someone write an easy example, where the statement in try block is executed and catch block skiped...can+t imagine such example now...

Many thanks!!!
Go to Top of Page

whitepear
Starting Member

24 Posts

Posted - 2014-04-21 : 05:20:00
Hello!
Strange error near "try"???


BEGIN TRANSACTION;

drop table if exists p;
drop table if exists n;

create table p(
idp integer primary key,
pname text
);

create table n(
nn text primary key
);

commit;

begin transaction;
insert into p values(1,'aa');
insert into p values(2,'bb');
insert into p values(3,'cc');
commit;

begin transaction;
insert into n values('');
commit;

select * from p;
select * from n;



begin;
savepoint sp1;
INSERT INTO P VALUES(4,'DD');
INSERT INTO P VALUES(7,'EE');
commit;

begin;
select * from p;

begin try
idp > 6
end try

begin catch
rollback to sp1
print 'raise_application_error(-20100, SQLERRM)'
END catch;


Output:
Executing the program....
$sqlite3 database.sdb < main.sql 2>&1

1|aa
2|bb
3|cc

1|aa
2|bb
3|cc
4|DD
7|EE
Error: near line 41: near "try": syntax error


Please, any ideas???
Many thanks!




Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2014-04-21 : 06:07:34
idp > 6
isn't a valid statement.

I suspect you need to move the begin try statement to the top. A try block just traps error, if you get an error it jumps to the catch block.

See
http://www.nigelrivett.net/Products/DWBuilder/TraceTable.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
   

- Advertisement -