aspose file tools*
The moose likes JDBC and the fly likes prepared statement in for loop Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "prepared statement in for loop" Watch "prepared statement in for loop" New topic
Author

prepared statement in for loop

Deeps Mistry
Ranch Hand

Joined: Jan 31, 2009
Posts: 189
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

Joined: Oct 13, 2005
Posts: 39828
    
  28
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

Joined: Mar 20, 2007
Posts: 317
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

Joined: Jan 31, 2009
Posts: 189
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

Joined: Jan 31, 2009
Posts: 189
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

Joined: May 26, 2003
Posts: 30924
    
158

Can you show the code for addBatch() ?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Stop using the ODBC bridge driver.

Use the JDBC driver which comes along with the RDBMS in question.
Deeps Mistry
Ranch Hand

Joined: Jan 31, 2009
Posts: 189
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

Joined: Jan 31, 2009
Posts: 189
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

Joined: Oct 08, 2006
Posts: 2458
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

Joined: Jan 31, 2009
Posts: 189
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

Joined: Oct 08, 2006
Posts: 2458
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

Joined: Jan 31, 2009
Posts: 189
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

Joined: Jan 31, 2009
Posts: 189
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.


 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: prepared statement in for loop