Result set is object for 1 record, array for many?

Question

I can't believe it, but all indications are that my PowerShell code is returning 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 returns nothing, and I can access the columns as $result.id and not $result[0].id. If there are 2 or more records returned the opposite is true.

Please tell me I'm doing something wrong, and that this is not the way PowerShell works.

score 5 · Answer 1 · answered Oct 31 '12 at 20:21

PowerShell returns results based on this simple algorithm:

More than one item --> return an array of those items
Just one item --> return the item

Often it is good practice to coerce PowerShell to always return an array, using either of these:

,(code)
@(code)

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

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

The comma array construction operator, on the other hand, works like this:

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

...because it builds a nested array with whatever it is given.

score 1 · Answer 2 · answered Oct 29 '12 at 06:28

A function "unrolls" a collection been returned, i.e. in fact it returns either null (0 items) or a single object (it may or may not have the property Count, in your case it does not) or an array of 2+ objects (but not the original collection instance).

In order to work around unrolling use the , operator:

return , $Rows

The comma operator creates an array of a single object (operand), i.e. a new collection. This new collection is also unrolled on return but not recursively. That is a single object is returned which is $Rows as it is, the same instance.

Result set is object for 1 record, array for many?

2 Answers2