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 Balaji <ba...@objectec.com> on 2006/01/11 19:50:35 UTC
Sybase proc with return value, results, IN & OUT params
I am trying to call a sybase stored proc which returns results set and a return value, has input paramters & output parameters.
I need to know how to to get the
1. return value ( 0 on success, -1 on failure)
2. out parms values - as_error, as_error_msg
3. Result set list ( if success)
Thanks,
Balaji
/******************************************************/
ERROR
/******************************************************/
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in unitTest/domain/f3721.xml.
--- The error occurred while applying a parameter map.
--- Check the F3721.GetAppsParam.
--- Check the results (failed to retrieve results).
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)
at unitTest.domain.DomainTest.main(DomainTest.java:41)
/*****************************************************************/
JAVA CODE:
/*****************************************************************/
public static Integer callProc() {
Map param = new HashMap();
param.put("value", new Integer(-1));
param.put("userid", "testuser");
//param.put("userid", ""); //for this too
try {
List list = sqlMap.queryForList("getApps", param);
} catch (SQLException sqe) {
sqe.printStackTrace();
}
System.out.println("as_error=" + param.get("as_error"));
System.out.println("as_error_msg=" + param.get("as_error_msg"));
return (Integer)param .get("value");
}
/*****************************************************************/
STORED PROCEDURE:
/*****************************************************************/
CREATE PROCEDURE proc_get_applist
(
@as_userid typ_ch_userid,
@as_error char(5) OUTPUT,
@as_error_msg varchar(255) OUTPUT
)
AS
--Check key values
IF @as_userid IS NULL OR @as_userid = ""
BEGIN
SELECT @as_error = '-1'
SELECT @as_error_msg = "Key values cannot be null."
RETURN 1
END
SELECT t1.id_app, t1.cd_edit_status,
FROM application t1
RETURN 0
/*********************************************************************************/
SQL MAP
/*****************************************************************************
<parameterMap id="GetAppsParam" class="java.util.HashMap">
<parameter property="value" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property ="userid" jdbcType="VARCHAR" javaType="string" mode="IN"/>
<parameter property ="as_error" jdbcType="VARCHAR" javaType="string" mode="INOUT"/>
<parameter property ="as_error_msg" jdbcType="VARCHAR" javaType="string" mode="INOUT"/>
</parameterMap>
<resultMap id="getAppsResult" class="java.util.HashMap">
<result property="id_app" column="ID_APP"/>
<result property="dt_create" column="DT_CREATE"/>
<result property="cd_edit_status" column="CD_EDIT_STATUS" nullValue=""/>
</resultMap>
<procedure id="getApps" parameterMap="GetAppsParam" resultClass="getAppsResult" >
{ ? = call proc_get_applist(?,?,?)}
</procedure>
Re: Sybase proc with return value, results, IN & OUT params
Posted by Clinton Begin <cl...@gmail.com>.
Unfortunately, if the results are inconsistent, iBATIS will have trouble
with it. My suggestion would be to wrap the oddball stored proc with
another proc that supplies a more predictable API and behaviour.
Cheers,
Clinton
On 1/12/06, Balaji <ba...@objectec.com> wrote:
>
>
> It works in JDBC and code is shown below. It prints out all -ie. error
> code, message, return value and either sql error message ( no results) or
> result set
> Note: It also works (ie returns all desired values as in JDBC ) in ibatis
> only when the query succeeds ie have result sets and passes any paramter
> condition checks. But fails/exception when the proc call does not
> pass parameters check or no results - it then DOES NOT return the OUT
> param values and Return Value set in the proc.
>
> Thanks,
> Balaji
>
> ....
> Connection con = null;
> CallableStatement stmt = null;
> ResultSet result = null;
> try {
> InitialContext ctx = new InitialContext();
> DataSource ds = (DataSource) ctx.lookup("java:comp/env/caresDB");
> con = ds.getConnection();
> stmt = con.prepareCall("{? = CALL proc_get_applist(?,?,?)}");
> stmt.registerOutParameter(1, Types.INTEGER);
> stmt.setString(2, "testuser");
> stmt.registerOutParameter(3, Types.VARCHAR);
> stmt.registerOutParameter(4, Types.VARCHAR);
> result = stmt.executeQuery();
> } catch (NamingException nex) {
> System.err.println("Naming Error: " + nex.getMessage());
> } catch (SQLException sqlex) {
> System.err.println("SQL Error: " + sqlex.getMessage());
> System.out.println("Return Value:" + stmt.getString(1));
> System.out.println("ErrorCode:" + stmt.getString(3));
> System.out.println("Error Message:" + stmt.getString(4));
> }
> ...
>
> ----- Original Message -----
> *From:* Clinton Begin <cl...@gmail.com>
> *To:* user-java@ibatis.apache.org ; Balaji <ba...@objectec.com>
> *Sent:* Thursday, January 12, 2006 9:02 AM
> *Subject:* Re: Sybase proc with return value, results, IN & OUT params
>
>
> This may be one of those cases where you have to write it in JDBC....even
> as just a test to see if it works with your database driver. Once that's
> done, you can post it here and we can see if there's anything iBATIS is
> doing to cause the problem.
>
> At first glance, I'm wondering if it would even work with plain old JDBC
> (it should, but the driver may not support it)...
>
> Cheers,
> Clinton
>
> On 1/11/06, Balaji <ba...@objectec.com> wrote:
> >
> > I am trying to call a sybase stored proc which returns results set and a
> > return value, has input paramters & output parameters.
> >
> > I need to know how to to get the
> > 1. return value ( 0 on success, -1 on failure)
> > 2. out parms values - as_error, as_error_msg
> > 3. Result set list ( if success)
> >
> > Thanks,
> > Balaji
> >
> >
> > /******************************************************/
> > ERROR
> > /******************************************************/
> >
> > com.ibatis.common.jdbc.exception.NestedSQLException:
> > --- The error occurred in unitTest/domain/f3721.xml.
> > --- The error occurred while applying a parameter map.
> > --- Check the F3721.GetAppsParam.
> > --- Check the results (failed to retrieve results).
> > --- Cause: java.lang.NullPointerException
> > Caused by: java.lang.NullPointerException
> > at
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> > (GeneralStatement.java:188)
> > at
> > com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> > (GeneralStatement.java:123)
> > at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExecutorDelegate.java:610)
> > at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> > SqlMapExecutorDelegate.java:584)
> > at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> > SqlMapSessionImpl.java:101)
> > at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> > SqlMapClientImpl.java:78)
> > at unitTest.domain.DomainTest.main(DomainTest.java:41)
> >
> > /*****************************************************************/
> > JAVA CODE:
> > /*****************************************************************/
> >
> > public static Integer callProc() {
> > Map param = new HashMap();
> > param.put("value", new Integer(-1));
> > param.put("userid", "testuser");
> > //param.put("userid", ""); //for this too
> >
> > try {
> > List list = sqlMap.queryForList("getApps", param);
> > } catch (SQLException sqe) {
> > sqe.printStackTrace();
> > }
> >
> > System.out.println("as_error=" + param.get("as_error"));
> > System.out.println("as_error_msg=" + param.get("as_error_msg"));
> > return (Integer)param .get("value");
> > }
> >
> > /*****************************************************************/
> > STORED PROCEDURE:
> > /*****************************************************************/
> >
> > CREATE PROCEDURE proc_get_applist
> >
> > (
> > @as_userid typ_ch_userid,
> > @as_error char(5) OUTPUT,
> > @as_error_msg varchar(255) OUTPUT
> > )
> >
> > AS
> >
> > --Check key values
> > IF @as_userid IS NULL OR @as_userid = ""
> > BEGIN
> > SELECT @as_error = '-1'
> > SELECT @as_error_msg = "Key values cannot be null."
> > RETURN 1
> > END
> >
> >
> > SELECT t1.id_app, t1.cd_edit_status,
> > FROM application t1
> >
> > RETURN 0
> >
> >
> > /*********************************************************************************/
> > SQL MAP
> >
> > /*****************************************************************************
> > <parameterMap id="GetAppsParam" class="java.util.HashMap">
> > <parameter property="value" jdbcType="INTEGER" javaType="
> > java.lang.Integer" mode="OUT"/>
> > <parameter property ="userid" jdbcType="VARCHAR" javaType="string"
> > mode="IN"/>
> > <parameter property ="as_error" jdbcType="VARCHAR"
> > javaType="string" mode="INOUT"/>
> > <parameter property ="as_error_msg" jdbcType="VARCHAR"
> > javaType="string" mode="INOUT"/>
> > </parameterMap>
> >
> >
> > <resultMap id="getAppsResult" class="java.util.HashMap">
> > <result property="id_app" column="ID_APP"/>
> > <result property="dt_create" column="DT_CREATE"/>
> > <result property="cd_edit_status" column="CD_EDIT_STATUS"
> > nullValue=""/>
> > </resultMap>
> >
> >
> > <procedure id="getApps" parameterMap="GetAppsParam"
> > resultClass="getAppsResult" >
> > { ? = call proc_get_applist(?,?,?)}
> > </procedure>
> >
> >
> >
> >
>
>
Re: Sybase proc with return value, results, IN & OUT params
Posted by Balaji <ba...@objectec.com>.
It works in JDBC and code is shown below. It prints out all -ie. error code, message, return value and either sql error message ( no results) or result set
Note: It also works (ie returns all desired values as in JDBC ) in ibatis only when the query succeeds ie have result sets and passes any paramter condition checks. But fails/exception when the proc call does not pass parameters check or no results - it then DOES NOT return the OUT param values and Return Value set in the proc.
Thanks,
Balaji
....
Connection con = null;
CallableStatement stmt = null;
ResultSet result = null;
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/caresDB");
con = ds.getConnection();
stmt = con.prepareCall("{? = CALL proc_get_applist(?,?,?)}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.setString(2, "testuser");
stmt.registerOutParameter(3, Types.VARCHAR);
stmt.registerOutParameter(4, Types.VARCHAR);
result = stmt.executeQuery();
} catch (NamingException nex) {
System.err.println("Naming Error: " + nex.getMessage());
} catch (SQLException sqlex) {
System.err.println("SQL Error: " + sqlex.getMessage());
System.out.println("Return Value:" + stmt.getString(1));
System.out.println("ErrorCode:" + stmt.getString(3));
System.out.println("Error Message:" + stmt.getString(4));
}
...
----- Original Message -----
From: Clinton Begin
To: user-java@ibatis.apache.org ; Balaji
Sent: Thursday, January 12, 2006 9:02 AM
Subject: Re: Sybase proc with return value, results, IN & OUT params
This may be one of those cases where you have to write it in JDBC....even as just a test to see if it works with your database driver. Once that's done, you can post it here and we can see if there's anything iBATIS is doing to cause the problem.
At first glance, I'm wondering if it would even work with plain old JDBC (it should, but the driver may not support it)...
Cheers,
Clinton
On 1/11/06, Balaji <ba...@objectec.com> wrote:
I am trying to call a sybase stored proc which returns results set and a return value, has input paramters & output parameters.
I need to know how to to get the
1. return value ( 0 on success, -1 on failure)
2. out parms values - as_error, as_error_msg
3. Result set list ( if success)
Thanks,
Balaji
/******************************************************/
ERROR
/******************************************************/
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in unitTest/domain/f3721.xml.
--- The error occurred while applying a parameter map.
--- Check the F3721.GetAppsParam.
--- Check the results (failed to retrieve results).
--- Cause: java.lang.NullPointerException
Caused by: java.lang.NullPointerException
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:610)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:584)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:101)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:78)
at unitTest.domain.DomainTest.main(DomainTest.java:41)
/*****************************************************************/
JAVA CODE:
/*****************************************************************/
public static Integer callProc() {
Map param = new HashMap();
param.put("value", new Integer(-1));
param.put("userid", "testuser");
//param.put("userid", ""); //for this too
try {
List list = sqlMap.queryForList("getApps", param);
} catch (SQLException sqe) {
sqe.printStackTrace();
}
System.out.println("as_error=" + param.get("as_error"));
System.out.println("as_error_msg=" + param.get("as_error_msg"));
return (Integer)param .get("value");
}
/*****************************************************************/
STORED PROCEDURE:
/*****************************************************************/
CREATE PROCEDURE proc_get_applist
(
@as_userid typ_ch_userid,
@as_error char(5) OUTPUT,
@as_error_msg varchar(255) OUTPUT
)
AS
--Check key values
IF @as_userid IS NULL OR @as_userid = ""
BEGIN
SELECT @as_error = '-1'
SELECT @as_error_msg = "Key values cannot be null."
RETURN 1
END
SELECT t1.id_app, t1.cd_edit_status,
FROM application t1
RETURN 0
/*********************************************************************************/
SQL MAP
/*****************************************************************************
<parameterMap id="GetAppsParam" class="java.util.HashMap">
<parameter property="value" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
<parameter property ="userid" jdbcType="VARCHAR" javaType="string" mode="IN"/>
<parameter property ="as_error" jdbcType="VARCHAR" javaType="string" mode="INOUT"/>
<parameter property ="as_error_msg" jdbcType="VARCHAR" javaType="string" mode="INOUT"/>
</parameterMap>
<resultMap id="getAppsResult" class="java.util.HashMap">
<result property="id_app" column="ID_APP"/>
<result property="dt_create" column="DT_CREATE"/>
<result property="cd_edit_status" column="CD_EDIT_STATUS" nullValue=""/>
</resultMap>
<procedure id="getApps" parameterMap="GetAppsParam" resultClass="getAppsResult" >
{ ? = call proc_get_applist(?,?,?)}
</procedure>
Re: Sybase proc with return value, results, IN & OUT params
Posted by Clinton Begin <cl...@gmail.com>.
This may be one of those cases where you have to write it in JDBC....even as
just a test to see if it works with your database driver. Once that's done,
you can post it here and we can see if there's anything iBATIS is doing to
cause the problem.
At first glance, I'm wondering if it would even work with plain old JDBC (it
should, but the driver may not support it)...
Cheers,
Clinton
On 1/11/06, Balaji <ba...@objectec.com> wrote:
>
> I am trying to call a sybase stored proc which returns results set and a
> return value, has input paramters & output parameters.
>
> I need to know how to to get the
> 1. return value ( 0 on success, -1 on failure)
> 2. out parms values - as_error, as_error_msg
> 3. Result set list ( if success)
>
> Thanks,
> Balaji
>
>
> /******************************************************/
> ERROR
> /******************************************************/
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in unitTest/domain/f3721.xml.
> --- The error occurred while applying a parameter map.
> --- Check the F3721.GetAppsParam.
> --- Check the results (failed to retrieve results).
> --- Cause: java.lang.NullPointerException
> Caused by: java.lang.NullPointerException
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
> (GeneralStatement.java:188)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
> (GeneralStatement.java:123)
> at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:610)
> at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(
> SqlMapExecutorDelegate.java:584)
> at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(
> SqlMapSessionImpl.java:101)
> at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(
> SqlMapClientImpl.java:78)
> at unitTest.domain.DomainTest.main(DomainTest.java:41)
>
> /*****************************************************************/
> JAVA CODE:
> /*****************************************************************/
>
> public static Integer callProc() {
> Map param = new HashMap();
> param.put("value", new Integer(-1));
> param.put("userid", "testuser");
> //param.put("userid", ""); //for this too
>
> try {
> List list = sqlMap.queryForList("getApps", param);
> } catch (SQLException sqe) {
> sqe.printStackTrace();
> }
>
> System.out.println("as_error=" + param.get("as_error"));
> System.out.println("as_error_msg=" + param.get("as_error_msg"));
> return (Integer)param .get("value");
> }
>
> /*****************************************************************/
> STORED PROCEDURE:
> /*****************************************************************/
>
> CREATE PROCEDURE proc_get_applist
>
> (
> @as_userid typ_ch_userid,
> @as_error char(5) OUTPUT,
> @as_error_msg varchar(255) OUTPUT
> )
>
> AS
>
> --Check key values
> IF @as_userid IS NULL OR @as_userid = ""
> BEGIN
> SELECT @as_error = '-1'
> SELECT @as_error_msg = "Key values cannot be null."
> RETURN 1
> END
>
>
> SELECT t1.id_app, t1.cd_edit_status,
> FROM application t1
>
> RETURN 0
>
>
> /*********************************************************************************/
> SQL MAP
>
> /*****************************************************************************
> <parameterMap id="GetAppsParam" class="java.util.HashMap">
> <parameter property="value" jdbcType="INTEGER" javaType="
> java.lang.Integer" mode="OUT"/>
> <parameter property ="userid" jdbcType="VARCHAR" javaType="string"
> mode="IN"/>
> <parameter property ="as_error" jdbcType="VARCHAR" javaType="string"
> mode="INOUT"/>
> <parameter property ="as_error_msg" jdbcType="VARCHAR"
> javaType="string" mode="INOUT"/>
> </parameterMap>
>
>
> <resultMap id="getAppsResult" class="java.util.HashMap">
> <result property="id_app" column="ID_APP"/>
> <result property="dt_create" column="DT_CREATE"/>
> <result property="cd_edit_status" column="CD_EDIT_STATUS"
> nullValue=""/>
> </resultMap>
>
>
> <procedure id="getApps" parameterMap="GetAppsParam"
> resultClass="getAppsResult" >
> { ? = call proc_get_applist(?,?,?)}
> </procedure>
>
>
>
>