Difference between revisions of "ADO Tools"

From AutoIt Wiki
Jump to navigation Jump to search
(List all installed OLE DB providers: _ADO_OLEDBProvidersList - example refactoring)
(List all properties of an open ADO connection: new Attributes in _ADO_Connection_PropertiesToArray example)
Line 83: Line 83:
 
Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)
 
Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)
  
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "PropertyName|PropertyValue")
+
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")
 
 
 
EndFunc    ;==>_Example_ListProperties
 
EndFunc    ;==>_Example_ListProperties
  
 
Func _ADO_Connection_PropertiesToArray(ByRef $oConnection)
 
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 $oProperties_coll = $oConnection.Properties
Local $aProperties[$oProperties_coll.count][2]
+
Local $aProperties[$oProperties_coll.count][4]
 
Local $iIndex = 0
 
Local $iIndex = 0
  
 
For $oProperty_enum In $oProperties_coll
 
For $oProperty_enum In $oProperties_coll
 
$aProperties[$iIndex][0] = $oProperty_enum.Name
 
$aProperties[$iIndex][0] = $oProperty_enum.Name
$aProperties[$iIndex][1] = $oProperty_enum.Value
+
$aProperties[$iIndex][1] = $oProperty_enum.Type
 +
$aProperties[$iIndex][2] = $oProperty_enum.Value
 +
$aProperties[$iIndex][3] = $oProperty_enum.Attributes
 
$iIndex += 1
 
$iIndex += 1
 
Next
 
Next
  
 
$oProperties_coll = Null
 
$oProperties_coll = Null
Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $aProperties)
 
  
 
EndFunc    ;==>_ADO_Connection_PropertiesToArray
 
EndFunc    ;==>_ADO_Connection_PropertiesToArray

Revision as of 23:25, 23 February 2016

List all installed OLE DB providers

This tool 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) plus a description of the provider.

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

#include <Constants.au3>
#include <Array.au3>

_ADO_OLEDBProvidersList()

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

	ProgressOn($sTitle, "Processing the Registry", "", Default, Default, $DLG_MOVEABLE)

	; 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

	; 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
			$aResult[$iIndexResult][1] = RegRead($sKey & "\" & $sSubKey, "")
			$aResult[$iIndexResult][2] = RegRead($sKey & "\" & $sSubKey & "\OLE DB Provider", "")
			$iIndexResult += 1
		EndIf

		$iIndexReg += 1
	WEnd

	ProgressOff()
	ReDim $aResult[$iIndexResult][3]

	_ArrayDisplay($aResult, $sTitle, "", 0, Default, "Registry key|OLE DB Provider|Description")

EndFunc

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

EndFunc    ;==>_ADO_Connection_PropertiesToArray

Result:

Connection Properties