Guessing here, but it almost smells like a transaction timing issue.
Are the sybase drivers the same for both iBATIS versions? (Or, I
wonder if some code in 2.0.4 is synchronized and 2.1.5 is not?)
As an aside, is there a reason you're not just doing an update vs.
select/delete/insert?
On 9/26/05, Shannon, Bryan <[EMAIL PROTECTED]> wrote:
> I am having a very unusual problem after upgrading from iBatis 2.0.4 to
> 2.1.5.
>
> It has to do with a particular table and a few particularly simple maps that
> interact with that table.
>
> The table is in Sybase, and it has a clustered index (which in Sybase
> doesn't allow duplicate ROWS, ie. two rows having the exact same content).
>
>
>
> Essentially, the flow looks like this:
>
> 1) start transaction.
> 2) Load all rows from this table by an id number.
> 3) Delete all rows in table for this id number. (clearing them out)
> 4) Insert all the rows back into the table.
> 5) commit transaction.
>
>
> With the older iBatis, it works fine; queries for the existing rows;
> deletes them, and then re-inserts them. But the new iBatis SOMETIMES fails
> doing the first INSERT. But Only SOMETIMES!!!
>
> Here is what happens in each version of iBatis:
>
>
> Now for this one table, 2.0.4 works just as you'd expect:
> (it does a select for all the values with id "1", then the delete, then the
> inserts for each row.)
>
> DEBUG JakartaCommonsLoggingImpl: {conn-100000} Connection
> DEBUG JakartaCommonsLoggingImpl: {pstm-100001} PreparedStatement: SELECT
> mvn_name_num as nameNum, mvn_movie_num as movieNum,
> mvn_name_code as nameCode, mvn_role_code as roleCode FROM
> movie_db..movie_name_t WHERE mvn_movie_num = ?
> DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Parameters: [1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Types: [java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} ResultSet
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Header: [nameNum, movieNum,
> nameCode, roleCode]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C001, 1, 1, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C002, 1, 2, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C003, 1, 5990, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C004, 1, 114781, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C005, 1, 114782, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C006, 1, 114783, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C007, 1, 6559, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C008, 1, 7635, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C009, 1, 9047, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C010, 1, 7273, 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100003} PreparedStatement: DELETE
> FROM movie_db..movie_name_t WHERE movie_db..movie_name_t.mvn_movie_num = ?
>
> DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Parameters: [1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Types: [java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100004} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100004} Parameters: [C001, 1, 1, 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100004} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100005} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100005} Parameters: [C002, 1, 2, 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100005} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100006} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100006} Parameters: [C003, 1, 5990,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100006} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100007} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100007} Parameters: [C004, 1, 114781,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100007} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100008} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100008} Parameters: [C005, 1, 114782,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100008} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100009} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100009} Parameters: [C006, 1, 114783,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100009} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100010} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100010} Parameters: [C007, 1, 6559,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100010} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100011} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100011} Parameters: [C008, 1, 7635,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100011} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100012} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100012} Parameters: [C009, 1, 9047,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100012} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100013} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100013} Parameters: [C010, 1, 7273,
> 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100013} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
>
>
>
>
> ---------------------------------------------------------------
>
>
> However, when I use 2.1.5 for this SAME EXACT logic, *sometimes* it fails
> immediately for the first INSERT statement after the DELETE, causing an
> Exception as below:
>
>
> DEBUG JakartaCommonsLoggingImpl: {conn-100000} Connection
> DEBUG JakartaCommonsLoggingImpl: {pstm-100001} PreparedStatement: SELECT
> mvn_name_num as nameNum, mvn_movie_num as movieNum,
> mvn_name_code as nameCode, mvn_role_code as roleCode FROM
> movie_db..movie_name_t WHERE mvn_movie_num = ?
> DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Parameters: [1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100001} Types: [java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} ResultSet
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Header: [nameNum, movieNum,
> nameCode, roleCode]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C001, 1, 1, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C002, 1, 2, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C003, 1, 5990, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C004, 1, 114781, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C005, 1, 114782, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C006, 1, 114783, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C007, 1, 6559, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C008, 1, 7635, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C009, 1, 9047, 1]
> DEBUG JakartaCommonsLoggingImpl: {rset-100002} Result: [C010, 1, 7273, 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100003} PreparedStatement: DELETE
> FROM movie_db..movie_name_t WHERE movie_db..movie_name_t.mvn_movie_num = ?
>
> DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Parameters: [1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100003} Types: [java.lang.Long]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100049} PreparedStatement: INSERT
> INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num, mvn_name_code,
> mvn_role_code) VALUES (?, ?, ?, ?)
> DEBUG JakartaCommonsLoggingImpl: {pstm-100049} Parameters: [C001, 1, 1, 1]
> DEBUG JakartaCommonsLoggingImpl: {pstm-100049} Types: [java.lang.String,
> java.lang.Long, java.lang.Long, java.lang.Long]
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in TestMap.xml.
> --- The error occurred while applying a parameter map.
> --- Check the doInsert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: com.sybase.jdbc3.jdbc.SybSQLException: Attempt to insert
> duplicate row in table 'movie_name_t' with index 'name_indx' in database
> 'movie_db'. Could drop and recreate index with ignore duprow or allow
> duprow.
>
> Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Attempt to insert
> duplicate row in table 'movie_name_t' with index 'name_indx' in database
> 'movie_db'. Could drop and recreate index with ignore duprow or allow
> duprow.
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:91)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.insert(SqlMapExecutorDe
> legate.java:442)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.insert(SqlMapSessionImpl.jav
> a:81)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.insert(SqlMapClientImpl.java:
> 58)
> at sqlmaptest.SqlMapTest.runTest(SqlMapTest.java:54)
> at sqlmaptest.SqlMapTest.<init>(SqlMapTest.java:32)
> at sqlmaptest.SqlMapTest.main(SqlMapTest.java:61)
> Caused by: com.sybase.jdbc3.jdbc.SybSQLException: Attempt to insert
> duplicate row in table 'movie_name_t' with index 'name_indx' in database
> 'movie_db'. Could drop and recreate index with ignore duprow or allow
> duprow.
>
> at com.sybase.jdbc3.tds.Tds.processEed(Tds.java:3069)
> at com.sybase.jdbc3.tds.Tds.nextResult(Tds.java:2373)
> at
> com.sybase.jdbc3.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
> at
> com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:220)
> at
> com.sybase.jdbc3.jdbc.SybStatement.nextResult(SybStatement.java:203)
> at
> com.sybase.jdbc3.jdbc.SybStatement.executeLoop(SybStatement.java:1875)
> at
> com.sybase.jdbc3.jdbc.SybStatement.execute(SybStatement.java:1867)
> at
> com.sybase.jdbc3.jdbc.SybPreparedStatement.execute(SybPreparedStatement.java
> :640)
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPrepar
> edStatement.java:256)
> at sun.reflect.GeneratedMethodAccessor11.invoke(Unknown Source)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl
> .java:25)
> at java.lang.reflect.Method.invoke(Method.java:324)
> at
> com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStat
> ementLogProxy.java:62)
> at $Proxy1.execute(Unknown Source)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.jav
> a:84)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate
> (GeneralStatement.java:200)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(Ge
> neralStatement.java:78)
> ... 6 more
>
>
>
>
>
>
> Now, If I rerun this, it might throw the same exact error message, or... it
> might work exactly like it should! I am positive that the dataset is
> exactly the same each run.
>
> The symptoms are as if 2.1.5 didn't ACTUALLY do the DELETE statement; this
> error message about the duplicate row only happens when that row isn't
> deleted. But it doesn't throw the error every time!
>
> When I use 2.0.4, it NEVER fails, even though I use the same exact Sybase
> Driver, same SQL map config file, same exact everything, except for the new
> ibatis jars.
>
> Here are the map statements:
>
> <statement id="doDelete" parameterClass="long">
> DELETE FROM movie_db..movie_name_t WHERE
> movie_db..movie_name_t.mvn_movie_num = #value#
> </statement>
>
>
> <statement id="doInsert" parameterClass="sqlmaptest.TestPerson">
> INSERT INTO movie_db..movie_name_t (mvn_name_num, mvn_movie_num,
> mvn_name_code, mvn_role_code)
> VALUES (#nameNum#, #movieNum#, #nameCode#, #roleCode#)
> </statement>
>
>
> <statement id="getPeople" parameterClass="long"
> resultClass="sqlmaptest.TestPerson">
> SELECT mvn_name_num as nameNum,
> mvn_movie_num as movieNum,
> mvn_name_code as nameCode,
> mvn_role_code as roleCode
> FROM movie_db..movie_name_t
> WHERE mvn_movie_num = #value#
> </statement>
>
> Here is the snippet of java that uses the maps (TestPerson is a simple
> bean):
>
> try {
> sqlMap.startTransaction();
> List people = sqlMap.queryForList("getPeople", new Long(movieNum));
> sqlMap.delete("doDelete", new Long(movieNum));
>
> if(people != null) {
> for (Iterator iter = people.iterator(); iter.hasNext(); ) {
> TestPerson person = (TestPerson) iter.next();
> sqlMap.insert("doInsert", person);
> }
> }
> sqlMap.commitTransaction();
> } catch (Exception e) {
> e.printStackTrace();
> } finally {
> sqlMap.endTransaction();
> try {
> } catch (Exception e) {
> e.printStackTrace();
> }
> }
>
>
> Any help would be GREATLY appreciated! I've tried to come up with any
> possible reason why this works or fails SOMETIMES but not predictably.
>
> Thank you!
>
> -Bryan Shannon
>
--
I tend to view "truly flexible" by another term: "Make everything
equally hard". -- DHH
