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

Dear Monks

I've used the prepare statement (with placeholders) so I can call the execute sub now like
   $sth->execute(@cols) ;

[download]
In which @cols is the array with a value for each placeholder.
However, I think I noticed a problem. What if I would like to set a column to 'NULL' (not a string!!!).
I've tried a couple of possibilities, like 
   $cols[0] = undef ;  # gives default value of that column 
   $cols[1] = 'NULL' ; # interpreted as string...

[download]
....
How should I solve this ? did I miss something obvious ?

Thanks a lot
Luca

Replies are listed 'Best First'.
Re: MySQL: placeholders and NULL values
by japhy (Canon) on May 02, 2006 at 13:24 UTC
    The only time a default value is assigned to a column is when you do not include it in the INSERT statement, like so: 
    CREATE TABLE t1 (
  int x default 100,
  int y
);

INSERT INTO t1 (y) VALUES (1);

SELECT * FROM t1;

    [download]
    You get (100, 1) back. If you did INSERT INTO t1 VALUES (NULL, 1), you'll get NULL for x's value.
    Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
    How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart
[reply]
[d/l]
[select]
Re: MySQL: placeholders and NULL values
by davidrw (Prior) on May 02, 2006 at 13:24 UTC
    undef as a placeholder value is interpreted as NULL. what database? (update: it says MySQL in the title) what's the table schema? any triggers?
[reply]
[d/l]
      here is a simple example:
      mysql> show create table t1 ;
+-------+--------------------------------------------+
| Table | Create Table                                                
+                                                                 |
+-------+--------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(100) default 'TEST TABLE',
  `b` int(11) default '10'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------+

      [download]
      This is my test script ph.pl:
      #! /usr/bin/perl

use DBI;
use strict ;

my $dsn = "DBI:mysql:database=test;host=localhost";
my $dbh = DBI->connect($dsn, "root", "");

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

my @a = (undef,undef ) ;
$sth->execute(@a) ;

$dbh->disconnect() ;

      [download]
      Running this script gives:
      mysql> select * from t1 ;
+------+------+
| a    | b    |
+------+------+
|      | NULL |
+------+------+
1 row in set (0.00 sec)

      [download]
      So there is no NULL for column a!
[reply]
[d/l]
[select]
        I don't see any mention whether your columns can or can't be NULL. It looks to me like column b can be NULL, where column a can't. So, Mysql converts the NULL on input to the closest it can get: an empty string for VARCHAR — and it would use 0 for a number.
[reply]
        Did you miss my answer? The DEFAULT value only comes into play when you do an INSERT without naming that column.

        Update: the MySQL docs say you can also do INSERT INTO t1 VALUES (DEFAULT, 20) to force the default value for a particular column. I'm not sure how you do that via DBI.

        Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
        How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart
[reply]
[d/l]
Re: MySQL: placeholders and NULL values
by jeanluca (Deacon) on May 02, 2006 at 15:38 UTC
    I think now my problem is solved: 
    mysql> select * from t1 ;
+------+------+
| a    | b    |
+------+------+
|      | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t1 where a is null ;
+------+------+
| a    | b    |
+------+------+
|      | NULL |
+------+------+
1 row in set (0.00 sec)

    [download]
    So, the value of column 'a' is not an empty string, but its 'NULL'!
    Its very hard to see the difference :)
[reply]
[d/l]

Back to Seekers of Perl Wisdom