How to use ADODB in PowerShell

Question

I thought I'd post these code snippets for others who may find themselves trying to make ADODB calls from a PowerShell script. I inherited this convoluted mess, and had to make some changes to it.

We're using PlanetPress as part of a Docuware Document Imaging system. A PP workflow called a vbscript which in turn launched a PowerShell script. The PowerShell did the work to make two database queries. One was an update, and the other was a select. I'm not that great with PowerShell, and there may be a cmdlet out there to simplify this. But the code was creating ADODB.Connection, ADODB.Command, and ADODB.Resultset objects directly. The problem is there are no good resources for the syntax required to use these objects. Hopefully these code snippets will help some poor soul in a similar situation.

Using ADODB.Command:

$oConnection = New-Object -comobject "ADODB.Connection"

# Use correct ODBC driver
if ([Environment]::Is64BitProcess) {
   $oConnection.Open("DSN=DW64")
} else {
   $oConnection.Open("DSN=DW")
}

if ($oConnection.State -eq $adStateClosed) {
      Write-Output "Connection not established" 
      Write-Output $oConnection                
}

$UpdQuery = "Update dwdata.Purchasing `
             set Status='Processing' `
             WHERE DOCUMENT_TYPE = 'Check' `
             AND STATUS in ('Approved')"
$ra=-1

$oCommand = New-Object -comobject "ADODB.Command"
$oCommand.ActiveConnection = $oConnection
$oCommand.CommandText = $UpdQuery
$oCommand.CommandType = $adCmdText
$rs=$oCommand.Execute([ref]$ra)
Write-Output ("Count of Row[s] updated: " + $ra)

Using ADODB.Resultset:

$oRS = New-Object -comobject "ADODB.Recordset"

$query = "SELECT DWDOCID, DOCUMENT_DATE, CHECK_NUMBER, PAYEE_NAME, CHECK_AMOUNT, STATUS `
            FROM dwdata.Purchasing `
            WHERE DOCUMENT_TYPE = 'Check' `
            AND STATUS = 'Processing' `
            ORDER BY CHECK_NUMBER;"

# $oConnection object created in ADODB.Command snippet above
$oConnection.CursorLocation = $adUseClient
$oRS.Open($query, $oConnection, $adOpenStatic, $adLockOptimistic)

$reccount  = "Number of queried records: " + $oRS.RecordCount
write-output $reccount 

If (-not ($oRS.EOF)) {

    # Move to the first record returned, and loop
    $oRS.MoveFirst()
    $reccount = "Number of loop records: " + $oRS.RecordCount
    write-output $reccount 

    do {
        $outString  = '"' + $oRS.Fields.Item("DOCUMENT_DATE").Value.ToString("MM/dd/yyyy") + '"' + ',' 
        $outString += '"' + $oRS.Fields.Item("CHECK_NUMBER").Value                         + '"' + ',' 
        $outString += '"' + $oRS.Fields.Item("PAYEE_NAME").Value                           + '"' + ','
        $outString += '"' + $oRS.Fields.Item("CHECK_AMOUNT").Value                         + '"' + ','
        $outString | Out-File $bankfile -Append -Encoding ASCII

        $oRS.MoveNext()
    } until 
        ($oRS.EOF -eq $True)
} Else{
    Write-Output "No records returned from database query." 
}

$oRS.Close()
$oConnection.Close()

Some of this code is ugly (using do instead of while), but the idea is to help you get the right syntax for $oCommand.Execute and how to get a record count from the Recordset. $oRS.MoveFirst() needs to be called before the record count is available.

ss64.com and other resources usually give vbscript snippets. In vbscript variables are not preceeded with a $, and when or if you need to use parenthesis is unclear. This code does run and work.