Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

prepared statement in for loop

 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,
I am inserting data into a table. For this i am using prepared statement in a for loop.

I am able to insert the 1st row into the database, but while inserting 2nd row it is throwing a sql "General Error" exception.



Please help me out.

[edited to add code tags]
 
Campbell Ritchie
Sheriff
Posts: 48652
56
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't know. But you are making things difficult by not using the code button and by not quoting the details, including the stack trace. You can get a message, error code and SQL code from an SQLException; you need to get those details and look in the instructions for your database program what they mean.
 
Sridhar Santhanakrishnan
Ranch Hand
Posts: 317
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Giving more details about the exception would be helpful.

One more idea would be to add the queries into a batch using addBatch() and then use executeBatch once all the rows have been added.
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sridhar Santhanakrishnan wrote:Giving more details about the exception would be helpful.

One more idea would be to add the queries into a batch using addBatch() and then use executeBatch once all the rows have been added.


Hey,
I tried using addBatch() functionality, still giving me the same error.

I have caught an SQLException and printing e.getMeassage() on screen. The error message i am getting here is SQL general error. How else do i catch the exception so that i get more details about the same.

Thanks
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sridhar Santhanakrishnan wrote:Giving more details about the exception would be helpful.

One more idea would be to add the queries into a batch using addBatch() and then use executeBatch once all the rows have been added.


Hey using addBatch() i am getting the following SQL Exception:

sun.jdbc.odbc.JdbcOdbcBatchUpdateException: General error
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)
at ActionClass.AddPOAction.savePO(AddPOAction.java:176)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Exception in UPDATEEEEEEE: General error


Using normal update method i am getting the following SQl Exception:

java.sql.SQLException: General error
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)
at ActionClass.AddPOAction.savePO(AddPOAction.java:173)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Exception in UPDATEEEEEEE: General error

Please help me out

Thanks
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34195
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show the code for addBatch() ?
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stop using the ODBC bridge driver.

Use the JDBC driver which comes along with the RDBMS in question.
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:Can you show the code for addBatch() ?


I am using MS Access.

sun.jdbc.odbc.ee.DataSource ds = new sun.jdbc.odbc.ee.DataSource();
ds.setDatabaseName("PEWFACE");

// Getting a connection object
con = ds.getConnection();

strB2B = (String)poForm.get("POB2B");
strTaxRate =(String)poForm.get("POTX");
strPONO = (String)poForm.get("PONO");
String strQuery1 = "Insert into PurchaseOrder values (?,?,?,?,?,?,?,?)";

pst1 = con.prepareStatement(strQuery1);


for (int i = 0; i < lines.length; i++) {
System.out.println("inside for");

if (!nullOrBlank(lines[i].getPOD()))
{
System.out.println("inside if");


pst1.setString(1, "123");
pst1.setString(2, strPONO);
pst1.setString(3, lines[i].getPOD());
pst1.setString(4, lines[i].getPON());
pst1.setString(5, lines[i].getPOQ());
pst1.setString(6, lines[i].getUOM1());
pst1.setString(7, strB2B);
pst1.setString(8,strTaxRate);

pst1.addBatch();


}
int[] no = pst1.executeBatch();
System.out.println("rows updated:"+no.toString());
}
}

catch (SQLException objSQLException) {
objSQLException.printStackTrace();
System.err.println("Exception in UPDATEEEEEEE: "+objSQLException.getMessage());

}


Am i doing anything wrong?
Please help me out!!!

Thanks
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bauke Scholtz wrote:Stop using the ODBC bridge driver.

Use the JDBC driver which comes along with the RDBMS in question.


Hi,

How do i go about replacing ODBC with JDBC?
Do i need to download JDBC driver(which version?) for MS Access ?

I have googled on this but no concrete result to be found.

Please throw more light on this.

Thanks
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh, more trouble, you're using MS Access. This isn't a real RDBMS. You're using the worst of the both worlds (MS Access with ODBC).

If you can, please immediately drop it and replace it by a real RDBMS, such as MySQL, PostgreSQL, etc. If you're looking for a portable database, then look for an embedded RDBMS in Java. Examples are JavaDB (included since JDK6), Derby (its predecesor) or Hypersonic.

If you're ultimately sticked to MS Access, then live with the fact that you can't use the full RDBMS capabilities out of this. Whenever you got this kind of problems, accept it as a limitation of MS Access. Instead of effectively reusing PreparedStatement in a loop, you should ineffectively redo the complete job (connect, preparestatement, execute, commit, close) for every row. This will work for MS Access.
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bauke Scholtz wrote:Oh, more trouble, you're using MS Access. This isn't a real RDBMS. You're using the worst of the both worlds (MS Access with ODBC).

If you can, please immediately drop it and replace it by a real RDBMS, such as MySQL, PostgreSQL, etc. If you're looking for a portable database, then look for an embedded RDBMS in Java. Examples are JavaDB (included since JDK6), Derby (its predecesor) or Hypersonic.

If you're ultimately sticked to MS Access, then live with the fact that you can't use the full RDBMS capabilities out of this. Whenever you got this kind of problems, accept it as a limitation of MS Access. Instead of effectively reusing PreparedStatement in a loop, you should ineffectively redo the complete job (connect, preparestatement, execute, commit, close) for every row. This will work for MS Access.


Hey,

I am developing this application at home. Hence the decision to use MS Access. Can i download any of the real RDBMS for free? ( )

Also, if the downloading is not for free i need to do the following( correct me if i am wrong) :

I need to create a prepared statement in for loop

String strQuery1 = "Insert into PurchaseOrder values (?,?,?,?,?,?,?,?)";

for (int i = 0; i < lines.length; i++) {
System.out.println("inside for");

if (!nullOrBlank(lines[i].getPOD()))
{
System.out.println("inside if");

pst1 = new PreparedStatement();
pst1 = con.prepareStatement(strQuery1);
pst1.setString(1, "123");
pst1.setString(2, strPONO);
pst1.setString(3, lines[i].getPOD());
pst1.setString(4, lines[i].getPON());
pst1.setString(5, lines[i].getPOQ());
pst1.setString(6, lines[i].getUOM1());
pst1.setString(7, strB2B);
pst1.setString(8,strTaxRate);

pst1.executeQuery();


}

}
}

catch (SQLException objSQLException) {
objSQLException.printStackTrace();
System.err.println("Exception in UPDATEEEEEEE: "+objSQLException.getMessage());

}

Any other way to implement this functionality?

Thanks



 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Deeps Mistry wrote:
I am developing this application at home. Hence the decision to use MS Access. Can i download any of the real RDBMS for free? ( )

Just visit their homepage for the product details and you'll quickly see if it's free or not?

At least the aforementioned RDBMS's are just free.
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bauke Scholtz wrote:
Deeps Mistry wrote:
I am developing this application at home. Hence the decision to use MS Access. Can i download any of the real RDBMS for free? ( )

Just visit their homepage for the product details and you'll quickly see if it's free or not?

At least the aforementioned RDBMS's are just free.


Hey,

I have downloaded Java DB on my PC. I have also inserted the tables in the DB.

Problem Statement: Every time i want to connect to JAVA DB database i need to start the network server. So :

1) To run my application i need to start my tomcat server and Java DB server?
2) Also, in future, if i want to deploy it on other machine, i need to set up an altogether new Java DB on that machine?

Please help me out!!

Thanks
 
Deeps Mistry
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bauke Scholtz wrote:
Deeps Mistry wrote:
I am developing this application at home. Hence the decision to use MS Access. Can i download any of the real RDBMS for free? ( )

Just visit their homepage for the product details and you'll quickly see if it's free or not?

At least the aforementioned RDBMS's are just free.


Hey,

My problem is solved
Actually the problem was not with MS Access but a primary key violation.

Thanks for your help.


 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic