This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC driver for MS Access

 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,

I have a Tomcat 5.X running and Im trying to connect to a MS Access database, I know I need a JDBC driver from what I can gather from the info I have been reading, my question

i) which driver(s) do I need?
ii) Where do i find them>? I've been on MS's website and all that comes up is a SQL Server 2000 jdbc driver.

Thanks in advance for your time/advice

Regards
Zein
 
Naseem Khan
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you don't need to download any driver.

There is one driver that comes with j2sdk. Type 1 Jdbc-Odbc Bridge Driver

Its complete path is...

sun.jdbc.odbc.JdbcOdbcDriver

You need to write following code to make a database connection



dsn you have to create from control panel/odbc.


Naseem
[ June 28, 2006: Message edited by: Naseem Khan ]
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your reply Naseem, but I mentioned I'm using Tomcat, but forgot to state I am also using JNDI etc.. hence for I need a .jar file that I put in the ...common/lib folder.

MySQL and other DB's have drivers, I assumed that MS Access would also have one??

Am on the right lines guys?

Thanks
Zein
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Zein Nunna:
Thanks for your reply Naseem, but I mentioned I'm using Tomcat, but forgot to state I am also using JNDI etc.. hence for I need a .jar file that I put in the ...common/lib folder.

MySQL and other DB's have drivers, I assumed that MS Access would also have one??

Am on the right lines guys?

Thanks
Zein


Most people use the JDBC-ODBC bridge, which is built into Java; no seperate driver required.
http://java.sun.com/products/jdbc/faq.html

There are some commercial drivers for MS Access out there; I've never used one and don't know any particular names.
 
Naseem Khan
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Read Tomcat documentation for that from your http://localhost:8080/.
It provides complete description of jndi datasource. Tomcat provides read only jndi datasource object.

You need to expose your dsn by jndi datasource which you can do from your <Context> tag of Server.xml.

Again you don't need to download any extra driver for that irrespective of how you are getting database connection object.

Naseem
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys thanks for your responses so far,

Stu - jdbc-odbc is used when your not using JNDI - hence cant use it now that I am wanting to use JNDI

Naseem - I have followed the Tomcat instructions for configurig JNDI, so changed the web.xml and server.xml accordingly [adding the context tag, then resource etc..] still no luck though.

I have a funny feeling that MS Access is not JNDI compatible - I came across this in another javaranch forum:

If there is anybody out there who has got this combination working [MS Access + Tomcat + JNDI], let me know please. Thanks

Oh btw I'm using Tomcat 5.5

Regards
 
Wei Dai
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
>If there is anybody out there who has got this combination working [MS Access + Tomcat + JNDI]
How to set up HXTT Access with Tomcat4.1 as PoolableConnection?
But it's a commercial MS Access jdbc driver.
 
Naseem Khan
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tell us what version of Tomcat you are using?
 
Naseem Khan
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
On Tomcat 5.5.8, do it like this...

Add the Context tag in the Server.xml within the <Host> tag.



db1 is data source name which you have to create from control panel.

You don't need to put any entry for jndi in your web.xml.

Now with above Context tag in server.xml, here is a code snippet which will return Connection object from Connection pool.



I tested this on my machine, it worked.

Regards

Naseem
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok guys here's what I have done, I still get an error when I call the



I registerd my DB via control panel and the add to systemDSN (this points to the DB on my Tomcat).

The server.xml is as below (keyt snippets)



[the servlet i'm calling is packagless hence ive used "/", the database is in the root/DB folder]



myDB - is my MS Access Database

My serlvet is as below


I still get an error, the log reads as follow:



Any ideas? Thanks in advance for looking at my questions

Regards
Zein
 
Naseem Khan
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
what exact version of Tomcat you are using? I mean Tomcat 5.x.x? is it 5.5.8

second create user dsn not system dsn.

third why you are not creating a seperate web application for that with following structure....




Waiting for your reply


Naseem
[ June 30, 2006: Message edited by: Naseem Khan ]
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Naseem,

Sorry for the late reply I was away for the weekend

Ok the exact Tomcat is 5.5.17,

Now that I've chnaged the DSN from system to user, I get less errors, the error I do get is as follows:


Does this make sense to anyone? Seems like I've not done something, where the context needs to be set?

Thanks in advance, regards
Zein
 
Naseem Khan
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just I downloaded Tomcat 5.5.17 and it worked on this version under same setting. Same as that of 5.5.8

1. Context tag in server.xml under <Host> tag


2. db1 user datasource name.

3. Invoke Servlet in /ValidationRND/WEB-INF/classes/com/example



4. Mapped it by url pattern *.servlet in web.xml



This much entry I have made. Nothing else.

If you follow these steps, you will be able to insert the data in database by following url...

http://localhost:8080/test/*.servlet


Naseem
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Naseem/ everybody else,

In the previous post I noticed a inconsistency, (it might not be!)



db1 = the DSN name,
resource name = TestDB

In your servlet-code you refer to TestDB

?? If you look at one of your above posts, the name of the resource and DSN name were the same?

--------------------------------------------

I still have the problem above outstanding, I'm temporarily using classforname....etc. I still howevere want to solve this JNDI problem.

So just to go through this step by step
<Context path="/test" docBase="F:\NaseemKhan\j2ee_projects\ValidationRND

what is the /test ?? I have the directory name where my web app is, e.g
"/DB", absolute path is C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\DB

What abt the docBase?? I have the base directory where all the different web applications i'm working on are
e.g. C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps

My resources tag in the server.xml <Resource name="jdbc/db1"...
[is this the dsn-name you give your database resource?]
the url reads --> url= "jdbcdbc:db1"

My MS Access database is on my desktop and is added via DSN--> system, called db1 - real file name is testMyDb.mdb [DSN --> system, works for classforname�..]

The servlet code is as follow



It all seems ok to me? Why am I then having a problem? Error is something along the lines of JDBC is not bound in the context.

I�ve been reading the web and people advice to create a file called context.xml � is that necessary?

Is this an environmental problem or a coding problem? I call all my servlets directly by enabling the invoker servlet via conf/web.xml

Thanks in advance for your advice/views

Regards
Zein

[added line breaks so page doesn't wrap]
[ July 06, 2006: Message edited by: Jeanne Boyarsky ]
 
Zein Nunna
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ohh there was some more preceding code in the servlet that I forgot to post, as below

 
Ulf Dittmer
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just as a side note, be aware that the JDBC/ODBC bridge is not meant to be used in a multi-threaded enviromnent like a web application. It has serious shortcomings in this regard (as does Access itself). Make sure you only ever have a single thread using it.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic