User selecting "Data Source" via JDBC/ODBC

In my application I would like to have the user select a "data source" using either ODBC or JDBC environment.  Please note that I intend my application to operate in a cross platform environment (apple, ms windows).
snajalmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

for_yanCommented:
So what is your question?

Do you have two databses and want user to slecet one of the two?
What is the database you are talking about - is it Access with two mdb files or something else?
for_yanCommented:
Is it all happenning in the Java appalication or in the web site driven by the servlet ?
for_yanCommented:
In general I don't see much of a problem - give datasources some names or codes recognizable to the user, and in the program you identify selected
code and open JDBC connection to the database corresponding to the selected code - just assigning connection parameters using say if - else
statements
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

snajalmAuthor Commented:
There databases located on different servers and this application is meant to allow the user to access either of these databases through an OBDC or JDBC connection sending the query for execution and getting back the result through XML.  It's all supposed to be happening in the Java application!
for_yanCommented:



String connectionString = null;

if(selectedDatasource.equals("one")){
connectionString = ...
} else {
connectionString = ...

}

Cobnnection conn = Driver.getConnection(...)



for_yanCommented:
Server is in the connection string - so there is no problem with that.

What kind of databases you are talking about?
If they are on different serveer it should be say Oracle or SQLServer - so the server will be in the connection string
I don't think you need ODBC for that purpose;
for_yanCommented:

So I see no problem with it - JDBC will take care of it - you can open connection to different databases very easily - can even keep them open simultaneously if needed,
even though it will probably not be necessary in your case.
snajalmAuthor Commented:
The reason to use ODBC would be to identify which server type the user is intending to connect to.  The application does not know it before hand, that's why the user should be prompted for it.
for_yanCommented:
As far as I understand, you don't need to use ODBC - which is the type of the database?

Actually users do not need to know about particular serevr - they can use any name to identify the two databases - if they know about servers they can use name
of the server - otherwise any name which they recognize should be fine.

After you get selection form the user - you can establish the connevction and communicate iwth the database which they selected.
Please, explin where do you see the problem?
 
CEHJCommented:
If it's ODBC it's slightly more of a problem:

a. To browse candidate datasources WITH DSNs, you need native code
b. To browse candidate datasources WITHOUT DSNs, you'd need to iterate for file extensions such as .mdb
snajalmAuthor Commented:
See, there is an existing software written in C# that allows the user to connect the "Data Source" to the available "Data Set" as a 1 to 1 relationship.  In that page it gives the user the ability to choose a "Data Source" through Microsoft's "ODBC Manager/Administrator" for that connection (you know the one under Window's control panel).  Now this program is supposed to be written in Java to serve cross platform functionality (being able to operate both in Windows and Mac).  That's why I'm not how I should get around this situation so I could get a "Data Source" selection both in Windows and Mac.
for_yanCommented:
In java you most probably will not need ODBC.

What kind of databases are you talking about - is this Oracle or SQLServer, or something else?
for_yanCommented:

How is it impleented now - do you have both datasources mapped to different databases through  ODBC control?
for_yanCommented:
If so, you need to find the details of connections from the ODBC and make direct JDBC connection in Java using that information.
This would be system independent implementation.
snajalmAuthor Commented:
The "Data Sets" are all passed through by the server and the "Data Source Driver" is chosen by the user which could be anything because this application is used by different companies using different databases, so  this thing's gotta be able to interact with all databases through their relevant drivers, which I believe in Microsoft is set through the ODBC manager and in Mac, I'm not sure how it's handled.
for_yanCommented:
In java application you can ask user for the details of databses connection and the type of database and make direct connection
through JDBC - using appropriate connection string for each type of database.
Connection strings have different formats howevere SQL is mostly the same - it probably anyway works for all databases now - it will work also when you make direct jdbc connections


for_yanCommented:

This is a list of connection strings formats for different databases:
http://www.java2s.com/Tutorial/Java/0340__Database/AListofJDBCDriversconnectionstringdrivername.htm

It certainly requires some testing , but it is doable
for_yanCommented:
Just make a method or a class with static method - it will take the connection information and return java.sql.Connection object, which you could then use
to interact with the database

That can probably be also organized through Hibernate, but if you have no experience iwth Hibernate it
will take too much work and learning; besides it may require to change the logic of your application, so better to do it directly
through plain JDBC
CEHJCommented:
>>
See, there is an existing software written in C# that allows the user to connect the "Data Source" to the available "Data Set" as a 1 to 1 relationship.  In that page it gives the user the ability to choose a "Data Source" through Microsoft's "ODBC Manager/Administrator" for that connection (you know the one under Window's control panel).  Now this program is supposed to be written in Java to serve cross platform functionality (being able to operate both in Windows and Mac).
>>

That's the kind of thing i mentioned in my point a. Essentially it would be native code that's doing that. You'd need to do the same thing in Java, using, say JNA. Have you got the C# source for that?
snajalmAuthor Commented:
Is there a pre-written class or application out there that would enable me to pop out a windows (just like the ODBC Data Source Manager) with all the driver settings, providing the user the ability to add or remove drivers, to use in my code??!
snajalmAuthor Commented:
Yeah CEHJ, that's exactly what I mean (thanks for pointing that out).  No I don't have access to the C# source code.  What is JNA??!  If I could do the same in Java, it'd be just great!  If you could shed more light on this, I would very much appreciated!
CEHJCommented:
JNA: http://www.javaworld.com/javaworld/jw-02-2008/jw-02-opensourcejava-jna.html

I would start by looking at how you'd do this using C#. That will lead you more naturally to how you should do it in Java

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
for_yanCommented:

JNA is Java Native Access which allows you to access libraries written in native code from your java program.

In theory, if you need to have "cross platform" only on Windows and Apple you may look at ODBC administrator for apple:

 http://support.apple.com/kb/dl895

and call either windows ODBC control or this apple control through JNA after you determine the system in your java code
using System.getProperty("os.name");
In practice, these accesses to native code using JNA may be quite tricky and even not always possible.


However, if you are looking for truly cross platform solution you should rather stick to JDBC only.
JDBC was in fact designed for cross-platform operation - your JDBC code should work identically on windows, Mac, or unix.
It is true, that there will be some difference depnding on the type of databses, but this
is in many ccases limited to connection string differences.


You are saying that you don't have access to C# source code?
So what is your overall task?

Are you re-writing your application as a whole to Java starting not from the existing code but from specification?
Does this piece encapsulating the selcetion of the database represent a separate module or it is just embedded in
your big C# application?


 
 
snajalmAuthor Commented:
Thank you for your valuable information "for_yan".  I enjoy learning more about these technical concepts and being able to interact with experts like you!  

Well to answer your question, I must say that the legacy application in place is something that solely runs on Windows and is very badly written and it awfully using resources to the point that it caused occasional bottlenecks and system failures.  We also don't have access to the source code for some private reasons.  What I'm to do is to work with the known software requirements and adapt the functionality of the legacy system to some extent and ensure a reliable and optimized code with cross platform functionality as the primary objective of this project!  

The software is not meant to be anything large, it's just a mediator application running in between of the user and the server, providing the user with scheduled report retrieved off the an specific data server.

As "CEHJ" pointed out the legacy software in place triggers the Windows' native "ODBC Data Source Administrator" for the user to select a data source driver and uses that to connect to the specific database.  Now it would help me a great deal to know if there are any available "SIMPLE"  "JDBC  Data Source Administrators" out there which I'd be able to utilize or call from within my code to enable my user to select a corresponding data source driver, just like the legacy software making that call to the local Windows ODBC Data Source Administrator!!!!!

Regards
for_yanCommented:
This is an example of using JDBC-ODBC bridge:
http://www.rgagnon.com/javadetails/java-0107.html
Once you setup your datasources in the same way on Windows and Mac - you should be able
to use this drivber - which comes form Java distribuition -  to connect to your datasources "sun.jdbc.odbc.JdbcOdbcDriver"

The disadvatgae of that is that you have to have ODBC installed and configured on the machine
when deploying your application.
I know you usually have it installed on Windows (though drivers on the newest OS - windows 7 and 64 bit )
are a little bit of aproblem - many confusins with them), but I don't know how easy it is to install ODBC on MAC

So if I were you, I'd rather go with pure Java JDBC implementation as I outlined in my previous posts


snajalmAuthor Commented:
I think this is a good work around and will serve my purpose.  Just one extra question!  How would I be able to launch "ODBC Data Source Administrator" from within my Java application and after having the user chosen or added the DSN, retrieve his/her selection for my code's ultimate connection to the associated database!
for_yanCommented:

I think on Windows the program is called odbcad32.exe

In order to launch it form java program you'll do it:

Process p = Runtime.getRuntime().exec("C:\WINDOWS\system32\odbcad32.exe");

I don't know exactly how it is called on Mac and is it installed by defaulst on Mac systems


snajalmAuthor Commented:
Great, but how are we supposed to fetch the arguments/parametes selected by the user??!  I'd need to have them to incorporate them in my java code!
for_yanCommented:
Can you explain what is hapenning in your aplication right now?
for_yanCommented:
I was thinking that it opens ODBC control and the user configures datasource himself?
snajalmAuthor Commented:
When the user launched the "ODBC Data Source Administrator" from my java app using the command you just mentioned, they would then get to select a data source for their intended connection.  When they click ok and get out of the "ODBC Data Source Administrator" the Java app would need to read the selection and create a connection instance to the selected data source.  I hope it answers your question!
for_yanCommented:

ODBC Data Source Administrator allows to configure datasources - you can configure many datasources there - how will
the (current C#) program which continues  ecexcution after user configures dataosurce - know which of the datasources to use?
Does it ask the user to configure the datasource with some pre-determined name before it launches ODBC?

snajalmAuthor Commented:
In the Data Settings section it links a "Data Set" to a "System DSN" provided in the "ODBC Data Source Administrator"!  The user can add their "System DSN" in the "ODBC Data Source Administrator" and select that to be linked to their available "Data Set"!

No it doesn't ask for a per-determined name and it just receives information from the "ODBC Data Source Administrator"
snajalmAuthor Commented:
In fact it gets a listing of the available "System DSN" for the user to choose from!  The link to "ODBC Data Source Administrator" is there for any possible addition or modification of DSNs!
for_yanCommented:
Then you just do the same think - you offer the user to slect of the existing list (or maybe type some new as I undersatnd)
then you fire this ODBC from your code using

Process p = Runtime.getRuntime().exec("C:\WINDOWS\system32\odbcad32.exe");

and when the user finishes configuring and closes it - you go forward with your java application and slected DSN and connect
using the JDBC-ODBC bridge as in the example above:

http://www.rgagnon.com/javadetails/java-0107.html
CEHJCommented:
>>Great, but how are we supposed to fetch the arguments/parametes selected by the user??!  I'd need to have them to incorporate them in my java code!

You won't be able to - which is exactly why i suggested using JNA with the Windows ODBC api calls. Using the existing Windows utilities won't help you, unless you start writing some nasty kludges to capture window text fields (not recommended)

>>I think this is a good work around and will serve my purpose.  

It's not in any way a workaround. The purpose of the JDBC-ODBC bridge is to establish a connection, when you have ALL the connection information to hand. The objective of your question is how to enumerate and select the connection information. Actuallu *using* it to get a connection is relatively simple.

I DID mention a workaround ( http:#36899085 ), which is to enumerate .mdb files, but if you want to do it properly, you need to follow what i mentioned and not be sidetracked.
snajalmAuthor Commented:
I found a very interesting and easy way of retrieving "System DSN" listing located in Windows registry.  This way I can easily associate a "System DSN" to my data set and ultimately make the appropriate connection with JDBC-ODBC bridge.

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

Thank you guys for your useful tips and your resourceful presence!
snajalmAuthor Commented:
Again many thanks to "for_yan" and "CEHJ" for helping me through this!
CEHJCommented:
:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTTP Protocol

From novice to tech pro — start learning today.