Databases
StackOverflow2013
Postsdbo
POSetting NULL datetime with Rose::DB::Object and MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSetting NULL datetime with Rose::DB::Object and MySQL
    primarykey
    data
    text
    <p>I could be wrong here, but it looks like there's conflicting standards here.</p> <p>MySQL treats a stored datetime of "0000-00-00 00:00:00" as being equivalent to NULL. (update - only, it seems, if the datetime is defined as NOT NULL)</p> <p>But Rose::DB::Object uses DateTime for MySQL DATETIME fields, and trying to set a null DATETIME from "0000-00-00" throws an exception in the DateTime module. ie, I can't create a DateTime object with year 0, month 0, day 0, because this throws an exception in the DateTime module.</p> <p>I checked in Rose::DB::Object::Metadata::Column::Datetime, and can't see a way of explicitly handling a NULL DateTime when creating an entry or when retrieving.</p> <p>Am I missing something?</p> <p>ie, can Rose::DB::Object handle NULL datetime (MySQL) fields even though DateTime (Perl module) can't.</p> <p>Sample code:</p> <pre><code>#!/usr/bin/perl use strict; use warnings; use lib 'lib'; use RoseDB::dt_test; my $dt_entry = RoseDB::dt_test-&gt;new(); $dt_entry-&gt;date_time_field('0000-00-00'); $dt_entry-&gt;save; 1; __END__ # definition of table as stored in DB mysql&gt; show create table dt_test \G *************************** 1. row *************************** Table: dt_test Create Table: CREATE TABLE `dt_test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `date_time_field` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) </code></pre> <p>with the RoseDB::dt_test module being:</p> <pre><code>package RoseDB::dt_test; use strict; use warnings; # this module builds up our DB connection and initializes the connection through: # __PACKAGE__-&gt;register_db use RoseDB; use base qw(Rose::DB::Object); __PACKAGE__-&gt;meta-&gt;setup ( table =&gt; 'dt_test', columns =&gt; [ id =&gt; { type =&gt; 'int', primary_key =&gt; 1 }, date_time_field =&gt; { type =&gt; 'datetime' }, ], ); sub init_db { RoseDB-&gt;get_dbh } 1; </code></pre> <p>When I run it, I get the error "Invalid datetime: '0000-00-00' at tmp.pl line 8"</p> <p>When I change the date to "2010-01-01", it works as expected:</p> <pre><code>mysql&gt; select * from dt_test\G *************************** 1. row *************************** id: 1 date_time_field: 2010-01-01 00:00:00 </code></pre> <p>I finally managed to recreate the NULL MySQL query example!</p> <pre><code>mysql&gt; create table dt_test(dt_test_field datetime not null); Query OK, 0 rows affected (0.05 sec) mysql&gt; insert into dt_test values(null); ERROR 1048 (23000): Column 'dt_test_field' cannot be null mysql&gt; insert into dt_test values('0000-00-00'); Query OK, 1 row affected (0.00 sec) mysql&gt; select * from dt_test; +---------------------+ | dt_test_field | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec) mysql&gt; select * from dt_test where dt_test_field is null; +---------------------+ | dt_test_field | +---------------------+ | 0000-00-00 00:00:00 | +---------------------+ 1 row in set (0.00 sec) </code></pre> <p>Looks like the table definitions where the datetimes are defined with "NOT NULL" and then trying to use the MySQL "fake null" is the issue. I'm too tired to play with this now, but I'll see what happens when I change the table structure in the morning.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScliveholloway
    1. UScliveholloway
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSetting NULL datetime with Rose::DB::Object and MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSetting NULL datetime with Rose::DB::Object and MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSetting NULL datetime with Rose::DB::Object and MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSome example code would help. What is the before and after state of the database, and what Perl code are you using to try to make that change?
      singulars
      1. POSetting NULL datetime with Rose::DB::Object and MySQL
      1. USJohn Siracusa
    2. COSo, to sum up, there's two issues here - (1) MySQL is just plain weird with a datetime field set to '0000-00-00' when it is defined as NOT NULL. (2) I am still unable to set a datetime field to NULL through Rose::DB::Object
      singulars
      1. POSetting NULL datetime with Rose::DB::Object and MySQL
      1. UScliveholloway
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload