Accessing MS SqlServer from Oracle using jdbc

[thomgreen]

I have jdbc installed and the thin driver is working. How can I use this to access a MS SqlServer db? I have heard of other systems accomplishing this but not quite sure how to go about it.
OS: Sun Solaris 8
db: Oracle 8.1.7

Do I need to download unix version of sqlserver drivers? a little direction would be appreciated.

 

[palbano]

Access MS SQL Server from Oracle ??

I don't get that.

>> Do I need to download unix version of sqlserver drivers?

To access MS SQL Server from Java on a unix OS, yes.

Sun maintains a list of JDBC drivers:

http://industry.java.sun.com/products/jdbc/drivers

-pete

 

[thomgreen]

Excuse my ignorance but I am somewhat new to using Java/jdbc. I want to access a MS SQLServer table and pull it into Oracle - can not use ODBC for this(won't pay for license). Is It possible to use JDBC to get the info from SQLServer and insert int Oracle db. Can I open a connection to MSSQLServer and query, then open a conn. for Oracle and insert. I don't know... just throwing out possible(or maybe not) ideas.

 

[palbano]

Sounds perfectly reasonable to me. A question though. Is this an operation that will be ongoing on a frequent basis? Because if it’s just once in a while thing there are simpler mechanisms provided by the databases themselves for doing things like that.

-pete

 

[JVZ]

I found this on the Java.Sun.Com Forum...Hope this helps...Also you have want to get with you DBA to see if heterogeneous services on Oracle can do want you want.

**** TAKEN FROM JAVA.SUN.COM ****
Re: JDBC, ODBC, UNIX, SQLServer. Help!
Author: jschell
In Reply To: JDBC, ODBC, UNIX, SQLServer. Help! Jan 23, 2002 5:24 PM

Reply 2 of 3


Somewhere I have explained this. Actually I have explained it multiple times. But since I can't find it using the search engine I wouldn't really expect anyone else to.

So the background first...

------------------------------------
This section has nothing to do with java. Keep that in mind even though some of the terms are the same.

ODBC is a shell that runs on a particular OS. ODBC uses drivers to access different data sources. Most of the time those data sources are databases.

The ODBC shell and drivers only runs on that OS. You can't move it to another OS nor can you access it any way from a remote machine - even if the remote machine has the same OS.

There are ODBC implementations on a number of platforms: windows, linux, solaris, HP and others.

Microsoft only makes ODBC drivers for windows. They threatened to make them for unix at various times but I have never actually seen one.

There are third party companies which claim they make ODBC drivers for MS SQL Server and MS Access for OSes besides windows. However, every implementation of this that I have seen is a proxy driver (explained below.)

Windows automatically comes with ODBC.

Other OSes, in particular Solaris and Linus must have the ODBC shell explicitly installed. And keep in mind that the shell does nothing without drivers. So if one wants to use ODBC on Solaris or Unix one must find drivers for it.

The only way to connect to a remote database is if the ODBC driver supports connecting to a remote database.

And remember none of the above had anything to do with java.

------------------------------------
This section applies to java.

Java has specified an API to databases which is called jdbc. It is similar to ODBC. It is a shell which relies on drivers to do the actual work.

The jdbc-odbc bridge is such a driver. That driver talks to ODBC on the machine. Note that it does not talk to the ODBC drivers but rather the ODBC shell.

The jdbc-odbc bridge exists on Windows and Solaris. It probably exists on Linux.

There is no way for the jdbc-odbc bridge to talk to a remote machine. All it talks to is the ODBC shell.

So in java, the only way to connect to a remote database is if the ODBC driver supports connecting to a remote database.

------------------------------------
Miscelleneous stuff:

A proxy client/server consists of two processes. One is the server and one is the client. The client, for purposes of this discussion, would be a specific jdbc driver. The server waits for a request from the client and does the actual work and then returns a result to the client. The server, again for this discussion, would be a process that would run on a windows box.

A proxy clien/server solution can be thought of as a type 3 driver if that helps any.

Keep in mind that a proxy solution, in a unix windows configuration, would require that a proxy server is running on a windows box. And this process is not the same as the database.

-------------------------------------
So how does this to the original question:

First there is no ODBC driver on any unix platform that will talk to MS SQL Server - keep in mind that as I said there are commercial solutions which claim this. And all of them are proxy solutions. Maybe that meets your needs, or maybe not.

I know of two drivers which might work in your situation. Both are type 4. One is a just released from Microsoft. You will have to search this site for a link to it. The other is from

http://www.freetds.org.

I haven't used either.

I do know of one free proxy driver which I have used successfully. It is rather limited but it is free, it comes with the source code and it works. It is RmiJdbc at

http://www.objectweb.org/

----------------------------------