none
What small thing am I missing? RRS feed

  • Question

  • Can anyone help with this?

    I have a script that has served its purpose...it queries DB2 directly and returns the rows populated...but I now need the actual dataset. Can anyone give me a tip on modifying this script to do that? 

    I can follow what the script is doing: bouncing my query against db2 through odbc and then it populates a datatable which sits in memory. I've tried piping the datatable out through a couple different commands to no luck.

    $connectstring = "DSN=XXXXXXXXX;Uid=XXXXXXX;Pwd=XXXXXXXX;"
    
    
    $sql = "my sql query;"
     
    $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
    $cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
    $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
    $dt = New-Object system.Data.datatable
     
    $da.SelectCommand = $cmd;
     
    $conn.Open();
     
    $RowsReturned = $da.Fill($dt);
     
    $wshell = New-Object -ComObject Wscript.Shell
     
    $wshell.Popup("PreAudit DB2 returned $RowsReturned rows")
     
    ;

     
    Sunday, July 30, 2017 4:00 PM

Answers

  • Thank you!

    Does it make sense that I can now get an output file (...and this happened before while I was trying to figure this out) ...but no data is populated? I tried outputting a csv file and a txt file. There are placeholders but no data.

    How and what were you trying to export.  A data adapter cannot be directly exported.  A DataTable can be exported assuming the query returns rows.


    \_(ツ)_/

    • Marked as answer by Mikepellas Sunday, July 30, 2017 7:24 PM
    Sunday, July 30, 2017 6:53 PM

All replies

  • This is the correct way to extract data from a database.  The DataAdapter class is used with forms and when you want to do CRUD operations on the data.

    $connectstring = 'DSN=XXXXXXXXX;Uid=XXXXXXX;Pwd=XXXXXXXX;'
    $sql = 'my sql query;'
    
    $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
    $conn.Open();
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $sql
    $rdr = $cmd.ExecuteReader()
    $dt = New-Object System.Data.DataTable
    $dt.Load($rdr)
    if($count = $dt.Rows.Count){ Write-Host "PreAudit DB2 returned $count rows" $dt | Export-Csv datarows.csv }else{ Write-Host 'No rows returned' }


    \_(ツ)_/




    • Edited by jrv Sunday, July 30, 2017 4:54 PM
    Sunday, July 30, 2017 4:48 PM
  • Thank you!

    Does it make sense that I can now get an output file (...and this happened before while I was trying to figure this out) ...but no data is populated? I tried outputting a csv file and a txt file. There are placeholders but no data.

    Sunday, July 30, 2017 6:49 PM
  • Thank you!

    Does it make sense that I can now get an output file (...and this happened before while I was trying to figure this out) ...but no data is populated? I tried outputting a csv file and a txt file. There are placeholders but no data.

    How and what were you trying to export.  A data adapter cannot be directly exported.  A DataTable can be exported assuming the query returns rows.


    \_(ツ)_/

    • Marked as answer by Mikepellas Sunday, July 30, 2017 7:24 PM
    Sunday, July 30, 2017 6:53 PM
  • Thank you for the quick reply! 

    Everything I tried in the past was centered around the datatable. I get a row count of over 1,000,000 (same as the output from the script in my first post...and I've matched that in Access).

    When I even try your script example above I get an output file....but no data outside of row placeholders. I got this before but thought I did something wrong.

    Any thoughts? I really appreciate this. Your expertise is helping me take a huge next leap in my career.

    Sunday, July 30, 2017 7:04 PM
  • Try the following:

    $dt | select <choose two or three simple fields> | Export-Csv <filename> -NoType

    You cannot reliably export binary fields to a CSV.


    \_(ツ)_/

    Sunday, July 30, 2017 7:08 PM
  • Got it working. Thank you!
    Sunday, July 30, 2017 7:24 PM