Bug 75595 - No tables displayed when connecting to MS Access using the JDBC-ODBC driver
No tables displayed when connecting to MS Access using the JDBC-ODBC driver
Status: RESOLVED FIXED
Product: db
Classification: Unclassified
Component: Code
5.x
All All
: P3 with 6 votes (vote)
: 7.4
Assigned To: David Vancouvering
issues@db
: NETFIX
: 66787 180624 (view as bug list)
Depends on:
Blocks: 66787
  Show dependency treegraph
 
Reported: 2006-04-27 05:38 UTC by hsalameh
Modified: 2013-02-13 01:50 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
:


Attachments
Stack trace (1.30 KB, text/plain)
2006-09-13 15:18 UTC, Andrei Badea
Details
Screenshot of working state (91.60 KB, image/png)
2013-01-20 17:52 UTC, matthias42
Details
potential patch (only compile tested - needs a real test!) (3.18 KB, patch)
2013-01-20 20:39 UTC, matthias42
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description hsalameh 2006-04-27 05:38:11 UTC
When I connect to an MS Access database using the db explorer and using a
jdbc-odbc bridge, with dns-less connection, the connection fails.
the connection string is as follows:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\db1.mdb
Comment 1 Andrei Badea 2006-09-11 19:26:43 UTC
Which error message did you get? I can connect with the following database URL:

jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\\NetBeans\\db1.mdb

but I get an error message that the schemas could not be retrieved. That seems
to be caused by the bridge or the driver, which results in a SQLException when
DatabaseMetaData.getSchemas() is called. But I can press OK in that dialog and
the new connection is added to the Runtime tab.
Comment 2 hsalameh 2006-09-11 19:51:52 UTC
That is true. The connection is established, but i cannot see any tables in 
the "Tables" node.
As you explained, it DatabaseMetaData.getSchemas() is throwing an exception 
(optional feature not implemented).
But I was evaluating MyEclipse sometime in the past, and I used the same driver 
(Sun's JDBC-ODBC bridge) and the same connection string, and MyEclipse db 
explorer was able to read the database structure and display the tables 
correctly.

My goal here is to get MSAccess to work with JPA in a webapp (to get 
the "entities from database wizard" work with msaccess.
Comment 3 Andrei Badea 2006-09-13 15:13:37 UTC
Reopening then. An exception (see attachment) is thrown when expanding the
Tables node, but it is not thrown in a simple JDBC program which just connects
to the database and does conn.getMetaData().getTables(null, null, "%", new
String[] { "TABLE" }). We're probably doing something the driver doesn't like.
Comment 4 Andrei Badea 2006-09-13 15:18:26 UTC
Created attachment 33883 [details]
Stack trace
Comment 5 ariise 2007-09-13 11:17:08 UTC
I've had the same problem connecting to Access from the Netbeans 6 preview (M9). Tried again using version 5.1.1. with 
the JDK 6, second update. The problem is still there. When connecting using the db wizard, I get I dialog box saying 
that "There is insufficient information to establish a connection to obtain a list of schemas. Check database url, user 
and password. Driver message:[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented".
Still, when I hit OK, the connection is established.
When I try to explore the database, I dialog box tells me "Unable to read the database structure, connection is 
broken;[Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented". 
Comment 6 David Vancouvering 2008-01-25 23:25:06 UTC
*** Issue 66787 has been marked as a duplicate of this issue. ***
Comment 7 ulfzibis 2008-02-08 13:50:49 UTC
Note, that JDBC-ODBC Bridge will be enhanced --> https://jdbc-odbc-enhanced.dev.java.net/
Have you ever tried it's milestones ?

Comment 8 David Vancouvering 2008-03-28 08:06:12 UTC
yes, we're calling "getDatabaseProductName" and I suspect Access doesn't support it.  We should catch the exception and
handle it correctly rather than bailing.
Comment 9 David Vancouvering 2008-03-28 08:12:24 UTC
I'm making a change that I am pretty confident will work: I bascically handle any exception that can come from querying
metadata.  Once I mark this as fixed (which means the change has been integrated into the main codeline), can one of you
using Access please verify?  I don't have Access set up, and trying to do so would make it impossible for me to get this
fix in in time for code freeze...

Thanks! 
Comment 10 David Vancouvering 2008-03-28 17:36:54 UTC
http://hg.netbeans.org/main?cmd=changeset;node=0b02ff0c674f
Comment 11 gelosoft2004 2010-11-24 11:24:43 UTC
Hi to all , I had the same problem using Netbeans IDE 6.1-6.5 if you are try to connect JDBC-ODBC using MSAcces inside the IDE it fails. But if you going to use Java Main Class and put some sample code in JDBC-ODBC it works perfectly and I feel this will work only on Java Desktop Application, I have no idea if I can pool this code in Visual Web JSF in any Palette Component. If you have any ideas pertaining JDBC-ODBC using MS Access on Visual WEb JSF much appriciated. Many thanks.
Comment 12 nm1418 2013-01-10 12:50:45 UTC
The issue still exists in netbeans 7.x.x (tried on 7.0.1, 7.1.2, 7.2.1)
Also when a connection pool is created using glassfish admin console, the pool gets created (i.e. you can ping successfully)but when you try to create an entity from database, no schema or tables show up in the dialog.
Please suggest how can we use 'Create Entity from database' option in cojunction with MS Access connection pool & jdbc data source.
Comment 13 matthias42 2013-01-20 17:24:11 UTC
*** Bug 180624 has been marked as a duplicate of this bug. ***
Comment 14 matthias42 2013-01-20 17:52:02 UTC
Created attachment 130423 [details]
Screenshot of working state

I tested on windows 2003 server - the zip database from here:

http://databases.about.com/od/access/a/zipcodedatabase.htm

Downloaded the Microsoft Access Database Engine 2010 Redistributable and installed it.

I created a System-DSN pointing to the .mdb Databasefile (called xxy in the screenshot).

This works.

Netbeans 7.2.1, Oracle JRE 7u11
Comment 15 matthias42 2013-01-20 18:11:58 UTC
Closing as the first entry from this bug also works (adapted for my paths).

For the entities from database action - I suspect a generic problem. If I recall correctly this is already discussed and being worked on.
Comment 16 matthias42 2013-01-20 20:37:31 UTC
Reopened as the entity generation problem is really odbc/access specific. It's caused in DriverSpecification.java - I only checked getTables and getProcedures, but I suspect any method on DatabaseMetaData (DMD) taking a schema information is affected.

If you query the DMD like this:

rs = dmd.getTables(catalog, schema, tableNamePattern, types);

you'll get an error if schema != null. The error states that quering with a schema is an optional feature and not supported by access.

There is already a hack in DriverSpecification for drivers not supporting handling of non-null catalogs - the attached patch translates that to the schema handling.
Comment 17 matthias42 2013-01-20 20:39:18 UTC
Created attachment 130425 [details]
potential patch (only compile tested - needs a real test!)
Comment 18 Jaroslav Havlin 2013-02-11 14:56:25 UTC
Thank you very much for the patch, Matthias!
It seems fine and works correctly.
I've added some tests as you'd suggested.

Integrated as http://hg.netbeans.org/core-main/rev/aa71fae0e5db
Comment 19 Quality Engineering 2013-02-13 01:50:47 UTC
Integrated into 'main-golden', will be available in build *201302122300* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress)
Changeset: http://hg.netbeans.org/main-golden/rev/aa71fae0e5db
User: Jaroslav Havlin <jhavlin@netbeans.org>
Log: #75595: No tables displayed when connecting to MS Access using the JDBC-ODBC driver


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo