Pass and return custom array object in ibatis and oracle in java

Question

I've looked around for a good example of this, but I haven't run into one yet. I want to pass a custom string array from java to oracle and back, using the IBATIS framework. Does anyone have a good link to an example? I'm calling stored procs from IBATIS.

Thanks

Answer 1

You've got to start with a custom instance of TypeHandler. We'd prefer to implement the simpler TypeHandlerCallback, but in this scenario we need access to the underlying Connection.

public class ArrayTypeHandler implements TypeHandler {

    public void setParameter(PreparedStatement ps, int i, Object param, String jdbcType)
            throws SQLException {
        if (param == null) {
            ps.setNull(i, Types.ARRAY);
        } else {
            Connection conn = ps.getConnection();
            Array loc = conn.createArrayOf("myArrayType", (Object[]) param);
            ps.setArray(i, loc);
        }
    }

    public Object getResult(CallableStatement statement, int i)
            throws SQLException {
        return statement.getArray(i).getArray();
    }
    ...
}

Then, to wire it up in the iBATIS config:

<?xml version="1.0"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"&gt;

<sqlMap namespace="arrayTest">

    <parameterMap id="storedprocParams" class="map">
        <parameter property="result" mode="OUT" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>
        <parameter property="argument" mode="IN" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>
    </parameterMap>

    <procedure id="storedproc" parameterMap="arrayTest.storedprocParams">
        {? = call My_Array_Function( ? )}
    </procedure>

</sqlMap>

Hope this helps!

Answer 2

bsanders gave me a good starting point - here's what I had to do to make it work within the RAD environment (websphere 6.2).

 public Object
 getResult(CallableStatement statement,
 int i)       throws SQLException {
     return
 statement.getArray(i).getArray(); 
 //getting null pointer exception here
    }

    public void
 setParameter(PreparedStatement ps, int
 i, Object param,     String jdbcType)
 throws SQLException {   if (param ==
 null) {      ps.setNull(i, Types.ARRAY);
     } else {  
          String[] a = (String[]) param;   //ARRAY aOracle = ARRAY.toARRAY(a,
 (OracleConnection)ps.getConnection());
      //com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
 w =
 (com.ibm.ws.rsadapter.jdbc.WSJdbcConnection)ps.getConnection());
      //com.ibm.ws.rsadapter.jdbc.WSJdbcObject
 x;
           Connection nativeConnection = Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)ps.getConnection());

      ArrayDescriptor descriptor =
 ArrayDescriptor.createDescriptor(
 "F2_LIST", nativeConnection);    ARRAY
 dataArray = new ARRAY(descriptor,
 nativeConnection, a);
          ps.setArray(i, dataArray);  }
    }

Notice the nativeConnection I had to get, the descriptor I had to make, and so on. However, while I can pass things into the database as an array of Strings, I haven't been able to figure out why I'm not getting anything back. My OUT parameter (the getResult(CallableStatement statment, int i) is throwing a null pointer exception, even though I'm setting the out parameter in the plsql in the database.

--stored procedure to take a | delimited ids
   PROCEDURE array_test (argument IN f2_list, result OUT f2_list) 
   AS
      l_procname_v   VARCHAR2 (50)                 := 'array_test';
      l_param_list   VARCHAR2 (2000)
                   := l_procname_v || ' param_values: p_string: ';

      p_status_n     NUMBER;
      p_message_v    VARCHAR2 (2000);
      ret_list f2_list := new f2_list();
      l_count_v varchar2(200);
   BEGIN

      l_count_v := argument.COUNT;
      for x in 1..argument.count
      LOOP
         pkg_az_common_util.az_debug (package_nm,
                                   l_procname_v,
                                   pkg_az_data_type_def.debug_num,
                                   argument(x)
                                  );
      end loop;

      pkg_az_common_util.az_debug (package_nm,
                                   l_procname_v,
                                   pkg_az_data_type_def.debug_num,
                                   l_count_v
                                  );
      ret_list.extend();
      ret_list(1) := 'W';
      ret_list.extend();
      ret_list(2) := 'X';
      ret_list.extend();
      ret_list(3) := 'Y';
      ret_list.extend();
      ret_list(4) := 'Z';

      result := ret_list;


   EXCEPTION
      WHEN OTHERS
      THEN
         p_status_n := pkg_az_common_util.get_error_code;
         p_message_v :=
               TO_CHAR (p_status_n)
            || '|'
            || 'Oracle Internal Exception('
            || l_procname_v
            || ')'
            || '|'
            || TO_CHAR (SQLCODE)
            || '|'
            || SQLERRM
            || l_param_list;
         standard_pkg.log_error (package_nm,
                                 l_procname_v,
                                 SQLCODE,
                                 p_message_v
                                );

         IF p_status_n = 1
         THEN
            RAISE;
         END IF;
   END array_test;

Here is how I'm accessing it:

Map queryParamsTest = new HashMap();

     String[] testArray = {"A", "B", "C"};

     queryParamsTest.put("argument", testArray);



     DaoUtils.executeQuery(super.getSqlMapClientTemplate(),
       "arrayTest", queryParamsTest, queryParamsTest
       .toString());  //just executes query


     String[] resultArray = (String[])queryParamsTest.get("result");

     for(int x = 0; x< resultArray.length; x++)
     {
      System.out.println("Result: " + resultArray[x]);
     }



<parameterMap id="storedprocParams" class="map">        
     <parameter property="argument" mode="IN" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>    
     <parameter property="result" mode="OUT" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>        
    </parameterMap>    
    <procedure id="arrayTest" parameterMap="storedprocParams">        
     {call pkg_az_basic_dev.array_test(?, ? )}    
    </procedure>

Any ideas?

Answer 3

Try using statement.getObject(i) and then casting to an array.

Answer 4

round the string value "234.567" to "234.57"

Answer 5

Hi, M trying the first option in my code,where i need to pass an array of strings to a stored procedure using ibatis. In type handler m getting this error...can anyone help me with this...

this is the setParameter() method of handler public void setParameter(ParameterSetter paramSetter, Object obj) throws SQLException {

    try{

        Array array = null;

        PreparedStatement ps = paramSetter.getPreparedStatement();
        Connection con = ps.getConnection();

        Object parameter = null; 

        if ( obj instanceof String[] ){

            array = con.createArrayOf("ARRAY", (String[]) obj);

        }

        paramSetter.setArray(array);


    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    catch(Throwable t)
    {
        t.printStackTrace();
    }
}

This is hw I have configured it into configuration xml

error stack trace -->

java.lang.AbstractMethodError: org.apache.tomcat.dbcp.dbcp.PoolableConnection.cr eateArrayOf(Ljava/lang/String;[Ljava/lang/Object;)Ljava/sql/Array; at recon.handler.ArrayTypeHandler.setParameter(Unknown Source) at com.ibatis.sqlmap.engine.type.CustomTypeHandler.setParameter(CustomTy peHandler.java:46) at com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap.setParameter( ParameterMap.java:166) at com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap.setParameters (ParameterMap.java:126) at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure( SqlExecutor.java:276) at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExec uteQuery(ProcedureStatement.java:39) at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQue

Answer 6

what if you try adding

stmt.registerOutParameter(i, Types.ARRAY);

to getResult function