RefCursor result mapping fails
------------------------------
Key: IBATIS-453
URL: https://issues.apache.org/jira/browse/IBATIS-453
Project: iBatis for Java
Issue Type: Bug
Components: SQL Maps
Affects Versions: 2.2.0
Reporter: adamb
http://opensource.atlassian.com/confluence/oss/pages/viewpage.action?pageId=5653
I am following this example, except that I am mapping back to a map rather than
a java object.
The result map expects the column names from the query, not from the definition
of the ref cursor.
The ResultSet in UnknownTypeHandler, debugged using Eclipse:
String result="";
for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
result+=String.format("(%s)%s\n",i,rs.getMetaData().getColumnName(i));
}
return result;
shows the following columns in the result set:
(1)CLIENT_NO
(2)MSD_NAME
(3)MSD_BIRTH_DATE
(4)SERVICE_SHORT_CODE
(5)SERVICE_STATUS_DESC
(6)SERVICE_START_DATE
(7)SERVICE_END_DATE
(8)MATCH_CASE_ID
(9)MATCH_RUN_ID
(10)MATCH_RUN_TYPE_DESC
(11)MATCH_RUN_DATE
(12)ASSIGNMENT_TYPE_ID
(13)URGENT
(14)SPECIAL_BENEFIT
(15)HIGH_EARNINGS
(16)ASSIGNED_USER_NAME
(17)LAST_EVENT_DETAILS
(18)CASE_RECORD_STATUS_DESC
(19)PENDING_EVENT_DETAILS
(20)CASEWHENMC.CASE_STATUS_CODE=:B10ANDMC.ASSIGNMENT_TYPE_ID!=:B5THENMR.OOT_TO_OPEN_DATEWHENMC.CASE_STATUS_CODEIN(:B9,:B8,:B7,:B6,:B11)ANDMC.ASSIGNMENT_TYPE_ID!=:B5THENMR.OOT_NO_OUTCOME_DATEWHENMC.CASE_STATUS_CODE=:B10ANDMC.ASSIGNMENT_TYPE_ID=:B5THENMR.ABC_OO
(21)DISTRICT_NAME
(22)GENDER
(23)UNDER_INVESTIGATION
(24)COMMISSION_SALES
(25)CONTACT_869A
(26)MULTI_CLIENT
(27)LINK_ID
(28)MATCHED_SERVICES
(29)MATCHED_NAME
(30)NVL(MREC.MATCHED_BIRTH_DATE,MC.MSD_BIRTH_DATE)
(31)RELATION_IND
(32)OS_PENSION
(33)DPB_CSI
(34)MATCH_IND
(35)EMP_LEGAL_ACTION
(36)MP
(37)MATCH_IND_2
(38)MATCH_LEVEL
(39)AGENCY_DETAIL_TYPE1
(40)AGENCY_IND1
(41)AGENCY_REFERENCE2
(42)AGENCY_REFERENCE1
(43)AGENCY_DETAIL_TYPE2
(44)REL
(45)STDT
However the definition is:
TYPE MatchCase IS RECORD (
client_no ndm_match_case.client_no%TYPE,
msd_name VARCHAR2(100),
msd_birth_date ndm_match_case.msd_birth_date%TYPE,
service_short_code ndm_service.service_short_code%TYPE,
service_status_desc ndm_service_status.service_status_desc%TYPE,
service_start_date ndm_match_case.service_start_date%TYPE,
service_end_date ndm_match_case.service_end_date%TYPE,
match_case_id ndm_match_case.match_case_id%TYPE,
match_run_id ndm_match_case.match_run_id%TYPE,
match_run_type_desc ndm_match_run_type.match_run_type_desc%TYPE,
match_run_date ndm_match_run.match_run_date%TYPE,
assignment_type_id ndm_match_case.assignment_type_id%TYPE,
urgent VARCHAR2(3),
special_benefit VARCHAR2(2),
high_earnings VARCHAR2(2),
assigned_name VARCHAR2(121),
last_event_details VARCHAR2(100),
case_record_status_desc
ndm_case_record_status.case_record_status_desc%TYPE,
pending_event_details VARCHAR2(100),
milestone_date ndm_match_run.oot_to_open_date%TYPE,
district_name ndm_district.district_name%TYPE,
gender VARCHAR2(10),
under_investigation VARCHAR2(3),
commission_sales VARCHAR2(3),
contact_869a VARCHAR2(5),
multi_client VARCHAR2(1),
link_id ndm_match_case.link_id%TYPE,
matched_services VARCHAR2(100),
matched_name VARCHAR2(122),
matched_birth_date DATE,
relation_ind ndm_match_case.relation_ind%TYPE,
os_pension VARCHAR2(3),
dpb_csi VARCHAR2(3),
match_ind ndm_match_record.match_ind%TYPE,
emp_legal_action VARCHAR2(2),
mp VARCHAR2(3),
match_ind_2 ndm_match_record.match_ind%TYPE,
match_level ndm_match_record.match_level%TYPE,
agency_detail_type1 ndm_match_record.agency_detail_type1%TYPE,
agency_ind1 ndm_match_record.agency_ind1%TYPE,
agency_reference2 ndm_match_record.agency_reference2%TYPE,
agency_reference1 ndm_match_record.agency_reference1%TYPE,
agency_detail_type2 ndm_match_record.agency_detail_type2%TYPE,
rel VARCHAR2(5),
stdt VARCHAR2(5)
);
TYPE MatchCaseCur IS REF CURSOR RETURN MatchCase;
As you can see, if I try to get column "assigned_name", "milestone_date", or
"matched_birth_date" it will fail.
SQLMAP:
<resultMap id="viewCaseCaseHeaderBaseQueryResultMap"
class="java.util.HashMap">
<result property="clientNo" column="CLIENT_NO"/>
<result property="multiClient" column="MULTI_CLIENT"/>
<result property="msdName" column="MSD_NAME"/>
<result property="linkId" column="LINK_ID"/>
<result property="agencyDetailType1"
column="AGENCY_DETAIL_TYPE1"/>
<result property="agencyInd1" column="AGENCY_IND1"/>
<result property="agencyReference2" column="AGENCY_REFERENCE2"/>
<result property="agencyReference1" column="AGENCY_REFERENCE1"/>
<result property="agencyDetailType2"
column="AGENCY_DETAIL_TYPE2"/>
<result property="matchedServices" column="MATCHED_SERVICES"/>
<result property="mp" column="MP"/>
<result property="matchedName" column="MATCHED_NAME"/>
<result property="matchedBirthDate"
column="MATCHED_BIRTH_DATE"/>
<result property="relationInd" column="RELATION_IND"/>
<result property="msdBirthDate" column="MSD_BIRTH_DATE"/>
<result property="serviceShortCode"
column="SERVICE_SHORT_CODE"/>
<result property="serviceStatusDesc"
column="SERVICE_STATUS_DESC"/>
<result property="serviceStartDate"
column="SERVICE_START_DATE"/>
<result property="serviceEndDate" column="SERVICE_END_DATE"/>
<result property="districtName" column="DISTRICT_NAME"/>
<result property="gender" column="GENDER"/>
<result property="matchCaseId" column="MATCH_CASE_ID"/>
<result property="matchRunTypeDesc"
column="MATCH_RUN_TYPE_DESC"/>
<result property="matchRunId" column="MATCH_RUN_ID"/>
<result property="matchRunDate" column="MATCH_RUN_DATE"/>
<result property="assignmentTypeId"
column="ASSIGNMENT_TYPE_ID"/>
<result property="matchInd2" column="MATCH_IND_2"/>
<result property="matchLevel" column="MATCH_LEVEL"/>
<result property="osPension" column="OS_PENSION"/>
<result property="dpbCsi" column="DPB_CSI"/>
<result property="urgent" column="URGENT"/>
<result property="specialBenefit" column="SPECIAL_BENEFIT"/>
<result property="highEarnings" column="HIGH_EARNINGS"/>
<result property="rel" column="REL"/>
<result property="assignedName" column="ASSIGNED_NAME"/>
<result property="lastEventDetails"
column="LAST_EVENT_DETAILS"/>
<result property="matchInd" column="MATCH_IND"/>
<result property="contact869a" column="CONTACT_869A"/>
<result property="commissionSales" column="COMMISSION_SALES"/>
<result property="underInvestigation"
column="UNDER_INVESTIGATION"/>
<result property="empLegalAction" column="EMP_LEGAL_ACTION"/>
<result property="stdt" column="STDT"/>
<result property="caseRecordStatusDesc"
column="CASE_RECORD_STATUS_DESC"/>
<result property="pendingEventDetails"
column="PENDING_EVENT_DETAILS"/>
<result property="milestoneDate" column="MILESTONE_DATE"/>
</resultMap>
<parameterMap id="viewCaseCaseHeaderBaseQueryParameterMap" class="map">
<parameter property="result" javaType="java.sql.ResultSet"
jdbcType="ORACLECURSOR" mode="OUT"
resultMap="viewCaseCaseHeaderBaseQueryResultMap"/>
<parameter property="pMatchCaseId"/>
</parameterMap>
<procedure id="viewCaseCaseHeaderBaseQuery"
parameterMap="viewCaseCaseHeaderBaseQueryParameterMap"
>{call pkg_match_case.get_match_case(?,?)}</procedure>
If I change the sqlMap from ORACLECURSOR to MATCHCASECUR I get:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in
nz/govt/msd/aimos/viewCase/server/CaseHeaderSqlMap.xml.
--- The error occurred while executing query procedure.
--- Check the {call pkg_match_case.get_match_case(?,?)}.
--- Check the output parameters (register output parameters failed).
--- Cause: java.sql.SQLException: Invalid column type
Caused by: java.sql.SQLException: Invalid column type
Note: This is just one of thousands of autogenerated mappings, so I can't just
add aliases to every query in the database, or rename the map aliases.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
