How to write a select query using parameters that can be null?

How to write a select query using parameters that can be null?

Postby betawarz » Tue 23 Jul 2013 18:30

Hi all,

I'm trying to write a query like the following

Code: Select all
cmd.CommandText = "SELECT * FROM foo WHERE col = :val"


And, I'm inserting that parameter like the following

Code: Select all
cmd.Parameters.Add("val", PgSqlType.Date).Value = DBNull.Value


This example is simplified to show the exact situation, but the value is actually set using the result of a function that either returns a date or the dbnull.

But basically in this query, the syntax "WHERE col = null" is not valid. Does dotConnect convert this to "WHERE col IS null" for me? It doesn't look like it is because I'm not getting any results.

Any ideas?
betawarz
 
Posts: 10
Joined: Mon 06 Dec 2010 18:15

Re: How to write a select query using parameters that can be null?

Postby Pinturiccio » Wed 24 Jul 2013 13:17

NULL cannot be compared to any value using the comparison operators. As a workaround, you may use the following:
Code: Select all
cmd.CommandText = "SELECT * FROM foo WHERE col = :val OR (:val IS NULL AND col IS NULL)";
cmd.Parameters.Add("val", PgSqlType.Date).Value = DBNull.Value;
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for PostgreSQL