CODEWITHSUNDEEP
javasql-serveroracle-databasedatabase-designdatabase-connection
NIVESH SENGAR
NIVESH SENGAR

Reputation: 1333

Bridge Between Oracle and MS SQL Server

We are making an application which is machine dependent.
Now we deploy our application on several client machines but problem is that every client have database from different vendors. Currently we are facing conflicts between SQL Server and Oracle.
Our Application built on Oracle database and now we want to access SQL Server. Is there any way to do it as I am little amateur with databases and I dont want to change the queries and configuration settings for different databases.

Upvotes: 0

Views: 1030

Answers (3)

pili
pili

Reputation: 825

Create Linked Server and use openquery if necessary.

http://technet.microsoft.com/en-us/library/ms188279.aspx

Upvotes: 0

Stephen C
Stephen C

Reputation: 718826

What you need is something that provides a layer that provides database independence. There are a variety of ways of doing this.

  • You could use a ORM (Object Relational Mapping) technology such as JPA (with Hibernate being the prime example). A typical JPA implementation has backends for a range of different databases.

  • You could use an existing product that supports database independence by (roughly speaking) mapping SQL statements between different SQL dialects.

  • Some people suggest using ODBC.

  • You could implement a DAO API with different DAO implementation classes for each backend database. If you stick to SQL-92 conformant DDL and DML as much as possible, there is a good chance that there will be a lot of commonality between the DAO implementations. (JDBC provides database independence at the API level, provided you don't use vendor specific extensions. I recall having problems with Oracle's JDBC drivers doing things in non-standard ways ... but they may have gotten their act together now.)

... I dont want to change the queries and configuration settings for different databases.

If you use an ORM and its query language, you won't have to. If you implement your database stuff using SQL and JDBC, it is largely a matter of sticking to the SQL standard and the standard JDBC API methods respectively.

Related question:

Upvotes: 3

Joe2013
Joe2013

Reputation: 997

Recommended approach is to use ORM tools like Hibernate. if that is not possible then use StoredProcedures ( make sure that it uses only normal PL SQL and no database specific features) for database operations

Upvotes: 0

Related Questions