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 properties of an open ADO connection: _Example_ListProperties())
Line 55: Line 55:
 
#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 $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
+
 
$oADOConnection.Open($sADOConnectionString) ; Open the connection
+
Func _Example_ListProperties()
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")
+
Local $oADOConnection = ObjCreate("ADODB.Connection") ; Create a connection object
Global $aProperties[1000][2], $iIndex = 0
+
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the connection object!")
For $oProperty In $oADOConnection.Properties
+
 
    $aProperties[$iIndex][0] = $oProperty.Name
+
; Open the connection
    $aProperties[$iIndex][1] = $oProperty.Value
+
Local $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
    $iIndex = $iIndex  + 1
+
$oADOConnection.Open($sADOConnectionString)
Next
+
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection object!")
$oADOConnection.Close ; Close the connection
+
 
$oADOConnection = 0 ; Release the connection object
+
Local $aProperties = _ADO_Connection_PropertiesToArray($oADOConnection)
ReDim $aProperties[$iIndex][2]
+
 
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "PropertyName|PropertyValue")
+
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "PropertyName|PropertyValue")
 +
 +
EndFunc    ;==>_Example_ListProperties
 +
 
 +
Func _ADO_Connection_PropertiesToArray(ByRef $oConnection)
 +
Local $oProperties_coll = $oConnection.Properties
 +
Local $aProperties[$oProperties_coll.count][2]
 +
Local $iIndex = 0
 +
 
 +
For $oProperty_enum In $oProperties_coll
 +
$aProperties[$iIndex][0] = $oProperty_enum.Name
 +
$aProperties[$iIndex][1] = $oProperty_enum.Value
 +
$iIndex += 1
 +
Next
 +
 
 +
$oProperties_coll = Null
 +
Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $aProperties)
 +
 
 +
EndFunc    ;==>_ADO_Connection_PropertiesToArray
 
</syntaxhighlight>
 
</syntaxhighlight>
  

Revision as of 07:23, 19 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>
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")

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, "PropertyName|PropertyValue")
	
EndFunc    ;==>_Example_ListProperties

Func _ADO_Connection_PropertiesToArray(ByRef $oConnection)
	Local $oProperties_coll = $oConnection.Properties
	Local $aProperties[$oProperties_coll.count][2]
	Local $iIndex = 0

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

	$oProperties_coll = Null
	Return SetError($ADO_ERR_SUCCESS, $ADO_EXT_DEFAULT, $aProperties)

EndFunc    ;==>_ADO_Connection_PropertiesToArray

Result:

Connection Properties