in reply to MySQL: placeholders and NULL values

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?

Replies are listed 'Best First'.
Re^2: MySQL: placeholders and NULL values
by jeanluca (Deacon) on May 02, 2006 at 13:52 UTC
    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]
        here is some additional info:
        mysql> desc t1 ;
+-------+--------------+------+-----+------------+-------+
| Field | Type         | Null | Key | Default    | Extra |
+-------+--------------+------+-----+------------+-------+
| a     | varchar(100) | YES  |     | TEST TABLE |       |
| b     | int(11)      | YES  |     | 10         |       |
+-------+--------------+------+-----+------------+-------+

        [download]
        so they can be NULL!
[reply]
[d/l]
      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]
        Did you miss the question? It asks how to set a column to NULL with placeholders, not set it to the default. NULL may or may not be the default for a column.
[reply]

In Section Seekers of Perl Wisdom