Solved

Read ODBC DSN Attributes

Posted on 1998-08-28
11
1,717 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

738 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