Re: Execute batch process for Update statement

[Jasmin_Mehta]

The execute batch is not working for me.

Here is the sql-map config

<update id="updateStatus" parameterClass="java.util.Map">
      UPDATE OTM_STAGING SET STATUS = #status#
      <dynamic prepend="where">
        <iterate property="sequenceList">
            SEQUENCE = #sequenceList[]#
        </iterate>
      </dynamic>
  </update>

Here is the SqlMapClient's code


    public static int updateStagingTableStatus(ArrayList sequences, String 
newStatus)
                    throws SQLException 
    {
        int totalUpdatedStatus = 0;
        Map parameterMap = new HashMap();
        try 
        {
             sqlMapper.startTransaction();
             parameterMap.put("sequenceList", sequences);
             parameterMap.put("status", newStatus);
             sqlMapper.update("updateStatus", parameterMap);
             totalUpdatedStatus = sqlMapper.executeBatch();
             sqlMapper.commitTransaction();
        }
        catch(SQLException sqlEx)
        { 
             logger.error("SQLException " +sqlEx); 
        }
        finally 
        {
            try 
            {
                sqlMapper.endTransaction ();
            }
            catch(SQLException sqlEx)
            {
                logger.error("SQLException " + sqlEx);
            } 
        }
        return totalUpdatedStatus;
    }
 


Here is the method call:


         logger.debug("Update staging table");
         ArrayList tempSeq = new ArrayList();
         tempSeq.add(new Integer(1));
         tempSeq.add(new Integer(3));
         int updateCount = XmlService.updateStagingTableStatus(tempSeq, 
"test_status");
         logger.info("updateCount in server "+updateCount);

Here is the debug log:

23 Jan 2008 10:57:31] DEBUG [Connection] {conn-100003} Connection
[23 Jan 2008 10:57:31] DEBUG [Connection] {conn-100003} Preparing 
Statement:        UPDATE OTM_STAGING SET STATUS = ?       where  SEQUENCE 
= ?                      SEQUENCE = ? 
[23 Jan 2008 10:57:31] DEBUG [PreparedStatement] {pstm-100004} Executing 
Statement:        UPDATE OTM_STAGING SET STATUS = ?       where  SEQUENCE 
= ?                      SEQUENCE = ? 
[23 Jan 2008 10:57:31] DEBUG [PreparedStatement] {pstm-100004} Parameters: 
[test_status, 1, 3]
[23 Jan 2008 10:57:31] DEBUG [PreparedStatement] {pstm-100004} Types: 
[java.lang.String, java.lang.Integer, java.lang.Integer]
[23 Jan 2008 10:57:31] ERROR [RmsToOtmXmlBuilder] SQLException 
com.ibatis.common.jdbc.exception.NestedSQLException: 
--- The error occurred while applying a parameter map. 
--- Check the updateStatus-InlineParameterMap. 
--- Check the statement (update failed). 
--- Cause: java.sql.SQLException: ORA-00933: SQL command not properly 
ended


===========================================

What should I fix in the code which gives me something like:


 PreparedStatement prepStmt = con.prepareStatement( "UPDATE OTM_STAGING 
SET STATUS = ? WHERE SEQUENCE = ?");           
for (int i = 0; i < sequenceList.size(); i++)
{
    prepStmt.setString(1, "updatedStatus");
    prepStmt.setInt(2,  sequenceList[i]);
    prepStmt.addBatch(); 
}
int [] numUpdates=prepStmt.executeBatch();             


Thanks
Jasmin




"Chetan Nayak" <[EMAIL PROTECTED]> 
01/23/2008 09:14 AM
Please respond to
user-java@ibatis.apache.org


To
user-java@ibatis.apache.org
cc

Subject
Re: Execute batch process for Update statement






I think u need to use <iterate> for this query ,take a look at IBATIS 
developer guide ,they have examples on this .
 
Thanks
Chetan


 
On 1/23/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: 

Jay, 

I saw your posting in iBATIS forum with subject line "iBatis batch 
executing same performance as Jdbc - Thanks!" 

I am using iBATIS 2.3.0 and do not have any DAO (no Spring). My 
requirement is to run execute batch update for prepared statement : 

update otm_staging set status = 'Processed' where sequence = ? 

Where 'sequence' will get values from array of integer like {1, 2, 3, 5, 
7, 9, 11, 12, 14 ........ } 

How should I configure my sqlMap resource xml file for the query? Can  I 
still use your method 

'private Object batch(SqlMapClient sqlMap, int batchType, String 
methodName, Object argument)' 

from the posting ? 

thanks 
jasmin 
******************************************************************************

ATTENTION ATTENTION ATTENTION ATTENTION ATTENTION 
Our domain name is changing.  Please take note of the sender's
e-Mail address and make changes to your personal address list,
if needed.  Both domains will continue to work, only for a limited
time. 
******************************************************************************
This email and any files transmitted with it are intended solely for 
the use of the individual or agency to whom they are addressed. 
If you have received this email in error please notify the Navy 
Exchange Service Command e-mail administrator. This footnote 
also confirms that this email message has been scanned for the
presence of computer viruses. 
Thank You!            
******************************************************************************