You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Clinton Begin <cl...@gmail.com> on 2005/01/04 18:54:30 UTC
Re: IBatis and Oracle stored functions
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 <Ja...@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
Re: IBatis and Oracle stored functions
Posted by Clinton Begin <cl...@gmail.com>.
Of course, if this is a single case, you might just want to use JDBC too...
Clinton
On Tue, 4 Jan 2005 11:30:59 -0700, Clinton Begin
<cl...@gmail.com> wrote:
> You can look at the Blob and ClobTypeHandlerCallback implementations
> included with 2.0.9 as an example of a CTH. As fot the getCursor
> stuff, you won't have access to the PreparedStatement, but you should
> be able to use getObject(1) to get the cursor back.
>
> In the worst case scenario, 2.0.9 includes support for implementing
> full TypeHandler implementations, which are harder to write, but will
> give you access to the PS.
>
> Cheers
> Clinton
>
> On Tue, 04 Jan 2005 19:05:56 +0100, Jan Vissers <Ja...@cumquat.nl> wrote:
> > 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 <Ja...@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/>
> >
> >
>
Re: IBatis and Oracle stored functions
Posted by Clinton Begin <cl...@gmail.com>.
You can look at the Blob and ClobTypeHandlerCallback implementations
included with 2.0.9 as an example of a CTH. As fot the getCursor
stuff, you won't have access to the PreparedStatement, but you should
be able to use getObject(1) to get the cursor back.
In the worst case scenario, 2.0.9 includes support for implementing
full TypeHandler implementations, which are harder to write, but will
give you access to the PS.
Cheers
Clinton
On Tue, 04 Jan 2005 19:05:56 +0100, Jan Vissers <Ja...@cumquat.nl> wrote:
> 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 <Ja...@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/>
>
>
Re: IBatis and Oracle stored functions
Posted by Jan Vissers <Ja...@cumquat.nl>.
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
>>>
>>>
>><Ja...@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/>
RE: IBatis and Oracle stored functions
Posted by Jerome Jacobsen <je...@gentootech.com>.
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
Re: IBatis and Oracle stored functions
Posted by Jan Vissers <Ja...@cumquat.nl>.
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 <Ja...@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/>