ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Shannon, Bryan" <BShan...@Tribune.com>
Subject Multiple results during insert/update (Sybase)
Date Mon, 17 Nov 2008 19:43:44 GMT
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
select 1
select 2
rollback tran
raiserror 2323342
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

View raw message