RE: ArrayDescriptor issue

[Rao, Satish]

Here's the standalone code that works.....


import java.sql.*;

import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

class TestOrder {

    public static void main(String[] args) throws SQLException {

                int error_code;
                Connection conn = null;
                CallableStatement cstmt = null ;
                ResultSet rset;

                try {

                                //Loading Oracle Driver
                                DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
                                conn =
DriverManager.getConnection("jdbc:oracle:oci8:@SID", "user",
"password");

                                cstmt = conn.prepareCall("{call
ORDER_TEST(?,?)}");

                                int elements[] = { 41 };
                                ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("NUM_ARRAY", conn);
                                ARRAY newArray = new ARRAY(desc, conn,
elements);

                                cstmt.setArray(1, newArray);
                                cstmt.registerOutParameter(2,
OracleTypes.CURSOR);

                                cstmt.execute();
                        
                                rset = (ResultSet) cstmt.getObject(2);
                        
                                while (rset.next())
                                {
                                 System.out.println("print - " +
rset.getString("ORD_ID"));
                                }

                } catch (SQLException e) {

                        error_code = e.getErrorCode();
                        conn.close();
                } finally {
                        cstmt.close();
                        if (conn != null && !conn.isClosed())
                                conn.close();
                }
        }
 }


Here's the stored procedure
Create or replace type num_array as TABLE OF NUMBER(38)

CREATE OR REPLACE PROCEDURE Order_Test (IN_ORD_ID IN num_array,
OUT_RESULTSET OUT SYS_REFCURSOR) IS
BEGIN
        FOR i IN IN_ORD_ID.FIRST..IN_ORD_ID.LAST LOOP
                  OPEN OUT_RESULTSET FOR
                 SELECT ORD_ID FROM ORD_TABLE WHERE ORD_ID =
TO_CHAR(IN_ORD_ID(i));
        END LOOP;
END Order_Test;
/

Here's the ArrayHandler code that doesn't work

public class ArrayHandler implements TypeHandlerCallback {

        public void setParameter(ParameterSetter arg0, Object arg1)
                throws SQLException {
                if (arg1 != null)  {
                        
                        //code to parse ArrayList and create int[]
                        //for testing I have hardcoded int[] below
                        int orderIdArr[] = { 41 };
                        
                        PreparedStatement p =
arg0.getPreparedStatement();
                        Connection conn =
arg0.getPreparedStatement().getConnection();
                                                
                        ArrayDescriptor descriptor =
ArrayDescriptor.createDescriptor("NUM_ARRAY", conn);
                        ARRAY array = new ARRAY (descriptor, conn,
orderIdArr);
                        arg0.setArray(array);
                        }
                }
        }

        public Object getResult(ResultGetter arg0) throws SQLException {
                return null;
        }

        public Object valueOf(String arg0) {
                return null;
        }

Here's the sqlmap entry

        <parameterMap id="test-detail-proc-param" class="Order" >
                <parameter property="orderId" mode="IN" jdbcType="ARRAY"
javaType="java.util.ArrayList"/>
                <parameter property="runDetailRS"
jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"/>
        </parameterMap>
        <procedure id="testorder" parameterMap="test-detail-proc-param">
                {call ORDER_TEST(?,?)}
        </procedure>

And the SQLMapConfig entry

          <typeHandler javaType="java.util.ArrayList"
callback="ArrayHandler"/>

I get the following error...

--- The error occurred while applying a parameter map.  
--- Check the test-detail-proc-param.  
--- Check the parameter mapping for the 'orderId' property.  
--- Cause: java.sql.SQLException: ORA-04043: object "DIPDBO"."NUM_ARRAY"
does not exist


Hope this helps.


-----Original Message-----
From: Niels Beekman [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 16, 2005 10:48 AM
To: user-java@ibatis.apache.org
Subject: RE: ArrayDescriptor issue


Could you paste the JDBC-code, otherwise we are not getting any further
:)

Niels

-----Original Message-----
From: Rao, Satish [mailto:[EMAIL PROTECTED] 
Sent: dinsdag 16 augustus 2005 16:47
To: user-java@ibatis.apache.org
Subject: RE: ArrayDescriptor issue

I tried it again. It works with straight jdbc but fails with sqlmap

-----Original Message-----
From: Koka [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 16, 2005 3:39 AM
To: user-java@ibatis.apache.org
Subject: Re: ArrayDescriptor issue


Can you test whether your code works using just JDBC (without iBatis)?
I think that way we'd know direction where to look

Koka