ADO Tools

From AutoIt Wiki
Jump to navigation Jump to search

List all installed OLE DB providers

This tool (last update: 2024-08-13) lists all available providers installed on the computer. You get

  • the registry key,
  • the name of the provider (as to be used in the connection string),
  • the description of the provider,
  • the path of the provider DLL,
  • the version of the DLL (taken from the file properties),
  • the last modification date/time of the DLL (taken from the file properties).

Idea taken from http://www.motobit.com/help/RegEdit/sa117.htm

#include <Constants.au3>
#include <Debug.au3>

_ADO_OLEDBProvidersList()

Func _ADO_OLEDBProvidersList()
	Local $sKey = "HKCR\CLSID"
	Local $iIndexReg = 1, $iIndexResult = 0
	Local $iMax = 100000, $iMin = 1, $iPrevious = $iMin, $iCurrent = $iMax / 2
	Local $aResult[200][6]

	ProgressOn("OLE DB Providers", "Processing the Registry", "", Default, Default, $DLG_MOVEABLE)
	AutoItSetOption("ExpandEnvStrings", 1)

	; Count the number of keys
	While 1
		RegEnumKey($sKey, $iCurrent)
		If @error = -1 Then ; Requested subkey (key instance) out of range
			$iMax = $iCurrent
			$iCurrent = Int(($iMin + $iMax) / 2)
			$iPrevious = $iMax
		Else
			If $iPrevious <= ($iCurrent + 1) And $iPrevious >= ($iCurrent - 1) Then ExitLoop
			$iMin = $iCurrent
			$iCurrent = Int(($iMin + $iMax) / 2)
			$iPrevious = $iMin
		EndIf
	WEnd

	Local $iPercent = 0
	Local $sKeyValue = ''
	; Process registry
	While 1
		If Mod($iIndexReg, 10) = 0 Then
			$iPercent = Int($iIndexReg * 100 / $iCurrent)
			ProgressSet($iPercent, $iIndexReg & " keys of " & $iCurrent & " processed (" & $iPercent & "%)")
		EndIf
		$sSubKey = RegEnumKey($sKey, $iIndexReg)
		If @error Then ExitLoop

		$sKeyValue = RegRead($sKey & "\" & $sSubKey, "OLEDB_SERVICES")
		If @error = 0 Then
			$aResult[$iIndexResult][0] = $sKey & "\" & $sSubKey                                   ; Registry Key
			$aResult[$iIndexResult][1] = RegRead($sKey & "\" & $sSubKey, "")                      ; OLE DB Provider
			$aResult[$iIndexResult][2] = RegRead($sKey & "\" & $sSubKey & "\OLE DB Provider", "") ; Description
			$aResult[$iIndexResult][3] = RegRead($sKey & "\" & $sSubKey & "\InprocServer32", "")  ; DLL file
			If $aResult[$iIndexResult][3] <> "" Then
				$aResult[$iIndexResult][4] = _FileGetProperty($aResult[$iIndexResult][3], "Dateiversion")
				$aResult[$iIndexResult][5] = _FileGetProperty($aResult[$iIndexResult][3], "Änderungsdatum")
			EndIf
			$iIndexResult += 1
		EndIf

		$iIndexReg += 1
	WEnd

	ProgressOff()
	ReDim $aResult[$iIndexResult][UBound($aResult, 2)]

	_DebugArrayDisplay($aResult, "OLE DB Providers", "", 0, Default, "Registry key|OLE DB Provider|Description|DLL|DLL Version|DLL last modified")

EndFunc   ;==>_ADO_OLEDBProvidersList

;===============================================================================
; Function Name.....: _FileGetProperty
; Description.......: Returns a property or all properties for a file.
; Version...........: 1.0.2
; Change Date.......: 05-16-2012
; AutoIt Version....: 3.2.12.1+
; Parameter(s)......: $FGP_Path - String containing the file path to return the property from.
;                     $FGP_PROPERTY - [optional] String containing the name of the property to return. (default = "")
;                     $iPropertyCount - [optional] The number of properties to search through for $FGP_PROPERTY, or the number of items
;                                       returned in the array if $FGP_PROPERTY is blank. (default = 300)
; Requirements(s)...: None
; Return Value(s)...: Success: Returns a string containing the property value.
;                     If $FGP_PROPERTY is blank, a two-dimensional array is returned:
;                         $av_array[0][0] = Number of properties.
;                         $av_array[1][0] = 1st property name.
;                         $as_array[1][1] = 1st property value.
;                         $av_array[n][0] = nth property name.
;                         $as_array[n][1] = nth property value.
;                     Failure: Returns an empty string and sets @error to:
;                       1 = The folder $FGP_Path does not exist.
;                       2 = The property $FGP_PROPERTY does not exist or the array could not be created.
;                       3 = Unable to create the "Shell.Application" object $objShell.
; Author(s).........: - Simucal <Simucal@gmail.com>
;                     - Modified by: Sean Hart <autoit@hartmail.ca>
;                     - Modified by: teh_hahn <sPiTsHiT@gmx.de>
;                     - Modified by: BrewManNH
; URL...............: http://www.autoitscript.com/forum/topic/34732-udf-getfileproperty/page__view__findpost__p__557571
; Note(s)...........: Modified the script that teh_hahn posted at the above link to include the properties that
;                     Vista and Win 7 include that Windows XP doesn't. Also removed the ReDims for the $av_ret array and
;                     replaced it with a single ReDim after it has found all the properties, this should speed things up.
;                     I further updated the code so there's a single point of return except for any errors encountered.
;                     $iPropertyCount is now a function parameter instead of being hardcoded in the function itself.
;===============================================================================
Func _FileGetProperty($FGP_Path, $FGP_PROPERTY = "", $iPropertyCount = 500)
	If $FGP_PROPERTY = Default Then $FGP_PROPERTY = ""
	$FGP_Path = StringRegExpReplace($FGP_Path, '["'']', "") ; strip the quotes, if any from the incoming string
	If Not FileExists($FGP_Path) Then Return SetError(1, 0, "") ; path not found
	Local Const $objShell = ObjCreate("Shell.Application")
	If @error Then Return SetError(3, 0, "")
	Local Const $FGP_File = StringTrimLeft($FGP_Path, StringInStr($FGP_Path, "\", 0, -1))
	Local Const $FGP_Dir = StringTrimRight($FGP_Path, StringLen($FGP_File) + 1)
	Local Const $objFolder = $objShell.NameSpace($FGP_Dir)
	Local Const $objFolderItem = $objFolder.Parsename($FGP_File)
	Local $Return = "", $iError = 0
	If $FGP_PROPERTY Then
		For $I = 0 To $iPropertyCount
			If $objFolder.GetDetailsOf($objFolder.Items, $I) = $FGP_PROPERTY Then
				$Return = $objFolder.GetDetailsOf($objFolderItem, $I)
			EndIf
		Next
		If $Return = "" Then
			$iError = 2
		EndIf
	Else
		Local $av_ret[$iPropertyCount + 1][2] = [[0]]
		For $I = 1 To $iPropertyCount
			If $objFolder.GetDetailsOf($objFolder.Items, $I) Then
				$av_ret[$I][0] = $objFolder.GetDetailsOf($objFolder.Items, $I - 1)
				$av_ret[$I][1] = $objFolder.GetDetailsOf($objFolderItem, $I - 1)
;~              $av_ret[0][0] += 1
				$av_ret[0][0] = $I
			EndIf
		Next
		ReDim $av_ret[$av_ret[0][0] + 1][2]
		If Not $av_ret[1][0] Then
			$iError = 2
			$av_ret = $Return
		Else
			$Return = $av_ret
		EndIf
	EndIf
	Return SetError($iError, 0, $Return)
EndFunc   ;==>_FileGetProperty

Result:

OLE DB Providers

List all properties of an open ADO connection

This tool lists all properties for an open connection.

#include <Array.au3>
#include <MsgBoxConstants.au3>

_Example_ListProperties()

Func _Example_ListProperties()
	Local $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
	If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the connection object!")

	; Open the connection
	Local $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
	$oADOConnection.Open($sADOConnectionString)
	If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")

	Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)

	_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")
	
EndFunc    ;==>_Example_ListProperties

Func _ADO_Connection_PropertiesToArray(ByRef $oConnection)
	; Property Object (ADO)
	; https://msdn.microsoft.com/en-us/library/windows/desktop/ms677577(v=vs.85).aspx
	Local $oProperties_coll = $oConnection.Properties
	Local $aProperties[$oProperties_coll.count][4]
	Local $iIndex = 0

	For $oProperty_enum In $oProperties_coll
		$aProperties[$iIndex][0] = $oProperty_enum.Name
		$aProperties[$iIndex][1] = $oProperty_enum.Type
		$aProperties[$iIndex][2] = $oProperty_enum.Value
		$aProperties[$iIndex][3] = $oProperty_enum.Attributes
		$iIndex += 1
	Next

	$oProperties_coll = Null
	Return $aProperties
EndFunc    ;==>_ADO_Connection_PropertiesToArray

Result:

Connection Properties