Tag: DBNull

Recently during a project I had to some DB queries using powershell. While that is straightforward and easy enough (I will create a post on querying DBs with powershell later on), the problem I ran in to was checking for an empty return.

I wouldn’t blame you for thinking “but scriptigator this should be simple right?” – because I thought the same. Query the DB, check if its null, presto manifesto yes? Well… sort of – IF you’re using Invoke-MysqlQuery this will work

$query_result = Invoke-Mysql -Query "SELECT * FROM table_name"

if($query_result -eq $null){
    Write-Host "This will work if you're using invoke-mysql"
}

else{
    Write-Host "need to try something else"
}

The problem with this approach is that empty or the $null global variable in Powershell is not exactly the same as an SQL null value. So we will have to use a workaround to get around the issue. There are a couple of ways to do this :

1. Checking the row count of a query

If the query does not match any records, how many rows would it output? Should be 0 right? Let’s use that logic here

$query_result = Invoke-MysqlQuery "SELECT * FROM table_name"
$query_row_count = ($query_result).count
if($query_row_count -eq 0){
    Write-Host "This WILL work!"
}

And yes this will work!

2. Using DBNull

If you’re using SQL reader method to query the Database you can use the system provided value of DBNull or System.DBNull that makes life a little easier. ( I had trouble with this when using Invoke-MySQL – script life can’t get too easy now can it? )

//DBNull and System.DBNull are interchangeable

if($query_result -eq [DBNull]::Value){
    Write-Host "This will work if you're using sqlreader"
}


if(([DBNull]::Value).Equals($query_result)){
    Write-Host "This will work if you're using sqlreader"
}

Conclusion time…

So there it is – all the methods I found to check for empty returns in Powershell/PowerCLI. As you know I do not claim to be an all-knowing demigod in scripting so if you find any alternative methods to these please feel free to share!

Till the next time – happy scripting!