Skip to Content
0

Mapping structure to call stored procedure with parameter as table type in jdbc receiver adapter

Sep 26, 2017 at 05:55 AM

166

avatar image
Former Member

Hi All,

We have SOAP to JDBC Syncronous interface requirement. In receiver PI has to call Stored Proc which has output param as table type (OUT pram3 SAP.VIEW_TABLE) Procudure code

--------------------------

PROCEDURE XYZ_prc" ( IN parm1 NVARCHAR(100), IN parm2 NVARCHAR(100), OUT pram3 SAP.VIEW_TABLE, OUT param4 NVARCHAR(100), OUT param5 NVARCHAR(100))

-----------------------------

As The following SQL data types are supported as type for input and out prameters how can we achieve this in PI

-----------------------------------------------------------------------------------------------------------------------INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output; only in connection with the Oracle JDBC driver)

------------------------------------------------------------------------

I assigned varchar/string to "pram3"-type in message mapping ,tested the scenario and end up with following error as SAP.VIEW_TABLE is custom type in procedure and i assigned different type in mapping

9/24/2017 10:57:45.071 PMErrorException caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. "XYZ_prc" (structure Statement): com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [1281]: wrong number or types of parameters in call: too many arguments: line 1 col 72 (at pos 71)

Please let me know is there any way to call above stored procedure from receiver jdbc adapter.

Thanks.

Regards,

Ramesh M

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Evgeniy Kolmakov Sep 26, 2017 at 06:30 AM
0

Hi Ramesh!

What is your database provider and what actions does your stored procedure perform exactly?

Regards, Evgeniy.

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Evgeniy,

Here DB Provider is SAP HANA and we are using SAP PI 7.4 single stack to connect DB.

The mentioned procedure takes param1,param2 as input and do some validation(null check etc) on these param's ,based on the output of validation (true of false) it will fetch records(each record contains 6 columns) from one of the tables and write to param3 which is table type.

It assign some constant values to param4,param5 and return param3,param4,param5 as ouput.

Thanks.

Regards,

Ramesh M.

0

Hi Ramesh!

The only way to get result of table type that I'm aware of is using table-type stored procedure returning table data.

Regards, Evgeniy.

0