SQLGetPrivateProfileString() failing to recognize system DSN

[justing-bq]

In our ODBC driver, we call SQLGetPrivateProfileString() to look up connection attributes configured in our DSNs. However, on MacOs using iODBC, this only works with user DSNs. When trying to look up a system DSN, we get nothing back from SQLGetPrivateProfileString().
Now the iODBC driver manager can still recognize a system DSN in general even though SQLGetPrivateProfileString() is not recognizing it. The reason we know this is because, when providing a bogus DSN, the iODBC driver manager will reject the connection before our driver ever gets to SQLGetPrivateProfileString().

On my system I have the following system DSN defined at /Library/ODBC/odbc.ini:

[ODBC Data Sources]
looker_dsn_system = Google Looker ODBC Driver

[looker_dsn_system]
Driver       = /Users/justing/Documents/dev/looker-open-source/looker-odbc-driver/build/odbc/lib/liblooker-odbc.1.0.0.dylib
host         = sample_host
clientId     = sample_id
clientSecret = sample_secret

And I have the following user DSN defined at ~/Library/ODBC/odbc.ini:

[ODBC Data Sources]
looker_dsn_user = Google Looker ODBC Driver

[looker_dsn_user]
Driver       = /Users/justing/Documents/dev/looker-open-source/looker-odbc-driver/build/odbc/lib/liblooker-odbc.1.0.0.dylib
host         = sample_host
clientId     = sample_id
clientSecret = sample_secret

When looking at the comments in your source code, it appears that the expected precedence order is:

1. Check for $ODBCINI variable, if exists return $ODBCINI.
2. Check for $HOME/Library/ODBC/odbc.ini, 
    if exists return it.
3. Check for SYS_ODBC_INI build variable, if exists return
     it. (ie : /etc/odbc.ini).
4. Check for /Library/ODBC/odbc.ini
    file, if exists return it.
5. No odbc.ini presence, return NULL.

Since the user odbc.ini is ahead of the system odbc.ini in precedence, I thought perhaps the existence of my user odbc.ini is the problem. However I still see the same behavior when temporarily removing the user odbc.ini. I also tried hardcoding the path to my system odbc.ini as an argument to SQLGetPrivateProfileString() and I still saw the same behavior.
Explicitly setting the ODBCINI variable to point at my system odbc.ini does solve the problem. However it's not a great solution as we would still need to update this variable each time we are switching between system and user DSNs. And it also doesn't work when using a GUI application such as iODBC administrator.

[HughWilliams]

Do you use the SQLSetConfigMode call to indicate the use of both system and user DSNs by the driver ?

What ODBC Driver Manager is your Google Looker ODBC Driver linked against, is it iODBC , unixODBC or other ?

The directory name /Users/justing/Documents/dev/looker-open-source/looker-odbc-driver/build/odbc/lib/liblooker-odbc.1.0.0.dylib implies this is an open source ODBC driver, thus is the source code publicly available for review ?

[justing-bq]

Hi @HughWilliams. Thanks for the prompt response.

Yes we are using iODBC driver manager.

Our repo will eventually be public open source but it is private for now so I can't yet share it. However I can point you to another repo which is facing the exact same issue.

I did previously try SQLSetConfigMode with all three possible options and still saw the same behavior. However I didn't realize until this morning that calling it just once is insufficient. Rather we need to repeatedly call SQLSetConfigMode before each call to SQLGetPrivateProfileString as the config mode appears to be reset to the default after each SQLGetPrivateProfileString call.

I tried out all three config mode options once again with this new understanding and observed that the default config mode ODBC_BOTH_DSN behaves the same as ODBC_USER_DSN while ODBC_SYSTEM_DSN now correctly results in the system DSN being recognized by SQLGetPrivateProfileString

Fortunately this means, at worst, we can call SQLGetPrivateProfileString twice for each connection attribute. However we would certainly prefer having the default config mode ODBC_BOTH_DSN work as expected and not need to think about the config mode at all. Any idea why ODBC_BOTH_DSN is not working for us?

[pkleef]

Development is working on a solution.

[justing-bq]

Just checking in to see if there's been any movement on this?