Which database do I query?

[rvBasic]

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]

 

[jrbarnett]

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

 

[Thargy]

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

 

[JarlH]

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

 

[Olaf Doschke]

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.

 

[rvBasic]

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]

 

[Olaf Doschke]

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.

 

[rvBasic]

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.

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

 

[Olaf Doschke]

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.

 

[Thargy]

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

 

[SQLSister]

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