design for using different dbs

design for using different dbs

Post by j » Wed, 16 Jul 2003 20:37:46



I'd like to get some ideas on best object design for using multiple dbs
e.e. Oracle and Sybase. In the past I've only done some re-writing of
apps to point at either DB and just did some and then use a case
statement, looked at the JDBC driver class name. If it had the word
Sybase in it I did one thing and if it had Oracle in it I did another.

When I start the app I use parameters which tell it the URL for the DB
and which driver to use.

I could use one DB object for each database but I think that would get
unweildy with at lesat 2 methods for each object with whose data is
inthe db, e.g. customer, invoice, line item.

I could write one object for each database so I'd have a sybaseCustomer,
  oracleCutomer etc.

Then were do you make the decision? Do I use and object factory that
knows about which of the two to use?

ObjectFActory.getObject("Customer");

Just looking for advice fromthose that have been down the road before.

Thank
jw

 
 
 

design for using different dbs

Post by J » Thu, 17 Jul 2003 08:39:42


On Tue, 15 Jul 2003 7:37:46 -0400, j wrote

IWith my web app, I use Hibernate for all my database/object mapping.  So
when I need to use a different database, I change the properties file for
Hibernate.  Then the system will use the correct database.

Makes writing db objects so easy, and makes the switching between dbs a
little easier.

J

 
 
 

design for using different dbs

Post by Ed Y » Thu, 17 Jul 2003 10:37:14


I've written a little white paper on how this can be achieved.

http://www.asg.sc.edu/pdf/DatabaseIndependence.pdf

Good luck!


> I'd like to get some ideas on best object design for using multiple dbs
> e.e. Oracle and Sybase. In the past I've only done some re-writing of
> apps to point at either DB and just did some and then use a case
> statement, looked at the JDBC driver class name. If it had the word
> Sybase in it I did one thing and if it had Oracle in it I did another.

> When I start the app I use parameters which tell it the URL for the DB
> and which driver to use.

> I could use one DB object for each database but I think that would get
> unweildy with at lesat 2 methods for each object with whose data is
> inthe db, e.g. customer, invoice, line item.

> I could write one object for each database so I'd have a sybaseCustomer,
>   oracleCutomer etc.

> Then were do you make the decision? Do I use and object factory that
> knows about which of the two to use?

> ObjectFActory.getObject("Customer");

> Just looking for advice fromthose that have been down the road before.

> Thank
> jw

 
 
 

design for using different dbs

Post by j » Thu, 17 Jul 2003 20:27:56



> I've written a little white paper on how this can be achieved.

> http://www.asg.sc.edu/pdf/DatabaseIndependence.pdf

Thanks Ed for the pointer. you've provided some great tips but
unfortunatly I don't see how they anser my question.

I have a app that I want to point to Oracle today and Sybasee Tomorrow.

Both dbs have a stored proc named GetClientList which returns a result
set to the client. The way the Oracle does it and the way Sybase does it
are totaly differrent in how they pass back the result set so that you
can't just change JDBC drivers. Someplace in the app I have to write
database specific, Oracle, Sybase whatever, code for methods with the
same name that return the same ResultSet. It's only a couple of lines of
code different but it still means that any db accessor that that calls
an SP that returns a Result set needs to be database specific.

My question is what is the best way to, or more probably what are the
trades offs in the different ways, to do this?

jw

 
 
 

1. Mapping different DBs to a single DB

Is this possible???

Our company over the years has stored information on various DBMS, namely

-dBase IV
-ms access
-Oracle
- SQL server
My boss would like to leave the data stored at its original
place and have one DB (or application) "map" to the other DB's
and pull the data when requested.

Are there any tools or any applications that can help me do this?

Thank you

Magog

2. How to insert unknown data using batch update method?

3. Dimensions from 2 Different DBs

4. backend dies when a user defined type returns null

5. access data from two different dbs

6. Searching for text

7. Join query with tables from _separate_ DBs on different SQL Servers

8. DB size and performance ?

9. Stored Proc permission different in 2 DBs

10. Info from 2 different DBs

11. Join 2 tables from 2 different DBs

12. Refrencing Tables In Different DBs

13. Performance Bug? Timing different on identical DBs