Provide expected Comparison handling for DBNull - by Cookie.Monster

Status : 

 


10
1
Sign in
to vote
ID 830412 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 3/8/2014 8:21:45 AM
Access Restriction Public

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/
Sign in to post a comment.
Posted by jyao on 12/16/2014 at 4:08 PM
If MS can solve all the open connect items, MS products will be as solid as rocks. I vote for this one as well.
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".