prepared statement in for loop

[Deeps Mistry]

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.

try
		{
			//AddPOForm objAddPOForm = (AddPOForm)form;
			
			//System.out.println("CUST NAME::"+objAddPOForm.getSelected());
			strCustName = (String)poForm.get("selected");
			System.out.println("CUST NAME:"+strCustName);
			AddPOBean[] lines = (AddPOBean[]) poForm.get("lines");

			// Setting up the DataSource object
		      sun.jdbc.odbc.ee.DataSource ds 
		        = new sun.jdbc.odbc.ee.DataSource();
		      ds.setDatabaseName("PEWFACE");

		      // Getting a connection object
		      con = ds.getConnection();
//		      //Getting cust id for the corresponding cust name   
		      String strQuery = "Select CustID from CustomerInfo where CustName = ?";
		      pst = con.prepareStatement(strQuery);
		      pst.setString(1, strCustName);
		      rs = pst.executeQuery();
		      rs.next();
		      
		      String strCustID = rs.getString(1);
		      System.out.println("CUST ID::"+strCustID);
		      
		      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");
			    	   
//			    	   System.out.println((lines[i].getPOD()));
//			    	   System.out.println((lines[i].getPON()));
//			    	   System.out.println((lines[i].getPOQ()));
//			    	   System.out.println((lines[i].getUOM1()));
			    	   pst1.setString(1, strCustID);
					   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);
			    	   
		        	   int no = pst1.executeUpdate();
		        	   System.out.println("iteration no:"+i +"done and rrows updated:"+no);
		           }
		      }
		}

		catch (SQLException objSQLException) {
			System.err.println("Exception in UPDATEEEEEEE: "+objSQLException.getMessage());
			SQLException nextException = objSQLException.getNextException();  
			   while (nextException != null) {  
			       
			     // print out info on nextException  
			     nextException = nextException.getNextException();  
			     System.out.println(nextException);
			   }  
		}  
		catch (NumberFormatException ex) {
           }
	      catch(Exception e)
	      {
	    	  e.printStackTrace();
	      }
	      finally
	      {
	    	  pst.close();
	    	  pst1.close();
	    	  con.close();
	      }

Please help me out.

[edited to add code tags]

[Campbell Ritchie]

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]

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]

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]

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]

Can you show the code for addBatch() ?

[Bauke Scholtz]

Stop using the ODBC bridge driver.

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

[Deeps Mistry]

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]

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]

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]

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]

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]

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]

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.