Dear All,

I’m trying to pass an array to the stored procedure with signature.

The JDBC type of the input IN is “ARRAY”.

I have tried mapping the output of the adater service to the JDBC Array object and also tried passing the java array to this object, in both cases it gives

“[ADA.1.340] The input data for field “P_ARRAY” is not “java.sql.Array”.
com.wm.adk.cci.record.WmRecord”

PS: P_ARRAY is the parameter name for the JDBC ARRAY type.

I’m not sure how should the array type java.sql.Array be created to pass to this input ARRAY object for the stored procedure.

Integration Server : 6,5 SP2
IS_6-5_SP2_FlatFile_Fix1
IS_6-5_SP2_PubSub_Fix1
IS_6-5_SP2_SrvPrtcl_Fix2
IS_6-5_SP2_XA_Fix1
IS_6-5_SP2_WebSvcsXML_Fix1
IS_6-5_SP2_Core_Fix4
IS_6-5_SP2

JDBC Adapter: 6.5
Oracle: 10g

On searching wM advantage gave a single useful link

[URL]https://advantage.webmethods.com/advantage?targChanId=kb_home&oid=1614024800[/URL]

"<<stored procedures having arrays/struct/table as IN/OUT/IN OUT params are supported by StoredProcedureWithSignature template>>

<<Right now,the only way you can pass arrays/struct as IN params,is by using output fields of adapter services which are mapped to java.sql.Array”/ “java.sql.Struct.>>

So a few points:

• Stored Procedures are supported for use with *WithSignature services in oracle 10g.
• Arrays/Struct/Table as IN/OUT/IN OUT params is supported.
• However, you must map it from an output param of an adapter service to the input of another adapter service. You cannot simply map it from a “String List” to the input of an Array object on an adapter service.
• It is not even possible to create this in a java service. Even Execute Service (Java Service) template doesntt allow creation of ARRAY/STRUCT data types. This is because of Execute Service use the Proxy Connection and not the real Connection object. "

But doesnt seem to help me a lot.

Since this is a very urgent requirement, would anyone be able to help me in this?

Thanks !
–Manik

Hi Manik,

Pretty specific problem there. If I understand you correctly, I think we ran into nearly the same issue, I remember coming to the conclusion that it uses a proxy connection and not the connection object itself. (maybe I even opened a thread on wM users for this)

One of the possibilities we explored was creating our own connection pool (non wM) but we didn’t want to do this, because we want to keep control over the connection properties through the IS.

We opted for another option; storing the array in an intermediary table and passing on an identifier for further processing. I’m not sure this will work in your scenario; we were executing a query, trying to return the data and pass it on for further processing again. Obviously, if the data you want to pass to the stored procedure is not stored in the database, this workaround will not work for you.

HTH

Loic

Hi Loic

Many Thanks for your response.

I too couldnt see of any possibility to pass the array using JDBC 6.5 to SP.

The Alternatives thought were

1. As described to store the data in a temporary table and let the stored procedure process it.
2. Pass the input values which was supposed to go in an array with some funky delimiters(as the user can goof this up. For ex use: “$^%” as delimiter) and then let the store procedure split and process it.

But since the operation I was doing was taking a snapshot of the data from the portal to the DB. We are now planning to using separate SPs for corresponding purposes like add/update/delete instead of doing it in one SP as planned.

Thanks for your suggestions.

Kind Regards
Manik

Hi All,

I have a stored procedure that will return the output as a table. The stored procedure takes input to update the table and return me a list of rows from the table. I am trying to create a DB service to call that stored procedure and get that output. How should i be defining the output in the DB service ? Will the ouput be coming as a document list with column names in it ?