Solved

Powershell 3 questions

Posted on 2013-12-17
1
287 Views
Last Modified: 2013-12-17
Here's a short sample to show the issue I'm experiencing with PS 4.0
I don't understand the logic of what is happening.
My background is BASIC.

I run a SQL query and then expect to be able to change the data on the current record being processed, which I take for granted is assigned to $CSVRecord by the FOREACH statement. However, it seems that the results are illogical, so please explain what is happening here.
My goal is to process a bunch of database records, change some of the fields in each record, duplicate some of the records by appending them to the end of the $CSV, and then finally exporting.
I have a 500 line program written and everything runs without errors, but the results of appended records are not what is expected.
I can do the same thing on the command line and it seems to work as I expected it to and I verify by using the index of $CSV to display the entire record.
For example, the record appended in the program is just a copy of the original record in $CSV, instead of being a modified $CSVRecord, but on the command line I can then modify a field manually (as done in the program) and append it correctly. I can then use something like $CSV[$CSV.count-1] to view the last appended record and it is correct.

What is going on?




# requires powershell 3+
clear-host
cls
$Query = "SELECT 'one', 'two', 'three' "

$CSV = Invoke-Sqlcmd -Query $Query
$CSV = @($CSV) + @($CSV)


write-host Show two idnetical records
$CSV
pause
cls
[int]$i=0
write-host Why does this NOT show 2 unique records???

foreach ($CSVRecord in $CSV)
{
$i++
#loop through each record in $CSV and modify the 2nd column to be unique
$CSVRecord.Column2 = "RECORD #"+$i
}

$CSV
pause
cls
write-host NOW WHAT is happening???
$CSVRecord.Column3 = "BUTTER"
$CSV=$CSV+$CSVRecord
$CSV
0
Comment
Question by:johnj_01201
1 Comment
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39724454
The issue is that the SQL query's results are NOT duplicated. You are only copying the "container" for those columns, and the container points to individual column objects - those pointers are copied, instead of the content.
You always need to be very careful when duplicating objets in PowerShell. Only simple types (strings, ints, aso) are copied, everything else remains a pointer to the same object element.

Further, $CSVRecord at the very end points to the last $CSV row, and again you are just copying a pointer to the last row.

In general, if you want to create valid copies, use New-Object, or the Clone() method if existing.
However, both are not correct with the result of Invoke-SqlCmd, which is a [System.Data.DataRow] array. It is much more complex, see http://msdn.microsoft.com/en-us/library/system.data.datarow(v=vs.110).aspx for background info.

What you want to do has to be done differently. You cannot add rows to a query result of Invoke-SqlCmd. You'll have to use DataReader and stuff to get managable objects, like the table definition, and then be able to insert rows.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question