[ Pobierz całość w formacie PDF ]
.Print "DATA SOURCE / DRIVER"Do While nRetCode = SQL_SUCCESSDebug.Print Left$(sServerName, _nServerNameLength) & " / " & Trim0(sDescription)' Next data source nRetCode = SQLDataSources(lHEnv, SQL_FETCH_NEXT, _sServerName, Len(sServerName), nServerNameLength, _sDescription, Len(sDescription), nDescriptionLength)LoopnRetCode = SQLFreeHandle(SQL_HANDLE_ENV, lHEnv)End Sub' -----------------------------------------Private Sub ListODBCDrivers()' Prints a list of ODBC drivers on systemDim lHEnv As LongDim sDriverDesc As String * 1024Dim sDriverAttr As String * 1024Dim sDriverAttributes As StringDim nDriverDescLength As IntegerDim nAttrLength As IntegerDim x As IntegerDim sAll As String' Allocate an environment handle.nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)' Set ODBC behaviornRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _SQL_OV_ODBC2, SQL_IS_INTEGER)' Get first drivernRetCode = SQLDrivers(lHEnv, SQL_FETCH_FIRST, sDriverDesc, _Len(sDriverDesc), nDriverDescLength, sDriverAttr, _Len(sDriverAttr), nAttrLength)sAll = ""Do While nRetCode = SQL_SUCCESS' Replace NULL separators with colonssDriverAttributes = Left$(sDriverAttr, nAttrLength - 1)Dox = InStr(sDriverAttributes, Chr$(0))If x = 0 Then Exit DosDriverAttributes = Left$(sDriverAttributes, x - 1) & _" : " & Mid$(sDriverAttributes, x + 1)LoopsAll = sAll & Left$(sDriverDesc, nDriverDescLength) & _" / " & sDriverAttributes & vbCrLf' Next data sourcenRetCode = SQLDrivers(lHEnv, SQL_FETCH_NEXT, sDriverDesc, _Len(sDriverDesc), nDriverDescLength, sDriverAttr, _Len(sDriverAttr), nAttrLength) LoopDebug.Print "ODBC Drivers"Debug.Print sAllnRetCode = SQLFreeHandle(SQL_HANDLE_ENV, lHEnv)End SubThe output produced by running ListODBCSources on my system is:DATA SOURCE / DRIVERMS Access 7.0 Database / Microsoft Access Driver (*.mdb)Visual FoxPro Tables / Microsoft Visual FoxPro DriverVisual FoxPro Database / Microsoft Visual FoxPro DriverMS Access 97 Database / Microsoft Access Driver (*.mdb)OLE_DB_NWind_Jet / Microsoft Access Driver (*.mdb)OLE_DB_NWind_SQL / SQL ServerConnectExcel / Microsoft Excel Driver (*.xls)ConnectAccess / Microsoft Access Driver (*.mdb)ConnectText / Microsoft Text Driver (*.txt; *.csv)The output of ListODBCDrivers is:ODBC DriversSQL Server / UsageCount=10 : SQLLevel=1 : FileUsage=0 :DriverODBCVer=02.50 : ConnectFunctions=YYY : APILevel=2 :\Setup=sqlsrv32.dll :.01= : s=YYN : DSNConverted=F : CPTimeout=60 :FileExtns=NullMicrosoft ODBC Driver for Oracle / UsageCount=3 : SQLLevel=1 :FileUsage=0 : DriverODBCVer=02.50 : ConnectFunctions=YYY : APILevel=1Microsoft Access Driver (*.mdb) / UsageCount=10 : APILevel=1 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=2 :FileExtns=*.mdb : SQLLevel=0 : s=YYNMicrosoft dBase Driver (*.dbf) / UsageCount=6 : APILevel=1 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 :FileExtns=*.dbf,*.ndx,*.mdx : SQLLevel=0 : [g= : = : ;g= :g=Microsoft FoxPro Driver (*.dbf) / UsageCount=6 : APILevel=1 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 :FileExtns=*.dbf,*.cdx,*.idx,*.ftp : SQLLevel=0Microsoft Excel Driver (*.xls) / UsageCount=4 : APILevel=1 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 :FileExtns=*.xls : SQLLevel=0Microsoft Paradox Driver (*.db ) / UsageCount=3 : APILevel=1 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 :FileExtns=*.db : SQLLevel=0 Microsoft Text Driver (*.txt; *.csv) / UsageCount=4 : APILevel=1 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 :FileExtns=*.,*.asc,*.csv,*.tab,*.txt,*.csv : SQLLevel=0Microsoft ODBC for Oracle / UsageCount=2 : SQLLevel=1 : FileUsage=0 :DriverODBCVer=02.50 : ConnectFunctions=YYY : APILevel=1 : CPTimeout=120Microsoft Visual FoxPro Driver / UsageCount=2 : APILevel=0 :ConnectFunctions=YYN : DriverODBCVer=02.50 : FileUsage=1 :FileExtns=*.dbc,*.dbf : SQLLevel=0Let us briefly describe the ODBC functions used in these procedures.You can skip thismaterial if it does not interest you.C.6.1 PreliminariesBefore using the ODBC functions we are interested in, we must first get a handle to theODBC environment.Obtaining an environment handle is done by callingSQLAllocHandle, whose Visual Basic declaration is:Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal HandleType As Integer, _ByVal InputHandle As Long, _OutputHandlePtr As Long) As IntegerThe actual call to use is:nRetCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, lHEnv)The return value is an error code or if no error has occured, in which case lHEnv willreceive the handle as a Long.Once we have obtained an environment handle, we must set the environment attributeknown as ODBC behavior , using the SQLSetEnvAttr function, as follows:' Set ODBC behaviornRetCode = SQLSetEnvAttr(lHEnv, SQL_ATTR_ODBC_VERSION, _SQL_OV_ODBC2, SQL_IS_INTEGER)Note the use of the lHEnv argument to identify the environment handle.This function callsets the ODBC behavior to ODBC version 2.x (SQL_OV_ODBC2).Actually, it does notseem to matter whether we set the behavior to ODBC version 2 or version 3(SQL_OV_ODBC3) as long as we set it to one of these values!C.6.2 Getting Driver InformationTo get information about the installed ODBC drivers on a system, we use the SQLDriversfunction.The declaration for this function is: Declare Function SQLDriverConnect Lib "odbc32.dll" ( _ByVal ConnectionHandle As Long, ByVal WindowHandle As Long, _ByVal InConnectionString As String, ByVal StringLength1 As Integer,_ByVal OutConnectionString As String, ByVal BufferLength As Integer,_StringLength2Ptr As Integer, ByVal DriverCompletion As Integer) AsIntegerThe following is the complete procedure to list all drivers and their attributes in a textbox.(This procedure, and the following ones, are bare-bones, with no error checking.Feel free to augment them for your own use.)Private Sub ListODBCDrivers()Dim lHEnv As LongDim sDriverDesc As String * 1024Dim sDriverAttr As String * 1024Dim sDriverAttributes As StringDim nDriverDescLength As IntegerDim nAttrLength As IntegerDim x As IntegerDim sAll As StringtxtDrivers = ""' Allocate an environment handle
[ Pobierz całość w formacie PDF ]