[Dbix-class] Oracle xmltype

[John Napiorkowski]

--- On Thu, 4/30/09, Morgon Hed <morgonhed at yahoo.com> wrote:

> From: Morgon Hed <morgonhed at yahoo.com>
> Subject: [Dbix-class] Oracle xmltype
> To: dbix-class at lists.scsys.co.uk
> Date: Thursday, April 30, 2009, 7:28 AM
> 
> Hi,
> 
> I am rather new to DBIx::Class and I wonder if someone
> could explain to me how to deal with Oracle
> XmlType-columns.
> 
> I have a table which contains a column of type XmlType
> and  I want that column to be inflated/deflated as an
> object of it's own.
> 
> What I need to do in order to get to the raw XML in plain
> DBI is to do something like the following:
> 
> $sth->bind_param(":xml", $xml, { ora_type =>
> ORA_XMLTYPE } );
> 
> And I cannot figure out how to incorporate this binding
> into DBIx::Class.
> 
> Please note that I want both to retrieve and to update this
> column.
> 
> Many thanks!
> 
> Not 100% sure I know what you need, but I imagine this could be handled the same way we hacked bind parameters to handle how some databases need to know about bytea or blob types.  You might want to check out DBIx::Class::Storage::DBI::Pg and in particular the method 'bind_attribute_by_data_type' which is used to add some bind parameter support for postgresql.  Adding this to the Oracle DBD should be easy, something like:

(in package DBIx::Class::Storage::DBI::Oracle)

use DBD::Oracle qw(:ora_types); ## just guessing

sub bind_attribute_by_data_type {
  my ($self,$data_type) = @_;

  my $bind_attributes = {
    xml => { pg_type => DBD::Oracle::ORA_XMLTYPE },
  };
 
  if( defined $bind_attributes->{$data_type} ) {
    return $bind_attributes->{$data_type};
  }
  else {
    return;
  }
}


and then in your storage class, under __PACKAGE__->add_columns you'd do:

__PACKAGE__->add_columns(
  'xmltype' => {
    data_type => 'xml',
    is_nullable => 1,
  },
  .....

And it should just work, at least for reading and writing, if that's what you mean.  If you are asking about deeper support for the way Oracle (and now Postgresql I believe) has implemented sql functions and extensions to do stuff like selecting bits inside an xml stored doc, or doing joins againt info stored inside a xml document, right now I think you'll need to bind the functions as some of the documented examples for using functions in your SQLA.

jnapiorkowski


John