rendler has asked for the wisdom of the Perl Monks concerning the following question:

In MySQL I have a column defined as: colour varchar(7) default NULL,

When doing an INSERT some variables are undefined. So I figured I would pass it though something along the lines of $var || undef,

But of course this results in '' in the database. And if I try to pass NULL through as a scalar it results in 'NULL'.

How the heck do I pass NULL through? Thanks.

Replies are listed 'Best First'.
Re: NULL and placeholders
by Abigail-II (Bishop) on Jul 26, 2002 at 13:16 UTC
    To get a NULL, you need it to pass an undefined value. If $var is undefined, $var || undef is still undefined. What is the code you are using to do the insert, and what's the code you are using to test the result?

    Abigail

[reply]
[d/l]
[select]
      This is using MHQ::DBI as the abstraction layer (which I should have mentioned in the first place, sorry). 
              dbi_exec("INSERT INTO nodes VALUES NULL,?,?,?,?,NULL,NULL,?,?,
+?,?,?,?,?,?,?)",
        $node_type,
        $node_name,
        $node_subject,
                ..............
        );

      [download]
[reply]
[d/l]
        I don't understand - in your code you've hardcoded to NULL, better to omit columns if you want to always have the values empty in your code like this: 
        INSERT INTO nodes ( column1, column3) values ( ?, ?)

        [download]
        I thought your question was about what happens when you want to execute a query and some of your variables are empty. This is what I get when working with DBI, DBD::mysql. 
        
mysql> show fields from emptycols;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | varchar(12) |      |     |         |       |
| b     | varchar(5)  | YES  |     | NULL    |       |
| c     | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

my $dbh = DBI->connect('dbi:mysql:test',$ENV{'USER'});

my $sth = $dbh->prepare("INSERT INTO emptycols VALUES (?,?,?)");

$sth->execute("apple", "red",17);
$sth->execute("apple", undef,10);
$sth->execute("orange", "ora",undef);

mysql> select * from emptycols;
+--------+------+------+
| a      | b    | c    |
+--------+------+------+
| apple  | red  |   17 |
| apple  | NULL |   10 |
| orange | ora  | NULL |
+--------+------+------+
3 rows in set (0.00 sec)

        [download]
[reply]
[d/l]
[select]
Re: NULL and placeholders
by rendler (Pilgrim) on Jul 26, 2002 at 16:09 UTC

    Okay I tried this little fix just before the statement gets executed in prepare_and_execute()

        my $i = 0;
    for (@binds) { undef $binds[$i] unless $binds[$i]; ++$i; }

    [download]

    That seems to get the desired results now.</code>

[reply]
[d/l]
      The problem with   for (@binds) { undef $binds[$i] unless $binds[$i]; ++$i; } is that you'll bind NULL in place of zero or blank. If that's what you want, then O.K. If it's not what you want, then you need to make sure that you're carrying around a blank or zero in your data structures where you really undef/NULL. Then you can omit the statement above entirely.

[reply]
[d/l]
        Yes someone else pointed me to that little problem as well on the mason-users mailing list. Someone also mentioned a little bit about the problem I am having. Unless the value is explicitly defined as undef MySQL will automatically interpret it as foo varchar(50) NOT NULL default ''. It was also said that this behaviour cannot be changed in MySQL.
[reply]
[d/l]

Back to Seekers of Perl Wisdom