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
insert into largeorder values('yyy',1,2,sysdate);
result := resultset('aaa', 'bbb');
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 = "";
conn = getDBConnection("connpool1", false);
Vector paramData = new Vector(2);
CwDBStoredProcedureParam outParam =
String result = (String)(outParam.getValue());
logInfo("Exception :" + e.getMessage());
Above way is impossible to work in current ICS version (220.127.116.11). 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
For now as the limitation of the ICS DBConnection APIs and the JDBC Driver which ICS is using, no Solution could use the CwDBStoredProcedureParam class with Array as the parameter
Rate this page:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.