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:
|
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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
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.
Answer:
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:
|
1 2 3 |
,(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:
|
1 2 3 |
$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:
|
1 2 3 |
$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.