Execute Multiple DML on single java.sql.Statement

Question

How transactions will behave; If I use following syntax to execute multiple DML using single java.sql.Statement

String sqlStr = "INSERT INTO.... \n update t1 set....";

statemet.execute(sqlStr);

I am not altering default connection setting (autocommit = true).

I am aware this is not the right way / best practice; just curious to know the transactional nature of above code That is if update fails with SQLException, will insert rollback?

skaffman · Accepted Answer · 2010-11-09 23:37:52Z

1

With autocommit set to true, then the individual statements would very likely execute in their own individual transactions. This might depends on the database, but I suspect not.

Best way to find out is to try it.

answered Nov 9, 2010 at 23:37

skaffman

405k96 gold badges825 silver badges775 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dhananjay · Accepted Answer · 2010-11-10 11:01:19Z

0

Tried out, got some surprising results with MS SQL sever 2008

I tried 2 update statements and as per observation they run in a single transaction; that is if second update fails first update statement is rolled back.

The surprising thing I noticed is :

https://connect.microsoft.com/SQLServer/feedback/details/620522/invalid-object-exception-not-thrown-by-com-microsoft-sqlserver-jdbc-sqlserverdriver#

answered Nov 10, 2010 at 11:01

Dhananjay

4,0052 gold badges33 silver badges45 bronze badges

1 Comment

Sean Reilly Over a year ago

In this sort of situation, you can make SQL Server roll back previous statements (or not) by using SET XACT_ABORT: msdn.microsoft.com/en-us/library/ms188792.aspx

Jaydeep_Ranipa · Accepted Answer · 2013-10-12 17:22:14Z

0

I tried in MySQL environment. It worked very well. And I think it is the basic way to do so.

answered Oct 12, 2013 at 17:22

Jaydeep_Ranipa

112 bronze badges

Collectives™ on Stack Overflow

Execute Multiple DML on single java.sql.Statement

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related