Dbic and inflating Oracle DATE columns

Ea has asked for the wisdom of the Perl Monks concerning the following question:

Newbie trying to get DBIx::Class to work with Oracle DATE columns. Managed to get my error to go away, so I'm putting this out there for expert comment and for those newbies searching on the same problem.

I used dbicdump with components=["InflateColumn::DateTime"] on an Oracle schema which produced a Result class which has

__PACKAGE__->load_components("InflateColumn::DateTime");
...
__PACKAGE__->add_columns(
  "start_time",
  {
    data_type   => "datetime",
    is_nullable => 1,
    original    => { data_type => "date" },
  },
);
[download]

but after searching with dbic and getting a resultset

$resultset = $schema->resultset('Ical')
                    ->search({ uid => $id });
[download]

calling $resultset->start_time would give me DBIx::Class::InflateColumn::DateTime::catch {...} (): Error while inflating '24-APR-18' for start_time on Timetable::Schema::Result::Ical: Invalid date format: 24-APR-18. There are lots of hints in the documentation, but nothing explicit on how to avoid this error.

I found that the error went away when I added the on_connect_call option to the connect method.

my $schema = Timetable::Schema->connect("dbi:Oracle:$schema_name",
                        $db_username, $db_password,
                        {on_connect_call => 'datetime_setup'}
                );
[download]

Just thought I'd get it down while it was fresh in my mind and I'll update the post when I know more about what I've done.

ta!

Edit

The more documentation I read, the more I think I did the Right Thing.

How to connect

How to customize InflateColumn (which I didn't need)

Ea

Sometimes I can think of 6 impossible LDAP attributes before breakfast.

Mojoconf was great!

Comment on Dbic and inflating Oracle DATE columns - solved Select or Download Code

Replies are listed 'Best First'.
Re: Dbic and inflating Oracle DATE columns - solved by Ea (Chaplain) on Jan 14, 2019 at 15:23 UTC
(answering my own question) ilmari on IRC pointed me towards DBIx::Class::Storage::DBI::Oracle::Generic and the connect_call_datetime_setup method. While I can't say for certain that the on_connect_call option goes and finds the correct formatter, the docs say that it Does the Right Thing, i.e. it sets the necessary values and environment variables for DBIx::Class::InflateColumn::DateTime and DateTime::Format::Oracle. It just works. There is a no-op sub in the base class DBIx::Class::Storage::DBI so that your code is portable across databases. The documentation on on_connect_call explains that `on_connect_call => 'datetime_setup'` calls `DBIx::Class::Storage::DBI::Oracle::Generic->connect_call_datetime_setup()` on Oracle databases which makes your InflateColumn call work. some example code `my $schema = Timetable::Schema->connect("dbi:Oracle:$schema_name", $db_username, $db_password, {on_connect_call => 'datetime_setup'} ); my $resultset = $schema->resultset('Ical') ->search($id); print "Start time is ", $resultset->start_time; # Start time is 2018-04-30T16:00:00` [download] works with `use utf8; package Timetable::Schema::Result::Ical; use base 'DBIx::Class::Core'; __PACKAGE__->load_components("InflateColumn::DateTime"); __PACKAGE__->table_class("DBIx::Class::ResultSource::View"); __PACKAGE__->table("ICAL"); __PACKAGE__->add_columns( "id", { data_type => "varchar2", is_nullable => 0, size => 255 }, "start_time", { data_type => "datetime", is_nullable => 1, original => { data_type => "date" }, }, }; 1;` [download] Ea Sometimes I can think of 6 impossible LDAP attributes before breakfast. Mojoconf was great!	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Dbic and inflating Oracle DATE columns - solved
by Ea (Chaplain) on Jan 14, 2019 at 15:23 UTC

ilmari on IRC pointed me towards DBIx::Class::Storage::DBI::Oracle::Generic and the connect_call_datetime_setup method. While I can't say for certain that the on_connect_call option goes and finds the correct formatter, the docs say that it Does the Right Thing, i.e. it sets the necessary values and environment variables for DBIx::Class::InflateColumn::DateTime and DateTime::Format::Oracle. It just works.

There is a no-op sub in the base class DBIx::Class::Storage::DBI so that your code is portable across databases. The documentation on on_connect_call explains that on_connect_call => 'datetime_setup' calls DBIx::Class::Storage::DBI::Oracle::Generic->connect_call_datetime_setup() on Oracle databases which makes your InflateColumn call work.

some example code

my $schema = Timetable::Schema->connect("dbi:Oracle:$schema_name",
                        $db_username, $db_password,
                        {on_connect_call => 'datetime_setup'}
                );

my $resultset = $schema->resultset('Ical')
                       ->search($id);
print "Start time is ", $resultset->start_time;
# Start time is 2018-04-30T16:00:00
[download]

use utf8;
package Timetable::Schema::Result::Ical;
use base 'DBIx::Class::Core';

__PACKAGE__->load_components("InflateColumn::DateTime");
__PACKAGE__->table_class("DBIx::Class::ResultSource::View");
__PACKAGE__->table("ICAL");
__PACKAGE__->add_columns(
  "id",
  { data_type => "varchar2", is_nullable => 0, size => 255 },
  "start_time",
  {
    data_type   => "datetime",
    is_nullable => 1,
    original    => { data_type => "date" },
  },
};

1;
[download]

Ea

Sometimes I can think of 6 impossible LDAP attributes before breakfast.

Mojoconf was great!

[reply]
[d/l]
[select]