Does Hibernate work with Microsoft Access?

mjeaslea · **Joined:** Wed Sep 29, 2004 9:13 pm **Posts:** 6

Question to the community.

Is there a dialect for Microsoft Access? Has anybody had any success in using Microsoft Access? The majority of my clients will be using MySQL / Oracle / SQL Server but a few want to remain on access.

Cheers

sfg · **Joined:** Fri Oct 08, 2004 3:14 am **Posts:** 10

I would think that as long as there is a vanilla jdbc/odbc bridge thing it should.

mjeaslea · **Joined:** Wed Sep 29, 2004 9:13 pm **Posts:** 6

Agreed, So i've been looking for how to use a generic ODBC bridge with hibernate, i though perhaps i'd have to use the Generic Dialect but i haven't had any luck.

mjeaslea · **Joined:** Wed Sep 29, 2004 9:13 pm **Posts:** 6

I've just had some success with the Generic Dialect and the Sun JDBC ODBC bridge.

At the moment my application only uses about 10 tables in read only mode. Hibernate is generating the following WARNING:

WARNING: exception clearing maxRows/queryTimeout
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6879)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7036)
at sun.jdbc.odbc.JdbcOdbc.SQLGetStmtOption(JdbcOdbc.java:4371)
at sun.jdbc.odbc.JdbcOdbcStatement.getStmtOption(JdbcOdbcStatement.java:1208)
at sun.jdbc.odbc.JdbcOdbcStatement.getQueryTimeout(JdbcOdbcStatement.java:697)
at net.sf.hibernate.impl.BatcherImpl.closeQueryStatement(BatcherImpl.java:148)
at net.sf.hibernate.impl.BatcherImpl.closeQueryStatement(BatcherImpl.java:103)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:302)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1544)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1521)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1513)

Is this a serious problem, and can i expect further issues using Microsoft Access and Hibernate?

christian · **Posted:** Thu Oct 28, 2004 1:07 am

I'm pretty sure that this combination will not work. Hibernate has been designed to work with SQL databases that have good JDBC drivers.

max · **Posted:** Thu Oct 28, 2004 3:21 am

it seems like MS jdbc driver don't like setmaxresulset etc.

If you don't use that then you should be fine ;)

mjeaslea · **Joined:** Wed Sep 29, 2004 9:13 pm **Posts:** 6

Thanks for the posts guys.

What i really want to know is if anybody out there is using MS Access with Hibernate and their experiences with it. Is there any plans to add it to the list of "officially supported" databases? Is there structual reasons why it hasn't been added to the list already? And is there problems using the sun JDBC - ODBC bridge instead of a vendor JDBC driver?

Cheers

max · **Posted:** Thu Oct 28, 2004 4:04 am

there does not seem to be a quality jdbc driver for it + noone has stepped up and maintained any such dialect.

I know people have asked for it before and tried to use a standard dialect - but i haven't heard anything solid on wether they succeded or failed after that

mjeaslea · **Joined:** Wed Sep 29, 2004 9:13 pm **Posts:** 6

So am i correct in saying that the sun JDBC - ODBC bridge is not sufficent - that a vendor JDBC driver is required for Hibernate to work correctly?

michael · **Posted:** Thu Oct 28, 2004 9:35 pm

Of course Hibernate works with any JDBC compliant driver - thats what a standard like JDBC is for after all. The sun JDBC-ODBC bridge is however not very well known for standard compliance.

stolley · **Posted:** Sun Oct 31, 2004 8:00 pm

Don't Sun explicitly state that the JDBC-ODBC bridge is for prototyping-type stuff only and is not production-supported?

mjeaslea · **Joined:** Wed Sep 29, 2004 9:13 pm **Posts:** 6

Ok, Thanks guys for clearing this up. Looks like access is a no go then - unless somebody can find a decent JDBC driver for it and has written a dialect for it.

As i said i'd had some success using the Sun JDBC - ODBC bridge (with Genereic Dialect) but its only a readonly application at the momemnt - i haven't tried modifying and saving any of the objects (nor generating the DDL).

Cheers

stolley · **Posted:** Sun Oct 31, 2004 8:12 pm

Are you using Access just because you want to deploy a DB with your app?
Or were/are you actually planning on using Access as the RDBMS for multi-user system?

If you just want a simple DB to deploy with your app, there are a couple of pretty good embeddable Java DBs around that Hibernate is known to work with (HSLDB being the only one I can think of off the top of my head).