aspose file tools*
The moose likes JDBC and the fly likes Retrieving a List of All ODBC/JDBC Data Sources Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retrieving a List of All ODBC/JDBC Data Sources" Watch "Retrieving a List of All ODBC/JDBC Data Sources" New topic
Author

Retrieving a List of All ODBC/JDBC Data Sources

Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
All,
I have a need in my application to present the user with a list of all available ODBC/JDBC data sources so he or she can select one.
After searching Google, the closest thing I found was the following:
////////////////////////////////////////////////////////////////////
Enumeration enum = DriverManager.getDrivers();
System.out.println(" Before - while (enum.hasMoreElements())");
while (enum.hasMoreElements())
{
System.out.println("Has Driver");
System.out.println(enum.nextElement().toString());
}
System.out.println(" After - while (enum.hasMoreElements())");

////////////////////////////////////////////////////////////////////

It returns nothing. Am I missing something here?
I appreciate any help.
Thanks,
Rich
Joe Ess
Bartender

Joined: Oct 29, 2001
Posts: 8710
    
    6

I don't think you can get a list of data sources without using JNI to hit the Win32 API. According to the Java API Docs, getDrivers():

Retrieves an Enumeration with all of the currently loaded JDBC drivers to which the current caller has access.

So it just repeats back what you've set up with registerDriver() or Class.forName(java.sql.Driver). Maybe you could make a database-based application that keeps a current list of the ODBC data source names and query that through the JDBC/ODBC bridge.


"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Joe,
I really appreciated the reply. If this is really the case, it is yet another example of how Microsoft's OLE DB/ADO/ADO.NET is superior in functionality to SUN's JDBC. In my opinion, this should really be built in to the data access library.
Rich
[ December 16, 2003: Message edited by: Rich Brockway ]
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

In this day and age, you won't find many people using JDBC-ODBC drivers in production anyways. The deployment and maintenance of thick client programs using JDBC-ODBC bridge on individual PC's is frustrating at best. For all apps ( web and thick clint ), type 4 drivers are more reliable, scalable and are better performers on the whole. Also, type 4 drivers are readily available for almost all databases which begs the question why would someone limit themselves to the JDBC-ODBC bridge anyways?
What you are proposing can be easily implemented via a properties/preferences file anyways.
Jamie
[ December 16, 2003: Message edited by: Jamie Robertson ]
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Jamie Robertson:
In this day and age, you won't find many people using JDBC-ODBC drivers in production anyways. The deployment and maintenance of thick client programs using JDBC-ODBC bridge on individual PC's is frustrating at best. For all apps ( web and thick clint ), type 4 drivers are more reliable, scalable and are better performers on the whole. Also, type 4 drivers are readily available for almost all databases which begs the question why would someone limit themselves to the JDBC-ODBC bridge anyways?
What you are proposing can be easily implemented via a properties/preferences file anyways.
Jamie
[ December 16, 2003: Message edited by: Jamie Robertson ]

Jamie,
First off, this is for a prototype application. We have no desire to invest a heap of money in a JDBC driver.
Finally, if this was something which could be accomplished with a simple "properties/preferences file," I would have completed it long ago. Your "solution" has a hole the size of Texas in it. How do you allow the user to select data sources added outside your application? You simply cannot. You need someone to physically add the new data source; this is unacceptable because there could be new unique data sources on each client machine every few days. The OLE DB/ADO/ADO.NET solution has an easy function call to allow the user to do this.

Rich
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
Rich,
What do you mean by "ODBC/JDBC data sources" ???
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Simon Lee:
Rich,
What do you mean by "ODBC/JDBC data sources" ???

Simon,
My application is essentially a data mining and data transformation application. The user needs to be able to use data from whatever data sources he or she has on the client machine. So, the user needs to see a list of the data sources. For example, in OLE DB/ADO/ADO.NET, you make a simple function call to display a dialog which displays all the drivers and lets them select a driver and a data source. For example, the user can select the Access driver and select an Access database. I was looking for similar functionality in JDBC.
Thanks,
Rich
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24166
    
  30


The OLE DB/ADO/ADO.NET solution has an easy function call to allow the user to do this.

Well, then you should probably consider building your prototype using that particular alphabet soup rather than Java. Since the whole concept of an "ODBC data source" is platform-dependent, and irrelevant anyplace but Windows, so you might as well just build a Windows-only solution.
Note that by definition, a true relational database offers uniform access to all information through the query language -- including the names of available catalogs (the closest SQL concept to the "data source.") Just because the implementors of the JDBC-ODBC bridge (not written by Sun, incidentally, but by a contractor) didn't go out of their way to work around the inadequacies of the ODBC "technology" is no reason to criticise the Java platform as a whole. If you want to use databases on a computer, Java works just fine. If, on the other hand, you want to use Access on Windows, well, hand Bill your pound of flesh and do it the officially sanctioned way. Don't use a caliper to pound a rusty nail.


[Jess in Action][AskingGoodQuestions]
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Originally posted by Rich Brockway:
Joe,
I really appreciated the reply. If this is really the case, it is yet another example of how Microsoft's OLE DB/ADO/ADO.NET is superior in functionality to SUN's JDBC. In my opinion, this should really be built in to the data access library.
Rich
[ December 16, 2003: Message edited by: Rich Brockway ]

I used the feel the same way. However, it is outside of the goal of Sun's JAVA to do something like this. As Java is a write once run anywhere HIGH LEVEL language, it wouldn't make since to provide Windows specific functionality to the core Java API.
The OLE DB/ADO/ADO.NET solution has an easy function call to allow the user to do this.
Of course it does. That API was written by Microsoft for Microsoft.
We have no desire to invest a heap of money in a JDBC driver.
Who does? That is why most of them are free.


GenRocket - A Test Data Generation Platform
paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20271
    ∞

We have no desire to invest a heap of money in a JDBC driver.

Evidence that you come from a MicroSoft world?
Welcome to Java. If you have work to get done, you don't have to drag in the executive and bean counters for permission for every little thing. Damn near everything is free.


permaculture Wood Burning Stoves 2.0 - 4-DVD set
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Ernest Friedman-Hill:

Well, then you should probably consider building your prototype using that particular alphabet soup rather than Java. Since the whole concept of an "ODBC data source" is platform-dependent, and irrelevant anyplace but Windows, so you might as well just build a Windows-only solution.
Note that by definition, a true relational database offers uniform access to all information through the query language -- including the names of available catalogs (the closest SQL concept to the "data source.") Just because the implementors of the JDBC-ODBC bridge (not written by Sun, incidentally, but by a contractor) didn't go out of their way to work around the inadequacies of the ODBC "technology" is no reason to criticise the Java platform as a whole. If you want to use databases on a computer, Java works just fine. If, on the other hand, you want to use Access on Windows, well, hand Bill your pound of flesh and do it the officially sanctioned way. Don't use a caliper to pound a rusty nail.

Ernest,
No one mentioned the mythical "platform independence." You mean build once debug everywhere.
I was criticizing the JDBC architecture; it has a huge hole. If Microsoft found a way to work this miracle, SUN should have been able to do this as well. Plainly, it was an oversight.
As for giving Bill a pound of flesh, C# (.NET SDK) and ADO.NET are both free.
In the end, SUN AND Microsoft are both corporations focusing on maximizing profit. One is not holier than the other.
Rich
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Paul Wheaton:

Evidence that you come from a MicroSoft world?
Welcome to Java. If you have work to get done, you don't have to drag in the executive and bean counters for permission for every little thing. Damn near everything is free.

Paul,
I have not looked at every single DBMS out there. When I did a few years ago, most of the DBMSs did not have a free driver. As a matter of fact, the "smaller" DBMSs required a per seat license fee.
As for Microsoft, you very rarely if ever paid for the data access library. Microsoft provided DAO, ADO and ADO.NET for free.
Rich
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Gregg Bolinger:

I used the feel the same way. However, it is outside of the goal of Sun's JAVA to do something like this. As Java is a write once run anywhere HIGH LEVEL language, it wouldn't make since to provide Windows specific functionality to the core Java API.
The OLE DB/ADO/ADO.NET solution has an easy function call to allow the user to do this.
Of course it does. That API was written by Microsoft for Microsoft.
We have no desire to invest a heap of money in a JDBC driver.
Who does? That is why most of them are free.

Gregg,
Exactly how does supplying a list of data sources break so called "platform independence?" It does not. When they built the library, all they had to do was figure out a standard way to get the data sources.
Rich
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
How will your application work for your Mac and Linux users?
As anyone knows, if you want to write code that works on a native platform you write native code. What is the big deal about writing a little C program to get the available ODBC sources on the Windows box?
By the way, the JDBC-ODBC driver is not recommended for production use.
As far as the joke about "write once, debug everywhere" I have been writing code that runs on multiple platforms and have never run into this mythical debugging problem. Java runs smoothly from platform to platform without change.
[ December 17, 2003: Message edited by: Thomas Paul ]

Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Rich, your best bet is to use the Microsoft tools. If you are running only on Microsoft technology, using Microsoft tools, and want to know Microsoft datasources, your best solution is probably a microsoft solution.
but...
You could load simulate the ODBC datasource by providing a Connection broker class that allows you to choose an already defined Connection to a list of known datasources, and load it accordingly. These connection properties can be saved locally in a Properties file. You can also allow your users to create their own new Connections by providing the same information you would have to specify when creating an ODBC datasource ( server name, instance name, etc. ) and choose the appropriate available driver( as you would an ODBC datasource ). The only information you would need in addition to the ODBC properties you would set in ODBC datasources is a port number to connect to. These new Connections properties can be added to the properties file and will be available next time in addition to the existing datasources. These holes the size of Texas ( as you put it ) can easily be filled in when implementing the above. It isn't as easy as calling a method from ADO.NET, but can be accomplished with some thought and effort( and a properties file dang it! ).
*note: I've done this and has been in production for 2 years now. It allows you choose from a pre-defined list of available network databases or use the available drivers to create your own connection to any of a DB2, Access, Oracle, Sybase, and MSSQL Server.
Jamie
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24166
    
  30


When they built the library, all they had to do was figure out a standard way to get the data sources.

As I said, real databases let you get this information from the query language. It's not Java's job to work around a deficiency in Microsoft Access.
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
Rich,
For example, the user can select the Access driver and select an Access database. I was looking for similar functionality in JDBC
Ok, so what I figure you want to do is something like.
1) seach for all classes in your classpath that extend java.sql.Driver (or allow the user to select/add another jar using file dialog)
2) put them in a dropdown, along with connection string user/password textfields.
3) Connect to the database.
This is pretty much what you get in application server datasource configuration screens. As you've already realised java doesn't maintain an 'odbc' style list of pre-configured sources. Applications servers do actually do this by using jndi, so you could store them in the same manner (which is how I would do it - i.e. rip off app server jndi pool code).
Look at some of the screendumps on this page http://www.webagesolutions.com/knowledgebase/waskb/waskb001/ is that the kind of things you want to do?
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Originally posted by Rich Brockway:

Gregg,
Exactly how does supplying a list of data sources break so called "platform independence?" It does not. When they built the library, all they had to do was figure out a standard way to get the data sources.
Rich

It breaks platform independence because Microsoft Windows is the only OS that provides some sort of Datasource Bridge (ODBC). So this task would be specific to Windows. Even though your program would be written specifically for the Windows platform, you should be able to take it to any OS and run it without a hitch. Providing a Windows only solution in the API would break that.
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Ernest Friedman-Hill:

As I said, real databases let you get this information from the query language. It's not Java's job to work around a deficiency in Microsoft Access.

Ernest,
Again, I want a list of the data sources from JDBC. Getting the data from Access is trivial.
Rich
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Gregg Bolinger:

It breaks platform independence because Microsoft Windows is the only OS that provides some sort of Datasource Bridge (ODBC). So this task would be specific to Windows. Even though your program would be written specifically for the Windows platform, you should be able to take it to any OS and run it without a hitch. Providing a Windows only solution in the API would break that.


Gregg,
I totally disagree. It would list the available data sources for that platform. For example, Windows would list ODBC data sources as well as others. On Unix, it could list the drivers that system provides via something like iODBC.
In the end, the VMs have to have different implementations to support different platforms.
Rich
[ December 18, 2003: Message edited by: Rich Brockway ]
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Simon,
That is exactly what I am looking to find.
Rich
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
All,
I appreciate everyone's feedback on this. I do not want this to escalate into a full blown battle of SUN vs. Microsoft or Java vs. Microsoft.
I'm going to dig into JNI and call the appropriate DLLs to display the dialog. Yes, it is Windows only solution. However, at this point, that is all I need. If the application is ever moved to another OS, I'll address the issue then.
Thanks,
Rich
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

Originally posted by Rich Brockway:


Gregg,
I totally disagree. It would list the available data sources for that platform. For example, Windows would list ODBC data sources as well as others. On Unix, it could list the drivers that system provides via something like iODBC.
In the end, the VMs have to have different implementations to support different platforms.
Rich
[ December 18, 2003: Message edited by: Rich Brockway ]

You make a valid argument Rich. I'm glad you got something useful out of this thread. Good luck.
Dana Hanna
Ranch Hand

Joined: Feb 28, 2003
Posts: 227
I wrote a SQL ODBC toold in Java - it reads from "c:\WINDOWS\system32\ODBC.INI" and parses out the databases. It also launches the ODBC config window by spawning "c:\WINDOWS\system32\odbcad32.exe"
(on win 95-ME and XP), on 2K and NT replace WINDOWS with WINNT.
BTW - check out SquirrelSQL on www.sourceforge.net to see a JDBC driver search and connection saving mechanism...
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Gregg Bolinger:

You make a valid argument Rich. I'm glad you got something useful out of this thread. Good luck.

Gregg,
Thanks.
Rich
Rich Brockway
Ranch Hand

Joined: Oct 25, 2001
Posts: 34
Originally posted by Dana Hanna:
I wrote a SQL ODBC toold in Java - it reads from "c:\WINDOWS\system32\ODBC.INI" and parses out the databases. It also launches the ODBC config window by spawning "c:\WINDOWS\system32\odbcad32.exe"
(on win 95-ME and XP), on 2K and NT replace WINDOWS with WINNT.
BTW - check out SquirrelSQL on www.sourceforge.net to see a JDBC driver search and connection saving mechanism...

Dana,
Thanks for the tip. I'll check it out.
Rich
Mark Amabile
Greenhorn

Joined: Jul 27, 2011
Posts: 3
I found this on the net.

http://codeglobe.blogspot.com/2010/04/odbc-system-dsn-list-using-java.html#comment-form

and if the link doesn't work ... here's the code.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieving a List of All ODBC/JDBC Data Sources
 
Similar Threads
urgent help
JdbcOdbc Connection
URL error (DSN not found)
Error trying to connect MS Acess 12 database
problem using DBCP in struts application