[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Read ODBC DSN Attributes

Posted on 1998-08-28
11
Medium Priority
?
1,775 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 200 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

834 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