Insert/update issues

[Christopher . Mathrusse]

I'm working with Sybase ASE 12 using JConnect 6.0 JDBC driver. The problem that I am having is when I perform an insert or an update into a table that has a trigger, and the trigger calls RaiseError, the error does not get converted into an SQLException.  It appears that this information will be sent back to the calling application in the form of a resultSet. Seeing as a call to execute() could return multiple ResultSets, I'm told the proper way to access this information is to retrieve all resultSet returned by the PreparedStatement.

The JDBC API docs do make reference to this:
The execute method returns a boolean to indicate the form of the first result. You must call either the method getResultSet or getUpdateCount to retrieve the result; you must call getMoreResults to move to any subsequent result(s).

I'm told the following:

Some methods only handle a single result and anything but the single result will be an exception. When you consider stored procedures in particular there is practically anything that can be returned. e.g.

create proc xx as
  select * from table    ==> this only throw exception if something like the table has been dropped etc.
  raiserror xxx
  select 1
  etc.

Any method of query that only expects a single result is never going to see the raiserror.  Because it does not read the results far enough to get to the error.


result from server =  |1. results set rows | 2.  results message | 3. results set rows | end of results |

So consider results consisting of 3 different result sets if only result #1 was read by the method and the program went on then the #2 results message would
not be read unless you used a method like <statement>.execute() and process all results.  Trigger output is like this.

If let's say there were something wrong with the first select and there was not a row result but a message, then you would see the SQLException in the simple method like <statement>.executeQuery() .




So what is my work around? How can I configure iBatis so I can receive the error raised by the trigger?

Thanks...

Chris Mathrusse
[EMAIL PROTECTED]
Sybase, Inc
One Sybase Drive
Dublin, CA 94568
(925) 236-5553