Connection problem in File 2 JDBC

[Former Member]

Hi Guys,

I am trying to develop an scenario from File to MS Access using JDBC Acaptor.

In Design and configuration, I do not have any error and when I tried to send the document, it is giving an CC error saying

'Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//pidev/xi_files/RECEIVER/csk2.mdb' using the JDBC driver 'jdbc.odbc.JdbcOdbcDriver': 'com.sap.aii.adapter.jdbc.sql.DriverManagerException: Cannot establish connection to URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=//pidev/xi_files/RECEIVER/csk2.mdb': SQLException: [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xed8 Thread 0x1188 DBC 0x67c0024 Jet'.''

can anybody suggest me where i am doing wrong?

regards,

shylesh

[MichalKrawczyk · Accepted solution]

Hi,

try using this approach with access:

/people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

with the driver mentioned in the blog

and let me know if it worked

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>

[Former Member · Accepted solution]

Hi,

I tried using this blog only. But not working.

shylesh

[Former Member · Accepted solution]

Hi Everybody,

I am getting an error

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'EDS_PRE_EIM_AUTHORITY' (structure 'StatementName'): java.sql.SQLException: FATAL ERROR document format: structure 'StatementName', no key element found

can someone tell me where i am doing wrong.

sailesh

[MichalKrawczyk · Accepted solution]

Hi,

does the XML that you send to access look exactly like shown here:

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

??

Regards,

michal

[Former Member · Accepted solution]

Hi Michal,

Yes, I am using the same structure.

Regards,

Shylesh

[MichalKrawczyk · Accepted solution]

post the XML here

regards,

michal

[Former Member · Accepted solution]

Hi Michal,

Here is the XML,

<?xml version="1.0" encoding="ISO-8859-1"?>

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://ehro.eds.com/csk/file2jdbcnew" targetNamespace="http://ehro.eds.com/csk/file2jdbcnew">

<xsd:complexType name="DT_FILE2JDBC_IN">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

baacd3c32b3211dcc2dc00304887cef9

</xsd:appinfo>

</xsd:annotation>

<xsd:sequence>

<xsd:element name="StatementName">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4502b3211dc8e9d001b7723928f

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="TableName">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4512b3211dc93cc001b7723928f

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="TABLE" type="xsd:string">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4532b3211dca318001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="ACCESS" maxOccurs="unbounded">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4542b3211dc8e1c001b7723928f

</xsd:appinfo>

</xsd:annotation>

<xsd:complexType>

<xsd:sequence>

<xsd:element name="CON_EMP_NUM" type="xsd:string" maxOccurs="unbounded">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4552b3211dcc7f5001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="VALID_FROM" type="xsd:date" maxOccurs="unbounded">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4562b3211dc85bf001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="VALID_TO" type="xsd:date" maxOccurs="unbounded">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4572b3211dc8afe001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="AUTH_CODE" type="xsd:string" maxOccurs="unbounded">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4582b3211dcc098001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

<xsd:element name="ACTION" type="xsd:string" maxOccurs="unbounded">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4592b3211dca4f4001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

<xsd:attribute name="ACTION" type="xsd:string" use="required">

<xsd:annotation>

<xsd:appinfo source="http://sap.com/xi/TextID">

a505c4522b3211dcc2c0001b7723928f

</xsd:appinfo>

</xsd:annotation>

</xsd:attribute>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

regards,

sailesh

[justin_santhanam · Accepted solution]

Sailesh,

You gave the XSD structure , it would be helpful if u give us the XML structure.

Best regards,

raj.

[MichalKrawczyk · Accepted solution]

Hi,

what is that?

I want the XML that you send to jdbc adapter

so something like that (this is how you XML needs to look like) :

<StatementName2>

<dbTableName action=”INSERT”>

<table>realDbTableName</table>

<access>

<col1>my first value</col1>

<col2>my second value</col2>

</access>

<access>

<col1>columennumberone</col1>

</access>

</dbTableName>

</StatementName2>

Regards,

michal

[Former Member · Accepted solution]

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

<ns0:MT_FILE2JDBC_IN xmlns:ns0="http://ehro.eds.com/csk/file2jdbcnew">

<StatementName>

<TableName ction="INSERT">

<table>EDS_PRE_EIM_AUTHORITY</table>

<access>

<CON_EMP_NUM></CON_EMP_NUM>

<VALID_FROM></VALID_FROM>

<VALID_TO></VALID_TO>

<AUTH_CODE></AUTH_CODE>

<ACTION></ACTION>

</access>

</TableName>

</StatementName>

</ns0:MT_FILE2JDBC_IN>

[MichalKrawczyk · Accepted solution]

hi,

<TableName ction="INSERT">

<table>EDS_PRE_EIM_AUTHORITY</table>

is this: ction="INSERT" ?

it should be: <b>action</b>="INSERT"

Regards,

michal

[Former Member · Accepted solution]

Hi Michal,

Actually it is 'action' only. But when i was setting the alignment, it just deleted mistakenly.

Regards,

Shylesh

[MichalKrawczyk · Accepted solution]

hi,

and does you XML have any data inside?

as this one is pretty empty do you send empty insert to access?

or do you have any values inside those:

<CON_EMP_NUM></CON_EMP_NUM>

<VALID_FROM></VALID_FROM>

<VALID_TO></VALID_TO>

<AUTH_CODE></AUTH_CODE>

Regards,

michal

[justin_santhanam · Accepted solution]

Shailesh,

In addition to Michals discussion. Please see the below note and apply the parameters in receiver comm.channel to see exactly how SQL is generated.

Refer note : 801367

JDBC Receiver Adapter Parameters

1. Parameter name: "logSQLStatement"

Parameter type: boolean

Parameter value: true for any string value, false only for empty string

Best regards.

raj.

[Former Member · Accepted solution]

Hi ,

I tried with values only.

Here, is the XML

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

<ns0:MT_FILE2JDBC_IN xmlns:ns0="http://ehro.eds.com/csk/file2jdbcnew">

<StatementName>

<TableName action="UPDATE">

<table>EDS_PRE_EIM_AUTHORITY</table>

<access>

<CON_EMP_NUM>1001</CON_EMP_NUM>

<VALID_FROM>10/10/2005</VALID_FROM>

<VALID_TO>10/10/2006</VALID_TO>

<AUTH_CODE>ABC</AUTH_CODE>

<ACTION>XYZ</ACTION>

</access>

</TableName>

</StatementName>

</ns0:MT_FILE2JDBC_IN>

Based on this, I put the date format as 2006/10/10 in MS-Access.

Regards,

Shylesh

[Former Member · Accepted solution]

Hi,

In addition to that, I tried with INSERT as well as UPDATE also.

[justin_santhanam · Accepted solution]

Shailesh,

<b><TableName action="UPDATE"> ?</b> ,is it update or insert. If its Update then where is the key element?

Best regards,

raj.

[Former Member · Accepted solution]

Hi Raj,

In addition to that, I tried with INSERT as well as UPDATE also.

I changed the value to UPDATE as the error was coming as 'NOT UPDATEABLE'.

Regards,

Shylesh

[justin_santhanam · Accepted solution]

Shailesh,

Could u please confirm whether ur target structure looks like in the below weblog.

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Best regards,

raj.