• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Batch insertion problem

 
Mario Cageggi
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Mario Cageggi
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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

Mario
 
Samuel March
Ranch Hand
Posts: 39
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Mario Cageggi
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic