none
export query result RRS feed

  • Question

  • How do I export the results of this query?. I attempt to Out-file, Export-csv but I can't

    [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    [system.reflection.assembly]::LoadWithPartialName("MySql.Data")

    $myconnection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $myconnection.ConnectionString = "server=localhost;user id=xxxxxxx;password=xxxxxxxx;pooling=false"
    $myconnection.Open()

    $mycommand = New-Object MySql.Data.MySqlClient.MySqlCommand
    $mycommand.Connection = $myconnection
    $mycommand.CommandText = "SELECT * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='QUERIES' OR VARIABLE_NAME='THREADS_CONNECTED';"
    $myreader = $mycommand.ExecuteReader() 
    while($myreader.Read()){write-host $myreader.getstring(0)" : "$myreader.getstring(1)}
    $mycommand.Connection.close()

    Desired export:
    ddmmyyy-HH:mm ->  QUERIES  :  xxxx   //  THREADS_CONNECTED  :  xxx

    Thanks!
    Monday, January 30, 2017 7:44 PM

Answers

  • We use a datatable to capture the data from a query.

    $myreader = $mycommand.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($myreader)
    $mycommand.Connection.close()
    $dt | Export-Csv file.csv
    

    You can then use all PS formatting techniques:

    $dt | Format-Table -auto
    $dt | Format-List

    or create your own format with a computer select-Object statement.


    \_(ツ)_/


    Monday, January 30, 2017 8:29 PM

All replies

  • We use a datatable to capture the data from a query.

    $myreader = $mycommand.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($myreader)
    $mycommand.Connection.close()
    $dt | Export-Csv file.csv
    

    You can then use all PS formatting techniques:

    $dt | Format-Table -auto
    $dt | Format-List

    or create your own format with a computer select-Object statement.


    \_(ツ)_/


    Monday, January 30, 2017 8:29 PM
  • Thank you for the information. Do you know how could put it all on a single line?
    Tuesday, January 31, 2017 1:26 PM
  • Thank you for the information. Do you know how could put it all on a single line?

    Why?


    \_(ツ)_/

    Tuesday, January 31, 2017 7:10 PM
  • Hi jrv

    because what I'm going to ejeuctar several times and is more visual them results

    Tuesday, January 31, 2017 8:05 PM
  • Hi jrv

    because what I'm going to ejeuctar several times and is more visual them results

    ?????

    Your translation software is failing.


    \_(ツ)_/

    Tuesday, January 31, 2017 8:46 PM
  • Do you mean "ejecutar"  "run", "execute"?


    \_(ツ)_/

    Tuesday, January 31, 2017 8:47 PM
  • execute. 
    Tuesday, January 31, 2017 9:24 PM
  • What is it that you need to run repeatedly and only in a pipeline?


    \_(ツ)_/

    Tuesday, January 31, 2017 9:34 PM
  • Yes, all in a line.
    Wednesday, February 1, 2017 4:36 PM
  • Yes, all in a line.
    All of what in a line?  You have to be clear about what you are trying to do.

    \_(ツ)_/

    Wednesday, February 1, 2017 4:43 PM
  •        

    Hi JRV

    Output Current:

    VARIABLE_NAME                                               VARIABLE_VALUE                                             
    -------------                                                            --------------                                             
    QUERIES                                       164     
    THREADS_CONNECTED                     99

    I need this Output:

    QUERIES;164;THREADS_CONNECTED;99
    QUERIES;22;THREADS_CONNECTED;2
    QUERIES;344;THREADS_CONNECTED;10
    QUERIES;343;THREADS_CONNECTED;44


    Wednesday, February 1, 2017 5:35 PM
  • Then you will have t write a bit of code to format the output.

    'QUERIES;{0}THREADS;{1}' -f ($_.VARIABLE_NAME),($_.VARIABLE_VALUE)

    Of course you can also change the SQL to return that string.


    \_(ツ)_/


    • Edited by jrv Wednesday, February 1, 2017 5:45 PM
    Wednesday, February 1, 2017 5:44 PM
  • I do`nt understand what I say to modify the output of the SQL.

    the output that you mention, I must send to the Out-File, correct?

    Wednesday, February 1, 2017 5:54 PM
  • If that is what you want.  Of course you asked for an export.  This is not an export by any standard definitions.

    You could also use a calculated select statement.


    \_(ツ)_/



    • Edited by jrv Wednesday, February 1, 2017 5:58 PM
    Wednesday, February 1, 2017 5:57 PM