We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now


Read ODBC DSN Attributes

chrismo asked
Medium Priority
Last Modified: 2012-08-13
I use SQLConfigDataSource to write Attributes to a DSN - what call can I use to read these same Attributes? (ODBC 3.0)
Watch Question

You can get simple information from examining the .connect string after the connection is made, or for more info there is the SQLGetInfo WIN32 API call, that works like this:
Option Explicit

Public Declare Function SQLGetInfo Lib "odbc32.dll" (ByVal hdbc&, ByVal fInfoType%, ByVal rgbInfoValue As Any, ByVal cbInfoMax%, cbInfoOut%) As Integer
Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal hdbc&, phstmt&) As Integer
Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal hstmt&, ByVal fOption%) As Integer
Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal hstmt&, ByVal szSqlStr$, ByVal cbSqlStr&) As Integer

' Defines for SQLGetInfo
Public Const SQL_DATABASE_NAME As Long = 16                ' "D:\Pathways Analyst\data\AnalystDbBig_30"
Public Const SQL_DBMS_NAME As Long = 17                    ' "ACCESS"
Public Const SQL_QUALIFIER_NAME_SEPARATOR As Long = 41     ' "."
Public Const SQL_IDENTIFIER_QUOTE_CHAR As Long = 29        ' "`"

'Defines for RETCODEs
Public Const SQL_ERROR As Long = -1
Public Const SQL_INVALID_HANDLE As Long = -2
Public Const SQL_NO_DATA_FOUND As Long = 100
Public Const SQL_SUCCESS As Long = 0
Public Const SQL_SUCCESS_WITH_INFO As Long = 1

'Defines for SQLFreeStmt
Public Const SQL_CLOSE As Long = 0
Public Const SQL_DROP As Long = 1
Public Const SQL_UNBIND As Long = 2
Public Const SQL_RESET_PARAMS As Long = 3

Public Function GetDATABASE_Name(Cn As rdoConnection) As String
Dim i As Integer
Dim sName As String * 255

On Error GoTo GetDATABASE_NameErr

i = SQLGetInfo(Cn.hdbc, SQL_DATABASE_NAME, sName, 255, 255)
GetDATABASE_Name = Left$(sName, InStr(1, Trim(sName), Chr$(0)) - 1)
Exit Function

MsgBox "Unable to Get Database Name Using SQLGetInfo, hdbc= " + Format$(Cn.hdbc), vbExclamation
GetDATABASE_Name = vbNullString
End Function

Public Function GetDBMS_Name(Cn As rdoConnection) As String
Dim i As Integer
Dim sName As String * 255

On Error GoTo GetDBMS_NameErr

i = SQLGetInfo(Cn.hdbc, SQL_DBMS_NAME, sName, 255, 255)
GetDBMS_Name = Left$(sName, Len(Trim$(sName)) - 1)

Exit Function

MsgBox "Unable To Get DBMS Name using SQLGetInfo, hdbc= " + Format$(Cn.hdbc), vbExclamation
GetDBMS_Name = vbNullString
End Function

Public Function GetIDENTIFIER_QUOTE_Char(Cn As rdoConnection) As String
Dim i As Integer
Dim sName As String * 255


i = SQLGetInfo(Cn.hdbc, SQL_IDENTIFIER_QUOTE_CHAR, sName, 255, 255)
GetIDENTIFIER_QUOTE_Char = Left$(sName, 1)
Exit Function

MsgBox "Unable To Get Identifier Quote Using SQLGetInfo, hdbc =" + Format$(Cn.hdbc), vbExclamation
GetIDENTIFIER_QUOTE_Char = vbNullString
End Function

'and provides much more data, depending on the parameter you supply in the call.


Does SQLGetInfo require an active connection? If so, this will not work for me.

Somehow the ODBC GUI is able to display what the current DSN attributes are without establishing a connection.

I've considered SQLBrowseConnect, this seems to be a way to retrieve all the current values and I might be able to prevent a connection from actually occurring after I retrieve the current values. But I don't know if this function will return what the attribute settings currently are - or if it merely returns a list of required attributes.


I'm rejecting the answer for now so I can unlock the question and encourage others' input... SQLGetInfo doesn't seem to get what I need...

Yep, both SQLGetInfo and examining the .connect string require an active connection.
Unlock this solution and get a sample of our free trial.
(No credit card required)


Is this reliable for future versions? For example, we're still on ODBC 3.0 - but 3.5 is now out. I'd rather use an API call (of course, I can't seem to find one...) as I believe this will help ensure reliability of code against other versions. I've been thinking I may have to do something like this ...

I've checked on a machine running an older version (I'm running 3.0) and the entries are the same. Sure an API would be better and if one is available then great but I couldn't find anything (even in the VC6/MSDN documentation).


One more comment - what do you think about using SQLBrowseConnect? I know it will return to me the attributes I need - I don't know if it will return to me the current value of an attribute (it very well may not - though I might get a selection list (I'm shooting for the Service attribute of an Oracle DSN)) - and I assume I can abort the process before actually attempting a connection. Can you shed any light on this?

Actually I think SQLBrowseConnect might work for you. You need to call it a few times to drill down and get what you want. So your first call will pass "DSN=dsn_name". The driver manager will load the driver associated with this DSN (in your case, Oracle). Then call it with whatever attribute you're looking for and you should be in business. The only thing with calling SQLBrowseConnect is that you "MIGHT" need to allocate an environment handle (SQLAllocEnv) and a connection handle(SQLAllocConnect). Remember to call SQLDisconnct at the end of all this. Good luck!


I'm taking your answer. I used RegMon and FileMon to see what's hit when I open the Configure dialog from the ODBC Admin GUI.

It reads values from the registry - it only loads the setup .dll which only has one ODBC documented call (ConfigDSN). ConfigDSN won't read the values.

In addition, the registry structure is fully documented in the ODBC 3 SDK (I just looked it up - missed it before), so I think that's the official answer: read it from the registry.

Thanks for the feedback.

Glad to help. Should have went with my first instincts. Never thought of RegMon. Good utility! Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.