How to find the maximum in array of binary(8) using powershell?

Question

I'm trying to find the max by using

function ExecuteSqlQuery ($SQLQuery) {
try 
{
$Datatable = New-Object System.Data.DataTable    
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = $connStr
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = $SQLQuery
$Command.CommandTimeout=$commandTimeout
$Reader = $Command.ExecuteReader()
$Datatable.Load($Reader)
return $Datatable
}

$data= ExecuteSqlQuery "Select col1,col2,col3, RowVersion from table"
$byteArray = [System.Collections.ArrayList]@()
foreach ($row in $data) {

   $byteArray.Add($row.Item("RowVersion"))
}

$max=($byteArray | Measure-Object -Maximum).Maximum 
Write-host $max

But I get an error:

ERROR: Error: Cannot compare "System.Byte[]" because it is not IComparable

Then I tried to converting to int64 and finding the max

$byteArray = [System.Collections.ArrayList]@()
foreach ($row in $data) {

   $byteArray.Add([bitconverter]::ToInt64($row.Item("RowVersion"),0))
}
Write-Host ($byteArray | Measure-Object -Maximum).Maximum 
$max=[Convert]::ToByte(($byteArray | Measure-Object -Maximum).Maximum)   
Write-host $max

But still has an error, output:

9.1298706847202E+18
2017-10-25 21:00:51 ERROR: Error: Value was either too large or too small for >an Int32..

Are there any methods to solve this?
Converting a binary to an int32 retuns 0

Please do not add unrelated tags. Powershell is not even close to c# — Camilo Terevinto
– Camilo Terevinto, Commented Oct 25, 2017 at 18:05
Your examples are incomplete ( $data is not defined anywhere), you use $byteArray and $dateArray interchangably it seems, and there's no indication of what/where the error is thrown. Can you at the very least post the entire error message? — Mathias R. Jessen
– Mathias R. Jessen, Commented Oct 25, 2017 at 18:19
@MathiasR.Jessen , thax for attention, I've edited post. Error throws in first example $max=($byteArray | Measure-Object -Maximum).Maximum, second $max=[Convert]::ToByte(($byteArray | Measure-Object -Maximum).Maximum) — Pavel Galich
– Pavel Galich, Commented Oct 25, 2017 at 18:32

Bacon Bits · Accepted Answer · 2017-10-27 14:27:39Z

1

binary(8) from SQL should be represented as a [byte[]] in PowerShell, so you have an ArrayList of byte arrays. There's no built-in way to compare a byte array to other byte arrays in the .Net Framework for sorting. That behavior is not defined, so you'll have to define it or otherwise define an algorithm.

Assuming the first byte is the most significant, the second is the next most significant, and so on, you should be able to get the "maximum" it by doing something like this:

$byteArray | Sort-Object -Property {$_[0]},{$_[1]},{$_[2]},{$_[3]},{$_[4]},{$_[5]},{$_[6]},{$_[7]} -Descending | Select-Object -First 1

This only looks at the first 8 bytes, but, if the base type was really a binary(8), there's only 8 bytes in the field.

If that doesn't work, then you'll have to iterate through the ArrayList and find the maximum yourself by comparing bytes and saving the maximum.

edited Oct 27, 2017 at 14:27

answered Oct 25, 2017 at 18:31

Bacon Bits

32.5k6 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pavel Galich Over a year ago

for value 0x0000000005378C51 it returns 0 0 0 0 5 55 140 81, and I can't understand this =(

Bacon Bits Over a year ago

@PavelGalich 0x51 = 81. PowerShell displays byte arrays as decimal values. If you want them as hexadecimal values, you'll need to convert them. Something like '0x' + ($Max | ForEach-Object { $_.ToString('X2') }) -join [String]::Empty.

Pavel Galich Over a year ago

This is the answer. Thank you

Esperento57 · Accepted Answer · 2017-10-25 18:43:18Z

0

If you want only the max, you can use sql for this :

Select col1,col2,col3, RowVersion from table order by RowVersion desc

Then your first row contain the max

answered Oct 25, 2017 at 18:43

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

2 Comments

Pavel Galich Over a year ago

In sql it is very simple to find max value from RowVersion, but I must do it from powershell.

Esperento57 Over a year ago

Why use PowerShell in you case? My Second solution doesnt work?

Esperento57 · Accepted Answer · 2017-10-25 19:00:40Z

0

try this

$byteArray.Add([System.Text.Encoding]::ASCII.GetString($row.Item("RowVersion")))

answered Oct 25, 2017 at 19:00

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

Collectives™ on Stack Overflow

How to find the maximum in array of binary(8) using powershell?

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related