Thanks for your response. I don't see how it could adversely affect other drivers since this behaviour (of sybase) appears to be well within the JDBC specification. (It's kind of a grey area). I'll try to whittle together a patch that is well tested in our environment (we are using 2.3.0.677) and have not yet moved to the latest release, although I can confirm that the latest release doesn't have the solution. We'll test against the latest build and get the patch in.
In the meantime, I'll try to nail down how the sybase drivers use resultsets, if at all, to determine if a 'raiserror' was called. This was an issue that I brought up in 2006 when SqlExecutor.java was changed from executeUpdate() (JDBC) to execute() (apparently to handle Oracle Refcursors made between 2.0.9b release and the 2.1.0 release). As a side note, we also use the exact same sql maps (for parts of our application) that hit both Sybase and Oracle. I know that that is evil, and we do it with a bit of trickery... But it will give a good test for Oracle, as well. We use a custom wrapper driver that turns "database..tablename" (sybase) into "database.tablename" (oracle) on the fly. [of course, this wrapper isn't used when we are debugging this issue!] I'll keep everyone posted on this, if there are more Sybase/MSSql users besides just us, and Chris Mathrusse! Regards, Bryan Shannon -----Original Message----- From: Kai Grabfelder [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2008 3:20 PM To: user-java@ibatis.apache.org Subject: Re: Multiple results during insert/update (Sybase) 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 > >
