Issues with Batch updates using JDBC

Hi ,
i am quite new to this forum so kindly excuse any errors frm my side

Actually i have a requirement where in i need to update a large number of row ( abt 2000- 5000) in a single strech, hence i was trying to use the Batch Update facility provided by JDBC 2.0 but has met with limited success so far

My code is some thing like this

conn.setAutoCommit(false);

for ( Iterating over an array of 104 items)
{

String queryForBatch="UPDATE TABLE A SET "

+ " GROUP = '"+group +"', "
+" ITEM ='"+item +"', "
+" ITEM_EXT1 = '"+item_ext1 +"', "
+" MAXIMUM_NEED ='"+maximum_need +"', "
+" USAGE_GUIDE_NUM = '"+usage_guide_num +"', "
+" BASE ='"+base +"', "
+" SUPPL = '"+suppl +"', "
+" MEDICARE ='"+medicare +"', "
+" MEDICAL ='"+medical +"', "
+" HOSPICE ='"+hospice +"', "
+" GUIDE = '" + guide +"', "
+" REVIEW = '"+ review +"', "
+" ADDL_DOCUMENTS ='"+addl_documents +"', "
+" USAGE_GUIDE = '"+usage_guide +"', "
+" UNIT = '"+unit +"', ";
if(contract_eff_dt!=null)
{
queryForBatch=queryForBatch+"
CNTR_EFF_DT ='"+contract_eff_dt +"', ";
}
if (contract_end_dt!=null)
{
queryForBatch=queryForBatch+"
CNTR_END_DT ='"+contract_end_dt +"', ";
}

queryForBatch=queryForBatch+" ITM_ACTVTY_CD
='"+itemActiveInd+"'"
+" WHERE CODE ='" +code+"'";

stmt.addBatch(queryForBatch);
queryCount++;

}

if(queryCount>0)
{
int [] updateCounts = stmt.executeBatch();
conn.commit();
stmt.clearBatch();
}

However on executing this i get the following error for about 104 records

updateTableDetails() : Going to execute the update batch

4055840 [Servlet.Engine.Transports : 0] ERROR org.kp.dme.uploadformulary.UploadFormularyDAO - SQLException:>>>>>>>> [IBM][CLI Driver] CLI0118E Invalid SQL syntax. SQLSTATE=37000

is this error because of some of the values are greater than the length of the table column ??

However, I had logged all the 104 statements and executed them in a single shot using the Command Center Utility in DB2 UDB v7.0 and all the 104 went thru just fine

can anyone help me out here with what is wrong here

also is there an upper limit on the number of queries that can be added/executed using the batchUpdate feature?

Any help will be grateful

Regards
Abraham

Hi
I have no idea why ur batch statement is failing. But i would like to suggest you one more way to execute the same. Create a prepared statement, then pass the values in the for loop and hit execute per row. This will be really fast then the Batch.
I would request you to give a try.
Let me know if you feel any concerns.

Regards
Makarand Parab

Hi ,
actually the initial design was just the way u had suggested i.e creating a prepared statement and executing it for each record in the arraylist. However it seemed to be taking quite a long time esp with a larger number of records and that is why i was looking at using batch update functionality

but the error returned is quite strange even when i am able to manually execute each statement individually.

Any other possible reasons of failure for this method
also any upper limit on the number of records that can be updated in one shot using batch update?

from the error it looks like some problem with the sql syntax.
Are you sure that the SQL syntax is correct if it is so then tru adding a ;(semicolon) at the end of the statement which you would be submitting to the DB for execution (i.e) you try modifying
+" WHERE CODE ='" +code+"'";
to
+" WHERE CODE ='" +code+"';";