Need to Identify the Database name in an ODBC DSN connected application

Question

I have a Delphi 6 application that uses an ODBC DSN to connect to target databases. I want to include text that lists the name of the Database the DSN is connected to. I tried using the SQL command db_name() but only received a nil in response despite having it work when I log into the SQL server.

Is there a way within Delphi to identify which Database I'm connected to? I can pull up the sys.databases table, but am not certain how to identify which database is the one I'm connected to

As an Example:

if I am connecting to the dsn LocalDSN I want to be able to display to the user that they are connected to Database, where database is the name of the sql database they are communicating with.

Answer 1

The ODBC DSN is stored in the Windows Registry. Keep in mind that the Windows Registry, and therefore the ODBC DSN settings, are separated between 32 and 64 bit versions. You can access this information through HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\[YOUR_DSN_NAME] and then read the value Database or Server to know the database or server name.

You can read the server and database name with these functions:

uses
  Registry;

function ServerOfDSN(const Name: String): String;
var
  R: TRegistry;
  K: String;
begin
  K:= 'Software\ODBC\ODBC.INI\'+Name;
  R:= TRegistry.Create(KEY_READ);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(K) then begin
      if R.OpenKey(K, False) then begin
        if R.ValueExists('Server') then
          Result:= R.ReadString('Server');
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

function DatabaseOfDSN(const Name: String): String;
var
  R: TRegistry;
  K: String;
begin
  K:= 'Software\ODBC\ODBC.INI\'+Name;
  R:= TRegistry.Create(KEY_READ);
  try
    R.RootKey:= HKEY_LOCAL_MACHINE;
    if R.KeyExists(K) then begin
      if R.OpenKey(K, False) then begin
        if R.ValueExists('Database') then
          Result:= R.ReadString('Database');
        R.CloseKey;
      end;
    end;
  finally
    R.Free;
  end;
end;

Depending on what database engine and drivers you're using, the contents of this registry key may be different, and therefore there's a possibility that Server or Database might not be the registry value you need, but inspect it yourself and find your value names in the registry to know how to read it.