Home Dashboard Directory Help
Search

Provide expected Comparison handling for DBNull by Cookie.Monster


Status: 

Active


7
1
Sign in
to vote
Type: Suggestion
ID: 830412
Opened: 3/8/2014 8:21:45 AM
Access Restriction: Public
1
Workaround(s)
view

Description

Greetings,

First, my apologies. I have little development experience and thus may use improper terminology. My suggestion is to have PowerShell provide handling of System.DBNull values as one might logically expect (similar to Null)

Current situation:
if([System.DBNull]::Value){"I would not expect this to display"} #The text displays.

Expected situation:
if([System.DBNull]::Value){"I would not expect this to display"} #The text does not display

How this affects users:
When I run Invoke-SQLCMD, I cannot use helpful PowerShell shortcuts like Where-Object {$_.Something} - objects with DBNull value in 'Something' property will still return. They should not, from my perspective.


Current situation:
10 -gt [System.DBNull]::Value # Could not compare "10" to "". Error: "Cannot convert value "" to type "System.Int32". Error: "Object cannot be cast from DBNull to other types.""
[System.DBNull]::Value -gt 10 # Cannot compare "" because it is not IComparable.

Expected situation:
10 -gt [System.DBNull]::Value # From my perspective, should treat DBNull as Null, thus, True
[System.DBNull]::Value -gt 10 # From my perspective, should treat DBNull as Null, thus, False

How this affects users:
When I run Invoke-SQLCMD, I cannot use certain comparison operators like -gt or -lt. Where-Object {$_.Something -lt 1024} will spit out an error for every row where 'Something' has DBNull value

There are other scenarios where this would be beneficial, but the above two are the most painful in my limited experience. More details at this link:
http://powershell.org/wp/forums/topic/dealing-with-dbnull/
Details
Sign in to post a comment.
Posted by Stephen Mills on 6/20/2014 at 5:02 PM
I also agree on this. While it technically isn't correct as far as the SQL standard goes( in SQL NULL is considered unknown and therefore can't be compared), it is correct as far as PowerShell is concerned ( since $null -eq $null is true ). Since it is very difficult to work with DBNull in PowerShell, I usually go through and replace the DBNull values with $null. At least this way I can continue my work. Having it as a default would make a lot of sense and easier to work with. It's suprising they do this this way, but handle DataRows so poorly. Take a look at Connect ID 400549 to see what I mean, which is still a problem in PowerShell V4.

Give this a try to confuse you. By the way, the use of NULL doesn't matter. It only matters to have a property called "Item".
Invoke-Sqlcmd -ServerInstance MyServer -Query "Select NULL as [Item]"
Posted by Roman Kuzmin on 3/8/2014 at 11:17 AM
This makes sense, especially taking into account that PowerShell already treats DataRow in a special way (DataRowAdapter). So it is normal (and expected) that some database related types are recognized by PowerShell and processed "properly".
Sign in to post a workaround.
Posted by Cookie.Monster on 3/8/2014 at 8:29 AM
There are workarounds for this. None of them are acceptable for the non-developer types out there who use PowerShell. Details at http://powershell.org/wp/forums/topic/dealing-with-dbnull/ For example:

Get-XXXXServer | Where-Object{$_.VCServerContact} #This returns null server contacts
Get-XXXXServer | Where-Object{[string]$_.VCServerContact} #this does not

Get-XXXXServer | Where-Object{$_.VCNumCPU -gt 8} #This results in errors for every row where vcnumcpus is null
Get-XXXXServer | Where-Object{[string]$_.VCNumCPU -and $_.VCNumCPUs -gt 8} #This results in expected behavior of the above command

On a side note, thanks to Dave Wyatt for the suggestions and help with this : )