COMMIT and ROLLBACK with MS SQL Server

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Hello all

I have got a textfile that contains different datasets. With Matlab I read this textfile and I wanna save ALL the datasets in my MS SQL Database. So I was thinking of using Commit and Rollback to achieve this. I insert all the datasets into the database and if this is successful I use the commit command to do this permanently. If the program should crash during inserting the datasets I use the rollback function as soon as I have a connection to the database again. So I have another chance to write down the values in the dataset and I dont have to worry that I insert a dataset twice or that data is lost. Are my considerations right?

BTW: How can I turn off the auto commit of the MS SQL?

Thanks for your support

#2

Hi Patrick,

in your OCBC Connection setting you should find a checkbox for switching autocommit on and off.

If you are using Java, there is as method setAutocommit(boolean) in the connection object.

Then you have to put your statements within a begin - commit/rollback. If not, the statement will be autocommitted.

Regards

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#3

Thanks for your answer Peter. I have a final question
Lets say the SQL statement looks like this

BEGIN transaction
inseyrt into test bla
insert into test bla
insert into test bla
commit transaction

Okay if they program doesnt crash this is fine. But lets assume the program crashes after the second insert. So what is happening then? Do I have to make a rollback when I restart the program? Or are the first two inserts „commited“ to the database although i didnt call the command commit?

THanks a bunch for your help

Hi Patrick,

in your OCBC Connection setting you should find a checkbox for
switching autocommit on and off.

If you are using Java, there is as method
setAutocommit(boolean) in the connection object.

Then you have to put your statements within a begin -
commit/rollback. If not, the statement will be autocommitted.

Regards

Peter

#4

Hi Patrick,

transactions are committet completely or rollbacked completely. A system fail will cause a rollback. Therefore, no INSERT should be processed or all of them.

Regards

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#5

transactions are committet completely or rollbacked
completely. A system fail will cause a rollback. Therefore, no
INSERT should be processed or all of them.

And how can i make sure that a rollback is executed if the database connection is closed without a commit? I have to make sure that the transaction is completly rollbacked!

#6

Hi,

thats why you use a real database. The DB is doing this for you.
Any statement after the begin is rollbacked. Always!

Regards

Peter

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]