You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by BenBaril <be...@fbn.ca> on 2007/05/19 00:43:14 UTC
Retrieve stored proc return code
Hi All,
I know this issue has come up before, but I'm curious if there is a
solution. I am in the process of evaluating iBatis (and we have pretty much
decided on it) as the sql mapper for our IT-Revitalization project. We have
over 1000 Stored Procedures in a Sybase database. So far we have gotten
around issues such as not having a handle on the connection to retrieve
RAISERROR messages from a stored procedure as well as limiting the number of
results.
Our problem right now is that we cannot retrieve the return code from an SP.
We rely heavily on return codes to determine why a stored procedure may have
failed, and we need these codes to propagate back up the Java stack so we
can have a handle on them.
Is there a way to do this using iBatis right now?
Thank you,
Benjamin Baril
--
View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Retrieve stored proc return code
Posted by BenBaril <be...@fbn.ca>.
This is the solution that I just came up with and came here to post, however
you beat me to it.
I think this should be documented somewhere in the iBatis source as I
imagine I'm not alone in this issue, I think all TDS drivers have a return
code as their first parameter
jeffgbutler wrote:
>
> It sounds like you're not registering an output parameter. Here's an
> example:
>
> <sqlMap namespace="sqlMap">
> <parameterMap class="map" id="theParameterMap">
> <parameter property="rc" javaType="java.lang.Integer"
> jdbcType="INTEGER"
> mode="OUT" />
> <parameter property="parm1" javaType="java.math.Integer"
> jdbcType="INTEGER" mode="IN" />
> <parameter property="parm2" javaType="java.math.Integer"
> jdbcType="INTEGER" mode="IN" />
> </parameterMap>
>
> <procedure id="adder" parameterMap="theParameterMap">
> {? = call some_proc (?,?)}
> </procedure>
> </sqlMap>
>
> Jeff Butler
>
>
>
>
> On 5/21/07, Mikael Andersson <ma...@gmail.com> wrote:
>>
>> Hi,
>> I'm running into problems getting the return code from a sybase stored
>> procedure as well.
>>
>> I have tried the {? = call someproc(?,?)} construct, but it gives me the
>> following error message :
>> Cause: java.sql.SQLException : JZ0SC: Callable Statement: attempt to set
>> the return status as an input parameter.;
>>
>> I'm no DB guru, quite the opposite, and I've tried various apporaches
>> without success.
>> Tried defining a parameter element in the parameterMap, got an error
>> message.
>>
>> I have read through what I think are the relevant bits in "iBatis in
>> Action", but haven't found a solution yet.
>>
>> Does the stored procedure have to be created in a certain way to allow me
>> to grab a hold of the status code?
>>
>> Help greatly appreciated,
>> Mike
>>
>>
>>
>>
>> On 19/05/07, Jeff Butler <je...@gmail.com> wrote:
>> >
>> > By return code, do you mean something that can be obtained like this:
>> >
>> > {? = call someproc(?,?)}
>> >
>> > If so, then you can use the above syntax and register an output
>> > parameter.
>> >
>> > Jeff Butler
>> >
>> >
>> > On 5/18/07, BenBaril <benjamin.baril@fbn.ca > wrote:
>> > >
>> > >
>> > > Hi All,
>> > >
>> > > I know this issue has come up before, but I'm curious if there is a
>> > > solution. I am in the process of evaluating iBatis (and we have
>> pretty
>> > > much
>> > > decided on it) as the sql mapper for our IT-Revitalization project.
>> We
>> > > have
>> > > over 1000 Stored Procedures in a Sybase database. So far we have
>> > > gotten
>> > > around issues such as not having a handle on the connection to
>> > > retrieve
>> > > RAISERROR messages from a stored procedure as well as limiting the
>> > > number of
>> > > results.
>> > >
>> > > Our problem right now is that we cannot retrieve the return code from
>> > > an SP.
>> > > We rely heavily on return codes to determine why a stored procedure
>> > > may have
>> > > failed, and we need these codes to propagate back up the Java stack
>> so
>> > > we
>> > > can have a handle on them.
>> > >
>> > > Is there a way to do this using iBatis right now?
>> > >
>> > > Thank you,
>> > > Benjamin Baril
>> > > --
>> > > View this message in context:
>> http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
>> > >
>> > > Sent from the iBATIS - User - Java mailing list archive at
>> Nabble.com<http://nabble.com/>
>> > > .
>> > >
>> > >
>> >
>>
>
>
--
View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10724968
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Retrieve stored proc return code
Posted by Mikael Andersson <ma...@gmail.com>.
It works.
I didn't define javaType and jdbcType for the parameter element I added...
but when I added those attributes it works fine.
Many thanks,
Mikael.
On 21/05/07, Mikael Andersson <ma...@gmail.com> wrote:
>
> Hi Jeff,
> when I try adding a parameter element, I get this:
>
> org.springframework.jdbc.UncategorizedSQLException: SqlMapClient
> operation; uncategorized SQLException for SQL []; SQL state [JZ0SL]; error
> code [0];
> --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> --- The error occurred while executing update procedure.
> --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?,
> ?) }.
> --- Check the output parameters (register output parameters failed).
> --- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.;
> nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
>
> --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> --- The error occurred while executing update procedure.
> --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?,
> ?) }.
> --- Check the output parameters (register output parameters failed).
> --- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.
> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> --- The error occurred while executing update procedure.
> --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?,
> ?) }.
> --- Check the output parameters (register output parameters failed).
>
> I tried both with and without an extra ? in the in the stored procedure
> call.
>
> Cheers,
> Mike
>
> On 21/05/07, Jeff Butler < jeffgbutler@gmail.com> wrote:
> >
> > It sounds like you're not registering an output parameter. Here's an
> > example:
> >
> > <sqlMap namespace="sqlMap">
> > <parameterMap class="map" id="theParameterMap">
> > <parameter property="rc" javaType="java.lang.Integer"
> > jdbcType="INTEGER" mode="OUT" />
> > <parameter property="parm1" javaType="java.math.Integer"
> > jdbcType="INTEGER" mode="IN" />
> > <parameter property="parm2" javaType="java.math.Integer "
> > jdbcType="INTEGER" mode="IN" />
> > </parameterMap>
> >
> > <procedure id="adder" parameterMap="theParameterMap">
> > {? = call some_proc (?,?)}
> > </procedure>
> > </sqlMap>
> >
> > Jeff Butler
> >
> >
> >
> >
> > On 5/21/07, Mikael Andersson <mail.micke@gmail.com > wrote:
> > >
> > > Hi,
> > > I'm running into problems getting the return code from a sybase stored
> > > procedure as well.
> > >
> > > I have tried the {? = call someproc(?,?)} construct, but it gives me
> > > the following error message :
> > > Cause: java.sql.SQLException : JZ0SC: Callable Statement: attempt to
> > > set the return status as an input parameter.;
> > >
> > > I'm no DB guru, quite the opposite, and I've tried various apporaches
> > > without success.
> > > Tried defining a parameter element in the parameterMap, got an error
> > > message.
> > >
> > > I have read through what I think are the relevant bits in "iBatis in
> > > Action", but haven't found a solution yet.
> > >
> > > Does the stored procedure have to be created in a certain way to allow
> > > me to grab a hold of the status code?
> > >
> > > Help greatly appreciated,
> > > Mike
> > >
> > >
> > >
> > >
> > > On 19/05/07, Jeff Butler <jeffgbutler@gmail.com > wrote:
> > > >
> > > > By return code, do you mean something that can be obtained like
> > > > this:
> > > >
> > > > {? = call someproc(?,?)}
> > > >
> > > > If so, then you can use the above syntax and register an output
> > > > parameter.
> > > >
> > > > Jeff Butler
> > > >
> > > >
> > > > On 5/18/07, BenBaril <benjamin.baril@fbn.ca > wrote:
> > > > >
> > > > >
> > > > > Hi All,
> > > > >
> > > > > I know this issue has come up before, but I'm curious if there is
> > > > > a
> > > > > solution. I am in the process of evaluating iBatis (and we have
> > > > > pretty much
> > > > > decided on it) as the sql mapper for our IT-Revitalization
> > > > > project. We have
> > > > > over 1000 Stored Procedures in a Sybase database. So far we have
> > > > > gotten
> > > > > around issues such as not having a handle on the connection to
> > > > > retrieve
> > > > > RAISERROR messages from a stored procedure as well as limiting the
> > > > > number of
> > > > > results.
> > > > >
> > > > > Our problem right now is that we cannot retrieve the return code
> > > > > from an SP.
> > > > > We rely heavily on return codes to determine why a stored
> > > > > procedure may have
> > > > > failed, and we need these codes to propagate back up the Java
> > > > > stack so we
> > > > > can have a handle on them.
> > > > >
> > > > > Is there a way to do this using iBatis right now?
> > > > >
> > > > > Thank you,
> > > > > Benjamin Baril
> > > > > --
> > > > > View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> > > > >
> > > > > Sent from the iBATIS - User - Java mailing list archive at
> > > > > Nabble.com <http://nabble.com/>.
> > > > >
> > > > >
> > > >
> > >
> >
>
Re: Retrieve stored proc return code
Posted by Mikael Andersson <ma...@gmail.com>.
Final question.
When I register an output parameter and there is a foreign key constraint
violation. I get an Exception,
org.springframework.dao.DataIntegrityViolationException, which is fine.
But when I don't register an output parameter, there is no exception thrown
and the statement fails silently. Is this the expected behaviour?
Thanks,
Mike
On 21/05/07, Jeff Butler <je...@gmail.com> wrote:
>
> The error is probably in your parameter map - you're likely trying to use
> a non-standard JDBC type. Send your parameter map - that might help.
>
> Jeff Bulter
>
>
> On 5/21/07, Mikael Andersson <ma...@gmail.com> wrote:
> >
> > Hi Jeff,
> > when I try adding a parameter element, I get this:
> >
> > org.springframework.jdbc.UncategorizedSQLException : SqlMapClient
> > operation; uncategorized SQLException for SQL []; SQL state [JZ0SL]; error
> > code [0];
> > --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> > --- The error occurred while executing update procedure.
> > --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?,
> > ?, ?) }.
> > --- Check the output parameters (register output parameters failed).
> > --- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type
> > -99999999.; nested exception is
> > com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> > --- The error occurred while executing update procedure.
> > --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?,
> > ?, ?) }.
> > --- Check the output parameters (register output parameters failed).
> > --- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.
> > Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> > --- The error occurred while executing update procedure.
> > --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?,
> > ?, ?) }.
> > --- Check the output parameters (register output parameters failed).
> >
> > I tried both with and without an extra ? in the in the stored procedure
> > call.
> >
> > Cheers,
> > Mike
> >
> > On 21/05/07, Jeff Butler < jeffgbutler@gmail.com > wrote:
> > >
> > > It sounds like you're not registering an output parameter. Here's an
> > > example:
> > >
> > > <sqlMap namespace="sqlMap">
> > > <parameterMap class="map" id="theParameterMap">
> > > <parameter property="rc" javaType="java.lang.Integer"
> > > jdbcType="INTEGER" mode="OUT" />
> > > <parameter property="parm1" javaType="java.math.Integer"
> > > jdbcType="INTEGER" mode="IN" />
> > > <parameter property="parm2" javaType="java.math.Integer "
> > > jdbcType="INTEGER" mode="IN" />
> > > </parameterMap>
> > >
> > > <procedure id="adder" parameterMap="theParameterMap">
> > > {? = call some_proc (?,?)}
> > > </procedure>
> > > </sqlMap>
> > >
> > > Jeff Butler
> > >
> > >
> > >
> > >
> > > On 5/21/07, Mikael Andersson <mail.micke@gmail.com > wrote:
> > > >
> > > > Hi,
> > > > I'm running into problems getting the return code from a sybase
> > > > stored procedure as well.
> > > >
> > > > I have tried the {? = call someproc(?,?)} construct, but it gives me
> > > > the following error message :
> > > > Cause: java.sql.SQLException : JZ0SC: Callable Statement: attempt to
> > > > set the return status as an input parameter.;
> > > >
> > > > I'm no DB guru, quite the opposite, and I've tried various
> > > > apporaches without success.
> > > > Tried defining a parameter element in the parameterMap, got an error
> > > > message.
> > > >
> > > > I have read through what I think are the relevant bits in "iBatis in
> > > > Action", but haven't found a solution yet.
> > > >
> > > > Does the stored procedure have to be created in a certain way to
> > > > allow me to grab a hold of the status code?
> > > >
> > > > Help greatly appreciated,
> > > > Mike
> > > >
> > > >
> > > >
> > > >
> > > > On 19/05/07, Jeff Butler <jeffgbutler@gmail.com > wrote:
> > > > >
> > > > > By return code, do you mean something that can be obtained like
> > > > > this:
> > > > >
> > > > > {? = call someproc(?,?)}
> > > > >
> > > > > If so, then you can use the above syntax and register an output
> > > > > parameter.
> > > > >
> > > > > Jeff Butler
> > > > >
> > > > >
> > > > > On 5/18/07, BenBaril <benjamin.baril@fbn.ca > wrote:
> > > > > >
> > > > > >
> > > > > > Hi All,
> > > > > >
> > > > > > I know this issue has come up before, but I'm curious if there
> > > > > > is a
> > > > > > solution. I am in the process of evaluating iBatis (and we have
> > > > > > pretty much
> > > > > > decided on it) as the sql mapper for our IT-Revitalization
> > > > > > project. We have
> > > > > > over 1000 Stored Procedures in a Sybase database. So far we have
> > > > > > gotten
> > > > > > around issues such as not having a handle on the connection to
> > > > > > retrieve
> > > > > > RAISERROR messages from a stored procedure as well as limiting
> > > > > > the number of
> > > > > > results.
> > > > > >
> > > > > > Our problem right now is that we cannot retrieve the return code
> > > > > > from an SP.
> > > > > > We rely heavily on return codes to determine why a stored
> > > > > > procedure may have
> > > > > > failed, and we need these codes to propagate back up the Java
> > > > > > stack so we
> > > > > > can have a handle on them.
> > > > > >
> > > > > > Is there a way to do this using iBatis right now?
> > > > > >
> > > > > > Thank you,
> > > > > > Benjamin Baril
> > > > > > --
> > > > > > View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> > > > > >
> > > > > > Sent from the iBATIS - User - Java mailing list archive at
> > > > > > Nabble.com <http://nabble.com/>.
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: Retrieve stored proc return code
Posted by Jeff Butler <je...@gmail.com>.
The error is probably in your parameter map - you're likely trying to use a
non-standard JDBC type. Send your parameter map - that might help.
Jeff Bulter
On 5/21/07, Mikael Andersson <ma...@gmail.com> wrote:
>
> Hi Jeff,
> when I try adding a parameter element, I get this:
>
> org.springframework.jdbc.UncategorizedSQLException: SqlMapClient
> operation; uncategorized SQLException for SQL []; SQL state [JZ0SL]; error
> code [0];
> --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> --- The error occurred while executing update procedure.
> --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?,
> ?) }.
> --- Check the output parameters (register output parameters failed).
> --- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.;
> nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
>
> --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> --- The error occurred while executing update procedure.
> --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?,
> ?) }.
> --- Check the output parameters (register output parameters failed).
> --- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.
> Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
> --- The error occurred while executing update procedure.
> --- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?,
> ?) }.
> --- Check the output parameters (register output parameters failed).
>
> I tried both with and without an extra ? in the in the stored procedure
> call.
>
> Cheers,
> Mike
>
> On 21/05/07, Jeff Butler < jeffgbutler@gmail.com> wrote:
> >
> > It sounds like you're not registering an output parameter. Here's an
> > example:
> >
> > <sqlMap namespace="sqlMap">
> > <parameterMap class="map" id="theParameterMap">
> > <parameter property="rc" javaType="java.lang.Integer"
> > jdbcType="INTEGER" mode="OUT" />
> > <parameter property="parm1" javaType="java.math.Integer"
> > jdbcType="INTEGER" mode="IN" />
> > <parameter property="parm2" javaType="java.math.Integer "
> > jdbcType="INTEGER" mode="IN" />
> > </parameterMap>
> >
> > <procedure id="adder" parameterMap="theParameterMap">
> > {? = call some_proc (?,?)}
> > </procedure>
> > </sqlMap>
> >
> > Jeff Butler
> >
> >
> >
> >
> > On 5/21/07, Mikael Andersson <mail.micke@gmail.com > wrote:
> > >
> > > Hi,
> > > I'm running into problems getting the return code from a sybase stored
> > > procedure as well.
> > >
> > > I have tried the {? = call someproc(?,?)} construct, but it gives me
> > > the following error message :
> > > Cause: java.sql.SQLException : JZ0SC: Callable Statement: attempt to
> > > set the return status as an input parameter.;
> > >
> > > I'm no DB guru, quite the opposite, and I've tried various apporaches
> > > without success.
> > > Tried defining a parameter element in the parameterMap, got an error
> > > message.
> > >
> > > I have read through what I think are the relevant bits in "iBatis in
> > > Action", but haven't found a solution yet.
> > >
> > > Does the stored procedure have to be created in a certain way to allow
> > > me to grab a hold of the status code?
> > >
> > > Help greatly appreciated,
> > > Mike
> > >
> > >
> > >
> > >
> > > On 19/05/07, Jeff Butler <jeffgbutler@gmail.com > wrote:
> > > >
> > > > By return code, do you mean something that can be obtained like
> > > > this:
> > > >
> > > > {? = call someproc(?,?)}
> > > >
> > > > If so, then you can use the above syntax and register an output
> > > > parameter.
> > > >
> > > > Jeff Butler
> > > >
> > > >
> > > > On 5/18/07, BenBaril <benjamin.baril@fbn.ca > wrote:
> > > > >
> > > > >
> > > > > Hi All,
> > > > >
> > > > > I know this issue has come up before, but I'm curious if there is
> > > > > a
> > > > > solution. I am in the process of evaluating iBatis (and we have
> > > > > pretty much
> > > > > decided on it) as the sql mapper for our IT-Revitalization
> > > > > project. We have
> > > > > over 1000 Stored Procedures in a Sybase database. So far we have
> > > > > gotten
> > > > > around issues such as not having a handle on the connection to
> > > > > retrieve
> > > > > RAISERROR messages from a stored procedure as well as limiting the
> > > > > number of
> > > > > results.
> > > > >
> > > > > Our problem right now is that we cannot retrieve the return code
> > > > > from an SP.
> > > > > We rely heavily on return codes to determine why a stored
> > > > > procedure may have
> > > > > failed, and we need these codes to propagate back up the Java
> > > > > stack so we
> > > > > can have a handle on them.
> > > > >
> > > > > Is there a way to do this using iBatis right now?
> > > > >
> > > > > Thank you,
> > > > > Benjamin Baril
> > > > > --
> > > > > View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> > > > >
> > > > > Sent from the iBATIS - User - Java mailing list archive at
> > > > > Nabble.com <http://nabble.com/>.
> > > > >
> > > > >
> > > >
> > >
> >
>
Re: Retrieve stored proc return code
Posted by Mikael Andersson <ma...@gmail.com>.
Hi Jeff,
when I try adding a parameter element, I get this:
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation;
uncategorized SQLException for SQL []; SQL state [JZ0SL]; error code [0];
--- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
--- The error occurred while executing update procedure.
--- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?, ?)
}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.;
nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
--- The error occurred while executing update procedure.
--- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?, ?)
}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: JZ0SL: Unsupported SQL type -99999999.
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ibatis/COUNTRY_CONTRIBUTION_SqlMap.xml.
--- The error occurred while executing update procedure.
--- Check the { ? = call pr_JAEXP_CountryContrib_Sav(?, ?, ?, ?, ?, ?, ?, ?)
}.
--- Check the output parameters (register output parameters failed).
I tried both with and without an extra ? in the in the stored procedure
call.
Cheers,
Mike
On 21/05/07, Jeff Butler <je...@gmail.com> wrote:
>
> It sounds like you're not registering an output parameter. Here's an
> example:
>
> <sqlMap namespace="sqlMap">
> <parameterMap class="map" id="theParameterMap">
> <parameter property="rc" javaType="java.lang.Integer"
> jdbcType="INTEGER" mode="OUT" />
> <parameter property="parm1" javaType="java.math.Integer"
> jdbcType="INTEGER" mode="IN" />
> <parameter property="parm2" javaType="java.math.Integer "
> jdbcType="INTEGER" mode="IN" />
> </parameterMap>
>
> <procedure id="adder" parameterMap="theParameterMap">
> {? = call some_proc (?,?)}
> </procedure>
> </sqlMap>
>
> Jeff Butler
>
>
>
>
> On 5/21/07, Mikael Andersson <ma...@gmail.com> wrote:
> >
> > Hi,
> > I'm running into problems getting the return code from a sybase stored
> > procedure as well.
> >
> > I have tried the {? = call someproc(?,?)} construct, but it gives me the
> > following error message :
> > Cause: java.sql.SQLException : JZ0SC: Callable Statement: attempt to set
> > the return status as an input parameter.;
> >
> > I'm no DB guru, quite the opposite, and I've tried various apporaches
> > without success.
> > Tried defining a parameter element in the parameterMap, got an error
> > message.
> >
> > I have read through what I think are the relevant bits in "iBatis in
> > Action", but haven't found a solution yet.
> >
> > Does the stored procedure have to be created in a certain way to allow
> > me to grab a hold of the status code?
> >
> > Help greatly appreciated,
> > Mike
> >
> >
> >
> >
> > On 19/05/07, Jeff Butler <jeffgbutler@gmail.com > wrote:
> > >
> > > By return code, do you mean something that can be obtained like this:
> > >
> > > {? = call someproc(?,?)}
> > >
> > > If so, then you can use the above syntax and register an output
> > > parameter.
> > >
> > > Jeff Butler
> > >
> > >
> > > On 5/18/07, BenBaril <benjamin.baril@fbn.ca > wrote:
> > > >
> > > >
> > > > Hi All,
> > > >
> > > > I know this issue has come up before, but I'm curious if there is a
> > > > solution. I am in the process of evaluating iBatis (and we have
> > > > pretty much
> > > > decided on it) as the sql mapper for our IT-Revitalization project.
> > > > We have
> > > > over 1000 Stored Procedures in a Sybase database. So far we have
> > > > gotten
> > > > around issues such as not having a handle on the connection to
> > > > retrieve
> > > > RAISERROR messages from a stored procedure as well as limiting the
> > > > number of
> > > > results.
> > > >
> > > > Our problem right now is that we cannot retrieve the return code
> > > > from an SP.
> > > > We rely heavily on return codes to determine why a stored procedure
> > > > may have
> > > > failed, and we need these codes to propagate back up the Java stack
> > > > so we
> > > > can have a handle on them.
> > > >
> > > > Is there a way to do this using iBatis right now?
> > > >
> > > > Thank you,
> > > > Benjamin Baril
> > > > --
> > > > View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> > > >
> > > > Sent from the iBATIS - User - Java mailing list archive at
> > > > Nabble.com <http://nabble.com/>.
> > > >
> > > >
> > >
> >
>
Re: Retrieve stored proc return code
Posted by Jeff Butler <je...@gmail.com>.
It sounds like you're not registering an output parameter. Here's an
example:
<sqlMap namespace="sqlMap">
<parameterMap class="map" id="theParameterMap">
<parameter property="rc" javaType="java.lang.Integer" jdbcType="INTEGER"
mode="OUT" />
<parameter property="parm1" javaType="java.math.Integer"
jdbcType="INTEGER" mode="IN" />
<parameter property="parm2" javaType="java.math.Integer"
jdbcType="INTEGER" mode="IN" />
</parameterMap>
<procedure id="adder" parameterMap="theParameterMap">
{? = call some_proc (?,?)}
</procedure>
</sqlMap>
Jeff Butler
On 5/21/07, Mikael Andersson <ma...@gmail.com> wrote:
>
> Hi,
> I'm running into problems getting the return code from a sybase stored
> procedure as well.
>
> I have tried the {? = call someproc(?,?)} construct, but it gives me the
> following error message :
> Cause: java.sql.SQLException : JZ0SC: Callable Statement: attempt to set
> the return status as an input parameter.;
>
> I'm no DB guru, quite the opposite, and I've tried various apporaches
> without success.
> Tried defining a parameter element in the parameterMap, got an error
> message.
>
> I have read through what I think are the relevant bits in "iBatis in
> Action", but haven't found a solution yet.
>
> Does the stored procedure have to be created in a certain way to allow me
> to grab a hold of the status code?
>
> Help greatly appreciated,
> Mike
>
>
>
>
> On 19/05/07, Jeff Butler <je...@gmail.com> wrote:
> >
> > By return code, do you mean something that can be obtained like this:
> >
> > {? = call someproc(?,?)}
> >
> > If so, then you can use the above syntax and register an output
> > parameter.
> >
> > Jeff Butler
> >
> >
> > On 5/18/07, BenBaril <benjamin.baril@fbn.ca > wrote:
> > >
> > >
> > > Hi All,
> > >
> > > I know this issue has come up before, but I'm curious if there is a
> > > solution. I am in the process of evaluating iBatis (and we have pretty
> > > much
> > > decided on it) as the sql mapper for our IT-Revitalization project. We
> > > have
> > > over 1000 Stored Procedures in a Sybase database. So far we have
> > > gotten
> > > around issues such as not having a handle on the connection to
> > > retrieve
> > > RAISERROR messages from a stored procedure as well as limiting the
> > > number of
> > > results.
> > >
> > > Our problem right now is that we cannot retrieve the return code from
> > > an SP.
> > > We rely heavily on return codes to determine why a stored procedure
> > > may have
> > > failed, and we need these codes to propagate back up the Java stack so
> > > we
> > > can have a handle on them.
> > >
> > > Is there a way to do this using iBatis right now?
> > >
> > > Thank you,
> > > Benjamin Baril
> > > --
> > > View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> > >
> > > Sent from the iBATIS - User - Java mailing list archive at Nabble.com<http://nabble.com/>
> > > .
> > >
> > >
> >
>
Re: Retrieve stored proc return code
Posted by Mikael Andersson <ma...@gmail.com>.
Hi,
I'm running into problems getting the return code from a sybase stored
procedure as well.
I have tried the {? = call someproc(?,?)} construct, but it gives me the
following error message :
Cause: java.sql.SQLException: JZ0SC: Callable Statement: attempt to set the
return status as an input parameter.;
I'm no DB guru, quite the opposite, and I've tried various apporaches
without success.
Tried defining a parameter element in the parameterMap, got an error
message.
I have read through what I think are the relevant bits in "iBatis in
Action", but haven't found a solution yet.
Does the stored procedure have to be created in a certain way to allow me to
grab a hold of the status code?
Help greatly appreciated,
Mike
On 19/05/07, Jeff Butler <je...@gmail.com> wrote:
>
> By return code, do you mean something that can be obtained like this:
>
> {? = call someproc(?,?)}
>
> If so, then you can use the above syntax and register an output parameter.
>
> Jeff Butler
>
>
> On 5/18/07, BenBaril <be...@fbn.ca> wrote:
> >
> >
> > Hi All,
> >
> > I know this issue has come up before, but I'm curious if there is a
> > solution. I am in the process of evaluating iBatis (and we have pretty
> > much
> > decided on it) as the sql mapper for our IT-Revitalization project. We
> > have
> > over 1000 Stored Procedures in a Sybase database. So far we have gotten
> > around issues such as not having a handle on the connection to retrieve
> > RAISERROR messages from a stored procedure as well as limiting the
> > number of
> > results.
> >
> > Our problem right now is that we cannot retrieve the return code from an
> > SP.
> > We rely heavily on return codes to determine why a stored procedure may
> > have
> > failed, and we need these codes to propagate back up the Java stack so
> > we
> > can have a handle on them.
> >
> > Is there a way to do this using iBatis right now?
> >
> > Thank you,
> > Benjamin Baril
> > --
> > View this message in context:
> > http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> > Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >
> >
>
Re: Retrieve stored proc return code
Posted by Jeff Butler <je...@gmail.com>.
By return code, do you mean something that can be obtained like this:
{? = call someproc(?,?)}
If so, then you can use the above syntax and register an output parameter.
Jeff Butler
On 5/18/07, BenBaril <be...@fbn.ca> wrote:
>
>
> Hi All,
>
> I know this issue has come up before, but I'm curious if there is a
> solution. I am in the process of evaluating iBatis (and we have pretty
> much
> decided on it) as the sql mapper for our IT-Revitalization project. We
> have
> over 1000 Stored Procedures in a Sybase database. So far we have gotten
> around issues such as not having a handle on the connection to retrieve
> RAISERROR messages from a stored procedure as well as limiting the number
> of
> results.
>
> Our problem right now is that we cannot retrieve the return code from an
> SP.
> We rely heavily on return codes to determine why a stored procedure may
> have
> failed, and we need these codes to propagate back up the Java stack so we
> can have a handle on them.
>
> Is there a way to do this using iBatis right now?
>
> Thank you,
> Benjamin Baril
> --
> View this message in context:
> http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: Retrieve stored proc return code
Posted by BenBaril <be...@fbn.ca>.
OT: (Though I really do need a solution to this)
To retrieve RAISERROR messages from Sybase we used a feature that is built
into the sybase driver. You can set a message handler on the connection when
it's created. I used a feature of Websphere (the application container that
will be hosting my applications) called the DataStoreHelper in order to
intercept the creation of the connection.
To limit the maximum number of rows returned (a feature that we had
previously done by using our own custom driver) was to use the Sybase
connection property 'SQLINISTRING' to pass a 'set rowcount 2005' to sybase
when the connection was created. After extensive testing we saw that while
SPs could override the rowcount (which is fine) that override was scoped to
the life of the SP and no longer.
Lambert Torres wrote:
>
> Can you share what the workarounds were?
>
> OT:
> So far we have gotten
> around issues such as not having a handle on the connection to retrieve
> RAISERROR messages from a stored procedure as well as limiting the number
> of
> results.
>
>
> thanks,
> Lambert
>
> Lambert Torres
> NBA Information Technology
> Web Technology Group
> 100 Plaza Drive
> Secaucus, NJ 07094
> Ph : (201) 974-6785
> Fax: (201) 974-6001
>
>
>
>
> BenBaril
> <benjamin.baril@f
> bn.ca> To
> user-java@ibatis.apache.org
> 05/18/2007 06:43 cc
> PM
> Subject
> Retrieve stored proc return code
> Please respond to
> user-java@ibatis.
> apache.org
>
>
>
>
>
>
>
>
> Hi All,
>
> I know this issue has come up before, but I'm curious if there is a
> solution. I am in the process of evaluating iBatis (and we have pretty
> much
> decided on it) as the sql mapper for our IT-Revitalization project. We
> have
> over 1000 Stored Procedures in a Sybase database. So far we have gotten
> around issues such as not having a handle on the connection to retrieve
> RAISERROR messages from a stored procedure as well as limiting the number
> of
> results.
>
> Our problem right now is that we cannot retrieve the return code from an
> SP.
> We rely heavily on return codes to determine why a stored procedure may
> have
> failed, and we need these codes to propagate back up the Java stack so we
> can have a handle on them.
>
> Is there a way to do this using iBatis right now?
>
> Thank you,
> Benjamin Baril
> --
> View this message in context:
> http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
>
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
>
>
>
--
View this message in context: http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10694633
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Retrieve stored proc return code
Posted by Lambert Torres <lt...@nba.com>.
Can you share what the workarounds were?
OT:
So far we have gotten
around issues such as not having a handle on the connection to retrieve
RAISERROR messages from a stored procedure as well as limiting the number
of
results.
thanks,
Lambert
Lambert Torres
NBA Information Technology
Web Technology Group
100 Plaza Drive
Secaucus, NJ 07094
Ph : (201) 974-6785
Fax: (201) 974-6001
BenBaril
<benjamin.baril@f
bn.ca> To
user-java@ibatis.apache.org
05/18/2007 06:43 cc
PM
Subject
Retrieve stored proc return code
Please respond to
user-java@ibatis.
apache.org
Hi All,
I know this issue has come up before, but I'm curious if there is a
solution. I am in the process of evaluating iBatis (and we have pretty much
decided on it) as the sql mapper for our IT-Revitalization project. We have
over 1000 Stored Procedures in a Sybase database. So far we have gotten
around issues such as not having a handle on the connection to retrieve
RAISERROR messages from a stored procedure as well as limiting the number
of
results.
Our problem right now is that we cannot retrieve the return code from an
SP.
We rely heavily on return codes to determine why a stored procedure may
have
failed, and we need these codes to propagate back up the Java stack so we
can have a handle on them.
Is there a way to do this using iBatis right now?
Thank you,
Benjamin Baril
--
View this message in context:
http://www.nabble.com/Retrieve-stored-proc-return-code-tf3780537.html#a10691634
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.