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.

You cannot take the DSN name via sql, since AFAIK the engine itself is not aware of the mechanisms you're using to connect, but you for sure can take it from the property where it resides in the connection object you're using. For example, if you use TSQLConnection, it is stored on the parameters. — jachguate, Mar 01 '13 at 00:17
Unfortunately the connection object seems to just have the three properties - the name of the dsn and the users' username and password. The application is using a TQuery and TDatabase object (both from the vcl) if that helps — ChargerIIC, Mar 01 '13 at 14:54
@Jeroen I would, but I'm not sure to understand the question. ChargerIIC then, you have the name of the DSN there. Is not what you want? Your question is really unclear to me. — jachguate, Mar 01 '13 at 16:19
@Jachguate - What I wanted to show was the name of the server the DSN connects to. I hadn't found a property to expose the name in Delphi or A way to find out via sql - the db_name() command appears to misread by the dsn. — ChargerIIC, Mar 01 '13 at 18:54
@ChargerIIC it depends on the server you're connecting to (and not all the vendors have a way to get that information). AFAIK there's no standardized way to retrieve anything related to identify the database. If that's crucial for you and you are the owner of the databases, you can add a table to store the database friendly name and query that table. That's the only thing that comes to my mind that will work with any database vendor. — jachguate, Mar 01 '13 at 20:07
OR, if you're working with only one database vendor, appropriately tag your question! — jachguate, Mar 01 '13 at 20:08

Jerry Dodge · Accepted Answer · 2013-03-02T02:28:23.377

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.

NOTE: I only included the server name because of an initial mis-interpretation of the question. I originally answered with the Server name, and later added the Database name when I realized that's what was being asked. — Jerry Dodge, Mar 02 '13 at 03:03
Found the settings under the 64bit section of my OS. I'll be able to have the application pull from there and the 32bit location for the database name. Thanks! — ChargerIIC, Mar 04 '13 at 15:00

kriti · Answer 2 · 2017-12-23T17:50:14.260

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.

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

2 Answers2