ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kai Grabfelder <nos...@kinokai.de>
Subject Re: Multiple results during insert/update (Sybase)
Date Mon, 17 Nov 2008 20:19:58 GMT
Hi Bryan,

this or the dev list is the correct place for asking such questions. Could you please open
a jire issue an
attach your patch against the iBATIS trunk to it?

I'm not really sure how your supposed solution will affect other database / jdbc drivers.
Do you think that it
can do any harm?

Regards

Kai

--- Original Nachricht ---
Absender: Shannon, Bryan
Datum: 17.11.2008 20:43
> 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
> 
> 


Mime
View raw message