cancel
Showing results for 
Search instead for 
Did you mean: 

file to jdbc(msaccess)

Former Member
0 Kudos

Hi,

I'm doing a file to jdbc scenario, my databse is in msaccces.I have kept the database file on the xiserver in a shared folder.Now i get successfulll msg in moni, but in my receiver communication channel i get the following error:

Unable to execute statement for table or stored procedure. 'VENDOR_MASTER' (Structure 'update_insert') due to java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

My target msg from moni is as follows:

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

- <ns1:MT_sql_vendor xmlns:ns1="urn:tbit40:pune:filetojdbc00">

- <update_insert>

- <VENDOR_MASTER action="INSERT">

- <access>

<Vendor_number>pragati</Vendor_number>

<last_name>divekar</last_name>

<search_term>dive</search_term>

<currency>usd</currency>

<street>main street</street>

<city>us</city>

<zip>asds</zip>

<country>us</country>

</access>

- <access>

<Vendor_number>pragati</Vendor_number>

</access>

</VENDOR_MASTER>

</update_insert>

</ns1:MT_sql_vendor>

can anyone tell me what is wrong?

Accepted Solutions (1)

Accepted Solutions (1)

bhavesh_kantilal
Active Contributor
0 Kudos

Hi,

1. Is the VENDOR_MASTER the name of the table?

2. use the logSQLstatment in the Receiver JDBC adapter to make sure that the insert query being generated is a valid Insert Query?

Regards

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

VENDOR_MASTER is my table name.Can u tell me where in Receiver JDBC adapter can i find logSQLstatment

bhavesh_kantilal
Active Contributor
0 Kudos

Pragati,

Check note : 801367.

In the reciever JDBC adapter, select Table --> and then add the following value,

logSQLStatement --> true.

Regards

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Pragati,

Does the Receiver JDBC adapter give more info on the error? Is there more description than the earlier one available?

One issue can be that there are Primary Key violations when you are trying to insert the data to the database.

Regards

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I don't think there is any primary key voilation bcoz the table is blank, there are no contents.

I will chk the note 801367 and get back to u.

Former Member
0 Kudos

Hi bhavesh,

the following sql query gets formed when i send the data :

INSERT INTO VENDOR_MASTER (Vendor_number, last_name, search_term, currency, street, city, zip, country) VALUES (pragati, divekar, dive, usd, main street, us, asds, us)

The problem is that the values that are inserted shouls be in single quotes ( ' ), but they r not.How to solve this?

bhavesh_kantilal
Active Contributor
0 Kudos

Pragati,

To your datype, to all the elements ad dthe attribiute , hasQuot and in the mapping give the value,

<b>hasQuot = true</b>

The reason to use this is described in this link, in the final section,

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

Regards

Bhavesh

Former Member
0 Kudos

thanks for the help , my issue is resolved.

bhavesh_kantilal
Active Contributor
0 Kudos

Did using hasQuot = true did the trick?

Regards

Bhavesh

Former Member
0 Kudos

yes the hasquote attribute did work,but the value is not true its "YES".

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

As explained above see the receiver side structure see the below link

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

Please see the below from SAP Note No:831162

• Q: When sending a message to a JDBC receiver channel for the first time after an extended inactivity period, I am observing a java.sql.SQLException in the adapter's processing, which refers to a closed connection or a timeout? What is causing this and how do I work around this problem?

• A: The database server has apparently closed the adapter's JDBC connection from the server side. Nevertheless, the message should be processed successfully during the next retry. If you want to completely eliminate the symptoms, enable the setting "Advanced Mode" -> "Disconnect from database after each message processing". Note that this might have a negative performance impact for high-volume processing

Regards

Chilla

bhavesh_kantilal
Active Contributor
0 Kudos

><i>I am observing a java.sql.SQLException in the adapter's processing, which refers to a closed connection or a timeout?</i>

The error posted above does not mention that there is a connection error. It states that there is an error while trying to Insert data.

Not sure if this option is going to solve the exact problem.

Regards

Bhavesh

former_member184619
Active Contributor
0 Kudos

hi

check this link..

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

Sachin