Difference between revisions of "ADO Tools"

From AutoIt Wiki
Jump to navigation Jump to search
m (List all properties of an open ADO connection)
(List all installed OLE DB providers: _ADO_OLEDBProvidersList fixed)
 
(8 intermediate revisions by 2 users not shown)
Line 6: Line 6:
 
#include <Constants.au3>
 
#include <Constants.au3>
 
#include <Array.au3>
 
#include <Array.au3>
Global $sOutput, $oRegistry, $oKey, $sKey = "HKCR\CLSID", $iIndexReg = 1, $iIndexResult = 0
 
Global $aResult[200][3], $sTitle = "OLE DB Providers", $iMax = 100000, $iMin = 1, $iPrevious = $iMin, $iCurrent = $iMax/2, $iTemp
 
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 = $iIndexResult + 1
 
    EndIf
 
    $iIndexReg = $iIndexReg + 1
 
WEnd
 
ProgressOff()
 
ReDim $aResult[$iIndexResult][3]
 
_ArrayDisplay($aResult, $sTitle, "", 0, Default, "Registry key|OLE DB Provider|Description")
 
  
 +
_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][3]
 +
 +
ProgressOn("OLE DB Providers", "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
 +
 +
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
 +
$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, "OLE DB Providers", "", 0, Default, "Registry key|OLE DB Provider|Description")
 +
 +
EndFunc    ;==>_ADO_OLEDBProvidersList
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Line 51: Line 67:
  
 
= List all properties of an open ADO connection =
 
= List all properties of an open ADO connection =
 +
This tool lists all properties for an open connection.
 
<syntaxhighlight lang="autoit">
 
<syntaxhighlight lang="autoit">
 
#include <Array.au3>
 
#include <Array.au3>
 
#include <MsgBoxConstants.au3>
 
#include <MsgBoxConstants.au3>
Global $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
+
 
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the connection object!")
+
_Example_ListProperties()
Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
+
 
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the recordset object!")
+
Func _Example_ListProperties()
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
+
Local $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
$oADOConnection.Open($sADOConnectionString) ; Open the connection
+
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the connection object!")
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")
+
 
Global $aProperties[1000][2], $iIndex = 0
+
; Open the connection
For $oProperty In $oADOConnection.Properties
+
Local $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
    $aProperties[$iIndex][0] = $oProperty.Name
+
$oADOConnection.Open($sADOConnectionString)
    $aProperties[$iIndex][1] = $oProperty.Value
+
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")
    $iIndex = $iIndex + 1
+
 
Next
+
Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)
$oADOConnection.Close ; Close the connection
+
 
$oADOConnection = 0 ; Release the connection object
+
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "Name|Type|Value|Attributes")
ReDim $aProperties[$iIndex][2]
+
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "PropertyName|PropertyValue")
+
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
 
</syntaxhighlight>
 
</syntaxhighlight>
 +
 +
Result:
 +
 +
[[File:ADO_Connection_Properties.jpg||Connection Properties]]
  
 
[[Category:ADO]]
 
[[Category:ADO]]

Latest revision as of 07:48, 1 March 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"
	Local $iIndexReg = 1, $iIndexResult = 0
	Local $iMax = 100000, $iMin = 1, $iPrevious = $iMin, $iCurrent = $iMax / 2
	Local $aResult[200][3]

	ProgressOn("OLE DB Providers", "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

	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
			$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, "OLE DB Providers", "", 0, Default, "Registry key|OLE DB Provider|Description")

EndFunc    ;==>_ADO_OLEDBProvidersList

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