Using Array as the CwDBStoredProcedureParam class parameter in the Map or Collaboration template could not work in current ICS

Problem(Abstract)

For example, if using Oracle as the Database, and there is a storedprocedure like below

Create type RESULTSET as Table of varchar2(255);
CREATE OR REPLACE procedure test_resultset
(
test_in in varchar2,
result out ResultSet
)
is
begin
insert into largeorder values('yyy',1,2,sysdate);
result := resultset('aaa', 'bbb');
end test_resultset;

And then in the Map or Collaboration template, you used below code to created DBConnection and try to execute above StoredProcedure

CwDBConnection conn = null;
String id = "";

try
{
conn = getDBConnection("connpool1", false);
}
catch(CwDBConnectionFactoryException e)
{
logInfo(e.getMessage());
throw e;
}

try
{
Vector paramData = new Vector(2);
paramData.add(new CwDBStoredProcedureParam(PARAM_IN,
new String("aa")));
paramData.add(new CwDBStoredProcedureParam(PARAM_OUT,
java.sql.Array));
conn.executeStoredProcedure("test_resultset", paramData);

CwDBStoredProcedureParam outParam =
(CwDBStoredProcedureParam)paramData.get(1);
String[] result = (String[])(outParam.getValue());
}
catch(CwDBSQLException e)
{
logInfo("Exception :" + e.getMessage());
try
{
conn.rollback();
if(conn.isActive())
{
conn.release();
}
}
catch(Exception e1)
{
logInfo(e1.getMessage());
}
}

Above way is impossible to work in current ICS version (4.3.0.2). The "unsupported method" exception will be threw during the scenario executing.

In ICS code infrastructure and document, CwDBStoredProcedureParam class supports the java.sql.Array as the parameter value of a store procedure. But there are latency risks for use the Array as the parameter value in current version.

Firstly, current version, ICS provides DataDirect as the implementation of JDBC connections. And current version of DataDirect, which used by ICS, doesn't support any methods about java.sql.Array. In the DataDirect's official documents, it points that all Array related are not supported.So that, if the java.sql.Array object is used in the your Maps or Collaborations, the ICS server will throw a "unsupported method" exception from DataDirect driver. It would generate more troubles to the Maps and Collaborations. So we advice that don't use the java.sql.Array as the input or output parameter value.

Secondly, the ICS's CwDBConnection object doesn't provide the get JDBC Connection method for obtaining a jdbc connection. And the java.sql.Array object must to be obtained from the java.sql.ResultSet object which comes from jdbc connection object by ResultSet.getArray(x). It means there is not any way for generating a java.sql.Array in ICS API infrastructure. So, there is no way to instantiate a java.sql.Array instance through ICS's provided API now..

At last, if the you really want to use the java.sql.Array as your own store procedure's input or output parameter values, you have to escape the ICS provided API and DataDirect Driver, and turn to use the original Oracle published Driver and its API. To ICS server and adapter sides, this way might cause other problems about ICS internal used JDBC operations and functions. So we don't advice the way to use other kind of JDBC Driver. And suggest continue use the official supported JDBC Driver, which is DataDirect Driver. But as above reason, with using the offical supported JDBC Driver, the Array method are not supported, so it's impossible to use the Array as the parameter.

Resolving the problem