I know that other iBatis users have had problems in the past (including myself) when working with Sybase/MSSQL because of the "Done in proc" results that get sent by stored procedures or triggers. It appears that most of these issues have been resolved, but there is still room for error: In Sybase-land, you can do an update or an insert that causes results to come back too, if the update or insert fires a trigger. Most annoyingly is that it appears that if you have a trigger that contains a rollback or "raiserror" in it along with other intermediate selects, then the transaction may roll back on you without the driver/iBatis throwing a SQLException! That's bad for a very obvious reason, since your java code happily continues on, inserting or updating everything AFTER the rollback happened. Worst of all, there is no indication that there was even a problem!
I've confirmed this behaviour with the latest versions of both jConnect (sybase's driver) and jTDS. I know about IGNORE_DONE_IN_PROC, but I don't want a raiserror to be ignored... For example: If you have a trigger on a table that looks like: CREATE TRIGGER test_trigger on tablename for insert, update as BEGIN select 1 select 2 rollback tran raiserror 2323342 END Then any attempt to insert or update "tablename" from within a larger transaction will not only NOT work, but it will roll the transaction back SILENTLY, leaving any following inserts or updates taking effect. This might be due to the way the drivers recognize "raiserror", probably by processing some type of "error" resultset... Which iBatis isn't doing in SqlExecutor.executeUpdate(). After examining SqlExecutor.java (in iBatis' codebase) and seeing that the code for handling multiple result sets was only in the "read" methods, I changed my calls from "sqlMap.insert()" to "sqlMap.queryForObject()" and i changed my sql map xml to use "<statement></statement>" instead of "<insert></insert>" and THAT WORKED! (The raiserror in the trigger caused a SqlException alerting my DAO code.) So I downloaded the source and added the same call to "handleMultipleResults()" to the SqlExecutor.executeUpdate() method that the executeQuery() has, and I get the expected results (that is, the raiserror in the sql causes a SqlException to be thrown). My question is (or should I post this on another mailing list) if adding a handleMultipleResults() call to executeUpdate() an acceptable change in iBatis itself? I'd be happy to supply a patch. Thanks for your time! -Bryan Shannon Tribune Media Services
