Stuart Dryden
asked on
PowerShell Compare-Object misbehaving or objects not compatible for comparison?
I am trying to compare the names of noteproperty items in two different variables created by different means
The first is a list of columns in an SQL Server database, the second comes from ConvertFrom-Json
I have tried to make the objects compatible for comparison with each other by using several different techniques but every method i try fails.
Below is the best working example of code i have been able to come up with but the results it gives are not correct.
In the below example, just before the compare-object command the variables equal:
yet the result coming from the following Compare-Object command is:
As you can see timestamp exsits on both sides of the compare yet the result does not agree. event_id exsits on one side without the other yet is not listed. event is correct as it appears on both.
Can anyone explain what might be happening and how to get the required result?
My ultimate goal is to check a the database for columns that will require adding before a object derived from ConvertFrom-Json is added to a row. Every noteproperty obviously has to have a place in the database and so to eliminate errors i need to create those columns in advance. This part of the script is meant to compare what is needed and what is there already and therefore creating a list of what we don't have - this will get passed to an SQL ALTER table command to add the columns.
Extra information follows:
The first is a list of columns in an SQL Server database, the second comes from ConvertFrom-Json
I have tried to make the objects compatible for comparison with each other by using several different techniques but every method i try fails.
Below is the best working example of code i have been able to come up with but the results it gives are not correct.
In the below example, just before the compare-object command the variables equal:
C:\Users\Stuart\Desktop\Amy-EVA3 [master ≡ +0 ~1 -0 !]> $JournalObjectNotePropertyList
Name
----
event
FuelLevel
FuelUsed
JumpDist
StarPos
StarSystem
SystemAllegiance
SystemEconomy
SystemEconomy_Localised
SystemGovernment
SystemGovernment_Localised
SystemSecurity
SystemSecurity_Localised
timestamp
C:\Users\Stuart\Desktop\Amy-EVA3 [master ≡ +0 ~1 -0 !]> $AmyEventNotePPropertyList
Name
----
event
event_id
timestamp
yet the result coming from the following Compare-Object command is:
C:\Users\Stuart\Desktop\Amy-EVA3 [master ≡ +0 ~1 -0 !]> Compare-Object -ReferenceObject $JournalObjectNotePropertyList -DifferenceObject $AmyEventSQLColumns -PassThru -IncludeEqual
Name SideIndicator
---- -------------
event ==
FuelLevel <=
FuelUsed <=
JumpDist <=
StarPos <=
StarSystem <=
SystemAllegiance <=
SystemEconomy <=
SystemEconomy_Localised <=
SystemGovernment <=
SystemGovernment_Localised <=
SystemSecurity <=
SystemSecurity_Localised <=
timestamp <=
As you can see timestamp exsits on both sides of the compare yet the result does not agree. event_id exsits on one side without the other yet is not listed. event is correct as it appears on both.
Can anyone explain what might be happening and how to get the required result?
if ($SQLCredentials -eq $null)
{
$SQLCredentials = Get-Credential
}
$SQLPassword = $SQLCredentials.GetNetworkCredential().password
$Debug = 1
$SQLServer = 'sql.database.windows.net'
$SQLdbName = 'mydb'
$SQLEventTableName = 'event'
$SQLConnectionString = 'Data Source=' + $SQLServer + ';Initial Catalog=' + $SQLdbName + ';Integrated Security=False;User ID=' + $SQLCredentials.UserName + ';Password=' + $SQLPassword
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection ($SQLConnectionString)
$SQLConnection.Open()
if ($SQLConnection.State -eq [System.Data.ConnectionState]::Open)
{
if ($Debug -eq 1)
{
Write-Host -ForegroundColor Green 'Database connection successful !!!'
}
}
$SQLCommandQuery = "SELECT COLUMN_NAME FROM " + $SQLdbName + ".INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + $SQLEventTableName + "'"
if ($SQLCommandQuery)
{
$SQLCommand = New-Object system.Data.sqlclient.SqlCommand
$AmyEventSQLColumns = New-Object psobject
$SQLCommand.connection = $SQLConnection
$SQLCommand.commandtext = $SQLCommandQuery
$SQLResponse = $SQLCommand.ExecuteReader()
while ($SQLResponse.Read())
{
Add-Member -InputObject $AmyEventSQLColumns -MemberType NoteProperty -Name ($SQLResponse.GetValue($1)) -value "" > $null
}
}
$JSONContents = gc .\SmallJournal.json
$ErrorActionPreferenceBefore = $ErrorActionPreference
$ErrorActionPreference = "SilentlyContinue"
$JSONContents = $JSONContents.Replace(':true,', ':"1",')
$JSONContents = $JSONContents.Replace(':false,', ':"0",')
$ErrorActionPreference = $ErrorActionPreferenceBefore
$JournalObjectNotePropertyList = ($JSONContents | ConvertFrom-Json | gm -MemberType NoteProperty | Select-Object Name)
$AmyEventNotePPropertyList = $AmyEventSQLColumns | gm -MemberType NoteProperty | Select-Object Name
Compare-Object -ReferenceObject $JournalObjectNotePropertyList -DifferenceObject $AmyEventSQLColumns -PassThru -IncludeEqual
My ultimate goal is to check a the database for columns that will require adding before a object derived from ConvertFrom-Json is added to a row. Every noteproperty obviously has to have a place in the database and so to eliminate errors i need to create those columns in advance. This part of the script is meant to compare what is needed and what is there already and therefore creating a list of what we don't have - this will get passed to an SQL ALTER table command to add the columns.
Extra information follows:
C:\Users\Stuart\Desktop\Amy-EVA3 [master ≡ +0 ~1 -0 !]> $AmyEventNotePPropertyList |gm
TypeName: Selected.Microsoft.PowerShell.Commands.MemberDefinition
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Name NoteProperty string Name=event
C:\Users\Stuart\Desktop\Amy-EVA3 [master ≡ +0 ~1 -0 !]> $JournalObjectNotePropertyList |gm
TypeName: Selected.Microsoft.PowerShell.Commands.MemberDefinition
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Name NoteProperty string Name=event
SideIndicator NoteProperty string SideIndicator===
Unless you are comparing arrays of simple objects (like strings), you need to specify which property (or properties) you want to compare with the Compare-Object -Property parameter. So in this case it looks like you would specify -Property Name.
ASKER
Hi footecch,
Thanks for your help time and time again. I tried the -Property Name option before but as the result was even worse than what i wrote above i moved past it.
results as follows:
Thanks for your help time and time again. I tried the -Property Name option before but as the result was even worse than what i wrote above i moved past it.
results as follows:
PS C:\Users\Stuart\Desktop\Amy-EVA3> Compare-Object -Property Name -ReferenceObject $JournalObjectNotePropertyList -DifferenceObject $AmyEventSQLColumns -PassThru -IncludeEqual
event_id timestamp event SideIndicator
-------- --------- ----- -------------
=>
<=
<=
<=
<=
<=
<=
<=
<=
<=
<=
<=
<=
<=
<=
Shouldn't your compare-object command have $AmyEventNotePPropertyList as the DifferenceObject?
ASKER
i thought it would be bi-directional and so i have tried it both ways with every different technique i tried.
switched around it gives better result but still not correct, there is some kind of blank in there and incorrect results for timestamp and event.
switched around it gives better result but still not correct, there is some kind of blank in there and incorrect results for timestamp and event.
PS C:\Users\Stuart\Desktop\Amy-EVA3> Compare-Object -Property Name -ReferenceObject $AmyEventSQLColumns -DifferenceObject $JournalObjectNotePropertyList -PassThru -IncludeEqual
Name SideIndicator
---- -------------
event =>
FuelLevel =>
FuelUsed =>
JumpDist =>
StarPos =>
StarSystem =>
SystemAllegiance =>
SystemEconomy =>
SystemEconomy_Localised =>
SystemGovernment =>
SystemGovernment_Localised =>
SystemSecurity =>
SystemSecurity_Localised =>
timestamp =>
<=
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks VERY much for doing that. I will break down the differences and work out what is going wrong.
much appreciated !!
much appreciated !!
ASKER
proof that concept should work - had to re-write my code which solved the problem