JDBC adapter

[Former Member]

Hi Guys,

I am sending data from file to jdbc (MS access on local machine)

In the jdbc adapter I have given:

<b>Driver: sun.jdbc.odbc.JdbcOdbcDriver

Connection: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//134.13.85.184/My Documents/XI.mdb;</b>

Now do i need to give any other command so that the incoming data is written the access db like <b>insert</b> etc

What other fields do i need to fill in the JDBC adapter???

Also how do i create a data type for inserting data in DB like:

<StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

</StatementName2>

pls advice

Regards,

Ashish

Message was edited by:

Ashish Goyal

[Former Member · Accepted solution]

Hi

1. Should share the file and have write aceess to the db

2. Format output message format can be like

<statement> can be anything

<dbTableName action=”INSERT”> create a tag with dbtablename and create an attribute named action and give a constant value INSERT

<table>realDbTableName</table> This is optional tag If you want to give table name at runtime you can use this tag. If you use this tag then dbTableName value can be overridden in the tag <dbTableName action=”INSERT”>

create an aceess tag and create sub tags with the same column names in the table

Regards,

Sai

[Former Member · Accepted solution]

Hi guys,

Thanks for your reply.

To answer your questions:

1) I have stored the test.mdb in 'My documents' folder on my local system and i have given sharing permission to it.

2)The XI server is in other city.

So as per SDN my connection string should contain <b>'ipaddress of local machine/shared folder/test.mdb'</b>

So my connection string is : jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//13.16.85.184/My Documents/XI.mdb;

When i run the scenario is get the following error message in the Comm Channel monitoring: <b>Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//132.186.85.184/My Documents/XI.mdb;' using the JDBC driver 'sun.jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//132.186.85.184/My Documents/XI.mdb;': SQLException: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xde8 Thread 0x9ff4 DBC 0x510b024 Jet'.'</b>

Also what user name password do i have to give?? i am using MS access and there is no Password for it. Do I have to give system login?

Regards,

Ashish

[Former Member · Accepted solution]

Hi,

No need to give any password when you are using MS Access.

This in not the problem regarding your configuration.

Refer this link to solve the above error.

http://support.microsoft.com/kb/295297

Regards,

Sudheer.

Message was edited by:

sudheer babu

[Former Member · Accepted solution]

The error message that i get in Comm channel is : Audit Log for Message: 73e3394a-9aa2-814b-bc74-f1f7b9c0c3fb

Time Stamp Status Description

2006-11-16 11:09:27 Success Message successfully received by messaging system. Profile: XI URL: http://bomw076a:50300/MessagingSystem/receive/AFW/XI Credential (User): PIISUSER

2006-11-16 11:09:27 Success Using connection AFW. Trying to put the message into the receive queue.

2006-11-16 11:09:27 Success Message successfully put into the queue.

2006-11-16 14:56:33 Success The message was successfully retrieved from the receive queue.

2006-11-16 14:56:33 Success The message status set to DLNG.

2006-11-16 14:56:33 Success Delivering to channel: JDBC_Receiver_MSAccess

2006-11-16 14:56:33 Success Receiver JDBC adapter: processing started; QoS required: ExactlyOnce

<b>2006-11-16 14:56:33 Error JDBC adapter receiver channel JDBC_Receiver_MSAccess not initialized. Unable to proceed due to null

2006-11-16 14:56:33 Error Exception caught by adapter framework: null

2006-11-16 14:56:33 Error Delivery of the message to the application using connection AFW failed, due to: RecoverableException.</b>

Pls advice.

Regards,

Ashish

[Former Member · Accepted solution]

Hi,

Please check your JDBC receiver message type structure. It has to be in the same format as expected by the adapter. Make sure that you have specified your database name and drivers correctly in the CC parameters.

Regards,

Sudheer.

[Former Member · Accepted solution]

Hi Sudheer,

This is the final data going out after i test interface mapping:

<b><?xml version="1.0" encoding="UTF-8"?>

<ns0:JDBC_Vendor xmlns:ns0="http://Ashish_File_to_idoc">

<Data_DB>

<XI action="INSERT">

<TABLE>XI</TABLE>

</XI>

<Access>

<Vendor_Number>446</Vendor_Number>

<Last_Name>Goyal</Last_Name>

<Search_term>Home</Search_term>

<Currency>AUD</Currency>

<Street>Anthony</Street>

</Access>

</Data_DB>

</ns0:JDBC_Vendor></b>

1) My table name is XI.

2) It has many fields but i am filling only a few as per the above message shown.

3) My XI.mdb is in 'JDBC' folder that i created and placed it on the desktop. Am i giving the right path in the connection string: <i><b> jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=//132.186.85.184/JDBC/XI.mdb</b></i>

4) The driver i have given is :<i><b> sun.jdbc.odbc.JdbcOdbcDriver</b></i>

What other parameters do i need to give in JDBC adpter channel?

5) What do you mean when you say ; <i>JDBC receiver message type structure. It has to be in the same format as expected by the adapter.</i> Pls put some light on the issue..

Appreciate your patience...

Regards,

Ashish

[Former Member · Accepted solution]

Hi,

<StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2</col2>

</access>

<access>

<col1>val11</col1>

</access>

</dbTableName>

</StatementName2>

Look at the above strucure you are closing your <XI> tag even before you finish your <Access> tags.All Access tags in your structur should be inside XI tag.

Regards,

Sudheer.

[bhavesh_kantilal · Accepted solution]

Ashish,

you missed something,

<i><?xml version="1.0" encoding="UTF-8"?>

<ns0:JDBC_Vendor xmlns:ns0="http://Ashish_File_to_idoc">

<Data_DB>

<XI action="INSERT">

<TABLE>XI</TABLE>

<Access>

<Vendor_Number>446</Vendor_Number>

<Last_Name>Goyal</Last_Name>

<Search_term>Home</Search_term>

<Currency>AUD</Currency>

<Street>Anthony</Street>

</Access>

<b></XI></b>

</Data_DB>

</ns0:JDBC_Vendor></i>

Regards,

Bhavesh

[Former Member]

Hi,

Please refer this link for receiver JDBC format types.

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

Refer this link for JDBC receiver configuration.

http://help.sap.com/saphelp_nw2004s/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

Regards,

Sudheer.

[bhavesh_kantilal]

Ashish,

Please take a look at this blog,,

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

Regards,

Bhavesh

[MichalKrawczyk]

hi,

did you try your scenario?

do you have access to this folder: My Documents/XI.mdb ?

<b>is it on your XI server?</b> - if not try a folder on your XI server

maybe try with a folder without space like MY_Documents ?

Regards,

michal

-

---

<a href="/people/michal.krawczyk2/blog/2005/06/28/xipi-faq-frequently-asked-questions"><b>XI / PI FAQ - Frequently Asked Questions</b></a>

[prabhu_s2]

You might require a KEY field if any <i><b>updates</b></i> are made to a particular record. Else it is fine to execute.