It doesn't look to me like you really want 'where status ='. Certainly the
generated SQL doesn't make any sense. Try something like this:
<update id="updateStatus" parameterClass="java.util.Map">
UPDATE OTM_STAGING SET STATUS = #status#
<dynamic prepend="where SEQUENCE in">
<iterate open="(" close=")" property="sequenceList"
conjunction=",">
SEQUENCE = #sequenceList[]#
</iterate>
</dynamic>
</update>
I've got something similar to that which is working fine for me with an
Oracle DB. It should produce a statement like ' UPDATE OTM_STAGING SET
STATUS = ? where SEQUENCE in (1,3)'
Dave
We must begin not just to act, but to think, for there is no better slave
than the one who believes his slavery to be freedom, and we are in
no greater peril than when we cannot see the chains on our minds
because there are yet no chains on our feet.
-- Michael Reid
[EMAIL PROTECTED]
eb.org
To
01/23/2008 11:15 user-java@ibatis.apache.org
AM cc
Subject
Please respond to Re: Execute batch process for
[EMAIL PROTECTED] Update statement
apache.org
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]>
To
user-java@ibatis.apache.org
01/23/2008 09:14 AM cc
Subject
Please respond to Re: Execute batch process
user-java@ibatis.apache.org 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!
******************************************************************************
