locked
Output Window Showing Results In Incorrect Order? RRS feed

  • Question

  • This is my syntax, and it shows query1, query2, then the createdirectory, then query3 even though the createdirectory is called 1st.  Why does this occur?

    $MyArray = $null
    $Query1WBName = "Query1"
    $Query2WBName = "Query2"
    $Query3WBName = "Query3"
    $Query1 = ""
    $Query2 = ""
    $Query3 = ""
     
    if (Execute-SQLquery $Query1WBName $Query1)
    {
                    if (Execute-SQLquery $Query2WBName $Query3)
                    {
                                    Execute-SQLquery $Query3WBName $Query2
                    }
    }
     
    Function Execute-SQLquery {
        param ($QueryName, $QueryString)
     
                    $server = "Server1"
                    $database = "DB01"
                    $MasterPath = "C:\Templates\"
     
                    $extension = ".csv"        
                    $date = Get-Date -f 'MM.dd.yy'
     
                   
                    $connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
                    $connectionString = [string]::Format($connectionTemplate, $server, $database)
                    $connection = New-Object System.Data.SqlClient.SqlConnection
                    $connection.ConnectionString = $connectionString
     
                    $command = New-Object System.Data.SqlClient.SqlCommand
                    $command.CommandText = $QueryString
                    $command.Connection = $connection
     
                    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
                    $SqlAdapter.SelectCommand = $command
                    $DataSet = New-Object System.Data.DataSet
                    $rowCount = $SqlAdapter.Fill($DataSet)
     
                    if(!(Test-Path -path "$MasterPath\$date\"))
                    {
                                    New-Item "$MasterPath\$date\" -type directory
                    }
                    $extractFile = switch($QueryName)
                    {
                                    "Query1" { "C:\\Uno.csv" }
                                    "Query2" { "C:\\Two.csv" }
                                    "Query3" { "C:\\Three.csv" }
                                    default { throw "Illegal extractFile" }
                    }
                    $dirName = switch($QueryName)
                    {
                                    "Query1" { "$MasterPath\$date\One\" }
                                    "Query2" { "$MasterPath\$date\Two\" }
                                    "Query3" { "$MasterPath\$date\Three\" }
                                    default { throw "Illegal dirName" }
                    }
                    $name = switch($QueryName)
                    {
                                    "Query1" { "One" }
                                    "Query2" { "Two" }
                                    "Query3" { "Three" }
                                    default { throw "Illegal name" }
                    }
                                    [System.IO.Directory]::CreateDirectory($dirName)
                                    Write-Host "$name - $rowCount" -fore Red
                                    $filename = [IO.Path]::GetFileNameWithoutExtension($extractFile) + "_$date" + [IO.Path]::GetExtension($extractFile)
                                    $extractFile = Join-Path $dirname $filename                                      
                                    $DataSet.Tables[0] | Export-Csv $extractFile -NoTypeInformation
                                    $Global:MyArray += "$name - $rowCount `n"
                    $connection.Close()
    }

    And this is my outpput

    My output window shows
    Query1 - 25
    Query2 - 4
    Mode           LastWriteTime 
    ----           -------------
    d----          07/02/2015 10:06 AM
    Query3 - 2

    Thursday, July 2, 2015 6:48 PM

Answers

  • This is the general pattern that you want to use.  If you can understand it and make it work then you can try to generalize it.

    Function Execute-SQLquery {
        param (
            $QueryString
        )
        
        $server = 'Server1'
        $database = 'DB01'
        $connStr = 'Data Source={0};Integrated Security=SSPI;Initial Catalog={1};' -f $server, $database
        $conn=New-Object System.Data.SqlClient.SQLConnection($connStr)
        $cmd=$conn.CreateCommand()
        $cmd.CommandText = $QueryString
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
        $dt = New-Object System.Data.DataTable
        if($SqlAdapter.Fill($dt) -gt 0){
            $dt
        }
        $conn.Close()
    }
    $MasterPath='C:\Templates\' + [DateTime]::Today.ToString('MM.dd.yy') #'
    New-Item $MasterPath -type directory -Force
    
    if($dt = Execute-SQLquery  'select * from xyz'){
        $path=Join-Path $MasterPath 'one'
        New-Item $path -ItemType Directory
        $csvpath=Join-Path $path one.csv
        $dt | Export-Csv $csvpath
        if ($dt = Execute-SQLquery  'other select') {
            $path = Join-Path $MasterPath 'two'
            New-Item $path -ItemType Directory
            $csvpath = Join-Path $path two.csv
            $dt | Export-Csv $csvpath
            if ($dt = Execute-SQLquery  'select * from three') {
                $path = Join-Path $MasterPath three
                New-Item $path -ItemType Directory
                $csvpath = Join-Path $path three.csv
                $dt | Export-Csv $csvpath
            }
        }
    }
        


    \_(ツ)_/




    Monday, July 6, 2015 3:41 AM

All replies

  • Functions have to come first in a script file.

    \_(ツ)_/

    Thursday, July 2, 2015 7:23 PM
  • Functions have to come first in a script file.

    \_(ツ)_/

    It does come 1st, that was a copy paste error on my part.
    Thursday, July 2, 2015 7:25 PM
  • BUMP -- Does anyone have any ideas on what could be the culprit to this?
    Sunday, July 5, 2015 3:47 AM
  • It is not clear to me what you intend the function to return.  
    Please explain what it is supposed to return.
    Monday, July 6, 2015 12:23 AM
  • It is not clear to me what you intend the function to return.  
    Please explain what it is supposed to return.

    I would like the output window to display the output in the order that the syntax shows.  For example, my output window currently shows 

    Query1 - count

    Query2 - count

    Mkdir results

    Query3 - count

    The syntax itself is set-up to display the results as follows

    Mkdir results

    Query1 - count

    Query2 - count

    Query3 - count

    Why does the output window deviate from that?

    Monday, July 6, 2015 12:57 AM
  • Because it is written to output

    Query1 - count
    Query2 - count
    System.IO.DirectoryInfo object
    Query3 - count

    And it is written in a very confusing way.

    Why do you have code that depends on the return value of a function ?

    As written, it is equivalent to


      $null = Execute-SQLquery $Query1WBName $Query1 
      $null = Execute-SQLquery $Query2WBName $Query3  
      Execute-SQLquery $Query3WBName $Query2  
     


    instead of 

    if (Execute-SQLquery $Query1WBName $Query1)
    {
                    if (Execute-SQLquery $Query2WBName $Query3)
                    {
                                    Execute-SQLquery $Query3WBName $Query2
                    }
    }

     
    Monday, July 6, 2015 2:00 AM
  • Sorry for "bad syntax" very new to powershell and my goal (which works with the exception of the output order) is to 1st check if a directory exists.  If it does, carry on if not create it.  take query1 run query in SQL Server save in workbook1, take query2 run query in SQL Server save in workbook2, take query3 run query in SQL Server save in workbook3 while displaying the number of results returned to the window each iteration.

    The best way I could discover to do such (with trial and error) was to write an if statement like my example.  If you have a better approach I am more than acceptable to alter syntax!

    Monday, July 6, 2015 2:16 AM
  • The whole design is very convoluted and because you are trying to run before you have learned to walk.  Take more time to learn how to write a script and you will not have so much trouble

    To begin with your SQL is bogus and can never run because it is completely incorrect.  If we go through all of the code carefully it is clear that is cannot work in any way.  If you are havingbad messages it is likely due to a total logic failure.

    You can fix this by writing a little bit of code at a time and debug that piece.  Once you have basic functioning code you can set out to learn how to write callable functions.

    Here is a starter for you function:

    Function Execute-SQLquery {
        param (
            $QueryString
        )
        
        $server='Server1'
        $database='DB01'
        $connStr='Data Source={0};Integrated Security=SSPI;Initial Catalog={1};' -f $server, $database
        $conn= New-Object System.Data.SqlClient.SQLConnection
        $cmd=$conn.CreateCommand()  
        $cmd.CommandText = $QueryString    
        $SqlAdapter=New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
        $dt=New-Object System.Data.DataTable
        $SqlAdapter.Fill($dt)
        $dt
    }

    You would call it like this:

    if($dt=Execute-SQLquery  'select * from xyz'){
       # we have a table now check the folder
        ....
        $dt | Export-Csv $csvfile
        if(... execute next query.
    }

    Of course you still have to add error management.

    Use this more simple approach.


    \_(ツ)_/


    • Edited by jrv Monday, July 6, 2015 3:17 AM
    Monday, July 6, 2015 3:17 AM
  • This is the general pattern that you want to use.  If you can understand it and make it work then you can try to generalize it.

    Function Execute-SQLquery {
        param (
            $QueryString
        )
        
        $server = 'Server1'
        $database = 'DB01'
        $connStr = 'Data Source={0};Integrated Security=SSPI;Initial Catalog={1};' -f $server, $database
        $conn=New-Object System.Data.SqlClient.SQLConnection($connStr)
        $cmd=$conn.CreateCommand()
        $cmd.CommandText = $QueryString
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
        $dt = New-Object System.Data.DataTable
        if($SqlAdapter.Fill($dt) -gt 0){
            $dt
        }
        $conn.Close()
    }
    $MasterPath='C:\Templates\' + [DateTime]::Today.ToString('MM.dd.yy') #'
    New-Item $MasterPath -type directory -Force
    
    if($dt = Execute-SQLquery  'select * from xyz'){
        $path=Join-Path $MasterPath 'one'
        New-Item $path -ItemType Directory
        $csvpath=Join-Path $path one.csv
        $dt | Export-Csv $csvpath
        if ($dt = Execute-SQLquery  'other select') {
            $path = Join-Path $MasterPath 'two'
            New-Item $path -ItemType Directory
            $csvpath = Join-Path $path two.csv
            $dt | Export-Csv $csvpath
            if ($dt = Execute-SQLquery  'select * from three') {
                $path = Join-Path $MasterPath three
                New-Item $path -ItemType Directory
                $csvpath = Join-Path $path three.csv
                $dt | Export-Csv $csvpath
            }
        }
    }
        


    \_(ツ)_/




    Monday, July 6, 2015 3:41 AM
  • Why is $connStr given a value but never used afterwards?
    Monday, July 6, 2015 12:56 PM
  • It's a template showing you how to approach a solution.  I fixed the code to add the string in so recopy it.  You still have to complete the addition of error management and testing and understanding the script.


    \_(ツ)_/

    Monday, July 6, 2015 2:05 PM