Egregious Erroneous Exceptions

Continuing on from my previous rant, I've been dabbling more with PowerShell 2.0 and learning some of the nuances of error handling (or "error trapping", or "exception handling" or "exception management" or whatever the heck you want to call it). So I thought it might be worth posting a slightly modified, enhanced, updated, semi-improved version of the ado-query.ps1 script to illustrate this.

With VBScript 5.6 and later, you have a very simple, straightforward way to handle exceptions:

On Error Resume Next
'do something stupid here, like trying to open a file
'that doesn't exist
If Err.Number <> 0 Then
  'do something to handle the error
  Wscript.Echo "You did something stupid, as usual.  You idiot."
  'then bail out if you want to
  Wscript.Quit(0)
End If

With KiXtart 4.6x, you also have a fairly simple way to handle errors, which looks very much like that for other scripting languages, even VBScript. The KiXtart counterparts to VBScript's Err.Number and Err.Description properties are the @Error and @SError macros.

Break ON
;do something stupid here, like trying to read a bad registry value
$regval = ReadValue("HKLM\Software\BlahBlahBlah\YapYap")
If @Error <> 0
  ;do something to handle the error
  ? "You did something stupid, as usual.  You idiot."
  ;then bail out if you want to
  Exit(0)
EndIf

In PowerShell 1.x you have Trap{} and Throw{} which are akin to "On Error Resume Next" and "If Err.Number <> 0 Then..." code methods in VBScript. Well, kind of. With PowerShell 2.0 you now have Try/Catch/Fail, which is more like traditional compiled languages like C++, C#, Java, and so on.

#****************************************************************
# Filename..: ado_query.ps1
# Author....: skatterbrainz
# Date......: 05/06/09
# Purpose...: query ms-access database table
# SQL.......: (path and .mdb filename)
#****************************************************************
# Improved version with low-fat error handling added for free!
#****************************************************************
$dbfile = "c:\database.mdb"
$table  = "Table1"
$records = 100

$adOpenStatic   = 3
$adLockReadOnly = 1
$adUseClient = 3
$adCmdText   = 1

$t1 = Get-Date

$dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+$dbfile
$objConn = New-Object -ComObject ADODB.Connection
$objCmd  = New-Object -ComObject ADODB.Command
$objRS   = New-Object -ComObject ADODB.RecordSet

$strQuery = "SELECT TOP "+$records+" * FROM "+$table
Try {
    $objConn.Open($dsn)
}
Catch {
    "ado-Connection failure"
    Break
}
$objRS.CursorLocation = $adUseClient
$objRS.CursorType = $adOpenStatic
$objRS.LockType = $adLockReadOnly
$objCmd.ActiveConnection = $objConn
$objCmd.CommandType = $adCmdText
$objCmd.CommandText = $strQuery
Try {
    $objRS.Open($objCmd)
}
Catch {
    "ado-RecordSet open failure"
    Break
}
If ($objRS.BOF -And $objRS.EOF) {
    $objRS.Close()
    $objConn.Close()
    $objRS = $null
    $objCmd = $null
    $objConn = $null
    Write-Host "error: no records found"
    Break
}
While (-Not $objRS.EOF) {
    For ($i = 0; $i -lt $objRS.Fields.Count-1; $i++) {
            Write-Host $objRS.Fields.Item($i).Name: $objRS.Fields.Item($i).Value
    }
    Write-Host
    $objRS.MoveNext()
}
Write-Host "`nRecords: " $objRS.RecordCount
$objRS.Close()
$objConn.Close()
$objRS = $null
$objCmd = $null
$objConn = $null

$runtime = New-TimeSpan $t1 $(Get-Date)
Write-Host "Runtime: "$runtime.Milliseconds" msec"

Similar to most programming languages, you can also modify formatting to suit your particular tastes. For example, the Try/Catch code above for attempting the ADO connection could be reformatted as follows to compact the lines of code a little more...

Try {$objConn.Open($dsn)}
Catch {"ado-Connection failure"; Break}

Now, before you get anxious to fire off an over-caffeinated comment, please consider that this isn’t intended to be an “advanced” training course or anything like that.  It’s just the mindless drivel of a nobody blog-poster, um, uhh, well, that would be me.  Anyhow, I hope it helps somebody.  Post a comment if you find any mistakes or have something you’d like to share with me or other readers.  Cheers!