Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (herse.apache.org: domain of gchelidze@magtigsm.ge
 designates 81.95.160.41 as permitted sender)
Message-ID: <46135981.2070204@magtigsm.ge>
Date: Wed, 04 Apr 2007 11:53:37 +0400
From: George Chelidze <gchelidze@magtigsm.ge>
User-Agent: Debian Thunderbird 1.0.2 (X11/20070113)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: Oracle stored procedure with custom data types
Content-Type: multipart/mixed;
 boundary="------------090702070001080806000504"

This is a multi-part message in MIME format.
--------------090702070001080806000504
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

It's my first post to this list, so I'd like to say hello to all of you.

I have a procedure:

CREATE OR REPLACE PROCEDURE "S"."PREPROCPROC"(aInput in 
TPreprocInputMessageSet, aOutput out TPreprocOutputMessageSet) is
res TPreprocOutputMessageSet;
curi TPreprocInputMessage;
curo TPreprocOutputMessage;
begin
  res:=TPreprocOutputMessageSet();
  res.extend(aInput.Count);
 
  FOR i IN aInput.First .. aInput.Last
  LOOP
    curi:=aInput(i);
    curo:=TPreprocOutputMessage(null,null,null);
    curo.FieldA := curi.FieldA;
    curo.FieldB := curi.FieldB;
    curo.FieldC := null;
    res(i):=curo;
  END LOOP;
  aOutput:=res;
  res.delete;
end PreprocProc;

and the following custom types:
--
CREATE OR REPLACE TYPE TPREPROCINPUTMESSAGESET AS TABLE OF 
S.TPREPROCINPUTMESSAGE
GO
--
CREATE OR REPLACE TYPE TPREPROCOUTPUTMESSAGESET AS TABLE OF 
B1.TPREPROCOUTPUTMESSAGE
GO
--
CREATE OR REPLACE TYPE TPREPROCINPUTMESSAGE as object (
    FieldA varchar2(32),
    FieldB varchar2(32)
)
GO
--
CREATE OR REPLACE TYPE TPREPROCOUTPUTMESSAGE as object (
    FieldA varchar2(32),
    FieldB varchar2(32),
    FieldC varchar2(32)
)
GO
--
I have attached Main.java, TPreprocTypeHandler.java, SqlMapConfig.xml, 
SqlMapProcedure.xml files. The main problem  that I have is that in 
TPreprocTypeHandler.getResult(ResultGetter getter) method, 
getter.getObject().getArray() returns Object[] which contains instances 
of STRUCT not TPreprocInputMessage, so I have to map them to 
TPreprocInputMessage myself. Is it possible to tell ibatis to return 
instances of TPreprocInputMessage instead of instances of STRUCT?

Thanks in advance,

George


--------------090702070001080806000504
Content-Type: text/x-java;
 name="Main.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="Main.java"

package ibatis;

import com.ibatis.sqlmap.client.SqlMapClient;
import ibatis.datatypes.TPreprocInputMessage;
import ibatis.datatypes.TPreprocInputMessageSet;
import ibatis.datatypes.TPreprocOutputMessage;
import ibatis.datatypes.TPreprocOutputMessageSet;
import ibatis.util.DBConnector;
import java.util.HashMap;

public class Main {
    public static void main(String[] args) {
        try {
            SqlMapClient sqlMapClient = DBConnector.getSqlMapClient();
            TPreprocInputMessage[] inMessages = new TPreprocInputMessage[10];
            for (int i = 0; i < 10; i++) {
                inMessages[i] = new TPreprocInputMessage(null, null);
            }
            TPreprocInputMessageSet inMessageSet = new TPreprocInputMessageSet(inMessages);
            TPreprocOutputMessageSet outMessageSet = new TPreprocOutputMessageSet();
            HashMap params = new HashMap();
            params.put("inMessageSet", inMessageSet);
            params.put("outMessageSet", outMessageSet);
            sqlMapClient.queryForObject("Procedure.PreprocProc", params);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

--------------090702070001080806000504
Content-Type: text/xml;
 name="SqlMapConfig.xml"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="SqlMapConfig.xml"

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig
    PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    "sql-map-config-2.dtd">

<sqlMapConfig>
    <properties resource="ibatis/util/SqlMap.properties"/>
    <settings
        cacheModelsEnabled="true"
        enhancementEnabled="true"
        lazyLoadingEnabled="true"
        maxRequests="64"
        maxSessions="10"
        maxTransactions="5"
        useStatementNamespaces="true"
        defaultStatementTimeout="5"
    />

    <typeAlias alias="TPreprocTypeHandler" type="ibatis.handler.TPreprocTypeHandler"/>
    
    <transactionManager type="JDBC">
        <dataSource type="SIMPLE">
            <property name="JDBC.Driver" value="${driver}"/>
            <property name="JDBC.ConnectionURL" value="${url}"/>
            <property name="JDBC.Username" value="${username}"/>
            <property name="JDBC.Password" value="${password}"/>
            <property name="JDBC.DefaultAutoCommit" value="true" />
        </dataSource>
    </transactionManager>
    <sqlMap resource="ibatis/util/SqlMapProcedure.xml"/>
</sqlMapConfig>

--------------090702070001080806000504
Content-Type: text/xml;
 name="SqlMapProcedure.xml"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="SqlMapProcedure.xml"

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
    "sql-map-2.dtd">

<sqlMap namespace="Procedure">
    <parameterMap id="params" class="map">
        <parameter property="inMessageSet" jdbcType="TPREPROCINPUTMESSAGESET" javaType="ibatis.datatypes.TPreprocInputMessageSet" typeName="TPREPROCINPUTMESSAGESET" typeHandler="TPreprocTypeHandler" mode="IN"/>
        <parameter property="outMessageSet" jdbcType="TPREPROCOUTPUTMESSAGESET" javaType="ibatis.datatypes.TPreprocOutputMessageSet" typeName="TPREPROCOUTPUTMESSAGESET" typeHandler="TPreprocTypeHandler" mode="OUT"/>
    </parameterMap>
    <procedure id="PreprocProc" parameterMap="params">
        {call PREPROCPROC(?,?)}
    </procedure>
</sqlMap>

--------------090702070001080806000504
Content-Type: text/x-java;
 name="TPreprocTypeHandler.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="TPreprocTypeHandler.java"

package ibatis.handler;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;
import com.ibatis.sqlmap.engine.type.JdbcTypeRegistry;
import java.sql.SQLException;
import java.sql.Types;

public class TPreprocTypeHandler implements TypeHandlerCallback {
    static {
        JdbcTypeRegistry.setType("TPREPROCOINPUTMESSAGESET", Types.ARRAY);
        JdbcTypeRegistry.setType("TPREPROCOUTPUTMESSAGESET", Types.ARRAY);
    };
   
    public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
        setter.setObject(parameter);
    }
   
    public Object getResult(ResultGetter getter) throws SQLException {
       return getter.getObject();
    }
   
    public Object valueOf(String s) {
        return null;
    }
}

--------------090702070001080806000504--