CODEWITHSUNDEEP
powershell
user3140622
user3140622

Reputation: 37

Global variable loses value outside loop

I'm working on a script to extract data from BLOBs in a SQL database. The extraction process works great. I want to add some sort of progress indication to the script. I have a total record count from a SQL query, and an incremental counter that increases for each file exported. The incremental counter works, but the total record count - which I attempted to assign to a global variable - does not seem to hold its value. Am I declaring it incorrectly? 

## Export of "larger" Sql Server Blob to file            
## with GetBytes-Stream.         
# Configuration data     
$StartTime = Get-Date
$Server = "server";
$UserID = "user";
$Password = "password";
$Database = "db";
$Dest = "C:\Users\me\Desktop\Test\";
$bufferSize = 8192; 

# Counts total rows
$CountSql = "SELECT Count(extension) as countall from 
                (
                    SELECT  p.[people_id], right(pi.[file_name],4) as extension
                    FROM dbo.pictures as pi 
                    INNER JOIN dbo.people AS p ON p.person_picture = pi.pictures_id
                    where left([image_type], 5) = 'image'
                ) as countall"

# Selects Data
$Sql = "SELECT p.[people_id], pi.[image_file], right(pi.[file_name],4), ROW_NUMBER() OVER (ORDER BY people_id) as count
        FROM dbo.pictures as pi 
        INNER JOIN dbo.people AS p ON p.person_picture = pi.pictures_id
        where left([image_type], 5) = 'image'";    

# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=False;" + 
                        "User ID=$UserID;" +
                        "Password=$Password;" +           
                        "Initial Catalog=$Database";            
$con.Open();            

# New Command and Reader for total row count
$CountCmd = New-Object Data.SqlClient.SqlCommand $CountSql, $con;
$crd = $CountCmd.ExecuteReader();
While ($crd.Read())
{
    $crd.GetValue($global:1)
}
$crd.Close();

# New Command and Reader for rest of data     
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;            
$rd = $cmd.ExecuteReader();            

# Create a byte array for the stream.            
$out = [array]::CreateInstance('Byte', $bufferSize)            

# Looping through records
While ($rd.Read())            
{            
    $total = $global:1
    $counter = ($rd.GetValue(3));
    Write-Output ("Exporting $counter of $total`: {0}" -f $rd.GetGUID(0));

    # New BinaryWriter            
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetGUID(0) + $rd.GetString(2)), Create, Write;            
    $bw = New-Object System.IO.BinaryWriter $fs;            

    $start = 0;            
    # Read first byte stream            
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    While ($received -gt 0)            
    {            
       $bw.Write($out, 0, $received);            
       $bw.Flush();            
       $start += $received;            
       # Read next byte stream            
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);            
    }            

    $bw.Close();            
    $fs.Close();            
}            

# Closing & Disposing all objects            
$fs.Dispose();            
$rd.Close();            
$cmd.Dispose();            
$con.Close();            
$EndTime = Get-Date
$TotalTime = $EndTime - $StartTime    
Write-Host ("Finished in {0:g}" -f $TotalTime)

OUTPUT

PS C:\Users\me> C:\Scripts\ExportImagesFromNTST.ps1
21380
Exporting 1 of : 3089b464-e667-4bf4-80b3-0002d582d4fa
Exporting 2 of : 04cf7738-ae19-4771-92b8-0003c5f27947
Exporting 3 of : 94485b5d-fe71-438d-a097-000ad185c915

and so on. 21380 should be $1 which should also be $total.

Upvotes: 1

Views: 618

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36297

I think PetSerAl hit the nail on the head here. You create a SqlCommand object ($CountCmd), and from that create a SqlDataReader ($crd), and then tell $crd to use the GetValue() method that accepts an integer as a parameter, so that it knows which column to return the value of, but you reference a global variable with a name of '1', which is never defined, so you effectively pass $null to that method, so it doesn't get any value. I'm honestly surprised that it doesn't throw errors at you right there. You would probably want to just pass the integer 1 as the argument for that method, and assign it to $Total in the While loop. I'm honestly guessing here, but from what I see I think it should be:

$crd = $CountCmd.ExecuteReader();
While ($crd.Read())
{
    $Total = $crd.GetValue(0)
}
$crd.Close();

I'm pretty sure that will assign the value of the first column (which for that sql command should just be 1 row with 1 column, right? Just the total count?), anyway, assign the first column's value for the current row to $Total. Then later you can reference $Total just fine to update your progress.

You sir, need to look into the write-progress cmdlet if you want to track progress, it's perfect for your script.

Upvotes: 1

Related Questions