Determining the underlaying database?

When using ADO and Microsoft OLEDB, how can I determine the actual database (MS SQL Server vs Oracle vs Access etc). being connected to?

The dbConnection.Provider property always returns "MSDASQL.1".


Helmar B. Herman, VP ProtoProducts

perhaps you could try all the providers and have an error routine to redirect to the next

If you are writing an application that can go against multiple backend databases, here are a few options.

In your data access layer, assuming you broke it out multi-tiered, you would need a case statement in each procedure making a database call

Select Case strDatabase
'where strDatabase is passed in. it should be a constant in the tier that is calling for data services
Case "ORACLE"

Case "SQLSERVER"

Case "ACCESS"

End Select

The reason is that dates, for one, are handled differently and you need to format your SQL to match the required format.

We just use a constant in the code or we read a value from an .ini file.

If you put the constant at the highest level and just pass it down, you would only need to recompile the highest level to change the constant for different databases. If you use the .ini file, it is simple.

Good Luck,
Brian


Brian T. Wiehoff

Brian,

Thank you for your reply, but it doesn't help my particular problem.

I have a commercial application that accesses Oracle, MS SQL Server, or Access databases using ADO through MS OLEDB.

My app has no idea which database is being used, nor is the customer specifying it anywhere. For diagnostic report purposes, I would like to be able to determine (I would guess from MS OLEDB) what the end data base is.

Helmar B. Herman, VP ProtoProducts

Are you saying that some of the db calls are calling access, some are calling Oracle, but that they always call the SAME database.

OR

Are you saying that the same function may call a different database each time.



Brian T. Wiehoff

I have a single application. Some customers use Oracle, some customers use MS SQL Server and some want to use MS Access.

I've designed my application to be oblivious to what the underlaying database is and simply use ADO to connect and issue SQL commands. All SQL generated by my app is generic and works with all three database. The ADO connect string is specified by the customer. Once connected, it only accesses the one database.

I WOULD like to be able to determine what the underlaying database is (and version) in case the customer has issues.


Helmar B. Herman, VP ProtoProducts

You can use the query SELECT @@VERSION on SQL Server to find out if it is SQL Server and what version it is. You can trap the error that would occur if the SQL execution failed and could go to the next test. Unfortunately, I am not sure of how you could get this with Access or Oracle.

Hi,

Would using the API function "FindExecutable" to find the exe associated with the file be any use.

Phil

Got to love it,

There doesn't seem to be a clean solution, even though one was advertised by Microsoft. I had the same question and got some replies in my "Softcoded Connection Strings" thread. One of the replies told me how to retrieve a list of SQL servers on the local area network.

Basically, I think we are going to have to ask the end-abuser what type of database he/she is trying to attach to, and then adjust a custom developed 'dialog box' to request the proper information based on that choice. Then your application can use a case statement and some nicely hardcoded logic to generate the proper connection string. You can then save that connection string in an INI file or the registry. Your 'Recent File List' would then poll (nicely political) the ini or registry to retrieve the proper connection string. Sure looks ugly. What happens when the customer upgrade his database back end (look at the connection string required by Access - it is version specific).

Really dumb, Really dumb,

Roger Bogh