Public/Invoke-AdoQuery.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
function Invoke-AdoQuery {
    <#
    .SYNOPSIS
    Retrieves a resultset from any ADO provider, using the ADO COM interface.
 
    .DESCRIPTION
    Retrieves a resultset from any ADO provider, using the ADO COM interface. This does not return .NET objects.
 
    .PARAMETER Query
    What query should be run against the datasource?
 
    .PARAMETER Provider
    What describes the provider? Often this, is the guts that describe a "dsn-less" connection. It is used to choose the drivers that will be used to do to work of retrieving the data.
 
    .PARAMETER Datasource
    This describes the source of the data.
 
    .PARAMETER ExtendedProperties
    This allows the caller to provide extended property information, when required.
 
    .LINK
    http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx
 
    .LINK
    http://www.microsoft.com/technet/scriptcenter/funzone/games/solutions07/apssol05.mspx
    The initial basis for this came from here. I added the code to build $obj and output it in a pipeline-friendly way.
 
    .NOTES
    This is pretty slow (10 seconds to read about 6,000 small Visual FoxPro records, but it does work.
 
    .EXAMPLE
    This string works with ACE for Excel 2007.
    Note that the 12 in the Provider string is the Provider version, not the internal Excel (or, presumably, Office) version.
    You might need to use Excel 12.0 or Excel 13.0, in the extended properties
    Invoke-AdoQuery -Query "Select count(*) countOf From [authors$]"
        -Provider:"Microsoft.ACE.OLEDB.12.0"
        -DataSource:"c:\temp\pubs.xls"
        # .TODO what about extended properties.
        #-Extended-Properties:`"Excel 12.0 Xml;HDR=YES`";"
 
    .EXAMPLE
    Connection String for Excel 2003:
    Invoke-AdoQuery -Query "Select count(*) countOf From [authors$]"
        -Provider "Microsoft.Jet.OLEDB.4.0"
        -DataSource= "c:\temp\pubs.xls"
        # .TODO what about extended properties.
        #-Extended-Properties=`"Excel 8.0 Xml;HDR=YES;IMEX=1`";"
 
    .EXAMPLE
    Invoke-AdoQuery -Query "Select count(*) countOf From authors"
                        -provider "vfpoledb"
                        -datasource "c:\MyFoxFiles\"
 
    .EXAMPLE
    $provider = "Microsoft.Jet.OLEDB.4.0"
    if ($header) {
        $ExtendedProperties = "Excel 8.0;HDR=Yes"
        }
    else {
        $ExtendedProperties = "Excel 8.0;HDR=NO"
        }
    $ds ="c:\temp\pubs.xls"
    # Note that the sheet name always seems to end in a dollar sign, '$'
    $cmd = 'select * from [authors$]'
    Invoke-AdoQuery -datasource:$ds -Provider:$provider -Query:$cmd -ExtendedProperties:$ExtendedProperties
 
    #>


    param (
        [Parameter(Mandatory = $true)]
        [string] $Query,
        [Parameter(Mandatory = $true)]
        [string] $Provider,
        [Parameter(Mandatory = $True, ValueFromPipeline = $True, ValueFromPipelinebyPropertyName = $True)]
        [string] $Datasource,
        [string] $ExtendedProperties
    )

    begin {
        $adOpenstatic = 3
        $adLockOptimistic = 3
    }

    process {
        $objConnection = New-Object -comobject "ADODB.Connection"
        $objRecordSet = New-Object -comobject "ADODB.Recordset"

        $connectionString = "Provider=$provider;data source=$datasource;"
        if ($ExtendedProperties) {
            $connectionString += "Extended Properties=$ExtendedProperties"
        }

        $objConnection.Open($connectionString)
        trap [System.Runtime.InteropServices.COMException] {
            write-error "ERROR: $($_.Exception.Message)"
            # to keep processing, I'd use continue here
            break
        }

        $objRecordSet.Open($Query, $objConnection, $adOpenStatic, $adLockOptimistic)
        $objRecordset.MoveFirst()

        # We need an object with appropriate properties to hold the data from the fields.
        # I am initializing the value here (even though we are going to overwrite it shortly)
        # so I get the correct data type associated with the property.
        $record = New-Object PSObject
        foreach ($field in $objRecordset.Fields) {
            $record | Add-Member -membertype noteproperty -name $field.Name -value $field.Value
        }

        do {
            # get a record's worth of fields and put the values in our working object
            # with every trip through the do {} loop, we will overwrite what existed from
            # the last row.
            foreach ($field in $objRecordset.Fields) {
                $record.$($field.Name) = $field.Value
            }
            # emit the working object to the pipeline, then move to the next record and loop
            $record
            $objRecordset.MoveNext()
        }
        until ($objRecordSet.EOF -eq $True)

        $objRecordset.Close()
        $objConnection.Close()

        [gc]::Collect()
        [gc]::WaitForPendingFinalizers()

    }

}