Solved

Powershell 3 questions

Posted on 2013-12-17
1
283 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 68

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Why would I want to create a function for tracking messages? I am glad you asked. As with most monotonous/routine tasks, human error tends to creep in after doing the same task over and over again. By creating a function, you load the function onceā€¦
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now