[Dbix-class] Oracle xmltype

Morgon Hed morgonhed at yahoo.com
Mon May 4 14:02:15 GMT 2009 

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

Thanks for the link.

My fundamental problem is that I more and more come to the conclusion that DBIx::Class is just not for me as I don't understand it.

I simply have no systematic overview about what the individual components are supposed to model and I can't find any documentation that would explain that in any way that is accessible to me ... 

Here's one example:

In the spirit of your link I can do this:


$schema->resultset('Stream')->search(
                                               {},
                                                   {
                                                     select => [ { 'xmltype.getCLobVal' => 'xml' } ],
                                                     as     => ['xml'],
                                                   }
                                             );


And this works - I can retrieve the XML with that but that is not what I ultimately want.

I don't want to mention each and every attribute again in the above search (the above search retrieves the XML-field only), all I want is a way to tell DBIx::Class to treat one column a slightly little bit different from the others - and I want to tell that DBIx::Class on the object-level, not by basically supplying my own SQL.

I am pretty sure that DBIx::Class is well thought out and capable but for me it seems it makes hard things possible at the price of making simple things hard (at least for newbies).

Sorry if I sound frustrated...


--- 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, 12:33 PM
> 
> 
> 
> --- 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
> > 
> 
> 
> 
> 
> _______________________________________________
> 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
>

More information about the DBIx-Class mailing list