How to use Native SQL statement in JDBC receiver interface

[Former Member]

Dear All,

Can any one please help us in using Native SQL statement in a JDBC receiver channel. The reason why I need to use Native SQL statement instead of standard XML structure is that I need to execute a dynamic SQL query in third party database system lke:-

"

Select Field1 Field2 from TABLE Where Field3 like "%Name'

"

I expect the the response in the form of XML file which I can pick up using synchornous interface as mentioned on help.sap.com:-

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

The value for %Name can change dynamically according to the transaction and hence cannot be inluded as a KEY element in standard XLM structure,

Hence I need to know:-

1. What message mapping I should use in case if I have to use Native SQL statement.

2.What operation mapping I should use in case if I have to use Native SQL statement.

If guess correclty I may have to use Java mapping to do the above activities. Hence I want to know

3 .How do to go about it to do the Java mapping.

Thanks

Ameet

[Former Member]

>

> Dear All,
> 
> Can any one please help us in using Native SQL statement in a JDBC receiver channel. The reason why I need to use Native SQL statement instead of standard XML structure is that I need to execute a dynamic SQL query in third party database system lke:-
> 
> "
> Select Field1 Field2 from TABLE Where Field3 like "%Name'
> "
> I expect the the response in the form of XML file which I can pick up using synchornous interface as mentioned on help.sap.com:-
> 
> http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm
> http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm
> 
> The value for %Name can change dynamically according to the transaction and hence cannot be inluded as a KEY element in standard XLM structure,
> 
> Hence I need to know:-
> 
> 1. What message mapping I should use in case if I have to use Native SQL statement.
> 2.What operation mapping I should use in case if I have to use Native SQL statement.
> If guess correclty I may have to use Java mapping to do the above activities. Hence I want to know 
> 3 .How do to go about it to do the Java mapping.
> 
> Thanks
> Ameet

You can use a stored procedure, and call it from jdbc receiver adapter.

I also solve this issue, with a DBLookup in message mapping. You can refer to my blog, and this usefull 3d:

http://simonlesflex.wordpress.com/2010/12/07/pi-oracle-dblookup/

/people/alessandro.guarneri/blog/2006/03/27/sap-xi-lookup-api-the-killer

/people/siva.maranani/blog/2005/08/23/lookup146s-in-xi-made-simpler

[Former Member]

Thanks a lot for the reply...very useful...just have a question here..

my requirement is...i need to send some parameters to this mapping from ECC and use these parmeters in the select query to be framed.... thus i have generated a proxy....

Now when i complete the look-up using thie suggested function and collect the result; my requirement is to transfer the reuslt back to ECC and process it there.....

Can anybody please help mw with this question - how do i send the result back to ECC ?

[aashish_sinha]

Hi Amit,

When we use native SQL, that means database require exact Text statement to direct query from database. for this we need to send text to execute directly on database. For mapping, it is only possible through Java Mapping.

Also after looking around i came across with this statement:

XML Document Format for the Message Protocol Native SQL Format

This protocol is primarily for test purposes only. Instead of an XML document format, a text is expected that represents any valid SQL statement.

When inserting a line into a table the corresponding document looks as follows:

u201E

INSERT INTO tableName (column-name1, column-name2, column-name3) VALUES(u2018column-value1u2019, u2018column-value2u2019, u2018column-value3u2019)

u201C

native SQL is mostly used for test purposes. Second thing we can do is we can use some module in JDBC receiver adapter like below blog.

/people/francesco.bersani/blog/2009/11/27/preparedstatement-with-jdbc-receiver-adapter

Hope this will help you.

Regards

Aashish Sinha