MySQL: placeholders and NULL values
Dear Monks
I've used the prepare statement (with placeholders) so I can call the execute sub now like
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
How should I solve this ? did I miss something obvious ?
Thanks a lot
Luca
I've used the prepare statement (with placeholders) so I can call the execute sub now like
$sth->execute(@cols) ;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.......
How should I solve this ? did I miss something obvious ?
Thanks a lot
Luca
Re: MySQL: placeholders and NULL values
created: 2006-05-02 09:24:06
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;You get (100, 1) back. If you did INSERT INTO t1 VALUES (NULL, 1), you'll get NULL for x's value.
Re: MySQL: placeholders and NULL values
created: 2006-05-02 09:24:21
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?
Re^2: MySQL: placeholders and NULL values
created: 2006-05-02 09:52:31
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 | +-------+-------------------------------------------+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() ;
Running this script gives:mysql> select * from t1 ; +------+------+ | a | b | +------+------+ | | NULL | +------+------+ 1 row in set (0.00 sec)So there is no NULL for column a!
Re^3: MySQL: placeholders and NULL values
created: 2006-05-02 10:39:05
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.
Re^4: MySQL: placeholders and NULL values
created: 2006-05-02 10:52:35
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 | | +-------+--------------+------+-----+------------+-------+so they can be NULL!
Re^3: MySQL: placeholders and NULL values
created: 2006-05-02 10:53:44
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.
Re^4: MySQL: placeholders and NULL values
created: 2006-05-02 20:37:35
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.
Re^5: MySQL: placeholders and NULL values
created: 2006-05-03 01:12:19
The OP had this:
$cols[0] = undef ; # gives default value of that columnwhich I have shown to be untrue. Doing that WILL set it to NULL.
Re: MySQL: placeholders and NULL values
created: 2006-05-02 11:38:26
I think now my problem is solved:
Its very hard to see the difference :)
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)So, the value of column 'a' is not an empty string, but its 'NULL'!
Its very hard to see the difference :)