[Dbix-class] Oracle xmltype

John Napiorkowski jjn1056 at yahoo.com
Tue May 5 01:10:18 GMT 2009 



--- On Mon, 5/4/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: Monday, May 4, 2009, 10:02 AM
> 
> > 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...

It can be frustrating but I hope we can convince you to stay at it, just you may end up being the person in charge of the xml support implementation :)

I myself don't know anything about Oracle and XML types.  Last time I played with that it was with Oracle 8i and the XML type was some half done bit that needed Java to work properly.  My feeling here is that you may want to look at the work done to support arraytypes in the postgresql storage, this might be somewhat similar.

You're frustrated because you are the first person to need this to work, so it's not baked in.  However, this is an open source project and we are all here scratching itches and trying to help each other get some flea powder.  You have to decide how far you can afford to go with learning this.  I also got frustrated when I first started, cursed the thing out, and went back to writing sql libraries for a bit.  But eventually I saw the promise here.  It's a work in progress but it's probably not work everyone wants to do.  Hopefully we can get you going somehow.  Good luck,

John

> 
> 
> --- 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
> > 
> 
> 
> 
> 
> _______________________________________________
> 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