CODEWITHSUNDEEP
sqlsql-serverdatabasetransactionsfirebird
Mason Wheeler
Mason Wheeler

Reputation: 84550

How do I use transactions in Firebird?

In MS SQL Server, I can easily put multiple insert statements into a transaction, like so:

begin tran
insert into mytable values (1, 2, 3)
insert into mytable values (4, 5, 6)
commit tran

I'm trying to do the same thing in Firebird, but I can't figure out the syntax. Googling for "Firebird transaction syntax" returns nothing useful. I've found enough to know that transaction support exists, but no examples for how to use it right.

So I figure I may as well ask on here. Does anyone know how to write a transaction using multiple inserts for a Firebird database?

Upvotes: 8

Views: 24974

Answers (4)

Amy
Amy

Reputation: 35

If Firebird there is no need for extra syntax beyond

commit;

Below I provide screenshot from Firebird session showing how it works.

scrrenshot of isql

Upvotes: 0

Fr0sT
Fr0sT

Reputation: 3025

Since FB 2.5 it's possible to start a new transaction from inside the current one.

IN AUTONOMOUS TRANSACTION
DO
  < simple statement | compound statement >

http://www.firebirdsql.org/rlsnotesh/rlsnotes25.html#rnfb25-psql-auton

Upvotes: 4

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

Complementing @Allan's answer (which I upvoted, BTW), here's some more information.

When you do begin tran in SQL Server, it does not mean that you're starting the transaction now. You are already in transaction, since you are connected to the database! What begin tran really does is disable the "auto-commit at each statement", which is the default state in SQL Server (unless otherwise specified).

Respectively, commit tran commits and reverts the connection to "auto-commit at each statement" state.

In any database, when you are connected, you are already in transaction. This is how databases are. For instance, in Firebird, you can perform a commit or rollback even if only ran a query.

Some databases and connection libs, in the other hand, let you use the "auto-commit at each statement" state of connection, which is what SQL Server is doing. As useful as that feature might be, it's not very didactic and lead beginners to think they are "not in a transaction".

Upvotes: 8

Allan
Allan

Reputation: 17429

Firebird always uses transactions. The transaction is started as soon as you make a change in the database and remains open for that session until you commit. Using your code, it's simply:

insert into mytable values (1, 2, 3);
insert into mytable values (4, 5, 6);
commit;

Upvotes: 6

Related Questions