Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Dbic and inflating Oracle DATE columns - solved

by Ea (Chaplain)
on Jan 14, 2019 at 10:27 UTC ( [id://1228524]=perlquestion: print w/replies, xml ) Need Help??

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" }, }, );
but after searching with dbic and getting a resultset
$resultset = $schema->resultset('Ical') ->search({ uid => $id });
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'} );

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!

  • 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
      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;

      Ea

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

      Mojoconf was great!

    Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Domain Nodelet?
    Node Status?
    node history
    Node Type: perlquestion [id://1228524]
    Front-paged by Corion
    help
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this?Last hourOther CB clients
    Other Users?
    Others pondering the Monastery: (7)
    As of 2024-03-29 08:31 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found