JDBC to JDBC Scenerio

[Former Member]

Dear All,

i want to do one JDBC to JDBC scenario in which I want to update one table in MSAccess by taking data from the other table stored in MSAccess. Plz tell me the parameters which I have to maintain for this scenerio in Integration Directory & Repository. Also plz tell shuld I have to write any SQL Query for that & if yes then where I have to give that query. Also, plz tell me the good weblog for this scenario.

Waiting for ur valuable replies,

reg,

nishu

[bhavesh_kantilal]

Nishu,

You will find my email id in my business card.

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

As suggesed by the blog I has tested the mapping, though it is showing an success message there is no database details in the message. If u dont mind can u plz give me ur email id so that i can mail u screenshots?

reg,

nishu

[Former Member]

Nishu,

"It is showing an success message there is no database details in the message". This means your mapping is incomplete. Check whether you have assigned all mandatory node in target structure.

If you leave any root node with out mapping, even if you map the sub nodes it may not get initialized. Assign the root node with empty constants.

Regards,

Jai Shankar.

[Former Member]

Hi,

when i am doing the testing, it is showing a success message but at the reciever side I m not getting any data. So where i can chech what has gone wrong during mapping.

nishu

[Former Member]

Hi ,

I think you are getting only root node there...

map with constant value to that root node.

You can find the "constant" in message mapping functions..

Sekhar

[Former Member]

Hi Rohini,

I believe there is some error in Mapping bcoz when i m checking in SXMB_MONI, the message from the database is correctly in Payload upto "Message Branch According to Reciever List" but after that no values are coming in the Payload further. I have checked the mapping and as far I know my mapping is correct( I have done 1-1 mapping).

so might might be error.

nishu

[Former Member]

Nishu,

<i>" when i m checking in SXMB_MONI, the message from the database is correctly in Payload upto "Message Branch According to Reciever List"</i>

Use the payload and test your mapping in IR. There is an error. May be you have not assigned some root node.

Regards,

Jai Shankar.

[former_member187587]

Hi nishu,

please follow the help.sap.com site for JDBC sender\reciever

to get the source and target structures needed.there is a specific structure that must be met in order to perform the SQL statments using the JDBC adapter.

<a href="http://help.sap.com/saphelp_nw2004s/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm">JDBC</a>

p.s

as i recall I had problems with ms access ODBC driver for the XI scenario and manage to do it on SQL SERVER only.

Good luck anyway

Nimrod

[Former Member]

What exactly is the problem you are facing now?

We will try to help you more better

[Former Member]

Hi Bhavesh,

Thanks for ur inputs. I have check the Sender Data Type as told by you & what I have done is correct. Also when I am testing as per the weblog suggested by you, below it is showing that message has executed successfully but still the recieved message is having no database value in it.

Plz help me for this.

reg,

nishu

[bhavesh_kantilal]

Nishu,

Check into SXMB_MONI, is your payload picking up data from your DB? CHeck the mapping using this blog,

/people/michal.krawczyk2/blog/2005/09/16/xi-how-to-test-your-mapping-in-real-life-scenarios

Regards,

Bhavesh

[bhavesh_kantilal]

Nishu,

I had earlier pasted a blog to test your mapping. Please use thatr to see how mapping s can be tested using real time data.

/people/michal.krawczyk2/blog/2005/09/16/xi-how-to-test-your-mapping-in-real-life-scenarios

Regards,

Bhavesh

PS : Read the replies and try to understand the reply and ask for clarification if needed.

[Former Member]

Dear All,

As per my earlier post for this query, in JDBC to File scenario, JDBA Adapter is taking the values from database table but when XML message is getting created at target end it is empty ie. no values are there in that message.

Plz tell me what should be the DATA TYPE structure for the Sender & what should be the DATA TYPE structure for the reciever. I have done as per Weblog 1772 but there I am not able to understand why Anirab has used Elements like TEST, Table, access.

Plz guide me in this.

reg,

nishu

[bhavesh_kantilal]

Nishu,

<i> have done as per Weblog 1772 but there I am not able to understand why Anirab has used Elements like TEST, Table, access.</i>

Blog 1772 is for a File to JDBC . What you are trying is a JDBC to FILE right?

Regards,

bhavesh

[bhavesh_kantilal]

Nishu,

Please read the content under this link for Sender JDBC adapter,

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

The last part of this link states how the source Data Type should be ,

<i><DT_NAME>

<row> 0 to UB

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

</DT_NAME>

</i>

The DATA TYPe Name you create in your IR and the Namespace should be entered in your Sender JDBC adapter against , DOCUMENT NAME and DOCUMENT NAMESPACE respectively.

For receiver JDBC adapter,

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

Regards,

Bhavesh

[Former Member]

Hi,

Now I have started getting the messages at the recieve side but the messages are empty. Since I am taking data from database table and putting it getting as XML file, so there should be some table values in the messages but it is empty. Can u plz tell me what might be the error?

reg,

nishu

[Former Member]

Nishu,

There is a problem with your mapping. Go to Sxmb_moni -> Inbound message -> payload and copy the xml msg.

Test with this msg in IR( Message Mapping -> test tab).

Regards,

Jai Shankar.

[Former Member]

Hi ,

check your message mapping with test tab.

/people/michal.krawczyk2/blog/2005/09/16/xi-how-to-test-your-mapping-in-real-life-scenarios

sekhar

[Former Member]

Hi All,

Now I am able to update MSAccess table thry a flat file. But when I am doing JDBC to file scenario, the sender adapter is showing one message processed, but reciever adapter ie. file adapter is showing no messages processed upto now. Also, in RWB-->Message Monitoring, it is showing a success message but in SXMB_MONI it is showing an error message.

reg,

nishu

[Former Member]

Hi NIshu,

The problem is the message is sent form source toXI via JDBC adapter but it is not reaching the receiver. Check what is the error in SXMB_MONI> Click on the Msg_Id and find a node called error. Double click it and see the error. Post us the error for more help.

Regards,

Jai Shankar.

[Former Member]

Hi,

In JDBC to File Scenario, I want to take some fields from one or more MSAccess tables and put in a flat file. I am not able to understand where and how to write the SQL Query for that. Also, what will be the structure for the Sender JDBC Data Type. Also there is something called Document Namespace, so what we have to specify there.

reg,

nishu

[bhavesh_kantilal]

Hi,

In your sender JDBC adapter , you can give a JOIN select query.

Like,

select table1.col1,table1.col2,table2.col3 from table1,table2 where table1.col1=table2.col1 and table2.col4 = '1'

<i> Also there is something called Document Namespace, so what we have to specify there.</i>

You will create your Sender JDBC Datattype under a Namespace. GIve the DocumentNamespace value as this Namespace and Document Name as the Datatype Name.

<i>Also, what will be the structure for the Sender JDBC Data Type.</i>

For query above,

<i><DT_SORUCEDATATYPE>

<row> <b>0 to UB</b>

<col1> </col1>

<col2> </col2>

<col3> </col3>

</row>

</DT_SOURCEDATATYPE></i>

All this info is available on http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Please read the same for all info you need.

Regards,

Bhavesh

[Former Member]

Hi ,

<i>I am not able to understand where and how to write the SQL Query</i>

You have to write the query in the field UPDATE SQL statement in processing parameters of sender JDBC Adapter .There you can find the document name(your source message type) and document namespace.

sekhar

[Former Member]

Hi,

thanx for ur replies. i will try to complete it.

also, if i will do JDBC to File scenario, what all changes I have to do as compared to File to JDBC Scenario. I will try to do this also. as per the weblog 1772, should we have to do the same way for JDBC to File scenario.

waiting for replies,

nishu

[Former Member]

Hi ,

Jdbc to File :

Source : JDBC sender structure and use sender JDBC adapter.

Reciever :

Use any type structure....and use File Reciever Adapter

sekhar

[Former Member]

Hi ,

You have to maintain following structure for source...

source_dt

rows

fields

You have to give the data type name and namespace in the sender JDBC Adapter propertice aginist Document name and aocument namespace.

<i>Also plz tell shuld I have to write any SQL Query</i>

In Update SQL statement enter a valid query basing on the no tables you are using.

ex: If you are selecting data from single table...

select * from table

If you are selecting data from parent and child with the key fields...

SELECT table1.,table2.

FROM table1,table2

WHERE table1.key=table2.key

Hope this helps you...

sekhar

[Former Member]

Hi Nishu,

I suggest you go through these weblogs:

/people/siva.maranani/blog/2005/09/16/xi-how-to-on-jdbc-receiver-response

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

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

Regards,

Abhy

[bhavesh_kantilal]

Nishu,

You can take a look at this blog,

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

When you are using your JDBC as a sender, then your JDBC will poll over your database and select the rows that satisfy your Select Query. Also, there is another field in your JDBC adapter that is very important and that is the Update satatement. Once your JDBC adapter executes your select query and selects rows from the database, you might not need those rows to be selected again. In this csse, you can use the Update statement to update the database.

In the case of the receiver JDBC adapter, you can insert/ update/ delete data and execute stored prooedure into your databse.

For more info, look at these links,

<a href="http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm">Sender JDBC adapter</a>

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

Regards,

bhavesh