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 "Gaer,Jeffrey J" <je...@dads.state.tx.us> on 2007/05/24 18:18:33 UTC
RE: problems using stored procs on sybase
Thanks for the response Chris,
As you can see from the sqlMap definition we are using only ? parameters. We
didn't have a problem getting the output parameter back ( other than the
type conversion issue), but the ibatis code was failing because it was
expecting a result set. We are not sure why that is a problem with this proc
but with none of our update proc, but are guessing it is a configuration
issue or default somewhere. The conversion issue 'floats' with the value
that is passed so we a pretty sure it is related to precision and the
conversion process. TTBOMK Ibatis does not provide a means for specifying
the precision of decimal types when building the callable. Lots of big
decimal values wind up with long fractional values because of decimal to
base conversion.
_____
From: Christopher.Mathrusse@sybase.com
[mailto:Christopher.Mathrusse@sybase.com]
Sent: Thursday, May 24, 2007 10:59 AM
To: jeffrey.gaer@dads.state.tx.us; user-java@ibatis.apache.org
Subject: RE: problems using stored procs on sybase
First, your problem with JConnect. There is documented in the JConnect
reference guide one sentence that you should never overlook.
Executing stored procedures
*If you execute a stored procedure in a CallableStatement object that
represents parameter values as question marks, you get better performance
than if you use both question marks and literal values for parameters.
Further, if you mix literals and question marks, you cannot use output
parameters with a stored procedure.
Be certain that you are not mixing literals with the question marks when
executing the sp.
As far as BigDecimal not mapping, I currently have defined in one class a
BigDecimal field that I am mapping in my SQLMap from a DECIMAL jdbc type
without any issues. The database has this field defined as Money.
result column="unit_price" property="unitPrice" jdbcType="DECIMAL"
I would recommend looking through the JConnect Programmers Guide to ensure
that you are mapping things correctly. There is a great deal of information
in the guide and I can say from experience that "The Devil is in the
details." There are many little "gotchas" in this guide that you must pay
attention to.
_____
From: "Gaer,Jeffrey J" <je...@dads.state.tx.us> [mailto:"Gaer,Jeffrey
J" <je...@dads.state.tx.us>]
Sent: Thursday, May 24, 2007 7:59 AM
To: user-java@ibatis.apache.org
Subject: problems using stored procs on sybase
Running java 1.4.2 and Sybase jconnect driver ( I think we are version 12.0
) we had two problems running a particular stored proc.
The first was a null pointer exception processing the results. The proc set
an output value but did not return a result set. Adding a 'dummy' result
set to the proc eliminated the problem.
--------------------
Caused by: java.lang.NullPointerException
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.jav
a:375)
at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExec
utor.java:291)
at
com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuer
y(ProcedureStatement.java:34)
-----------------------
The other occurs when using sql DECIMAL types. We tried using both
BigDecimal and Double as the java type, but get the following exception. We
were able to work around this by adding a string conversion in the proc and
passing strings. I was looking for a way to set the precession in Ibatis,
but could not find one, the precession property seems to be only available
for .net.
----------------------------------
--- Cause: com.sybase.jdbc2.jdbc.SybSQLException: Arithmetic overflow during
implicit conversion of DECIMAL value '2905.00' to a NUMERIC field .
Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Arithmetic overflow during
implicit conversion of DECIMAL value '2905.00' to a NUMERIC field .
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWith
Callback(GeneralStatement.java:185)
at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForO
bject(GeneralStatement.java:104)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:561)
at
com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapEx
ecutorDelegate.java:536)
at
com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSession
Impl.java:93)
at
com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientIm
pl.java:70)
------------------------
Here's the sqlMap definition, we tried both with and without setting the
types in the result map.Thanks in advance for any insight.
-----------------------------
<resultMap id="bedsResult" class="java.util.HashMap" >
<result
property="total_fee"
column="AMOUNT"/>
</resultMap>
<parameterMap id="bedsCalcFeeParam" class="bedsFee" >
<parameter property ="id_app" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property ="id_service" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property ="app_action_string" jdbcType="VARCHAR"
javaType="java.lang.String" mode="IN"/>
<parameter property ="total_capacity" jdbcType="INTEGER"
javaType="java.lang.Integer" mode="IN"/>
<parameter property ="dt_effective" jdbcType="DATE"
javaType="java.util.Date" mode="IN"/>
<parameter property ="total_fee"
javaType="java.math.BigDecimal" jdbcType="DECIMAL" mode="INOUT"/>
</parameterMap>
<procedure id="bedsCalcFee" parameterMap="bedsCalcFeeParam"
resultClass="java.util.HashMap">
{call proc_calculate_fee_opa (?,?,?,?,?,?)}
</procedure>
Re: problems using stored procs on sybase
Posted by Jeff Butler <je...@gmail.com>.
How are you calling the SP? Are you using "queryForObject" or
"queryForList"? This would cause iBATIS to expect a result set.
If there's no result set, then call it with "insert" or "update" (as
non-intuitive as that may sound).
Jeff Butler
On 5/24/07, Gaer,Jeffrey J <je...@dads.state.tx.us> wrote:
>
> Thanks for the response Chris,
>
>
>
> As you can see from the sqlMap definition we are using only ? parameters.
> We didn't have a problem getting the output parameter back ( other than the
> type conversion issue), but the ibatis code was failing because it was
> expecting a result set. We are not sure why that is a problem with this proc
> but with none of our update proc, but are guessing it is a configuration
> issue or default somewhere. The conversion issue 'floats' with the value
> that is passed so we a pretty sure it is related to precision and the
> conversion process. TTBOMK Ibatis does not provide a means for specifying
> the precision of decimal types when building the callable. Lots of big
> decimal values wind up with long fractional values because of decimal to
> base conversion.
>
>
> ------------------------------
>
> *From:* Christopher.Mathrusse@sybase.com [mailto:
> Christopher.Mathrusse@sybase.com]
> *Sent:* Thursday, May 24, 2007 10:59 AM
> *To:* jeffrey.gaer@dads.state.tx.us; user-java@ibatis.apache.org
> *Subject:* RE: problems using stored procs on sybase
>
>
>
> First, your problem with JConnect. There is documented in the JConnect
> reference guide one sentence that you should never overlook.
>
> *Executing stored procedures*
>
> •If you execute a stored procedure in a CallableStatement object that
> represents parameter values as question marks, you get better performance
> than if you use both question marks and literal values for parameters. *Further,
> if you mix literals and question marks, you cannot use output parameters
> with a stored procedure.*
>
> Be certain that you are not mixing literals with the question marks when
> executing the sp.
>
>
>
> As far as BigDecimal not mapping, I currently have defined in one class a
> BigDecimal field that I am mapping in my SQLMap from a DECIMAL jdbc type
> without any issues. The database has this field defined as Money.
>
> result column="unit_price" property="unitPrice" jdbcType="DECIMAL"
>
> I would recommend looking through the JConnect Programmers Guide to ensure
> that you are mapping things correctly. There is a great deal of information
> in the guide and I can say from experience that "The Devil is in the
> details." There are many little "gotchas" in this guide that you must pay
> attention to.
>
>
> ------------------------------
>
> *From:* "Gaer,Jeffrey J" <je...@dads.state.tx.us>
> [mailto:"Gaer,Jeffrey J" <je...@dads.state.tx.us>]
> *Sent:* Thursday, May 24, 2007 7:59 AM
> *To:* user-java@ibatis.apache.org
> *Subject:* problems using stored procs on sybase
>
> Running java 1.4.2 and Sybase jconnect driver ( I think we are version
> 12.0 ) we had two problems running a particular stored proc.
>
>
>
> The first was a null pointer exception processing the results. The proc
> set an output value but did not return a result set. Adding a 'dummy'
> result set to the proc eliminated the problem.
>
> --------------------
>
> Caused by: java.lang.NullPointerException
>
> at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(
> SqlExecutor.java:375)
>
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(
> SqlExecutor.java:291)
>
> at
> com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery
> (ProcedureStatement.java:34)
>
> -----------------------
>
>
>
> The other occurs when using sql DECIMAL types. We tried using both
> BigDecimal and Double as the java type, but get the following exception. We
> were able to work around this by adding a string conversion in the proc and
> passing strings. I was looking for a way to set the precession in Ibatis,
> but could not find one, the precession property seems to be only available
> for .net.
>
>
>
> ----------------------------------
>
> --- Cause: com.sybase.jdbc2.jdbc.SybSQLException: Arithmetic overflow
> during implicit conversion of DECIMAL value '2905.00' to a NUMERIC field .
>
>
>
> Caused by: com.sybase.jdbc2.jdbc.SybSQLException: Arithmetic overflow
> during implicit conversion of DECIMAL value '2905.00' to a NUMERIC field .
>
>
>
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:185)
>
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForObject
> (GeneralStatement.java:104)
>
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(
> SqlMapExecutorDelegate.java:561)
>
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(
> SqlMapExecutorDelegate.java:536)
>
> at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(
> SqlMapSessionImpl.java:93)
>
> at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(
> SqlMapClientImpl.java:70)
>
>
>
> ------------------------
>
> Here's the sqlMap definition, we tried both with and without setting the
> types in the result map.Thanks in advance for any insight.
>
> -----------------------------
>
> <resultMap id="bedsResult" class="java.util.HashMap" >
>
> <result
>
> property="total_fee"
>
>
>
> column="AMOUNT"/>
>
> </resultMap>
>
>
>
> <parameterMap id="bedsCalcFeeParam" class="bedsFee" >
>
> <parameter property ="id_app" jdbcType="VARCHAR" javaType="
> java.lang.String" mode="IN"/>
>
> <parameter property ="id_service" jdbcType="VARCHAR" javaType=
> "java.lang.String" mode="IN"/>
>
> <parameter property ="app_action_string" jdbcType="VARCHAR"
> javaType="java.lang.String" mode="IN"/>
>
> <parameter property ="total_capacity" jdbcType="INTEGER"
> javaType="java.lang.Integer" mode="IN"/>
>
> <parameter property ="dt_effective" jdbcType="DATE" javaType="
> java.util.Date" mode="IN"/>
>
> <parameter property ="total_fee" javaType="
> java.math.BigDecimal" jdbcType="DECIMAL" mode="INOUT"/>
>
> </parameterMap>
>
>
>
> <procedure id="bedsCalcFee" parameterMap="bedsCalcFeeParam"
> resultClass="java.util.HashMap">
>
> {call proc_calculate_fee_opa (?,?,?,?,?,?)}
>
> </procedure>
>
>
>
>
>