commit and rollback problem

Hi,

I still haven't got a decent book on relational databases :-)

My stored procedure insert_wire inserts values into two tables (wire and
cablewire). The wire_ref (primary key) will be the same for both inserts.
However, if for any reason the first insert fails then I would like a
rollback system to take place. I have tried testing for an error (@.@.error
<> 0) after the 1st transaction but I just get a syntax error. Am I going
down the right lines here? Any tips appreciated.

Thanks, Mary.

CREATE procedure insert_wire(in wire_ref VARCHAR(22), in standard
VARCHAR(16), in a_color VARCHAR(16), in material VARCHAR(22),
in metres INTEGER, in amps FLOAT(3), in volts FLOAT(3), in ni SMALLINT, in
some_comment VARCHAR(32))
BEGIN
insert into cablewire
values(wire_ref, standard, a_color, material, metres, some_comment);
insert into wire
values(wire_ref, amps, volts, ni);
commit;
END!Mary Walker (123@.123.com) writes:
> I still haven't got a decent book on relational databases :-)
> My stored procedure insert_wire inserts values into two tables (wire and
> cablewire). The wire_ref (primary key) will be the same for both inserts.
> However, if for any reason the first insert fails then I would like a
> rollback system to take place. I have tried testing for an error (@.@.error
><> 0) after the 1st transaction but I just get a syntax error. Am I going
> down the right lines here? Any tips appreciated.

Probably not. Judging from the syntax in your posts, you are using
some other DB engine than Microsoft SQL Server, which is the RDBMS
this group is about. @.@.error, on the other hand is a feature in
MS SQL Server, that I would expect not appear anywhere else, except
for Sybase.

So I think you should first out what product you are using, and then
a forum for that product.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Commit and Rollback on a batch

Hi!

I am using VB.NET 2005 and/or SQL Server 2005 studio manager.

I have a string of about 20 or 30 inserts updates and deletes. I want to process all or nothing. If there is an error that prevents a single transaction from completeing, i want to roll back the entire batch.

One solution i read is to test fot @.@.error after each statement. This is not desirable as i receive the batch as a single string already made. I would have to separate all the statements and insert the error testing myself.

I would prefere to simply execute the batch as an all or nothing batch.

Certainly this is a common request. But the only solutions i can find involve extensive re-working of the source batch of transactions. I may have up to 100 statements in my batch.

Any Ideas?

Thank you

Jerry Cicierega

using System.Transactions;

...

using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection con = new SqlConnection())
{
using (SqlCommand cmd = new SqlCommand())
{
// Do stuff with your cmd object here such as running the aforementioned batch statements
}
}
ts.Complete()
}

If an error occurs during the processing, the whole thing will be rolled back. Of course, fill in the stuff you need for the connection and command objects in the using statements.