How to put Stored Procedure in Receiver JDBC channel

[Former Member]

Hi all,Good Evening,Iam using a JDBC to JDBC scenario in which I have to move the data from database into XI and we all know that the Interfaces which require stored procedures on Oracle database server for publishing the data needs to be called in combination of BPM & Receiver JDBC adapter.

In this method, a dummy interface will be created for triggering the process. This interface could be designed using any adapter which works on polling mechanism (sender JDBC/File). Dummy interface will invoke a small BPM designed for each business object. BPM will make a synchronous call to Oracle database by calling underlying stored procedure. Stored procedure will return the result set by using a cursor. BPM will send this result set to target application.

Now my question is how to put a stored procedure in a receiver JDBC channel which need to collect the data from database when BPM makes a synchronous call.

Because it is required in my scenario,can any one tell me how and where to put stored Procedure in JDBC receiver channel.

Thanks in advance,

Regards,

Prajwal

[Former Member]

Hi,

I want to know what is the use of putting Stroed Procedure in JDBC Channel. JDBC is an adapter to convert JDBC code to XML and vice versa. Stored procedure is used to change the Database tables or desply records from Database tables. I dont think so it is needed. Let me know what kind of thing you require StoredProcedure.

[Former Member]

Hi,

The receiver structure should contain the Stored Procedure name with action = EXECUTE and the required elements (parameters to stored procedure) under it. This will execute the stored procedure with the given paramters.

Regards

Bhanu.

[Former Member]

Hi,

there is one more way of calling Stored procedure in XI in receiver JDBC adapter.

You just create all the value you want to pass to stored procedure in Key fields of receiver message type in mapping.

write you query to call SP in a constant and map it to action.

You can see Sap help as this method is mentioned there also.

Kulwinder

Reward if helpful

[Former Member]

The ACTION on the SQL XML tag should be 'EXECUTE'.

This Blog might help u:

/people/michal.krawczyk2/blog/2005/06/28/xipi-faq-frequently-asked-questions

Pls reward points if helpful.

[former_member240483]

In your receiver structure for the table field map to Stored procedure name..

and action field to execute..

when ever data comes into maping it calls the stored procedure and executes that statement..

Regards

Rao

[GabrielSagaya]

Your action should be EXECUTE for stored procedures in Message mapping.

<StatementName5>

<storedProcedureName action=u201D EXECUTEu201D>

<table>realStoredProcedureeName</table>

<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName5>

action=EXECUTE

Statements with this action result in a stored procedure being executed. The name of the element is interpreted as the name of the stored procedure in the database. If you use the optional <table> element, the value specified here is used as the stored procedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder/PCK. If specified, <table> must be the first element in the block within <dbTableName>.

The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput=u201C1u201C (input parameter) or isOutput=u201C1u201C (output parameter) or both (INOUT parameter). If both attributes are missing, the element is interpreted as an input parameter. The parameter names must be identical to those of the stored procedure definition.

The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

/people/sap.user72/blog/2005/10/15/jdbc-adapter-execution-mode-chained-or-unchained

/people/luis.melgar/blog/2008/05/13/synchronous-soap-to-jdbc--end-to-end-walkthrough

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

[Former Member]

hi,

These may help you..

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

Thanks,

Vijaya.