MacLochlainns Weblog

I was trying to explain how to validate PowerShell script parameters to my students when I found the number and quality of enum data type examples was woefully inadequate. Here’s a series of examples that show you how to validate input parameters against an enum list of values.

The basic concept requires you to validate an element in an enum type. It uses a literal value

1
2
3
4
5
6
7
8
9
10

# Create a enum type.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# Check whether the value is found in the enum type.
if ([enum]::isDefined(([msgTypes]), [msgTypes]::moe)) {
  Write-Host "Success"
}

You test this testEnum1.ps1 script file with this syntax:

powershell testEnum1.ps1

It prints:

Success

The next sample embeds the validation in a local function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# Create a enum type.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# A local function for verbose reporting.
function Print-Message ($msg) {
  # Check whether the value is found in the enum type.
  if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
    Write-Host "Success"
  }
}
 
# Call the function with a literal value.
Print-Message "moe"

You test this testEnum2.ps1 script file with this syntax:

powershell testEnum2.ps1

It also prints:

Success

The next sample testEnum3.ps1 accepts a parameter and passes it to the validation function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# Create a enum type.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# A local function for verbose reporting.
function Print-Message ($msg) {
  # Check whether the value is found in the enum type.
  if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
    Write-Host "Success"
  }
}
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}
 
# Call the function with a literal value.
Print-Message "moe"

You test this testEnum.ps1 script file with this syntax:

powershell testEnum3.ps1 moe

It also prints:

Success

However, if you don’t pass a parameter to the testEnum3.ps1, like this

powershell testEnum3.ps1

It raises the following error:

Exception calling "IsDefined" with "2" argument(s): "Value cannot be null.
Parameter name: value"
At C:\Data\cit225\mysql\test\testEnum3.ps1:9 char:7
+   if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
+       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException

Rewriting the Print-Message function in the script may appear to fix the problem. At least, a missing parameter won’t cause an error when you wrap the call to the isDefined method inside an if-statement.

Here’s how that wrapping attempt would look:

7
8
9
10
11
12
13
14
15
16

# A local function for verbose reporting.
function Print-Message ($msg) {
  if (!($msg -eq $null)) {
    if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
      Write-Host "Success"
    }
  }
  else {
  Write-Host "Failure" }
}

While the prior change to the Print-Message function manages the lack of a parameter, it doesn’t prevent a failure when you pass an incorrect parameter. A new variation of the old error occurs when you pass a parameter that is not a member of the enum type, like

powershell testEnum4.ps1 Shem

It now prints:

Failure

So, you need to complete another step. Our woeful tale of parameter validation against a set of possible enum values isn’t quite complete. That’s because any incorrect parameter value raises a null value when isDefined method can’t find a valid value in the enum type. This standard behavior means that the isDefined method returns this error message:

Exception calling "IsDefined" with "2" argument(s): "Value cannot be null.
Parameter name: value"
At C:\Data\cit225\mysql\test\testEnum4.ps1:10 char:9
+     if ([enum]::isDefined(([msgTypes]), [msgTypes]::$msg)) {
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException

After all the effort to sort through how PowerShell handles the isDefined method and navigating Microsoft’s limited PowerShell examples, we’ve travelled down a rabbit hole. The problem is that the isDefined method isn’t terribly useful.

You need to use another getValues method, which returns the set of member values from the enum type. This requires you to write a new function. Find-EnumMatch seems an appropriate Pascal-like name for that function because that’s what it will do. Writing the new function also simplifies the Print-Message function.

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

# Create a enum type for statements, for future use.
Add-Type -TypeDefinition @"
  public enum msgTypes
    { moe, larry, curly }
"@
 
# A local function to find a valid enum member.
function Find-EnumMatch ($typeof, $member) {
  # Set default return value.
  $evaluated = $false
 
  # Check for a not null match to an enum member.
  if (!($msg -eq $null)) {  
    foreach ($msgValue in $msgTypes = [enum]::getValues(([enum]::'[' + $typeof + ']'))) {
      if ($msgValue -eq $member) {
 	    $evaluated = $true
 	    break }
    }
  }
  # Return whether true or false.
  return $evaluated
}   
 
# A local function for verbose reporting.
function Print-Message ($msg) {
  # Check for a not null match to an enum member.
  if (find-enumMatch $msg) { Write-Host "Success" }
  else { Write-Host "Failure" }
}
 
# Wrap the Parameter call to avoid a type casting warning.
try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}

Now, if we test the program with a valid, invalid, or null value parameter it works as expected. It prints “Success” when the parameter is found as a member of the enum type, and prints “Failure” when the parameter is null or not found as a member of the enum type. It also never raises an unhandled exception.

There’s an important explicit casting trick required on line #14 to avoid the following error:

Cannot convert argument "enumType", with value: "[msgTypes]", for "GetValues" to type "System.Type": "Cannot convert the "[msgTypes]" value of
type "System.String" to type "System.Type"."
At C:\Data\cit225\mysql\test\testEnum7.ps1:14 char:27
+ ... ($msgValue in $msgTypes = [enum]::getValues(('[' + $typeof + ']'))) {
+                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

While many developers might think that you can write the line without explicitly casting the enum name enclosed by square brackets, you actually have to explicitly cast it.

As always, I hope this helps those looking for an arcane bit of knowledge. While handling parameters is routine, it sure appears the method for doing so in PowerShell with an enum type isn’t quite well documented.

Playing around with passing parameters for a PowerShell project and ran into some interesting quirks. I found that you can pass in a set of values by declaring the parameter list as an array or string[] but the Microsoft documentation suggests using the array data type, which is implicitly cast as a string.

So, this would work provided you want to enter commas between the parameters:

param (
  [Parameter(Mandatory)][array]$args
)

However, you can’t use a -o for example because it’ll raise this error message:

C:\PowerShell\test\Params.ps1 : Parameter cannot be processed because the parameter name 'o' is ambiguous. Possible matches
include: -OutVariable -OutBuffer.
At line:1 char:21
+ ./Params.ps1 -o output.csv
+                     ~~
    + CategoryInfo          : InvalidArgument: (:) [Params.ps1], ParameterBindingException
    + FullyQualifiedErrorId : AmbiguousParameter,Params.ps1

The error doesn’t occur because the script but because the -OutVariable and -OutBuffer are parameters of the Powershell executable. You can get rid of the error and use an array without error by using two hyphens (—) back-to-back before your script’s parameters. The two hyphens together are the end-of-parameters operator, which means everything following it will be treated as arguments.

However, the better solution is to use a hashtable, like:

param (
  [Parameter(Mandatory)][hashtable]$args
)

However, as written above it raises the following error:

Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Collections.Hashtable".
At C:\PowerShell\test\Params.ps1:35 char:1
+ param (
+ ~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : ConvertToFinalInvalidCastException

There’s a great deal of humor if you recognize that the parameters are actually cast as strings in the hashtable and the error is more of a warning rather than a real error. All you need to do is wrap the param() call in a try-catch block, like:

try {
  param (
    [Parameter(Mandatory)][hashtable]$args
  )
}
catch {}

Then, you can call the Powershell script like this from the CLI:

powershell params -o outfile.csv -q query.sql

and, process it in a block like this, naturally qualifying the keys before assigning to local variables:

if ($args.count % 2) {
  Write-Host "Uneven number of parameters." 
  Exit }
else {
  for ($i = 0; $i -lt $args.count; $i += 2) {
    if ($args[$i] -eq "-o") {
      $outFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $queryFile = $args[$i + 1] }
  }
}

Since this is really simply a script file, I prefer the if-not logic or a verbose (wordy) list of options when the parameter list is incorrect. For example, you could rewrite the block above to exclude an error message as follows:

if (!($args.count % 2)) {
  for ($i = 0; $i -lt $args.count; $i += 2) {
    if ($args[$i] -eq "-o") {
      $outFile = $args[$i + 1] }
    elseif ($args[$i] -eq "-q") {
      $queryFile = $args[$i + 1] }
  }}
else {
  exit }

While I answered the question about pairs, it’s a bad approach. You can find the right way to handle options (or, switches) and parameters to your PowerShell scripts in this later blog post.

As always, I hope blogging tidbits helps others looking for a solution.

This post explains and demonstrates how to install, configure, and use the psqlODBC (32-bit) and psqlODBC (64-bit) libraries to connect your Microsoft PowerShell programs to a locally installed PostgreSQL 14 database. It relies on you previously installing and configuring a PostgreSQL 14 database. This post is a step-by-step guide to installing PostgreSQL 14 on Windows 10, and this post shows you how to configure the PostgreSQL 14 database.

If you didn’t follow the instructions to get the psqlODBC libraries in the installation blog post, you will need to get those libraries, as qualified by Microsoft with the PostgreSQL Stack Builder.

You can launch PostgreSQL Stack Builder after the install by clicking on Start -> PostgreSQL -> Stack Builder. Choose to enable Stack Builder to change your system and install the psqlODBC libraries. After you’ve installed the psqlODBC library, use Windows search field to find the ODBC Data Sources dialog and run it as administrator.

There are six steps to setup, test, and save your ODBC Data Source Name (DSN). You can click on the images on the right to launch them in a more readable format or simply read the instructions.

PostgreSQL ODBC Setup Steps

1. The Microsoft DSN (Data Source Name) dialog automatically elects the User DSN tab. Click on the System DSN tab.

2. The view under the System DSN is exactly like the User DSN tab. Click the Add button to start the workflow.

3. The Create New Data Source dialog requires you select the PostgreSQL ODBC Driver(UNICODE) option from the list and click the Finish button to proceed.

4. The PostgreSQL Unicode ODBC Driver Setup dialog should complete the prompts as follows below and consistent with the PostgreSQL 14 Configuration blog. If you opt for localhost as the server value because you have a DCHP IP address, make sure you’ve configured your hosts file in the C:\Windows\System32\drivers\etc directory. You should enter the following two lines in the hosts file:

   127.0.0.1 localhost ::1 localhost

   127.0.0.1 localhost ::1 localhost

   These are the string values you should enter in the PostgreSQL Unicode ODBC Driver Setup dialog:

   Data Source: PostgreSQL35W Database: videodb Server: localhost User Name: student Description: PostgreSQL SSL Mode: disable Port: 5432 Password: student

   Data Source: PostgreSQL35W Database: videodb Server: localhost User Name: student Description: PostgreSQL SSL Mode: disable Port: 5432 Password: student

   After you complete the entry, click the Test button.

5. The Connection Test dialog should return a “Connection successful” message. Click the OK button to continue.

6. The ODBC Data Source Administrator dialog should show the PostgreSQL35W System Data Source. Click the OK button to continue.

After you have created the System PostgreSQL ODBC Setup, it’s time to build a PowerShell Cmdlet (or, Commandlet). Some documentation and blog notes incorrectly suggest you need to write a connection string with a UID and password, like:

$ConnectionString = 'DSN=PostgreSQL35W;Uid=student;Pwd=student'

The UID and password is unnecessary in the connection string. As a rule, the UID and password are only necessary in the ODBC DSN, like:

$ConnectionString = 'DSN=PostgreSQL35W'

You can create a readcursor.ps1 Cmdlet like the following:

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

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=PostgreSQL35W'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT current_database();";
 
  # Attempt to read SQL command.
  try {
    $Reader = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($Reader.Read()) {
      Write-Host "Current Database [" $Reader[0] "]"}
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $Reader.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

Line 14 assigns a SQL query that returns a single row with one column as the CommandText of a Command object. Line 22 reads the zero position of a row or record set with only one column.

You call the readcursor.ps1 Cmdlet with the following syntax:

powershell .\readcursor.ps1

It returns:

Current Database [ videodb ]

A more realistic way to write a query would return multiple rows with a set of two or more columns. The following program queries a table with multiple rows of two columns, but the program logic can manage any number of columns.

# Define a ODBC DSN connection string.
$ConnectionString = 'DSN=PostgreSQL35W'
 
# Define a MySQL Command Object for a non-query.
$Connection = New-Object System.Data.Odbc.OdbcConnection;
$Connection.ConnectionString = $ConnectionString
 
# Attempt connection.
try {
  $Connection.Open()
 
  # Create a SQL command.
  $Command = $Connection.CreateCommand();
  $Command.CommandText = "SELECT last_name, first_name FROM contact ORDER BY 1, 2";
 
  # Attempt to read SQL command.
  try {
    $row = $Command.ExecuteReader();
 
    # Read while records are found.
    while ($row.Read()) {
      # Initialize output for each row.
      $output = ""
 
      # Navigate across all columns (only two in this example).
      for ($column = 0; $column -lt $row.FieldCount; $column += 1) {
        # Mechanic for comma-delimit between last and first name.  
        if ($output.length -eq 0) { 
          $output += $row[$column] }
        else {
          $output += ", " + $row[$column] }
        }
        # Write the output from the database.
        Write-Host $output
      }
 
  } catch {
    Write-Error "Message: $($_.Exception.Message)"
    Write-Error "StackTrace: $($_.Exception.StackTrace)"
    Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
  } finally {
    # Close the reader.
    $row.Close() }
 
} catch {
  Write-Error "Message: $($_.Exception.Message)"
  Write-Error "StackTrace: $($_.Exception.StackTrace)"
  Write-Error "LoaderExceptions: $($_.Exception.LoaderExceptions)"
} finally {
  $Connection.Close() }

You call the readcontact.ps1 Cmdlet with the following syntax:

powershell .\readcontact.ps1

It returns an ordered set of comma-separated values, like

Clinton, Goeffrey
Gretelz, Simon
Moss, Wendy
Royal, Elizabeth
Smith, Brian
Sweeney, Ian
Sweeney, Matthew
Sweeney, Meaghan
Vizquel, Doreen
Vizquel, Oscar
Winn, Brian
Winn, Randi

As always, I hope this helps those looking for a complete concrete example of how to make Microsoft Powershell connect and query results from a PostgreSQL database.