JDBC with MS Access: DB connection error

[Former Member]

Hi Dear Friends!

I'm implementing scenario File->PI->(JBDC) MS Access.

I follow the links:

1. Anirban Ghatak. FILE to JDBC Adapter using SAP XI 3.0

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

2. Sameer Shadab. Connecting to MS Access using receiver JDBC Adapter (Without DSN)

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

3.

But I have such error in Communication Channel Monitoring:

Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver 
(*.mdb)};DBQ=../../../work/PIUse/PiPattern1/TestDB.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=../../../work/PIUse/PiPattern1/TestDB.mdb': java.lang.NullPointerException'

In last link they say it isn't necessary to install and configure External Drivers for JDBC. (And I really have not done it yet.) My PI work on Linux platform. What I need to set or detect?

With the best regards,

Natalia Maslova.

[Former Member · Accepted solution]

Hi !

Some ideas:

• Try a DBQ value that is absolute, I mean no relative to some path like DBQ=../../, try something like "DBQ=/myData/test.mdb"

• Try to add "DriverID=22; READONLY=true" to your "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=.... " definition like

"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=.... ;DriverID=22; READONLY=true"

In some forums outside SDN, I've found:

"The problem is that there is no MS Access ODBC driver that runs under linux. And this still has nothing to do with Java nor with JDBC. If you can find an MS Access ODBC driver (which has nothing to do with java) that runs on linux then you would be set. Microsoft does not make one. There might be other vendors that do but I doubt it."

http://www.unixodbc.org/

I don't really know if it is true.

Regards,

Matias.

[Former Member]

Hi Natalia,

I am facing a similar error as you posted in your last message.

Please guide me how did you solve this .

Thanks,

Puneet

[Former Member]

Hi Puneet,

As I understand you have the similar error as in my case:

<b>Error when executing statement for table/stored proc. '1' (structure 'STATEMENTNAME'): java.sql.SQLException: Syntax error: Can't continue parse.</b>

My error was in name of database table. In <b>message mapping</b> I specify constant '1' for TABLE instead real name of table.

In <b>SXMB_MONI</b> in item <b>Request Message Mapping / Payloads</b> you can control which value is really used.

Please look through <b>SXMB_MONI</b> and in link:http://help.sap.com/saphelp_nw70/helpdata/en/14/80243b4a66ae0ce10000000a11402f/frameset.htm

find title "Document Formats for the Receiver JDBC Adapter" and attentively read syntax for your case (for example in my case I used INSERT).

So my error was in part

<table><b>realDbTableName</b></table>

Element realDbTableName is taken from message mapping and I need to specify the real name of database table.

Good luck!

Natalia.

[Former Member]

Hi Experts,

I have a similar requirement.Did you deployed Licensed MS Access jar files downloaded from http://www.hxtt.net/

Does SAP standard recommends this? If yes share me those details.

Please let me know if there is any other jar files that can be used.

[Former Member]

Hi

connecting to MS access (Without DSN) Receiver JDBC scenario - /people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

Also see below links

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

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

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

[Former Member]

Hi Natalia !!

I haven't tried yet that type of scenario, but after reading some posts I understand that this driver is included. Try this thread and see if there is any clue about configuration parameters..

Regards,

Matias.

[Former Member]

Hi Dear Friends!

I have looked through Forum touched my theme

(1. File - XI - MS Access JDBC Adpater

2. JDBC Adapter

etc.)

I deployed driver Access_JDBC20.jar (from www.hxtt.net) for MS Access according to links:

1. Providing External Drivers for the JDBC and JMS Adapters

http://help.sap.com/saphelp_nw04s/helpdata/en/14/39084136b5f423e10000000a155106/frameset.htm

2. How to Install and Configure External Drivers for the JDBC & JMS Adapters. Version 3.01 - March 2006

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/bf4b1055-0d01-0010-32a3-b3848d77...

At first I had the error:

8/21/07 3:15:18 PM

Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=/usr/sap/DX7/DWEBMGS01/work/PIUse/PiPattern1/TestDB.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=/usr/sap/DX7/DWEBMGS01/work/PIUse/PiPattern1/TestDB.mdb': ClassNotFoundException: jdbc.odbc.JdbcOdbcDriver'

but after that I have the previous error:

8/21/07 3:52:10 PM
Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=/usr/sap/DX7/DVEBMGS01/work/PIUse/PiPattern1/TestDB.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=/usr/sap/DX7/DVEBMGS01/work/PIUse/PiPattern1/TestDB.mdb': 
java.lang.NullPointerException'

And really when I look through content of Access_JDBC20.jar I don't see the class file with name jdbc.odbc.JdbcOdbcDriver.

Have you any ideas?

Thank you in advance.

Natalia Maslova.

[Former Member]

Hi Natalia,

Do you have that jar in your classpath?

best,

Wojciech

[Former Member]

Wojciech,

Please explain what I need to set?

[Former Member]

Hi,

Have you fallowed this document?

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/bf4b1055-0d01-0010-32a3-b3848d77...

CLASSPATH is system variable with all jars and classes. In this blog you will find something about it:

/people/laxman.molugu/blog/2006/08/13/integration-with-databases-made-easy-150-part-1

best,

wojciech

[Former Member]

Hi Wojciech!

Can you recommend me some other links for expanation and settings CLASSPATH?

The first link doesn't contain any info on this variable.

The second link use auxiliary run.bat for settings, but my system works on Linux platform.

Thanks.

Natalia.

[Former Member]

Hi Wojciech,

I specify CLASSPATH according to Setting the class path

http://java.sun.com/j2se/1.4.2/docs/tooldocs/windows/classpath.html

but I have the same error:

Error during database connection to the database URL 'jdbc:odbc:Driver={Microsoft Access Driver (.mdb)};DBQ=/usr/sap/DX7/DVEBMGS01/work/PIUse/PiPattern1/TestDB.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=/usr/sap/DX7/DVEBMGS01/work/PIUse/PiPattern1/TestDB.mdb': ClassNotFoundException: jdbc.odbc.JdbcOdbcDriver'

What I need to set?

Thank you in advance,

Natalia.

[ravi_raman2]

Natalia,

I guess it is pretty obvious that there is a missing jar..

Question is what jar is it and how do we add it so that it works.

q1) what is the database version.

2) what is the java level.

Can you let us know that.

Regards

Ravi Raman

[Former Member]

Ravi,

1. Database version - MS Access 2003.

2. Java version: J2RE 1.4.2 IBM build j9xa64142-20061124 (SR7)

Driver Access_JDBC20.jar - from www.hxtt.net.

XI on Linux Red Hat 4.0 server x86_64

Thanks,

Natalia.

[ravi_raman2]

Natalia,

I noticed that you have an IBM jre not jdk, jre might not have all the required jars. Can we start with upgrading this to a jdk.

A jdk will have all the jars that will be missing ina jre install.

Regards

Ravi raman

[Former Member]

Ravi,

I found JDK for my platform - IBM Java 1.4.2 for SAP Customers

IBMJava2-AMD64-142-SDK-1.4.2-7.0.x86_64.rpm (52851894) at

https://www14.software.ibm.com/webapp/iwm/web/reg/download.do?source=javasap&; lang=en_US&S_PKG=lin142-0SAP1&cp=UTF-8#

When we try to implement this .rpm file into our system we detected that the same version is already installed. So I think that info about java version is reflected both JRE and JDK. But in screen we see info only about JRE.

In Installation Guide (SAP NetWeaver 2004s SR1 ABAP+Java on Linux: MaxDB (Document version 1.0 April 10, 2006) item 3.9 - Installing the Java Development Kit - pg.62) I see: "The JDK includes the JRE".

I found more detailed (documented) version of JDBC driver for MS Access in www.hxtt.net - Access_JDBC30.jar. I deployed this version of driver. And in accordance to attached description I set the parameters for my JDBC Receiver Communication Channel <b>cc_jdbc_r</b> in ID:

<b>JDBC Driver: com.hxtt.sql.access.AccessDriver</b>

<b>Connection: jdbc:access:////usr/sap/DX7/DVEBMGS01/work/PIUse/PiPattern1/TestDB.mdb</b>

After these settings in RWB in Communication Channel Monitoring I see my channel <b>cc_jdbc_r</b> with red status, but without any explanation for Processing Details.

When I try to run my scenario I see in Processing Details for Sender Communication Channel success - file was read successful. And in Processing Details for Receiver Communication Channel <b>cc_jdbc_r</b> I see two rows:

1) Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. '1' (structure 'STATEMENTNAME'): java.sql.SQLException: Syntax error: Can't continue parse.

2) Processing started.

In Audit Log for first row I see such errors:

1.1) Unable to execute statement for table or stored procedure. '1' (Structure 'STATEMENTNAME') due to java.sql.SQLException: Syntax error: Can't continue parse.

1.2) MP: exception caught with cause com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. '1' (structure 'STATEMENTNAME'): java.sql.SQLException: Syntax error: Can't continue parse.

1.3) Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. '1' (structure 'STATEMENTNAME'): java.sql.SQLException: Syntax error: Can't continue parse.

1.4) Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.aii.af.ra.ms.api.RecoverableException: Error processing request in sax parser: Error when executing statement for table/stored proc. '1' (structure 'STATEMENTNAME'): java.sql.SQLException: Syntax error: Can't continue parse..

Can you advice me what to set?

Thank you in advance,

Natalia.

[Former Member]

Hi Dear Friends!

Thanks a lot for your ideas!

Yes! I implement it! I know how to organize connect with MS Access by JDBC Receiver for XI on Linux platform. (Solution is represented in my posts in this thread.)

My last error was in name of database table.

And now I will try JDBC Sender!

With the best regards,

Natalia Maslova.