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.
[EMAIL PROTECTED] 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/tigerschema. 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:[EMAIL PROTECTED] 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:
<[EMAIL PROTECTED]> 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
really supported.
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
--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/>
