Re^2: MySQL: placeholders and NULL values

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!

Comment on Re^2: MySQL: placeholders and NULL values Select or Download Code

Replies are listed 'Best First'.
Re^3: MySQL: placeholders and NULL values by bart (Canon) on May 02, 2006 at 14:39 UTC
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]
Re^4: MySQL: placeholders and NULL values by jeanluca (Deacon) on May 02, 2006 at 14:52 UTC
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]
Re^3: MySQL: placeholders and NULL values by japhy (Canon) on May 02, 2006 at 14:53 UTC
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^4: MySQL: placeholders and NULL values by cwry (Monk) on May 03, 2006 at 00:37 UTC
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]
Re^5: MySQL: placeholders and NULL values by japhy (Canon) on May 03, 2006 at 05:12 UTC
The OP had this: `$cols[0] = undef ; # gives default value of that column` [download] which I have shown to be untrue. Doing that WILL set it to NULL. 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]