ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khanna, Vijay \(GE, Corporate, consultant\)" <vijay.kha...@ge.com>
Subject RE: Stored Proc Issue in Ibatis 2.1.7 0 Stuck..
Date Wed, 16 Aug 2006 12:38:11 GMT
Thanks Viktor. I think, I understand now what was going wrong.
Appreciate it !
 
Thanks,
Vijay Khanna

________________________________

From: Viktor_X._Kopit@hud.gov [mailto:Viktor_X._Kopit@hud.gov] 
Sent: Tuesday, August 15, 2006 4:36 PM
To: user-java@ibatis.apache.org
Subject: Re: Stored Proc Issue in Ibatis 2.1.7 0 Stuck..



Below are code from 
1. sql-map-xxx. xml file 
2 Oracle stored procedure 
3. java implementation 

1//////////////////////////////////////////////////////////// 
<?xml version="1.0" encoding="UTF-8" ?> 
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
"http://www.ibatis.com/dtd/sql-map-2.dtd"> 
<sqlMap namespace="fee_list"> 
  <resultMap id="FeeListSPres"
class="irems.bean.ownerlist.ManagFeeList"> 
        <result property="seqNo" column="sequence_number"/> 
        <result property="feePer" column="mgmt_fee_percentage"/> 
        <result property="feeApprAmt" column="mgmt_fee_appr_amt"/> 
        <result property="feeGrossAmt" column="mgmt_fee_gross_amt"/> 
        <result property="caInd" column="mgmt_fee_capped_ind"/> 
        <result property="hhInd" column="mgmt_fee_hold_harmless_ind"/> 
        <result property="feeEffDateStr" column="mgmtFeeEffDateStr"/> 
        <result property="feeNotes" column="mgmt_fee_comment_text"/> 
        <result property="feeTypeName" column="mgmt_fee_type_name"/> 
    </resultMap> 
    
    
    <parameterMap id="FeeListSPpar" class="map" > 
        <parameter property="in_property_id" jdbcType="NUMERIC"
javaType="java.lang.Integer" mode="IN"/> 
        <parameter property="errorcode" jdbcType="NUMERIC"
javaType="java.lang.Integer" mode="OUT"/> 
        <parameter property="errormsg" jdbcType="VARCHAR"
javaType="java.lang.String" mode="OUT" /> 
        <parameter property="pa_cursor" jdbcType="ORACLECURSOR"
mode="OUT" resultMap="FeeListSPres"/> 
    </parameterMap> 
      
    <procedure id="getFeeListSP" parameterMap="FeeListSPpar"> 
         { call am_get_manag_fee_list_vk (?, ?, ?, ?) } 
    </procedure> 
    
    
</sqlMap> 

2///////////////////////////////////////////////////////////////////////
/////////////////// 
CREATE OR REPLACE procedure am_get_manag_fee_list_vk 
  (in_property_id in numeric, 
   errorcode out number, 
   errormsg out varchar2, 
   pa_cursor out pk_am_global.t_cursor) 
as 
   no_matching_data_found  exception; 
   invalid_sorting         exception; 
   invalid_input           exception; 
   no_input_parameter      exception; 
begin 
   errorcode := 0; 
   errormsg  := 'successfull'; 
   if (in_property_id is null) then 
     raise no_input_parameter; 
   end if; 
   if pa_cursor%isopen then 
   begin 
     errormsg := 'cursor is already opened ... must close it first'; 
     errorcode := -1; 
   end; 
   else begin 
       open pa_cursor for 
        select TO_CHAR(mf.sequence_number) sequence_number, 
      mf.mgmt_fee_percentage, mf.mgmt_fee_appr_amt,
mf.mgmt_fee_gross_amt, 
 
DECODE(mf.mgmt_fee_capped_ind,'Y','Yes','N','No','U','N/A','N/A')
mgmt_fee_capped_ind, 
 
DECODE(mf.mgmt_fee_hold_harmless_ind,'Y','Yes','N','No','U','N/A','N/A')
mgmt_fee_hold_harmless_ind, 
               TO_CHAR (mf.mgmt_fee_eff_date, 'MM/DD/YYYY')
mgmtFeeEffDateStr, 
      mf.mgmt_fee_comment_text, 
               mfr.mgmt_fee_type_name 
      from management_fee mf, management_fee_type_reference mfr 
        where mf.property_id = in_property_id 
          and mf.mgmt_fee_type_code = mfr.mgmt_fee_type_code; 
   end; 
  end if; 
  exception 
   when no_input_parameter   then 
    errormsg := 'input parameter should not be empty ... '; 
    errorcode := -3; 
   when others then 
    errorcode := sqlcode; 
    errormsg := substr(sqlerrm, 1, 200); 
end am_get_manag_fee_list_vk; 
/ 

3///////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////// 
 public ArrayList getManagFeeList( String propertyID ) 
                                         throws Exception { 
    String sqlMapConfigFile = SQLMapConfig.configFilePath; 
    SqlMapClient sqlMapClient = null; 
    ArrayList    result  = new ArrayList(); 
    Map map = new HashMap(); 
    map.put("in_property_id", new Integer(propertyID)); 
    try 
        { 
        
        Reader sqlMapConfigReader = Resources.getResourceAsReader(
sqlMapConfigFile ); 
            sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(
sqlMapConfigReader ); 
           sqlMapClient.queryForObject( "getFeeListSP", map ); 
            System.out.println("Error Code"); 
            System.out.println(map.get("errorcode")); 
            System.out.println("Error Message"); 
            System.out.println(map.get("errormsg")); 
            result = (ArrayList) map.get("pa_cursor"); 
           if (result == null){ 
           result = new ArrayList( ); 
           } 
          //debugList( "ArrayList getManagFeeList (String propertyID)",
result );             
           } 
        catch( SQLException e ) 
        { 
            e.printStackTrace(); 
            System.out.println( e.toString() ); 
        } 
        catch( IOException e ) 
        { 
            System.out.println( e ); 
          }   
        finally { 
        } 
    return result;       
  }   




	"Khanna, Vijay \(GE, Corporate, consultant\)"
<vijay.khanna@ge.com> 

08/15/2006 04:24 PM 
Please respond to user-java 


        
        To:        <user-java@ibatis.apache.org> 
        cc:        (bcc: Viktor X. Kopit/CIO/HHQ/HUD) 
        Subject:        Stored Proc Issue in Ibatis 2.1.7 0 Stuck..



Hello, 
  
We are migrating from Ibatis 1.x to 2.1.7. Nearly 20-30 projects. 
  
We use lot of stored procedures and relied on executeProcedure() API of
1.x,  which is no more present. 
  
I am getting error when trying to call procedure (simple ones & ones
returning Oracle Cursors). Always I get a NULL, even when using
queryForList() or queryForMap() or queryForObject(). We are really stuck
at this point.... 
  
SQLMAP 
------------- 

<parameterMap id="allocCount-params" class="java.util.HashMap"> 

<parameter property="errorFlag" jdbcType="INTEGER" mode="OUT" /> 

<parameter property="allocCount" jdbcType="INTEGER" mode="OUT" /> 

</parameterMap> 

<procedure id="getTodaysAllocationCount"
parameterMap="allocCount-params" resultClass="java.util.HashMap">{call
P_GET_ALLOC_COUNTS(?,?)}</procedure> 

 CODE 
---------- 
  
public Map executeStoredProcedure(String queryId, Object paramObject)
throws DBServiceException
{
 try
 {
  return (Map) sqlMapSession.queryForObject(queryId, paramObject,
    new HashMap());
 } catch (SQLException e)
 {
  e.printStackTrace();
  throw new DBServiceException(e);
 }
} 
  
Thanks, 
Vijay 




Mime
View raw message