SQL PreparedStatement & autocommit

Question

If I create a preparedstatement using a JDBC connection which has "autocommit off", do I need to call commit() to make the transaction permanent or only the prepare call is enough? Note that I only want to prepare a statement and keep it for later execution.

Many thanks!

Manson

Answer 1

The answer is: yes you have to call commit.

The "prepare" step does not do anything that is relevant for transaction handling on the server.

You have to call executeUpdate() (or execute()) to start a transaction (assuming the used SQL does in fact start a transaction)

Using a PreparedStatement without auto-commit requires the following steps:

1. Prepare the statement
2. Set the parameter values
3. call executeUpdate() (or execute() depending on the type of statement)
4. call Connection.commit()

To make things clearer: calling prepareStatement() does not require a commit().

Gazette

Actually, I have several CRUD SQL statements and I want to have them ready as preparedstatements when I initialize the connection objects (of connectionpool). Later on my application can execute those preparedstatements by setting appropriate parameters and certainly calling commit() when needed. So I wanted to know if I need to call commit after just the prepare call but if I understand you correctly, preparing doesn't do anything on the server until I call execute? – Manson

@AliM: That doesn't change anything regarding the need to call executeUpdate() and commit() – Gazette

did you get the last part of my previous post? so you answer still holds true for that? – Manson

@AliM: which "previous post"? Preparing a statement does not require a commit. – Gazette

no problem.. I got the answer! To make things clearer: calling prepareStatement() does not require a commit(). – Manson

Actually, a lot of the time a prepare does do things on the server (eg parse the statement and return type info to the client), just not things that need to be committed – Michel

@MarkRotteveel: yes, you are absolutely correct. I just wanted to keep this simple for the sake of the discussion. – Gazette

So, why do we need to commit if execute actually executes the query? Thanks – Fibro

While I am updating the database with the help of prepared statement, expecting to commit though I have not set auto-commit to false. This is happening for postgresql. Any reason for this? – Cherin