JDBC adapter questions

[Former Member]

Hello all,

I have been playing around with the JDBC adapter on my XI sandbox to figure it out. I read through a few guides and I almost have a good handle on it, but there is still a big question lingering. I have configured an example scenario in which I would like to pull data out of the USR01 table from the XI database and transform some user data and place it back in the same table. I was able to get my connections correct and have the communication channel successfully execute SQL statements to alter the data. For example, my <b>Query SQL Statement</b> in the sender communication channel is as follows:

SELECT * FROM USR01 WHERE BNAME='ZTEST2'

And the <b>Update SQL Statement</b> in the sender communication channel is:

UPDATE USR01 SET BNAME='ZTEST' WHERE BNAME='ZTEST2'

This works fine and when I go to transaction se16 and pull up that table I see that the username gets modified when the interface is activated. However, I haven't really figured out exactly how to configure the communication channel / data types so that I can run a SQL statement that will populate my data type that I created for mapping. In other words, lets say I created a data type that looks as follows:

mt_sender
    row
         MANDT
         BNAME
         STCOD

And I want to populate that data type with data from the USR01 table (in the corresponding fields), what would my SQL statement look like in the sender communication channel? I read a few weblogs on how to do scenarios like this, so I built my receiving Data Type to look like this:

mt_receiver
     STATEMENT
          ROW
               action
               TABLE
               access
                    MANDT
                    BNAME
                    STCOD

But I still don't understand how exactly XI correlates the data types / mapping and transformations to the SQL statements....

I'm having a hard time putting this into words, but basically I'm asking a rather high level question I think. How does XI relate the data types to the data it pulls from the SQL statements? How do I actually get the SQL statement to populate my data types so I can do mapping and then repopulate the database table again? Does this make sense?

[prabhu_s2 · Accepted solution]

Hi

xi correlates the data it picks and populates the MI NOT BASED on filed types. say u are pulling MANDT, BNAME, STCOD u shud ensure that the message type is having the same sequence. this way xi assumes the data are placed in the corrct fields. the difference what u can spot is similar like using

<b>select ... into correposinding fields of table itab</b> and <b>

select ...into itab

</b> ....xi places the data like the second query not using corresponding

[Former Member · Accepted solution]

So in other words, if I am going to pull data out of a table then my data type has to represent each field of the table? I was hoping that I could just make a data type that had the first couple of fields of the table and work with those just to see if that worked, instead of replicating the entire table structure.

[prabhu_s2 · Accepted solution]

u need to have the same set of fields as u use in the jdbc query. in this way xi will be able to place the data accordingly....otherwiese also it will place but evetuaaly it turns out to be a data error !!!

[Former Member · Accepted solution]

What about the outgoing data type? I've seen several different weblogs that structure it each their own way. Is there one particular format that the JDBC adapter looks for?

[prabhu_s2 · Accepted solution]

sender jdbc adapter looks in one of the two ways:

1. Stored procedures

2. Query

in recv JDBC we have format as in :

http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

[bhavesh_kantilal · Accepted solution]

Hi,

You would agree to the point that all messages processed by the Integration Server is XML.

So, now when dealing with Sender JDBC adapters, when some data is selected by the Sender JDBC adapter , the data that comes into the Integration Engine has to be an XML . So, the Sender JDBC adapter uses the standard format ( correctly pointed by you ) to convert the Data selected by it into XML and so you source XML has to be of the corresponding format.

The same logic in the receiver JDBC adapter. How does XI let the receiver JDBC adapter know what the query should be. Using a standard XML , the receievr JDBC adapter converts this XML into a corresponding SQL query so that the corresponding Action canbe perfromed by the Receiver JDBC adapter.

The format depends on the Action you are trying on the database and this link gives yhou the details,

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm">Document Format for the Receiver JDBC adapter</a>

Regards

Bhavesh

[Former Member]

Thanks everyone, I have a much better understanding of how the adapters work now.

[Former Member]

Hi Harrison,

Also check out these JDBC structure links related to your question:

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm">http://help.sap.com/saphelp_nw2004s/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/frameset.htm</a>

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm">http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm</a>

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm">http://help.sap.com/saphelp_nw2004s/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm</a>

Regards,

Subhasha Ranjan