ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jan Vissers <Jan.Viss...@cumquat.nl>
Subject Re: IBatis and Oracle stored functions
Date Wed, 05 Jan 2005 06:26:47 GMT
Thanks for this complete example! Wondering about two other things though.

1. (Maybe this is a lame question) I ran into serious trouble using a 
packaged function having not only *output* parameters

     function get_some( p_i_what     in varchar2
                        , p_i_howmany  in number
                        )
       return   <some (REF) cursor type>

   What would this look like from a sqlMap definition point of view and 
how would one call it?


2. What about using (a) java.sql.rowset.CachedRowSet impl instead of 
javax.servlet.jsp.jstl.sql.Result?


My compliments on this mail list, it is one of the best I've seen so far.

-J.

Jerome Jacobsen wrote:

>Hello Jan,
>
>Unfortunately I have no stored procedure/function example
>that does an UPDATE.
>
>However, here's the information I have about a PL/SQL
>FUNCTION that does a SELECT and returns a REFCURSOR.
>
>kschneider@gmail.com posted a message to this forum
>December 22, 2004 with an attached TypeHandler implementation.
>
>Subject was "Re: Problems with registering OUT parameter in
>Oracle stored procedure".
>
>Here's the link:
>
>http://www.mail-archive.com/ibatis-user-java@incubator.apache.org/msg00287.h
>tml
>
>>From his example I created one against the Oracle scott/tiger
>schema.  See attached files.  The attachments include the entire
>project including the script to create the test FUNCTION (in
>SCOTT schema).  The exception is that the JAR file dependencies
>are not attached.  See build.xml for the JAR file versions.
>
>After building the project you can run RefCursorTest.  It will
>output the records from table EMP.
>
>My Environment
>--------------
>Client
>- Windows 2000
>- JDK 1.4.2_06
>- SqlMaps 2.0.9
>- Oracle thin JDBC driver 9.0.3
>Server
>- Windows 2000
>- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
>
>
>As for CLOB, BLOB, XMLType I have no experience with these
>using ibatis.  I've done BLOB/CLOB stuff using JDBC directly
>but I was using Oracle JDBC API instead of java.sql.*.
>
>-Jerome
>
>  
>
>>-----Original Message-----
>>From: Jan Vissers [mailto:Jan.Vissers@cumquat.nl]
>>Sent: Tuesday, January 04, 2005 1:06 PM
>>To: ibatis-user-java@incubator.apache.org
>>Subject: Re: IBatis and Oracle stored functions
>>
>>
>>For instance:
>>
>>...
>>CallableStatement cstmt = conn.prepareCall("{? = call
>>pkg_refcur.f_refcur(?)}");
>>cstmt.registerOutParameter(1, OracleTypes.CURSOR);
>>cstmt.setString(2, i_etype);
>>cstmt.executeUpdate();
>>ResultSet rset = ((OracleCallableStatement)cstmt).getCursor(1);
>>...
>>
>>I've seen *part* of an example for a custom type handler, but no real
>>complete example including call sequence. This would help a great deal!
>>
>>Thx,
>>Jan.
>>
>>Clinton Begin wrote:
>>
>>    
>>
>>>How would you call them from JDBC?
>>>
>>>iBATIS supports pretty much anything supported by the standard JDBC APIs.
>>>
>>>Vendor specific extensions typically require a custom type handler, or
>>>may not be supportable at all.
>>>
>>>Clinton
>>>
>>>
>>>On Tue, 04 Jan 2005 18:43:52 +0100, Jan Vissers
>>>      
>>>
>><Jan.Vissers@cumquat.nl> wrote:
>>    
>>
>>>      
>>>
>>>>Hi,
>>>>
>>>>I'm evaluating IBatis sqlMap/DAO wrt Oracle functionality, like:
>>>>
>>>>"RETURNING ... INTO ..." Clause
>>>>CLOB (oracle.sql.CLOB) >32K
>>>>BLOB (oracle.sql.BLOB)
>>>>XMLType (oracle.xdb.XMLType)
>>>>CallableStatement... It looks to me that none of these are
>>>>        
>>>>
>>really supported.
>>    
>>
>>>>I, for instance have a packaged function:
>>>>
>>>>
>>>>function insert_record( p_i_values in pck2.refcursortype)
>>>>return   pck2.refcursortype;
>>>>
>>>>Is there any way to call these types of objects from IBatis?
>>>>
>>>>Thx.
>>>>Jan.
>>>>
>>>>--
>>>>
>>>> And
>>>>
>>>>
>>>>        
>>>>
>>>
>>>      
>>>
>>--
>>Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=0&t=70>
>> And  	Get Thunderbird
>>    
>>
><http://www.mozilla.org/products/thunderbird/>
>
>  
>
>------------------------------------------------------------------------
>
><?xml version="1.0" encoding="UTF-8" ?>
><!DOCTYPE sqlMap
>    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
>    "http://www.ibatis.com/dtd/sql-map-2.dtd">
>
><sqlMap>
>
>    <parameterMap id="noInputsSingleResult" class="map">
>        <parameter property="resultCode"
>                   jdbcType="NUMERIC"
>                   mode="OUT"/>
>        <parameter property="result"
>                   jdbcType="ORACLECURSOR"
>                   mode="OUT"
>                   typeHandler="ResultSetToResultHandler"/>
>    </parameterMap>
>
>    <procedure id="proc1" parameterMap="noInputsSingleResult">
>        <![CDATA[
>            {?= call IBATIS_REFCURSOR_TEST(?)}
>        ]]>
>    </procedure>
>
></sqlMap>
>
>------------------------------------------------------------------------
>
><?xml version="1.0" encoding="UTF-8" ?>
><!DOCTYPE sqlMapConfig
>    PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"
>    "http://www.ibatis.com/dtd/sql-map-config-2.dtd">
>
><!-- Be sure to always use the appropriate header as above!  Note the 2.0 DTDs. -->
>
><sqlMapConfig>
>
>
>  <!-- Using the properties element you can declare an external properties resource
>       either from the classpath (resource) or from a URL (e.g. file:// or http://)
>       The properties can be referred to throughout the rest of the file using
>       the syntax "${propName}". There can be only one properties element. -->
>
>  <properties resource="jdbc.properties"/>
>
>  <!-- The settings element has a number of configuration switches and properties
>       that configure the SQL Maps engine. Most are performance tweaks or resource
>       management.  They are pretty self explanatory. The defaults are shown here.
>       There can be only one settins element.  -->
>
>  <settings
>    lazyLoadingEnabled="true"
>    cacheModelsEnabled="true"
>    enhancementEnabled="true"
>    maxRequests="512"
>    maxSessions="128"
>    maxTransactions="32" 
>    />
>
>  <transactionManager type="JDBC">
>    <dataSource type="SIMPLE">
>      <property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
>      <property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
>      <property name="JDBC.Username" value="${jdbc.username}"/>
>      <property name="JDBC.Password" value="${jdbc.password}"/>
>    </dataSource>
>  </transactionManager>
>
>  <sqlMap resource="MySqlMap.xml" />
>
></sqlMapConfig>
>
>------------------------------------------------------------------------
>
>CREATE OR REPLACE FUNCTION IBATIS_REFCURSOR_TEST (RESULT OUT SYS_REFCURSOR)
>   RETURN NUMBER
>IS
>   tmpVar   NUMBER;
>BEGIN
>   OPEN RESULT FOR
>      SELECT *
>        FROM EMP;
>
>   tmpVar := 0;
>   RETURN tmpVar;
>END IBATIS_REFCURSOR_TEST;
>/
>  
>
>------------------------------------------------------------------------
>
><?xml version = '1.0' encoding = 'windows-1252'?>
>
><!--Ant buildfile generated by Oracle JDeveloper 10g-->
><!--Generated Jan 4, 2005 4:54:39 PM-->
><project name="RefCursor" default="make" basedir=".">
>
>   <!--Set the output directories-->
>   <property name="compile.outdir" value="classes"/>
>
>   <!--Set the classpath-->
>   <path id="classpath">
>      <pathelement location="${compile.outdir}"/>
>      <!-- Oracle JDBC Thin driver 9.0.3 -->
>      <pathelement location="lib/classes12.jar"/>
>      <!-- Apache Jakarta Commons Logging 1.0.3 -->
>      <pathelement location="lib/commons-logging.jar"/>
>      <!-- ibatis 2.0.9 -->
>      <pathelement location="lib/ibatis-common-2.jar"/>
>      <pathelement location="lib/ibatis-sqlmap-2.jar"/>
>      <!-- JSTL 1.0.3 -->
>      <pathelement location="lib/jstl.jar"/>
>      <!-- Apache Log4J 1.2.8 -->
>      <pathelement location="lib/log4j-1.2.8.jar"/>
>      <!-- Apache Standard Taglibs 1.0.6 -->
>      <pathelement location="lib/standard.jar"/>
>   </path>
>
>   <!--Set the source path-->
>   <property name="src.dir" value="src"/>
>   <path id="srcpath">
>      <pathelement location="${src.dir}"/>
>   </path>
>
>   <target name="init">
>      <tstamp/>
>   </target>
>
>   <target name="compile" depends="init">
>      <mkdir dir="${compile.outdir}"/>
>      <!--Compile Java source files-->
>      <javac destdir="${compile.outdir}" debug="on" encoding="Cp1252">
>         <classpath refid="classpath"/>
>         <src refid="srcpath"/>
>         <include name="ResultSetToResultHandler.java"/>
>         <include name="RefCursorTest.java"/>
>      </javac>
>      <!--Copy files to output directory-->
>      <copy file="${src.dir}/sql-map-client.xml" tofile="${compile.outdir}/sql-map-client.xml"/>
>      <copy file="${src.dir}/MySqlMap.xml" tofile="${compile.outdir}/MySqlMap.xml"/>
>      <copy file="${src.dir}/log4j.properties" tofile="${compile.outdir}/log4j.properties"/>
>      <copy file="${src.dir}/jdbc.properties" tofile="${compile.outdir}/jdbc.properties"/>
>      <copy file="${src.dir}/commons-logging.properties" tofile="${compile.outdir}/commons-logging.properties"/>
>   </target>
>
>   <target name="clean">
>      <!--Delete output directories-->
>      <delete dir="${compile.outdir}"/>
>   </target>
>
>   <target name="make" depends="compile"/>
>
>   <target name="rebuild" depends="clean,compile"/>
>
>   <target name="all" depends="compile"/>
>
></project>
>  
>

-- 
Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=0&t=55> 
 And  	Get Thunderbird <http://www.mozilla.org/products/thunderbird/>


Mime
View raw message