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.

Answer 2

You can use SQLGetPrivateProfileString ODBC API to get the contents of DSN created.

int SQLGetPrivateProfileString(  
 LPCSTR   lpszSection,  
 LPCSTR   lpszEntry,  
 LPCSTR   lpszDefault,  
 LPCSTR   RetBuffer,  
 INT      cbRetBuffer,  
 LPCSTR   lpszFilename);

Here,

lpszSection = registry section you want details for. it will be DSN name in your case.

lpszEntry = key which you want to extract value from. you want to get database name information so you need to check registry entry HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI[YOUR_DSN_NAME] to know what is the key name to store database name information. This is because different driver can have different key name to store database name.

lpszDefault = Default value for the key specified in last argument(lpszEntry) if key is not found.

RetBuffer = Pointer to Output buffer in which value for the specified key is received.

cbRetBuffer = size of buffer pointed to by RetBuffer in characters.

lpszFilename = File name where you search these entries in. It will be odbc.ini in your case.

Sample example

CHAR *dsn_name = "Your DSN name";
CHAR db_name[20];
char *odbcini = NULL;
odbcini = "odbc.ini";

SQLGetPrivateProfileString(dsn_name, (CHAR*)"DATABASE", (CHAR*)"", db_name, 
sizeof(db_name), odbcini);

It will search registry entry HKEY_CURRENT_USER or HKEY_LOCAL_MACHINE or both depending on the config mode set(It can be set using SQLSetConfigMode ODBC API). If mode is not explicitly set, it will search both HKEY_CURRENT_USER and HKEY_LOCAL_MACHINE. Please refer https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlgetprivateprofilestring-function for more information.