[Dbix-class] DBIC Storage::DBI::SQLite insert DATETIME set_on_create exception error ...

[Peter Rabbitson]

Kiffin Gish wrote:
> So are you saying that I need to replace my call $schema->populate(...)
> with something like var $results = $schema->populate(...)?
> 
> Doesn't seem very straight-forward to me or am I missing something?

Yes you are missing something. From the populate() POD:

Please note an important effect on your data when choosing between void
and wantarray context. Since void context goes straight to insert_bulk
in DBIx::Class::Storage::DBI this will skip any component that is
overriding insert. So if you are using something like DBIx-Class-UUIDColumns
to create primary keys for you, you will find that your PKs are empty. In
this case you will have to use the wantarray context in order to create
those values.

set_on_create => 1 *is* filled in in the create() chain, which you
explicitly bypass.

> I might also mention that DBIC Storage::DBI::MySQL works fine.
> 

Most likely your column allow NULL in mysql - this is why it appears
to work (but in fact you don't insert anything).

P.S. Also don't top post

> 
> 
> On Sat, 2009-06-20 at 00:29 +0200, Peter Rabbitson wrote:
>> Kiffin Gish wrote:
>>> Hi there.
>>>
>>> Since I upgraded DBIx::Class from 0.08103 to 0.08106, I've been having
>>> problems inserting records using SQLite, where a column is defined with
>>> data_type 'DATETIME', is_nullable => 0 and set_on_create => 1;
>>>
>>> I've defined a database with the following columns:
>>>
>>> __PACKAGE__->add_columns(
>>>     username => {
>>>         data_type      => 'VARCHAR',
>>>         size           => 25,
>>>         is_nullable    => 0,
>>>         is_foreign_key => 1,
>>>         },
>>>     group_id => {
>>>         data_type      => 'VARCHAR',
>>>         size           => 64,
>>>         is_nullable    => 0,
>>>         is_foreign_key => 1,
>>>         },
>>>     from_date => {
>>>         data_type      => 'DATETIME',
>>>         is_nullable    => 0,
>>>         timezone       => 'UTC',
>>>         set_on_create  => 1, 
>>>         },  
>>>     thru_date => {
>>>         data_type      => 'DATETIME',
>>>         is_nullable    => 1,
>>>         timezone       => 'UTC',
>>>         },
>>>     );
>>>
>>> The following call:
>>>
>>> $schema->populate(
>>>         'UserLoginSecurityGroup', [
>>>             [qw/ username group_id /],
>>>             [ 'admin', 'FULLADMIN' ],
>>>             [ 'admin', 'PARTYADMIN' ],
>>>             [ 'admin', 'ACCOUNT_OWNER' ],
>>>             ]
>>>         );
>>> INSERT INTO user_login ( email, party_id, password, username) VALUES
>>> ( ?, ?, ?, ? ): '0', '2', '1', '3'
>>> INSERT INTO user_login_security_group ( group_id, username) VALUES
>>> ( ?, ? ): '0', '1'
>>> user_login_security_group.from_date may not be NULL for populate slice:
>>> {
>>>   group_id => "FULLADMIN",
>>>   username => "admin"
>>> }
>>>  at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1010
>>>
>>> DBIx::Class::Schema::throw_exception('CMT::Store=HASH(0x88c16a0)',
>>> 'user_login_security_group.from_date may not be NULL for popul...')
>>> called at /usr/local/share/perl/5.10.0/DBIx/Class/Storage.pm line 122
>>>
>>>
>>> I noticed in the CHANGES file the following text:
>>>
>>> 0.08106 2009-06-11 21:42:00 (UTC)
>>>         - Switched SQLite storage driver to DateTime::Format::SQLite
>>>           (proper timezone handling)
>>>         - Fix more test problems
>>>
>> The correct changelog entry to look for is:
>>         - Fixed corner case when populate() erroneously falls back to
>>           create()
>>
>> Basically before create() was being called instead of populate, which
>> properly invoked the set_on_create flag. populate() in void context
>> explicitly does not do this. Either switch the context, or supply the
>> value.
>>