OK, so here is my problem and I am looking for the best solution to fix it. I want to create a JSP driven site that can run on multiple databases. The only simularity that the databases will have is the structure, but as we all know it isn't that easy to mix the SQL statements together. I don't have the money to create an application that will change up the SQL statements and make them work for all of the databases this is implemented on, but I will be able to program the SQL to make it run correctly on each platform. My problem is that I want to have SQL modules that can be changed based upon what DB I am using. Does anyone have any ideas on what might work and what would be the easiest way to do this. Thanks, Matt
My problem is that I want to have SQL modules that can be changed based upon what DB I am using.
Not sure I understand your question, but here are my thoughts:
What do you mean by SQL modules, and why would you want to change them per DB?
If your DBs have the same structure, why not just program in standard SQL, as opposed to using any DB-specific SQL extensions. Then, if your JSP files use JDBC to connect to your DB's, you should be able to migrate relatively seamlessly from DB to DB.
------------------ Miftah Khan - Sun Certified Programmer for the Java� 2 Platform - Sun Certified Web Component Developer for the Java� 2 Platform, Enterprise Edition
An IDE is no substitute for an Intelligent Developer.
Joined: Nov 22, 2008
Peter, thank you for your references. At first when I read your comment "In most cases, you'd be completely mad to develop at the JDBC level", I thought to myself, "What is he talking about?!?!" But then, I followed your Sun link and read http://www.sun.com/forte/ffj/resources/articles/transparent.html , and I have to say, it really opened my eyes!!! Now, I completely understand where you're coming from. And the whole concept of data objects, which used to just seem like a neat but theoretical concept to me, now seems very practical and applicable. Thank you, -Miftah ------------------ Miftah Khan - Sun Certified Programmer for the Java� 2 Platform - Sun Certified Web Component Developer for the Java� 2 Platform, Enterprise Edition [This message has been edited by Miftah Khan (edited October 02, 2001).]
I use an abstraction to allow my programs to dynamically switch databases at startup based on a properties file, but it could be modified to allow for a collection of connection objects. I set up for Connection, Query, and Exception, then sublcass off of them for Oracle and Sybase in my case. The Query and Exception classes are returned/used from the Connection so they are of the proper type. It's still rather crude, but heck, it works! I was forced to do this primarily becuase Sybase has funky exception handling that doesn't work with Oracle nicely. Both databases use the same SQL. Hope this helps!: JDBCConnector.java
Here's some usage code snippets:
CJP (Certifiable Java Programmer), AMSE (Anti-Microsoft Software Engineer)
Author of Posts in the Saloon