Solved

Read ODBC DSN Attributes

Posted on 1998-08-28
11
1,648 Views
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)
0
Comment
Question by:chrismo
  • 5
  • 4
  • 2
11 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1025589
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
 

Author Comment

by:chrismo
ID: 1025590
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
 

Author Comment

by:chrismo
ID: 1025591
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
 
LVL 9

Expert Comment

by:cymbolic
ID: 1025592
Yep, both SQLGetInfo and examining the .connect string require an active connection.
0
 
LVL 1

Accepted Solution

by:
bsimser earned 50 total points
ID: 1025593
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
 

Author Comment

by:chrismo
ID: 1025594
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
 
LVL 1

Expert Comment

by:bsimser
ID: 1025595
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
 

Author Comment

by:chrismo
ID: 1025596
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
 
LVL 1

Expert Comment

by:bsimser
ID: 1025597
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
 

Author Comment

by:chrismo
ID: 1025598
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
 
LVL 1

Expert Comment

by:bsimser
ID: 1025599
Glad to help. Should have went with my first instincts. Never thought of RegMon. Good utility! Thanks.
0

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now