Querying the MySQL db

Question

$Query1 = "INSERT INTO answers (gid,Key1,key2,key3,key4,key5) VALUES ($gid,'$key1','$key2','$key3','$key4','$key5');"

function Invoke-MySql {
  param($Query)

  $MySQLAdminUserName = 'root'
  $MySQLAdminPassword = <password>
  $MySQLDatabase = <username>
  $MySQLHost = 'localhost'
  $ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" +
                      $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword +
                      ";database=" + $MySQLDatabase
  Write-Log "Trying to connect MySql" 0

  try {
    [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()


    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $dataAdapter.Fill($dataSet, "data")
    $DataSet.Tables[0] | ft -AutoSize
    Write-Log "Querying the table with following query: $Query" 0
  } catch {
    Write-Log "ERROR : Unable to run query : $query `n$Error[0]" 2
  } finally {
    $Connection.Close()
    Write-Log "MySql connection closed" 0
  }
}

$Result1 = Invoke-MySql -Query $Query1

OUTPUT:

PS D:\MYSQL> D:\MYSQL\MySql_Insert&Query.ps1

   gid key1      key2    key3    key4  key5
   --- ----      ----    ----    ----  ----
 31657 c1        c2      c3      c4    c5
 31667 b1        b2      b3      b4    b5
112322 aa        bb      cc      dd    ee
212322 aa        bb      cc      dd    ee
212982 aa        bb      cc      dd    ee
215982 aa        bb      cc      dd    ee
215987 aa        bb      cc      dd    ee
315987 aa        bb      cc      dd    ee

So this is the output. I've fetched the results from MySQL, but now I want to filter the results like where I want to fetch only one value eg., key3 of gid=2122322.

Are you saying you want to filter on the $Result1 variable? You can do a $Result1 | Where-Object {$_.gid -eq '2122322'}, or write a new query to do a select with similar criteria. — Taylor
– Taylor, Commented Mar 28, 2016 at 12:08

Ansgar Wiechers · Accepted Answer · 2016-03-28 12:29:15Z

1

Your function is returning formatted output instead of the table itself:

$DataSet.Tables[0] | ft -AutoSize

Remove the | ft -AutoSize and you'll be able to work with the actual table data:

$Result1 = Invoke-MySql -Query $Query1 |
           Where-Object { $_.gid -eq 2122322 } |
           Select-Object -Expand key3

Format-* cmdlets are for formatting data when it's presented to the user. Never use them on data that should be subject to further processing (basically, never use them inside a function).

answered Mar 28, 2016 at 12:29

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

Collectives™ on Stack Overflow

Querying the MySQL db

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related