×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Which database do I query?

Which database do I query?

Which database do I query?

(OP)
I have a program that runs on several systems which have either MSServer or Oracle as supporting database.  Unfortunately a query that runs on one database may not run on another type, because of the different SQL dialects (especially with date/time manipulation)

As I have to cope with only two types of databases, as solved the problem as follows:

I deliberately execute a query that will fail on Oracle (in my case a SELECT DATEDIFF(...) FROM ....)
If it does indeed fail, then execute the Oracle SQL;  if not execute MSSQL.

That approach works, but I'm uneasy with it:
- There may be more than two types of DBs in the future (There shouldn't be, but life is unpredictable)
- One can simulate the DATEDIFF function on Oracle by implementing a user function/procedure.  So I can not 100% count on it (or any other function)

My question:  Can I find and access within the database itself information telling which data base it is? In some kind of catalog or system tables?

Or do you have other suggestions/approaches?

 

_________________________________
In theory, there is no difference between theory and practice.  In practice, there is. [attributed to Yogi Berra]

RE: Which database do I query?

Have a system parameter determining whether the back end database is Oracle or SQL Server. Have error checking at startup that this is a valid value with instructions to contact you ASAP if not.

Based on this, execute the appropriate SQL code.

If you then add a third database type, implement a new back end database engine type and select the code based on this.

John
 

RE: Which database do I query?

The is no way to be truly db independent, but a very close approximation is to have identical stored procedure interfaces, and obtain what you want that way.

If a stored procedure is created with an identical name and signature on each system, what each does behind the scenes is hidden from the user (encapsulated), and therefore irrelevant, provided it does what it's supposed to.

If you need some fancy processing in the sql server back end, just unleash TSQL, and for Oracle PL/SQL.  Alternatively, if the processing is simple, just create views on each system which provide the data with all columns aliased to the same names, thereby providing an identical read-only interface.

If you have code for each in the front end application, I'll leave you to enjoy the sheer hassle which this will become.

Regards

T

RE: Which database do I query?

Does Oracle or SQL Server support those old (*-- escape clauses?

RE: Which database do I query?

I second thargs recommendation of stored procedures.
This way you'd not implement certain function of one DB for another DB, you'd design a concept of stored procs appropriate for the data access layer.

Otherwise you always end up with a system working best on on one database and bad on other databases. Having the overhead of calling stored procs for each database is a small performance hit.

I wonder about your questioon, because you'll have something connecting to the database, therefore you should know on which database you're currently working anyway via some central database access class.

Bye, Olaf.

RE: Which database do I query?

(OP)
Thanks all for your responses.  Indeed stored procedures with identical signatures would be the cleanest way to implement this.  Although in this case, information about the queries will reside in at least two different places. And ... I have to agree with the data base administrator which introduces some burocratic overhead (to say the least), which is why I probably didn't even think of it.  But, I admit, I should.

Olaf:The program doesn't know anything about the database.  It's written in Perl, with DBI:ODBC
As such only the name of the data source is known to the program.  The connection parameters to the data base are stored within the (windows) datasource and as such are outside the program.

 

_________________________________
In theory, there is no difference between theory and practice.  In practice, there is. [attributed to Yogi Berra]

RE: Which database do I query?

Well, then you have excluded that knowlede of what DB is connected into the DSN. So you decided to not know about the database, to adress any database. Then either do it consequently and only use some basic SQL every database knows or break that decision and know what database you're connected to. You cannot have both at everytime.

It's like using an ajax framework capable to adress any browser and OS and then wanting to know which browser your code runs on to do some browser specific stuff.

I can understand your needs, it's perhaps just that the one-size-fits all has it's downsides and is not such a great idea anyway. But if you want to go with it, then do it consequently or don't do it at all.

Bye, Olaf.

RE: Which database do I query?

(OP)
Olaf: Yours are harsh words...
Imagine a company that acquired several other ones.  It's utopic to have all of them have the same type of database, even less so to have that unique central database.  Do I have to supply a stored procedure for each of those data bases?  Maintenance will not be that easy (as they even reside in different countries)
On the other hand, a query that calculates a difference between two dates is not that complex? or is it?
So, I thought a program that centralizes all queries in one place and figures out which one to use on the spot could be an adequate way to handle the situation.
But obviuosly, you disagree.  Nevertheless, I appreciate your comments.wink

_________________________________
In theory, there is no difference between theory and practice.  In practice, there is. [attributed to Yogi Berra]

RE: Which database do I query?

Yes, I was harsh, true.

While I don't know your special case and reasons, if you want to solve some 5% o SQL with database sepcific code, I'd simply make it a configuration. You can make it your knowledge initially by not offereing to connect through some system DSN, but by using a database specific connection string.

Bye, Olaf.

RE: Which database do I query?

rv,

I had assumed that you were in the situation of having a mere handful of db's, and that you could afford the time and resources to write SP's for them all.

In the circumstances you mention, views (and if using sql server user-defined functions) will be immensely beneficial.  You can select from a view of the same name on each db, and provided they deliver identical data, your front end need never know which db is being used.  Obviously this is suitable for read-only situations, if you want read/write, then I stand by my original remarks, and stored procedures are the only way to go.

Regards

T

RE: Which database do I query?

I've worked with a few database interfaces that are not dabase specifc and what I've learned is that they in general do not perform well as each type of database has differnt database specif language that performs better than ansii sql (which still may not work for some databases as all of them don't fully implement the standard). So to meet the need to stay nonspecific, you greatly sacrifice performance. But performance is one of the two things users need the most and the first htey will notice if it is bad.

In the case of organizations merging and having separate data stored separate ways, you still can't write one-size fits all code as the table and field structure are different. The best bet is to choose the best of the lot and convert all the others to it, running the old system until the conversion is in place (big project will take months). Start with financial systems first and HR, then prioritize the others depending on how much you need to see consolidated data between the units and how hard it will be to consolidate the functions and how many people you have available to do it. Converting to new systems is hard, but in the long run it is the only viable solution for most consolidated functions.

"NOTHING is more important in a database than integrity." ESquared
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close