ODBC API Function -- SQLGetInfo

[Schof]

Anyone with some sample code on this function? I have looked everywhere and cannot find anything that is MS-Access specific.

Background
I have an application where I want the user to be prompted to select an ODBC Data Source. I would like to then call this function to get some information about the ODBC driver so I can make RDBMS specific SQL calls.

I have used the connect connection property (see below) in the past but it does not give me enough information.

Dim wrkODBC As Workspace
Dim conPubs As Connection

' Create ODBCDirect Workspace object.
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("DBConnection", dbDriverPrompt, True, "ODBC;DSN=;UID=;PWD=")

 

[SiJP]

I've no code, but use the advapi32.dll to check the registry entries... sorry can't be of much more help :/ going from memory on a past proj I no longer have access to.

------------------------
Hit any User to continue

 

[Schof]

Put the code below in a procedure or function.

Dim strSQLInfo as String
Dim strConnStrIn as String
Dim strConnStrOut as String

strSQLInfo = GetSQLInfo(SQL_DBMS_NAME, strConnStrIn, strConnStrOut)

Either strSQLInfo will be Empty (if the referenced function was unable to get the information) or you owe me a star for all the hours of work I have just saved you

Note: The function returns the complete driver connection string. You can make subsequent calls to GetSQLInfo and by replacing "strConnStrIn" with "strConnStrOut" you can avoid the user getting prompted for the connection information.


Put the following code in a module:

Option Explicit

Public Declare Function SQLAllocHandle Lib "odbc32.dll" _
  (ByVal HandleType As Integer, _
   ByVal InputHandle As Long, _
   OutputHandlePtr As Long) As Long
   
Public Declare Function SQLDataSources Lib "odbc32.dll" _
  (ByVal hEnv As Long, _
   ByVal fDirection As Integer, _
   ByVal szDSN As String, _
   ByVal cbDSNMax As Integer, _
   pcbDSN As Integer, _
   ByVal szDescription As String, _
   ByVal cbDescriptionMax As Integer, _
   pcbDescription As Integer) As Long

Public Declare Function SQLConnect Lib "odbc32.dll" ( _
     ByVal hdbc As Long, _
     ByVal ServerName As String, _
     ByVal ServerNameLen As Integer, _
     ByVal UserName As String, _
     ByVal UserNameLen As Integer, _
     ByVal Authentication As String, _
     ByVal AuthenticationLen As Integer) As Long

Public Declare Function SQLDriverConnect Lib "odbc32.dll" ( _
     ByVal hdbc As Long, _
     ByVal hwnd As Long, _
     ByVal szConnStrIn As String, _
     ByVal cbConnStrIn As Integer, _
     ByVal szConnStrOut As String, _
     ByVal cbConnStrOutMax As Integer, _
     pcbConnStrOut As Integer, _
     ByVal fDriverCompletion As Integer) As Long
     
Public Declare Function SQLFreeHandle Lib "odbc32.dll" _
  (ByVal HandleType As Integer, _
   ByVal Handle As Long) As Long
                                                  
Public Declare Function SQLGetInfo Lib "odbc32.dll" ( _
     ByVal hdbc As Long, _
     ByVal fInfoType As Integer, _
     ByVal rgbInfoValue As String, _
     ByVal cbInfoValueMax As Integer, _
     ByRef pcbInfoValue As Integer) As Long
     
Public Declare Function SQLSetEnvAttr Lib "odbc32.dll" _
  (ByVal EnvironmentHandle As Long, _
   ByVal dwAttribute As Long, _
   ByVal ValuePtr As Long, _
   ByVal StringLen As Long) As Long
   
' ***********************************************************************
' ODBC API Declarations
' ***********************************************************************

' SQL Return Codes
Public Const SQL_ERROR As Long = -1
Public Const SQL_INVALID_HANDLE As Long = -2
Public Const SQL_SUCCESS As Long = 0
Public Const SQL_SUCCESS_WITH_INFO As Long = 1
Public Const SQL_NO_DATA_FOUND As Long = 100

' Data Source Request Flags
Public Const ODBC_ADD_DSN = 1            ' Add a new user data source
Public Const ODBC_CONFIG_DSN = 2         ' Configure (modify) an existing user data source
Public Const ODBC_REMOVE_DSN = 3         ' Remove an existing user data source
Public Const ODBC_ADD_SYS_DSN = 4        ' Add a new system data source
Public Const ODBC_CONFIG_SYS_DSN = 5     ' Modify an existing system data source
Public Const ODBC_REMOVE_SYS_DSN = 6     ' Remove an existing system data source
Public Const ODBC_REMOVE_DEFAULT_DSN = 7 ' Remove the default data source

' SQL Fetch Directions
Public Const SQL_FETCH_NEXT As Long = &H1&
Public Const SQL_FETCH_FIRST As Long = &H2&
Public Const SQL_FETCH_LAST As Long = &H4&
Public Const SQL_FETCH_PRIOR As Long = &H8&
Public Const SQL_FETCH_ABSOLUTE As Long = &H10&
Public Const SQL_FETCH_RELATIVE As Long = &H20&
Public Const SQL_FETCH_RESUME As Long = &H40&
Public Const SQL_FETCH_BOOKMARK As Long = &H80&

' Driver Completion Options
Public Const SQL_DRIVER_NOPROMPT As Long = 0
Public Const SQL_DRIVER_COMPLETE As Long = 1
Public Const SQL_DRIVER_PROMPT As Long = 2
Public Const SQL_DRIVER_COMPLETE_REQUIRED As Long = 3

' SQL Handle Types
Public Const SQL_NULL_HANDLE As Long = 0
Public Const SQL_HANDLE_ENV As Long = 1
Public Const SQL_HANDLE_DBC As Long = 2
Public Const SQL_HANDLE_STMT As Long = 3
Public Const SQL_HANDLE_DESC As Long = 4
Public Const SQL_HANDLE_SENV As Long = 5

'Driver Information Types
Public Const SQL_DATA_SOURCE_NAME As Long = 2
Public Const SQL_DRIVER_NAME As Long = 6
Public Const SQL_DRIVER_VER As Long = 7
Public Const SQL_ODBC_VER As Long = 10
Public Const SQL_SERVER_NAME As Long = 13
Public Const SQL_DBMS_NAME As Long = 17
Public Const SQL_DBMS_VER As Long = 18
Public Const SQL_USER_NAME As Long = 47
Public Const SQL_DRIVER_ODBC_VER As Long = 77

' Environment Attributes
Public Const SQL_ATTR_ODBC_VERSION As Long = 200

Public Const SQL_OV_ODBC3 As Long = 3
Public Const SQL_IS_INTEGER As Long = (-6)

Public Function GetSQLInfo(ByVal intInfoType As Integer, Optional ByRef strConnStrIn As String, _
                                                         Optional ByRef strConnStrOut As String) As String
On Error GoTo Err_GetSQLInfo

    Dim strInfoValue As String: strInfoValue = Empty
    Dim intInfoValueLength As Integer
    Dim intConnStrOutLength As Integer
    Dim hEnv As Long
    Dim hDBConn As Long
    Dim lngReturnValue As Long
    
    ' Allocate environment handle
    If SQLAllocHandle(SQL_HANDLE_ENV, _
                      SQL_NULL_HANDLE, _
                      hEnv) <> 0 Then
    
        ' Set environment attribute
        If SQLSetEnvAttr(hEnv, _
                         SQL_ATTR_ODBC_VERSION, _
                         SQL_OV_ODBC3, _
                         SQL_IS_INTEGER) <> 0 Then
        
            ' Allocate connection handle
            If SQLAllocHandle(SQL_HANDLE_DBC, _
                              hEnv, _
                              hDBConn) <> 0 Then
            
                strConnStrOut = Space$(1024)
                
                lngReturnValue = SQLDriverConnect(hDBConn, _
                                                  Screen.ActiveForm.hwnd, _
                                                  strConnStrIn, _
                                                  Len(strConnStrIn), _
                                                  strConnStrOut, _
                                                  1024, _
                                                  intConnStrOutLength, _
                                                  SQL_DRIVER_COMPLETE_REQUIRED)
                
                Select Case lngReturnValue
                Case SQL_SUCCESS, SQL_SUCCESS_WITH_INFO
                    strConnStrOut = Left$(strConnStrOut, intConnStrOutLength)
                Case Else ' Unable to connect to driver
                    GoTo Exit_GetSQLInfo
                End Select
                
                strInfoValue = Space$(255)
                
                lngReturnValue = SQLGetInfo(hDBConn, _
                                            intInfoType, _
                                            strInfoValue, _
                                            255, _
                                            intInfoValueLength)
                
                Select Case lngReturnValue
                Case SQL_SUCCESS, SQL_SUCCESS_WITH_INFO
                    strInfoValue = Left$(strInfoValue, intInfoValueLength)
                Case Else ' Unable to get info
                    strInfoValue = Empty
                    GoTo Exit_GetSQLInfo
                End Select
            End If ' SQLAllocHandle
        End If ' SQLSetEnvAttr
    End If ' SQLAllocHandle
                     
Exit_GetSQLInfo:
    GetSQLInfo = strInfoValue
    
    If hDBConn <> 0 Then
        ' Release connection handle
        Call SQLFreeHandle(SQL_HANDLE_DBC, hDBConn)
    End If
    
    If hEnv <> 0 Then
        ' Free environment handle
        Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv)
    End If
    Exit Function

Err_GetSQLInfo:
    MsgBox Err.Description
    Resume Exit_GetSQLInfo

End Function