iBATIS DataMapper
Added by Clinton Begin, last edited by Jeff Butler on Sep 20, 2006  (view change)
Labels: 
(None)


OK, this is in the User's Guide, but since you apparently did not read it, here it is again.

Stored procedures are supported via the <procedure> statement element. The following example shows how a stored procedure would be used with output parameters.

procedure.xml
<parameterMap id="swapParameters" class="map" >
   <parameter property="email1" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
   <parameter property="email2" jdbcType="VARCHAR" javaType="java.lang.String" mode="INOUT"/>
   </parameterMap>
   
   <procedure id="swapEmailAddresses" parameterMap="swapParameters" >
   {call swap_email_address (?, ?)}
   </procedure>

Calling the above procedure would swap two email addresses between two columns (database table) and also in the parameter object (Map). The parameter object is only modified if the parameter mappings mode attribute is set to ?INOUT? or ?OUT?. Otherwise they are left unchanged. Obviously immutable parameter objects (e.g. String) cannot be modified.

Note! Always be sure to use the standard JDBC stored procedure syntax. See the JDBC CallableStatement documentation for more information.

What SqlMapClient Method Should I Use?

It depends. Here is some help...

If your procedure returns a result set (not a result set in an OUT parameter, but a result set from the procedure itself), then use queryForList() or queryForObject(). Use queryForList() if you expect more than one result object, or queryForObject() if you expect only one result Object.

If your procedure returns a result set AND updates data, then you should also configure your <transactionManager> with the attribute commitRequired="true".

If your procedure does not return result sets, or only returns result sets in OUT parameters, then use the update() method.

Changing the call to something like this will not work:

{
call swap_email_address (?, ?)
}

It looks like it has to be on one line.

Posted by Anonymous at May 31, 2005 15:04

yes,in 2.0 ,we can use in this way.
but in 1.3.1 ,how we can handle it ? It seems that the return value stored procedure is not supported in 1.3.1.

Posted by Anonymous at Jun 30, 2005 06:48

in java code,you should be like this:

Map map=new HashMap();
map.put("email1","abc@com");
map.put("email2","123@net");
dao.query("swapEmailAddresses",map);//insert parameters to procedure
String email1=(String)map.get("email1");//get return result 1
String email2=(String)map.get("email1");//get return result 2

Posted by guo_q at Nov 30, 2005 21:04

I have a Oracle store procedure which has input & output parameters as Oracle user defined data type Arrays (described below), Is it possible invoke the stored proc using ibatis ? if yes how to achieve it ?

PROCEDURE UPDATE_DATA(INPUT_DATA IN INPUT_ARRAY,ERROR_OUTPUT out ERROR_ARRAY)

where

 INPUT_ARRAY &  ERROR_ARRAY are as follows :

CREATE OR REPLACE type INPUT_ARRAY AS TABLE OF INPUT_REC;

CREATE OR REPLACE type ERROR_ARRAY AS TABLE OF ERROR_REC;

where INPUT_REC & ERROR_REC are as follows :

create or replace TYPE INPUT_REC AS OBJECT(line NUMBER,acc NUMBER(20), id VARCHAR2(100)) ;

create or replace TYPE ERROR_REC AS OBJECT(line NUMBER,acc NUMBER(20), errorText VARCHAR2(4000)) ;

 

Posted by Sachin Raj at Feb 05, 2006 23:31

Hi,

I want to execte this Oracle stored procedure and am trying to pass as parameter a HashMap:
procedure newCert(iDigid in number, oCertId out number) is
begin
oCertId := newCert(iDigid);
end;

The relevant parts in the sqlmap.xml are:
<parameterMap id="combinatieVerzoek" class="map">
<parameter property="ndigid" javaType="long" mode="IN" jdbcType="NUMBER" />
<parameter property="ncertid" javaType="long" mode="INOUT" jdbcType="NUMBER" />
</parameterMap>
and
<procedure id="newConcept" parameterMap="combinatieVerzoek">

Unknown macro: {call cle_wsp.newCert(#ndigiD#,#ncertiD#)}

</procedure>

I call it this way:
Map mapConcept = new HashMap();
mapConcept.put("ndigiD",new Long(getProductLandCombsRequest.getNDigiD()));
mapConcept.put("ncertiD",new Long(0));
sqlMap.update("newConcept",mapConcept);

The original example applies to a DAO but it is not clear to me how I should call a stored procedure from a sqlMap since the sqlMap-API only offers insert, update, delete and a lot of queryForObjects and not a simple query()-method.
Every attempt fails with:
— The error occurred while executing update procedure.
— Check the

.
— Check the output parameters (register output parameters failed).

What call shoud I use for the sqlMap?

Ben

Posted by Ben Engbers at May 22, 2006 06:30

Hi all!

I've carefully read this thread.
Basically, it's clear how to describe a call to a stored procedure in the XML map files for iBatis.
After all we have to call them from the Java code, f.i.

Which method of the SqlMapClient class should be called to run a remote procedure?

Section "Executing Statements via the SqlMapClient API", p. 43, of the iBATIS-SqlMaps-2.pdf guide does NOT mention any method to call procedures.

Neither do the identifiers and the signatures of the methods available via the inherited interfaces.

Do I miss anything?

Thanks in advance for any help.
ciao

Cesare

Posted by Cesare.Zecca at Sep 19, 2006 10:14

Hi,
I too tried the way explained using iBatis 2.2, with following error,
Caused by: org.xml.sax.SAXParseException: Attribute "resultMap" must be declared for element type "parameter".

I ve allready defined resultMap, then why am I getting this error?
Am I missing somthing?

Following is code snippet :

My Java src
=========================================
map.put("result","");
map.put("input-param","1");
sqlMap.queryForObject("myFunc", map);
System.out.println("reached here");
ResultSet result = (ResultSet)map.get("result");
System.out.println(result.next() );

sqlMap
==============

<resultMap class="ResultData" id="result-mapping">
<result property="result-param1" column="col2" />
<result property="result-param2" column="col2" />
</resultMap>

<parameterMap id="myMap" class="map">
<parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="result-mapping"/>
</parameterMap>

<procedure id="getStringParameter" parameterMap="myMap">
{? = call myPkg.myFunction(100,200) }
</procedure>

Posted by amonpara at Oct 02, 2006 15:35

.

Posted by amonpara at Oct 02, 2006 15:37

I am using Spring/Ibatis and Sybase.I have a sybase stored procedure that needs to return a result set.The input parameter for the procedure is a string containing a date.I tried following:

<procedure id="getNewOrderMsgsBySearchDate" parameterClass="java.lang.String resultMap="msgAttrResult">

Unknown macro: {call sp_getNewOrdersBySearchDate(#searchDate#)}

</procedure>

where result map is:

<resultMap id="msgAttrResult" class="msgAttr">
<result property="msgId" column="id_ld_msg"/>
<result property="attributeName" column="nm_ld_msg_attr"/>
<result property="attributeValue" column="tx_ld_msg_attr_value"/>
</resultMap>

But I get the error :
[java] — The error occurred in sqlmap/FixMessage.xml.
[java] — The error occurred while applying a parameter map.
[java] — Check the getNewOrderMsgsBySearchDate-InlineParameterMap.
[java] — Check the results (failed to retrieve results).
[java] — Cause: java.lang.NullPointerException; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

I have two questions: Can we pass the input parameters to the stored procedures in Ibatis like I have done here.
Secondly can the output parameter(output of 'select') be taken as result set.

Posted by hema x iyer at Oct 24, 2006 09:22

I am trying to call the stored procedures in Ibatis 2.2 too and I am getting the same error as amonpara. Were you able to find a solution for the problem.

I am using Oracle and have to handle oracle cursors. The sqlmap config is as below:
Please help...

<resultMap id="planRefmapping" class="com.admin.DTO.Plan" >
<result property="planNumber" column="PLANNO" />
<result property="empName" column="ENAME" />
</resultMap>

<parameterMap id="getAllPlanParameters" class="map" >
<parameter property="cClnt" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="curPlan" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" resultMap="planRefmapping"/>
</parameterMap>

<procedure id="getAllPlan" parameterMap="getAllPlanParameters" >

Unknown macro: { call DBA.WebLinks.CWSWLGetAllPlan(?,?) }

</procedure>

Alternatively, I also tried the solution for Ibatis 2.0. Everything goes well, but then the result I am getting (which is a list) is always of size 10 and all elements in the list is empty.
The sqlmap for this is:

<parameterMap id="getAllPlanParameters" class="map" >
<parameter property="cClnt" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="curPlan" jdbcType="ORACLECURSOR" mode="OUT" />
</parameterMap>

<procedure id="getAllPlan" parameterMap="getAllPlanParameters" resultMap="planRefmapping">

</procedure>

Any errors in my config or any other ideas or suggestions on why this is not working is welcome. I need help...Please....

Thanks
Mag

Posted by Mahitha Krishnan at Dec 13, 2006 14:39

if the parameter is an Oracle object type, what can i do ?

for exeample:

create or replace type warrant.test_rshan_typ as object(id VARCHAR2(10),dat VARCHAR2(50));

create or replace type warrant.test_rshan_typ_arr as table of warrant.test_rshan_typ;

create or replace procedure warrant.shan_test_array(arr in test_rshan_typ_arr,isSuccess out varchar2 )
as
begin
for r in 1..arr.count loop
insert into test_rshan(id, dat) values(arr(r).id,arr(r).dat);
if MOD(r,20)=0 then --for transaction control...
commit;
end if;

end loop;
commit;
isSuccess:='success';
exception
when others then
rollback;
isSuccess:='unsuccess';
end shan_test_array;

Posted by Yan Liang at May 08, 2007 21:52

Hi,

 How do I call stored procedures in iBatis 3?

 Thank you.

Posted by Maciej Radochonski at Oct 25, 2009 23:50

Good question. I just figured out a solution that works for Oracle.

XML:

<update id="callMyProc" statementType="CALLABLE" parameterType="com.myCompany.MyObject">
      call myschema.myProc
      (
      #{field1, javaType=String, jdbcType=VARCHAR},
      #{field2, javaType=String, jdbcType=VARCHAR},
      #{field3, javaType=String, jdbcType=VARCHAR}
      )
      </update>

Java mapper:

void callMyProc(MyObject myObject);

Like any other iBatis DML statement, instead of an object, you can pass in individual params if that suits your situation better.

My proc doesn't return anything, so I used an <update> statement. If yours returns results, then you may have to use a <select> statement.

Hope this helps.

Posted by Guy Rouillier at Oct 27, 2009 23:13; last updated at Oct 29, 2009 15:50

Hey this tip helps a lot, I would definitely include this on my term paper research.

Posted by sophiegreen at Oct 29, 2009 16:42

Hi,
How do i call a stored procedure which takes one IN and one OUT parameter .

Regards,
Vishal

Posted by Vishal at Jan 06, 2010 02:44
Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators