CODEWITHSUNDEEP
perldbi
SS Sid
SS Sid

Reputation: 459

Why selectrow_array does not work with null values in where clause

I am trying to fetch the count from SQL server database and it gives 0 for fields with null values. Below is what I am using.

 my $sql = q{SELECT count(*) from customer where first_name = ? and last_name = ?};
my @bind_values = ($first_name, $last_name);
my $count = $dbh->selectrow_array($sql, undef, @bind_values);

This returns 0 if either value is null in the database. I know prepare automatically makes it is null if the passed parameter is undef, but I don't know why it's not working.

So here is weird observation. When I type the SQL with values in Toda for SQL server, it works :

SELECT count(*) from customer where first_name = 'bob' and last_name is null

but when I try the same query and pass values in the parameter for the first_name = bob and the last_name {null} . it does not work.

SELECT count(*) from customer where first_name = ? and last_name = ?

Upvotes: 0

Views: 413

Answers (2)

Andreas König
Andreas König

Reputation: 31

For NULL in the WHERE clause you simply need a different query. I write them below each other, so you can spot the difference:

...("select * from test where col2 = ?", undef, 1);
...("select * from test where col2 is ?", undef, undef);
...("select * from test where col2 is ?", undef, 1);
...("select * from test where col2 = ?", undef, undef);

The first two commands work, stick to those. The third is a syntax error, the fourth is what you tried and which indeed does not return anything.

The DBI manpage has a section of NULL values that talks about this case a bit more.

Upvotes: 3

SS Sid
SS Sid

Reputation: 459

So, here it is what I did. I added or field is null statement with each field if the value is undef.

my $sql = q{SELECT count(*) from customer where (first_name = ? or (first_name is null and ? = 1))  and (last_name = ? or (last_name is null and ? = 1))};
my @bind_values = ($first_name, defined($first_name)?0:1, $last_name, defined($last_name)?0:1);
my $count = $dbh->selectrow_array($sql, undef, @bind_values);

If anyone has better solution please post it.

Upvotes: 0

Related Questions