?
Solved

User selecting "Data Source" via JDBC/ODBC

Posted on 2011-10-02
39
Medium Priority
?
396 Views
Last Modified: 2012-05-12
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).
0
Comment
Question by:snajalm
  • 20
  • 14
  • 5
39 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 36898799
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?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36898801
Is it all happenning in the Java appalication or in the web site driven by the servlet ?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36898806
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
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:snajalm
ID: 36898807
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!
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36898810



String connectionString = null;

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

}

Cobnnection conn = Driver.getConnection(...)



0
 
LVL 47

Expert Comment

by:for_yan
ID: 36898814
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;
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36898823

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.
0
 

Author Comment

by:snajalm
ID: 36898824
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.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36898983
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?
 
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36899085
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
0
 

Author Comment

by:snajalm
ID: 36899112
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.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899114
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?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899116

How is it impleented now - do you have both datasources mapped to different databases through  ODBC control?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899119
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.
0
 

Author Comment

by:snajalm
ID: 36899130
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.
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899136
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


0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899141

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
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899148
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
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36899250
>>
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?
0
 

Author Comment

by:snajalm
ID: 36899303
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??!
0
 

Author Comment

by:snajalm
ID: 36899314
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!
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 900 total points
ID: 36899377
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
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36899835

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?


 
 
0
 

Author Comment

by:snajalm
ID: 36901450
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
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1100 total points
ID: 36901552
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


0
 

Author Comment

by:snajalm
ID: 36901657
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!
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1100 total points
ID: 36901683

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


0
 

Author Comment

by:snajalm
ID: 36901690
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!
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36901707
Can you explain what is hapenning in your aplication right now?
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36901715
I was thinking that it opens ODBC control and the user configures datasource himself?
0
 

Author Comment

by:snajalm
ID: 36901723
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!
0
 
LVL 47

Expert Comment

by:for_yan
ID: 36901737

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?

0
 

Author Comment

by:snajalm
ID: 36901781
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"
0
 

Author Comment

by:snajalm
ID: 36901790
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!
0
 
LVL 47

Assisted Solution

by:for_yan
for_yan earned 1100 total points
ID: 36901828
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
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 900 total points
ID: 36902111
>>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.
0
 

Assisted Solution

by:snajalm
snajalm earned 0 total points
ID: 36903790
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!
0
 

Author Closing Comment

by:snajalm
ID: 36935181
Again many thanks to "for_yan" and "CEHJ" for helping me through this!
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 36935593
:)
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A few customers have recently asked my thoughts on Password Managers.  As Security is a big part of our industry I was initially very hesitant and sceptical about giving a program all of my secret passwords.  But as I was getting asked about them mo…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Suggested Courses
Course of the Month16 days, 23 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question