Forum Stats

  • 3,782,266 Users
  • 2,254,631 Discussions
  • 7,880,033 Comments

Discussions

Categories

do we have to say connection.commit explicitly after prepared statement execution

Murray9654
Murray9654 Member Posts: 486
edited Jul 28, 2015 2:58PM in Java Programming

Hi i am inserting a record using database connection. I am obtaining connection from datasource. After inserting a record using preparedStatement.executeUpdate() do i have to explicitly write connection.commit() ?

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • Unknown
    Unknown
    edited Jul 28, 2015 2:58PM Accepted Answer 
    ii have obtained connection from datasource and executing update using prepared statement.i am not setting auto commit to false or true. i am just obtaining connection from data source and doing an insertion of record using prepared statement. I have hosted this app in the cloud. when i dont write connection.commit(), the transaction is getting rolled back. what could be the reason?  is commit mandatory for prepared statement?

    I already answered that question and provided a link to the documentation that discusses the ENTIRE TOPIC of commit.

    RTFM - it explains ALL of the places that the autocommit property can be set and what the default value is.

    If you get a rollback then it is because autocommit is set to false and you are NOT issuing a COMMIT.

    That is the reason.

    The code that creates the 'datasource' and the connections might be setting autocommit to FALSE.

    · Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Jul 28, 2015 1:51PM

    Yes - if you have autocommit set to FALSE.

    No - if you have autocommit set to TRUE.

    See the JDBC Developer Guide

    http://docs.oracle.com/cd/E11882_01/java.112/e16548/apxtips.htm#sthref980

    Disabling Auto-Commit Mode

    Auto-commit mode indicates to the database whether to issue an automatic COMMIT operation after every SQL operation. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables.
By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with the setAutoCommit method of the connection object, either java.sql.Conection or oracle.jdbc.OracleConnection.
In auto-commit mode, the COMMIT operation occurs either when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet object, the statement completes when the last row of the Result Set has been retrieved or when the Result Set has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, the COMMIT occurs when all results and output parameter values have been retrieved.
If you disable auto-commit mode with a setAutoCommit(false) call, then you must manually commit or roll back groups of operations using the commit or rollback method of the connection object.
    Murray9654
    · Share on TwitterShare on Facebook
  • morgalr
    morgalr Member Posts: 457
    edited Jul 28, 2015 1:49PM

    depends:

    https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

    · Share on TwitterShare on Facebook
  • Murray9654
    Murray9654 Member Posts: 486
    edited Jul 28, 2015 1:55PM

    ii have obtained connection from datasource and executing update using prepared statement.i am not setting auto commit to false or true. i am just obtaining connection from data source and doing an insertion of record using prepared statement. I have hosted this app in the cloud. when i dont write connection.commit(), the transaction is getting rolled back. what could be the reason?  is commit mandatory for prepared statement?

    · Share on TwitterShare on Facebook
  • Nagarjun_NM
    Nagarjun_NM Member Posts: 15
    edited Jul 28, 2015 2:09PM

    Hello murray,

         The Oracle JDBC driver, by default enables "AUTO COMMIT" on a connection. This might create problems, especially when calling a database store procedure in oracle which leverages global temporary tables. Having set the AUTO COMMIT by default, the data in temporary tables will be deleted after any data manipulation statement(INSERT, DELETE, UPDATE). To avoid this kind of scenario its better to disable AUTO COMMIT the JDBC connection explicitly.

    · Share on TwitterShare on Facebook
  • Murray9654
    Murray9654 Member Posts: 486
    edited Jul 28, 2015 2:14PM

    My database is not oracle. my database is mysql. I have replied explaining my problem in response to "rp0428". Plase read that once.

    · Share on TwitterShare on Facebook
  • Nagarjun_NM
    Nagarjun_NM Member Posts: 15
    edited Jul 28, 2015 2:24PM

    Its a preferred approach to disable auto commit at first and making it auto-commit after the transaction is successful.

    Murray9654
    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Jul 28, 2015 2:58PM Accepted Answer 
    ii have obtained connection from datasource and executing update using prepared statement.i am not setting auto commit to false or true. i am just obtaining connection from data source and doing an insertion of record using prepared statement. I have hosted this app in the cloud. when i dont write connection.commit(), the transaction is getting rolled back. what could be the reason?  is commit mandatory for prepared statement?

    I already answered that question and provided a link to the documentation that discusses the ENTIRE TOPIC of commit.

    RTFM - it explains ALL of the places that the autocommit property can be set and what the default value is.

    If you get a rollback then it is because autocommit is set to false and you are NOT issuing a COMMIT.

    That is the reason.

    The code that creates the 'datasource' and the connections might be setting autocommit to FALSE.

    · Share on TwitterShare on Facebook
This discussion has been closed.