Difference between revisions of "ADO Tools"

From AutoIt Wiki
Jump to navigation Jump to search
m (List all installed OLE DB providers)
m
Line 49: Line 49:
  
 
[[File:ADO OLE DB Providers.jpg||OLE DB Providers]]
 
[[File:ADO OLE DB Providers.jpg||OLE DB Providers]]
 +
 +
= List all properties of an open ADO connection =
 +
<syntaxhighlight lang="autoit">
 +
;------------------------------------------------------------------------
 +
; ADO_Tools_Connection_Properties
 +
; List all properties of an open connection
 +
;------------------------------------------------------------------------
 +
#include <Array.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!")
 +
Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
 +
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the recordset object!")
 +
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
 +
$oADOConnection.Open($sADOConnectionString) ; Open the connection
 +
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection the connection object!")
 +
Global $aProperties[1000][2], $iIndex = 0
 +
For $oProperty In $oADOConnection.Properties
 +
    $aProperties[$iIndex][0] = $oProperty.Name
 +
    $aProperties[$iIndex][1] = $oProperty.Value
 +
    $iIndex = $iIndex  + 1
 +
Next
 +
$oADOConnection.Close ; Close the connection
 +
$oADOConnection = 0 ; Release the connection object
 +
ReDim $aProperties[$iIndex][2]
 +
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "PropertyName|PropertyValue")
 +
</syntaxhighlight>
  
 
[[Category:ADO]]
 
[[Category:ADO]]

Revision as of 21:50, 17 January 2014

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

;------------------------------------------------------------------------
; ADO_Tools_Connection_Properties
; List all properties of an open connection
;------------------------------------------------------------------------
#include <Array.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!")
Global $oADORecordset = ObjCreate("ADODB.Recordset") ; Create a recordset object
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " creating the recordset object!")
Global $sADOConnectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' & @ScriptDir & ';Extended Properties="Text;HDR=NO;FMT=Delimited(,)"'
$oADOConnection.Open($sADOConnectionString) ; Open the connection
If @error Then Exit MsgBox($MB_ICONERROR, "Error", "Error " & @error & " opening the connection the connection object!")
Global $aProperties[1000][2], $iIndex = 0
For $oProperty In $oADOConnection.Properties
    $aProperties[$iIndex][0] = $oProperty.Name
    $aProperties[$iIndex][1] = $oProperty.Value
    $iIndex = $iIndex  + 1
Next
$oADOConnection.Close ; Close the connection
$oADOConnection = 0 ; Release the connection object
ReDim $aProperties[$iIndex][2]
_ArrayDisplay($aProperties, "ADO connection - List of properties", "", 0, Default, "PropertyName|PropertyValue")