A result set - an object for 1 record, an array for many?

Question

I can’t believe it, but everything indicates that my PowerShell code returns the result of a SELECT query that finds 1 record as an object, but if there are two or more records, the same code returns an array of objects. What am I doing wrong?

Below is the code:

 function Connect-MySQL([string]$MySQLHost, [string]$user, [string]$pass, [string]$database) { Write-Verbose "Connect-MySQL" # Load MySQL .NET Connector Objects [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") # Open Connection $connStr = "server=$MySQLHost;port=3306;uid=$user;pwd=$pass;database=$database;Pooling=FALSE" try { $con = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) $con.Open() } catch [System.Management.Automation.PSArgumentException] { Write-Verbose "Unable to connect to MySQL server, do you have the MySQL connector installed..?" Write-Verbose $_ Exit } catch { Write-Verbose "Unable to connect to MySQL server..." Write-Verbose $_.Exception.GetType().FullName Write-Verbose $_.Exception.Message exit } Write-Verbose "Connected to MySQL database $MySQLHost\$database" return $con } function Disconnect-MySQL($con) { Write-Verbose "Disconnect-MySQL" $con.Close() } function Execute-MySQLNonQuery($con, [string]$sql) { $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $con) # Create SQL command $Rows = $cmd.ExecuteNonQuery() # Execute command $cmd.Dispose() # Dispose of command object # if ($Rows) { return $Rows # } else { # return $false # } } function Execute-MySQLQuery($con, [string]$sql) { $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $con) # Create SQL command $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) # Create data adapter from query command $dataSet = New-Object System.Data.DataSet # Create dataset $dataAdapter.Fill($dataSet, "data") | Out-Null # Fill dataset from data adapter, with name "data" $cmd.Dispose() # Dispose of command object return $dataSet.Tables["data"] # Returns an array of results } # Connection Variables $MySQLHost = '10.10.10.111' $user = 'user' $pass = 'test' $database = 'test_db' # Connect to MySQL Database $con = Connect-MySQL $MySQLHost $user $pass $database # Get requests $sql = " SELECT * FROM o365_statuses as x WHERE x.action = 'Z' AND x.status = 0" $result = Execute-MySQLQuery $con $sql Write-Host $result.Count Foreach ($r in $result) { write-host "$($r.id) $($r.email)" } 

When $result is only 1 record, $result.Count nothing, and I can access the columns as $result.id and not $result[0].id . If there are 2 or more entries, the opposite is returned.

Please tell me that I am doing something wrong and that this is not how PowerShell works.

Answer 1

PowerShell returns results based on this simple algorithm:

• More than one element -> returns an array of these elements
• Only one element -> returns an element

It is often useful to force PowerShell to always return an array using any of them:

 ,(code) @(code) 

However, these operators are not identical! You can safely apply the grouping operator @() to anything to force an array to be output - the array always has the Count property, for example:

 $a = @(Get-Process | select -First 2); $a.Count # returns 2 $a = @(Get-Process | select -First 1); $a.Count # returns 1 

On the other hand, the operator for constructing a semicolon array works as follows:

 $a = ,(Get-Process | select -First 2); $a.Count # returns 1 $a = ,(Get-Process | select -First 1); $a.Count # returns 1 

... because it creates an array of nested with what it is given.

Answer 2

The function "expands" the collection was returned, i.e. in fact, it returns either zero (0 elements), or one object (it may or may not have the Count property, in your case it is not) or an array of 2+ objects (but not an instance of the original collection).

To get around the deployment, use the, operator:

 return , $Rows 

The comma operator creates an array of one object (operand), i.e. new collection. This new collection also expands on return, but not recursively. It returns one object that is $Rows as is, the same instance.