[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: Re: [Dbix-class] Oracle xmltype
> To: "DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
> Date: Thursday, April 30, 2009, 10:06 AM
> 
> Ok thanks, that is at least giving me a starting point.
> One more quirk though:
> 
> In order to get to the raw XML in plain DBI I have to do
> the follwing:
> 
> select xmltype.getclobval(xml_column) from table
> 
> i.e. I cannot simply select from xml_column but I have to
> pass that to the PL/SQL function xmltype.getclobval to get
> the xml as text.
> 
> How can I do that in the DBIx::Class scenario?

Not sure, but take a look at:

http://search.cpan.org/~ribasushi/DBIx-Class-0.08102/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures

that seems to offer an example of using procedures in your SQLA.  I very rarely do that myself, since I'd rather keep myself database agnostic so you might need to play with it.

jnapiorkowski

> 
> Many thanks!
> 
> --- On Thu, 4/30/09, John Napiorkowski <jjn1056 at yahoo.com>
> wrote:
> 
> > From: John Napiorkowski <jjn1056 at yahoo.com>
> > Subject: Re: [Dbix-class] Oracle xmltype
> > To: "DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
> > Date: Thursday, April 30, 2009, 9:29 AM
> > 
> > 
> > --- 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
> > 
> > 
> > 
> > 
> > _______________________________________________
> > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> > IRC: irc.perl.org#dbix-class
> > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> > Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
> > 
> 
> 
> 
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>