?
Solved

Powershell 3 questions

Posted on 2013-12-17
1
Medium Priority
?
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 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

Ransomware Attacks Keeping You Up at Night?

Will your organization be ransomware's next victim?  The good news is that these attacks are predicable and therefore preventable. Learn more about how you can  stop a ransomware attacks before encryption takes place with our Ransomware Prevention Kit!

Question has a verified solution.

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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
In this post we will be converting StringData saved within a text file into a hash table. This can be further used in a PowerShell script for replacing settings that are dynamic in nature from environment to environment.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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