Re: check if oracle or MSDE

On Oct 2, 4:26 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> Niall Litchfield <niall.litchfi..._at_dial.pipex.com> wrote innews:470261BC.6050100_at_dial.pipex.com:
>
> > seannakas..._at_yahoo.com wrote:
> >> Is there a way to write a single SQL statement that will run a
> >> statement if you are in Oracle and run another statement if you are in
> >> MSDE? It has to be a single SQL statement because I'm using a front
> >> end application that requires that, so it can't be an PL/SQL script,
> >> proc, nor a function. Our application runs with either Oracle or MSDE
> >> so that's why i have this need.
>
> > No there isn't. This is however an application written in some form of
> > programming language no? If so then use the programming language
> > appropriately to do this task. It has to establish a connection to the
> > db, so it pretty much has to know what database it is talking to.
>
> select count(*) from v$instance.
>
> If query returns a valid number, it was talking to Oracle.
> Any other response it was talking to MSDE.

We weren't asked "is there a sql statement which will tell us whether we are connected to Oracle or not", to which there are several but "Is there a statement which will run one statement in Oracle and another in MSDE", you need to do the detection first and then run the appropriate sql. My point, if there was one, was that the application could *already* know what type of database it is connected to (through the connection string) and so why would you bother querying the database unnecessarily (I've seen code that issues 'select 1 from dual' in order to detect whether the connection is open or not). Since posting of course I have thought that perhaps the posters application just uses a named ODBC data source and thus the underlying database may not be available via this route.

Incidentally I suspect that 'any other response it is MSDE' is a bad assumption (especially if it's a named ODBC data source) could be MSDE, could be mySQL could be PostGres, could be DB2 and so on.

Finally I would have thought that this is a classic piece of meta-data for storing in a config file as part of the install (or does no-one do that anymore). Received on Tue Oct 02 2007 - 15:01:58 CDT