ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Sybase proc with return value, results, IN & OUT params
Date Fri, 13 Jan 2006 16:38:24 GMT
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 <balaji@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 <clinton.begin@gmail.com>
> *To:* user-java@ibatis.apache.org ; Balaji <balaji@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 <balaji@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>
> >
> >
> >
> >
>
>

Mime
View raw message