Read ODBC DSN Attributes

I use SQLConfigDataSource to write Attributes to a DSN - what call can I use to read these same Attributes? (ODBC 3.0)
chrismoAsked:
Who is Participating?
 
bsimserConnect With a Mentor Commented:
You can get everything from the registry. In NT it's under \HKEY_LOCAL_MACHINE\SOFTWARE\ODBC. Create a system dsn named "testdsn" then do a search for it if you're under 95. The DSNs are listed under ODBC.INI and the installed DSNs are in ODBCINST.INI.
0
 
cymbolicCommented:
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

GetDATABASE_NameErr:
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

GetDBMS_NameErr:
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

On Error GoTo GetIDENTIFIER_QUOTE_CharErr

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

GetIDENTIFIER_QUOTE_CharErr:
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.


0
 
chrismoAuthor Commented:
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.
0
Easily manage email signatures in Office 365

Managing email signatures in Office 365 can be a challenging task if you don't have the right tool. CodeTwo Email Signatures for Office 365 will help you implement a unified email signature look, no matter what email client is used by users. Test it for free!

 
chrismoAuthor Commented:
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...
0
 
cymbolicCommented:
Yep, both SQLGetInfo and examining the .connect string require an active connection.
0
 
chrismoAuthor Commented:
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 ...
0
 
bsimserCommented:
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).
0
 
chrismoAuthor Commented:
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?
0
 
bsimserCommented:
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!
0
 
chrismoAuthor Commented:
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.
0
 
bsimserCommented:
Glad to help. Should have went with my first instincts. Never thought of RegMon. Good utility! Thanks.
0
All Courses

From novice to tech pro — start learning today.