Re: Insert/update issues

[Jeff Butler <je...@gmail.com>]

iBATIS does not support multiple result sets.  There are several open JIRA
issues related to stored procedures, and some talk about changing this.  But
it's a big job.

If you can't change the way Sybase works in this cse (i.e. raising an actual
error rather than sending another result set), then you are stuck.

Jeff Butler


On 3/24/06, Christopher.Mathrusse@sybase.com <
Christopher.Mathrusse@sybase.com> wrote:
>
>
> 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
> christopher.mathrusse@sybase.com
> Sybase, Inc
> One Sybase Drive
> Dublin, CA 94568
> (925) 236-5553
>

Re: Insert/update issues

[Jeff Butler <je...@gmail.com>]

Sounds like a "good" solution given the current state of iBATIS.

Jeff Butler


On 3/24/06, Christopher.Mathrusse@sybase.com <Christopher.Mathrusse@sybase.com
> wrote:
>
>
> Sybase has an option:
> You can tell jConnect to ignore the doneinprocs via a connection
> property "IGNORE_DONE_IN_PROC" = 'true'.
>
> This allowed the exception to be raised as listed below. The only issue I
> have with this is at first glance the exception appears to be related to the
> SQLMap rather than an error from the database.
>
>  Loading XML bean definitions from class path resource
> [org/springframework/jdbc/support/sql-error-codes.xml]
> 09:55:38,301  INFO [http-8080-Processor24] - SQLErrorCodes loaded: [DB2,
> HSQL, MS-SQL, MySQL, Oracle, Informix, PostgreSQL, Sybase]
> 09:55:38,551 ERROR [http-8080-Processor24] - {CustomerServiceAction} *
> org.springframework.jdbc.UncategorizedSQLException *: SqlMapClient
> operation; uncategorized *SQLException* for SQL []; SQL state [null];
> error code [90003];
> --- The error occurred in com/sybase/cosmos/dao/maps/BillTo.xml.
> --- The error occurred while applying a parameter map.
> --- Check the BillTo-insert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: *com.sybase.jdbc3.jdbc.SybSQLException*: The County being
> inserted is not valid for the Country
> and State being inserted.  Go to OEVALID to view/insert valid counties.;
> nested exception is *com.ibatis.common.jdbc.exception.NestedSQLException *:
>
> --- The error occurred in com/sybase/cosmos/dao/maps/BillTo.xml.
> --- The error occurred while applying a parameter map.
> --- Check the BillTo-insert-InlineParameterMap.
> --- Check the statement (update failed).
> --- Cause: *com.sybase.jdbc3.jdbc.SybSQLException*: The County being
> inserted is not valid for the Country
> and State being inserted.  Go to OEVALID to view/insert valid counties.
>
>
> Chris Mathrusse
> christopher.mathrusse@sybase.com
> Sybase, Inc
> One Sybase Drive
> Dublin, CA 94568
> (925) 236-5553
>
>
>   *"Jeff Butler" <je...@gmail.com>*
>
> 03/24/2006 09:47 AM   Please respond to
> user-java@ibatis.apache.org
>
>    To
> user-java@ibatis.apache.org  cc
>   Subject
> Re: Insert/update issues
>
>
>
>
> iBATIS does not support multiple result sets.  There are several open JIRA
> issues related to stored procedures, and some talk about changing this.  But
> it's a big job.
>
> If you can't change the way Sybase works in this cse (i.e. raising an
> actual error rather than sending another result set), then you are stuck.
>
> Jeff Butler
>
>
> On 3/24/06, *Christopher.Mathrusse@sybase.com*<Ch...@sybase.com><
> *Christopher.Mathrusse@sybase.com * <Ch...@sybase.com>>
> wrote:
>
> 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*
> **christopher.mathrusse@sybase.com * <ch...@sybase.com>
> Sybase, Inc
> One Sybase Drive
> Dublin, CA 94568
> (925) 236-5553
>
>