CODEWITHSUNDEEP
sql-serverdelphims-accessodbcado
BdR
BdR

Reputation: 3048

Determine ODBC database driver from TADOConnection object?

Using Delphi 7 with ADO objects, is it possible to determine the ODBC database driver from the TADOConnection object? So detect whether it is MS-Access or SQL Server or Oracle etc.

enter image description here

The program connects to a database by just using the name of an ODBC data source, and I want to determine whether that database is an MS-Access database or SQL Server. I want to do this because MS-Access and SQL Server use different SQL function names to cast an integer to a string.

The application builds an SQL string which retrieves the VERSION of some configuration objects. It works for SQL server using cast(), but I also want to support MS-Access which uses CStr():

SELECT NAME + '_' + CAST(VERSION as varchar) as OBJECT_NAME FROM ANALYSIS // SQL Server
SELECT NAME + '_' + CStr(VERSION) as OBJECT_NAME FROM ANALYSIS // MS-Access

I've tried looking at the TADOConnection.Provider but that is MSDASQL.1 in both cases.

if (myqry.Connection.Provider = 'MSDASQL.1') then
  strSQL := strSQL + 'cast(' + myfieldname + ' as varchar)' // always goes here..
else
  strSQL := strSQL + 'CStr(' + myfieldname + ')'; // ..never to here

I've looked at all the TADOConnection properties, but I'm starting to suspect it's not possible. Any ideas how to solve this?

Upvotes: 1

Views: 470

Answers (1)

J...
J...

Reputation: 31403

ODBC is designed to abstract away the implementation details of the server. You can use ODBC specific syntax that will be translated to a statement of the appropriate SQL flavour for the server. Here you can substitute :

... { fn CONVERT( VERSION, SQL_VARCHAR ) } AS OBJECT_NAME FROM ANALYSIS

These substitutions are known as ODBC Escape Sequences and can be substituted in queries where there are vendor-specific syntax differences.

Upvotes: 2

Related Questions