permaculture playing cards*
The moose likes JDBC and the fly likes Batch insertion problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Batch insertion problem" Watch "Batch insertion problem" New topic
Author

Batch insertion problem

Mario Cageggi
Greenhorn

Joined: Apr 22, 2009
Posts: 8
Hi folks!

I try to execute such an insert batch with the following code:
PreparedStatement pStmt = this.connection.prepareStatement("INSERT INTO person_contact_queue " +
" ( x, y, z, k, w ) " +
" VALUES (?,?,?,?,?);");
try{
this.connection.setAutoCommit(false);
for (PersonContactQueue person:people){

pStmt.setLong(1,x1);
pStmt.setLong(2, y1);
pStmt.setString(3, z1);
pStmt.setString(4, k1);
pStmt.setLong(5, w1);

pStmt.addBatch();
}
pStmt.executeBatch();

this.connection.commit();

}catch(Exception e){
e.printStackTrace();
this.connection.rollback();
}

Well, when I execute this code in my PC (connected to the same db) everything run smoothly;
when I execute it in the production environment, the insertion is done correctly, but at the following db access I get this error:

org.postgresql.util.PSQLException: ERROR: VACUUM cannot run inside a transaction block

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
at com.ecglobalpanel.dao.PersonContactQueueDAO.executeVacuum(PersonContactQueueDAO.java:29)
at com.ecglobalpanel.controller.ReserveSampleBatch.doGet(ReserveSampleBatch.java:108)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) (etc...)

or:

org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:233)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at com.ecglobalpanel.dao.ProjectTypeDAO.getProjectType(ProjectTypeDAO.java:44)
at com.ecglobalpanel.controller.GetProject.doGet(GetProject.java:46)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689) (etc...)

As far as I can read, it looks like I do not succeed to end the transaction block in the production server... I have no idea how to do it...

What do you think?
Please HEEEELPPPPP!!!

Cheers,
Mario
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2497
    
    8

What happens if you restore the autocommit setting after commit / rollback?
You changed the behavior of your connection in this piece of code, and did not revert it in it's original state. Maybe your other code expects that autocommit is true?

(and if possible, please prepare your statement in your try block)

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Mario Cageggi
Greenhorn

Joined: Apr 22, 2009
Posts: 8

I am testing right that... I will let you know!
and thanks anyway!

Mario
Samuel March
Ranch Hand

Joined: Oct 28, 2009
Posts: 39
Could you have been firing that as "not a Transaction" by setting in your development machine so it was transparent to seeing it as a transaction during the test?


...did you have the fish?!............................ No.
Mario Cageggi
Greenhorn

Joined: Apr 22, 2009
Posts: 8
I restored the autocommit true after the batch insertion and now it looks like it is working fine!

Thanks you all a lot guys! Always helpful!
Mario
 
Consider Paul's rocket mass heater.
 
subject: Batch insertion problem