CODEWITHSUNDEEP
mysqlperldbi
D2J2
D2J2

Reputation: 83

Perl DBI where IS NULL with undefined variable

I have a perl DBI mysql query that looks like this:

my $rows = get_rows(
    "select id from table where column1=?, column2=? and column3=?",
    $var1,$var2,$var3
    );

sub get_rows {

    my $sql = shift;
    my @vars = @_;      
    my $sth = $dbh->prepare($sql);
    $sth->execute(@vars);
    my $rows = $sth->fetchall_arrayref();
    $sth->finish();         
    return $rows;
    }

I'm running this to check if a particular row exists containing those vars. The problem I have however is with dealing with NULL values. These have to be selected as IS NULL rather than =?, which very often then misses rows that contain NULL values. For example, if vars 1, 2 and 3 contain '12', '23' and undef and table contains 12, 23 and NULL then the query returns no results. Is there a simple way to transform undef values into IS NULL values with DBI?

Upvotes: 0

Views: 1567

Answers (1)

choroba
choroba

Reputation: 241758

This is documented in DBI under NULL Values.

$sql_clause = defined $age? "age = ?" : "age IS NULL";
$sth = $dbh->prepare(qq{
  SELECT fullname FROM people WHERE $sql_clause
});
$sth->execute(defined $age ? $age : ());

Upvotes: 2

Related Questions