1

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

Cœur
  • 37,241
  • 25
  • 195
  • 267
Pavel Galich
  • 45
  • 7
  • 3
    Please do not add unrelated tags. Powershell is not even close to [tag:c#] – Camilo Terevinto Oct 25 '17 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 Oct 25 '17 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 Oct 25 '17 at 18:32

3 Answers3

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.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • for value 0x0000000005378C51 it returns 0 0 0 0 5 55 140 81, and I can't understand this =( – Pavel Galich Oct 25 '17 at 18:49
  • @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`. – Bacon Bits Oct 25 '17 at 19:07
  • This is the answer. Thank you – Pavel Galich Oct 26 '17 at 08:35
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

Esperento57
  • 16,521
  • 3
  • 39
  • 45
0

try this

$byteArray.Add([System.Text.Encoding]::ASCII.GetString($row.Item("RowVersion")))
Esperento57
  • 16,521
  • 3
  • 39
  • 45