[Dbix-class] InflateColumn DateTime tricks with MSSQL/Microsoft_SQL_Server

Marc Mims marc at questright.com
Tue Jan 15 17:01:49 GMT 2008


* Christopher H. Laco <claco at chrislaco.com> [080115 08:40]:
> Michael Higgins wrote:
>> Folks --
>>
>> What I thought would be trivial turns to be burdensome. Probably, it's
>> just me, but:
>>
>> I've done my best to understand what
>> DBIx-Class/lib/DBIx/Class/InflateColumn/DateTime.pm does, yet I can't
>> see how it honors the reported storage engine.
>>
>> But, if it does, I've tried setting the storage engine to ::DBI::MSSQL
>> with no joy.
>>
>> There is a new module, ::DBI::ODBC::Microsoft_SQL_Server.pm which
>> provides, if I understand it, some overloaded methods to accommodate.
>>
>> And, examining ::DBI::MSSQL, I see this:
>>
>> sub build_datetime_parser {
>>   my $self = shift;
>>   my $type = "DateTime::Format::Strptime";
>>   eval "use ${type}";
>>   $self->throw_exception("Couldn't load ${type}: $@") if $@;
>>   return $type->new( pattern => '%m/%d/%Y %H:%M:%S' );
>> }
>>
>> And in InflateColumn::DateTime:
>>
>> "This module figures out the type
>> of DateTime::Format::* class to inflate/deflate with based on the type
>> of DBIx::Class::Storage::DBI::*"
>>
>> So what am I missing? Why isn't my storage already determined when I
>> start my script? Why doesn't setting it explicitly to this module, with
>> this subroutine produce a proper parser..??
>>
>> Is it possible that this ::DBI::MSSQL sub is honored, but not properly
>> coded?        * %N
>>            Nanoseconds. For other sub-second values use "%[number]N".
>>
>> The error I was getting is: Invalid date format: 2008-01-14 00:00:00.000
>>
>> So, maybe this should be: return $type->new( pattern => '%m/%d/%Y
>> %H:%M:%S.%N' ); ???
>>
>> I've added the sub as above, with the nanosecond string,
>> to ::DBI::ODBC::Microsoft_SQL_Server.pm and set it explicitly in my
>> script.
>>
>> My classes look like => {accessor=>'order_date',data_type =>
>> 'datetime'},
>>
>> If I set the storage type in my script, then call ->ensure_connected,
>> it deletes the key I set:
>>
>> $trex_schema->storage_type('::DBI::ODBC::Microsoft_SQL_Server');
>> # $trex_schema->storage->ensure_connected();
>> print $trex_schema->storage_type;                       # overwritten to 
>> ::DBI using line above
>>
>> At any rate, nothing breaks in getting the records, the selects are
>> still working (for whatever reason) but now the call to ->mdy('/')
>> fails:
>>
>> Can't call method "mdy" on an undefined value 
>> and a call without ->mdy returns undef.
>>
>> Can anyone help figure this out? It seems like it should work as
>> advertised without my intervening, but didn't. I've done my best to try
>> to root out the problem, and failed.
>>
>> Any help appreciated. 
>
> I'm late to the party, but I use this component a lot.
> First, is DateTime::Format::MySQL actually installed? (did this thread 
> start about MySQL?)
>
>
> Second, InflateColumn::DateTime inflates db column values into DateTime 
> objects. Keep this in mind though, that when you set values using 
> accessors:
>
>     $obj->dtfield(DateTime->new)
>
> When setting as an object, no need to worry. It should DoTheRightThing...
>
>     $obj->dtfield($string)
>
> When setting as a string, you must supply the string in the same format as 
> the database you are using, and MySQL, or really SQLite will take just 
> about any thing because it's silly, but the MySQL formatter may not 
> understand it.
>
> Now, first things second: show us the source of your schema classes, where 
> you load this component and declate fields as a datetime type.

I think Michael is using DBIx::Class::Storage::ODBC::Microsoft_SQL_Server,
which doesn't provide build_datetime_parser---yet.  I need to add that.
Actually, DBI::MSSQL and DBI::ODBC::Microsoft_SQL_Server need to be
unified, but I hesitate to do that without assistance from someone
actually using DBI::MSSQL so we can ensure the unified code works on
both the DBD::ODBC and DBD::Sybase layers.

Micheal, make a local copy of Microsoft_SQL_Server and copy the
build_date_time_parser method from DBI::MSSQL into it.  If that works as
expected, then I'll add it Microsoft_SQL_Server and commit it.

However, inflation/deflation may not be the whole problem, here.  If I
read your original message correctly, you were trying to use a DateTime
object in a search.  There is no inflate/deflate done, there.  It's just
stringification.

If you have a valid DateTime object at that point, then $dt->mdy('/')
should do what you expect.

	-Marc



More information about the DBIx-Class mailing list