Member Avatar for Thirusha

I want to develop a web app that can allow the user to change the type of database he/she is using(Access, Sql Server, Oracle), just by changing the connection properties in a properties file.
I am assuming that this is possible.

Would it become too complicated meaning way too much work(its not for a client its just for the company), if there are differences in writing the sql statements depending on which database is used?

My big plan is that the app can be used with either of the above mentioned databases just by changing the connection properties.
Not sure if i have thought about all the implications.
Any comments or suggestions will be highly appreciated.

  • 2 Contributors
  • 1 Reply
  • 86 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by stephen84s
Member Avatar for stephen84s

This **seems** like a production level project.
Now considering using three different DBMSs', you will no doubt have to use three different drivers for each of the databases. First thing I suggest is is use a Type 4 Driver for each of the DBMSs (eg jtds for SQL Server, this for Oracle, etc) apart from MS Access for which I have not yet come across one (because the JDBC-ODBC bridge will give you the worst performance in terms of features as well as speed).
Next as the database changes, the driver that you choose to load (using Class.forName()) would also have to change, and with that the Connection URL would also change.

Next you would also have to limit yourself to using just the standard SQL features (SQL 92 if using SQL Server 2000) in order to avoid rewriting your SQL Statements again and again for each database.

These are just **some** of the things that come to mind that you will have to handle.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.