ForEach-Object loop over a MySql DataTable first row blank

Question

I'm trying to write a PowerShell script that queries a MySQL database, stores the result, and then performs a command for each row. In this case it will eventually email a report but this problem cropped up before I got there.

The table seems to store fine, and an extremely simple ForEach-Object loop returns what I expect, but when I attempt to make the loop more complex the first row is empty. Although I can work around it in this particular case, I'm trying to understand why it's happening and coming up short.

Here is the function that builds the query (taken from https://vwiki.co.uk/MySQL_and_PowerShell):

function Execute-MySQLQuery([string]$query) { 

  $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $conn)    # 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")                                          # Fill dataset from data adapter, with name "data"              
  $cmd.Dispose()
  return $dataSet.Tables["data"]                                               # Returns an array of results
}

This is called here:

# Connect to MySQL Database 
$conn = Connect-MySQL $user $pass $MySQLHost $database $certfile $certpass

$query = "SELECT * FROM reports;"
$result = Execute-MySQLQuery $query

$result | Format-Table

And returns this exactly:

PS C:\Users\redacted> C:\Users\redacted\Desktop\tmp\testsql.ps1
5

rep_id email_add1           user            sent_success
------ ----------           ----            ------------
     1 [email protected]    Jim Johnson                1
     2 [email protected]     John Doe                   1
     3 [email protected]    Stacy Something            0
     4 [email protected] Willy Gates                1
     5 [email protected] Paul Allen                 0

However when I try to use just a single column of this in a ForEach-Object loop I get a blank line. For testing purposes I tried a very basic:

$result | ForEach-Object {$_.user}

Which returned the expected:

PS C:\Users\redacted> C:\Users\redacted\Desktop\tmp\testsql.ps1
Jim Johnson
John Doe
Stacy Something
Willy Gates
Paul Allen

But as soon as I get more complicated than that:

$result | ForEach-Object {"Howdy $($_.user)!"}

I get:

PS C:\Users\redacted> C:\Users\redacted\Desktop\tmp\testsql.ps1
Howdy !
Howdy Jim Johnson!
Howdy John Doe!
Howdy Stacy Something!
Howdy Willy Gates!
Howdy Paul Allen!

Am I missing something obvious? I'm (clearly) still learning but I found this problem harder to Google for than others. Maybe I was just using the wrong terms, but I'd really appreciate anyone who could point me in the right direction. Thanks you.

[void]$dataAdapter.Fill($dataSet, "data")

user4003407
– user4003407

2017-09-26 21:33:36 +00:00
Commented Sep 26, 2017 at 21:33 — user4003407
– user4003407, Commented Sep 26, 2017 at 21:33

Tomalak · Accepted Answer · 2017-09-27 06:44:47Z

1

Every value that is produced in a script block (like a function body) will become part of the function result unless stored in a variable or explicitly discarded.

This function has three output values (try it):

function foo {
    1
    2
    3
}

foo | foreach { "I received $_" }

Note that the use of return is unnecessary. All return does is terminate the function. Also note that the foreach body has no return statement but still produces three values.

Many functions output values you don't really care for. Those values must be dealt with anyway in Powershell. If you want to eliminate them from your function's output, either store them or pipe them into Out-Null:

function Execute-MySQLQuery {
    param(
        [MySql.Data.MySqlClient.MySqlConnection]$connection,
        [string]$query
    )
    $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $conn)
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)
    $dataSet = New-Object System.Data.DataSet
    $dataAdapter.Fill($dataSet, "data") | Out-Null
    $cmd.Dispose()
    $dataSet.Tables["data"]
}

$conn = Connect-MySQL $user $pass $MySQLHost $database $certfile $certpass

$result = Execute-MySQLQuery -connection $conn -query "SELECT * FROM reports;"
$result | Format-Table

The value produced by the last line in the function is not stored anywhere and it's not discarded. Therefore it becomes part of what the function returns.

edited Sep 27, 2017 at 6:44

answered Sep 26, 2017 at 22:32

Tomalak

340k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RobertRSeattle Over a year ago

I see! Like piping to /dev/null. Thank you, that works perfectly. It looks like that extra line was coming from the Fill command but I can't find much about that in PowerShell docs, is that from .NET?

Tomalak Over a year ago

Yeah, that's the .NET function's return value. .Fill() returns the number of records filled, I think. But it works like that with all things that produce values, be it Powershell calculations, .NET functions, cmdlets, or regular text-based command line tools - keep an eye out, it's easy to overlook. You don't have to write | Out-Null behind every statement from now on, though, when in doubt check the documentation.

Collectives™ on Stack Overflow

ForEach-Object loop over a MySql DataTable first row blank

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related