[Dbix-class] Setting additional attributes in bind_param

John Napiorkowski jjn1056 at yahoo.com
Wed Nov 15 15:21:40 GMT 2006


--- Matt S Trout <dbix-class at trout.me.uk> wrote:

> John Napiorkowski wrote:
> > Hi,
> > 
> > I'm trying to pass some additional 'hinting'
> > information to the sql statement throught the dbi
> > bind_param method.  In 'straight' dbi procedural
> code
> > I might write something like:
> > 
> > my $update_sql	= q[
> > 
> >   update videos.content
> >   set content=?
> >   where catalog_id=? encoding_id=? format_id=?
> > ];
> > 	
> > my $sth   = $dbh->prepare($update_sql);
> > 
> > $sth->bind_param(1,$file, {pg_type =>
> > DBD::Pg::PG_BYTEA });
> > $sth->bind_param(2,$self->catalog_id());
> > $sth->bind_param(3,$self->encoding_id());
> > $sth->bind_param(4,$self->format_id());
> > 
> > $sth->execute();
> > 
> > Now I can see we are using SQL::Abstract to create
> the
> > SQL and bind lists which we pass straight to
> execute
> > in 
> > DBIx::Class::Storage::DBI.pm in the _execute
> method. 
> > So we'd need to change this someway if we want to
> do
> > binds with attributes and hinting.
> > 
> > In the docs for SQL::Abstract, it says we can do
> this
> > by passing a 'bindtype='columns' when we
> instantiate
> > the object.  The main drawback is that we need to
> loop
> > through the parameter bindings instead of just
> passing
> > it all to dbi excute, but in this case that sort
> of
> > flexibility is something I want.
> > 
> > In DBIx::Class::Storage::DBI.pm I find a couple of
> > methods that might help.  It looks like I can pass
> > some SQL::Abstract options to the connection
> method,
> > but right now these are limited to
> qw/limit_dialect
> > quote_char name_sep/.
> > 
> > I don't think it would be too hard to patch
> > DBIx::Class::Storage::DBI.pm so that you could
> pass
> > some hints to the bind_param method.  I think the
> > biggest choice would be to figure out how to set
> > and/or store the hinting info.  I'd be inclined to
> > have it in the column metadata, under a key
> called,
> > "bind_attributes" or something like that but I'm
> not
> > sure if that will interfere with some other future
> > plans for extension.
> > 
> > Otherwise it looks to me like it would just be 10
> or
> > so lines of code in one class to make it work.
> 
> Should really be in _execute, not in the SQLA stuff,
> I think. bind_info in 
> column_info seems like it'd be consistent with
> everything else, make sure you 
> leave room to support the extra-interesting stuff
> like filehandle access to 
> BLOBS that some DBs provide.
> 
> Patches (with tests) against -current welcome :)

Just a quick question came up for me as I am looking
at this:  in the _execute I see:

sub _execute {
  my ($self, $op, $extra_bind, $ident, @args) = @_;
  my ($sql, @bind) = $self->sql_maker->$op($ident,
@args);
  unshift(@bind, @$extra_bind) if $extra_bind;

If SQL::Abstract is instantiated with the
bindtype='columns' for options then @bind is going to
look like:

    @bind = (
        [ 'column1', 'value1' ],
        [ 'column2', 'value2' ],
        [ 'column3', 'value3' ],
    );

I traced back where that $extra_bind is coming from
and it seems to come only from select statements.  It
appears to be part of $attrs in:

sub select {
  my $self = shift;
  my ($ident, $select, $condition, $attrs) = @_;
...

under the $attrs->{bind} key.

So my question is, is that $attrs->{bind} going to use
SQL Abstract for figuring it's binds or is this a
manual thing the end user can configure?  I can't find
bind documented under the resultset attributes
section.

If SQL Abstract generates it, then I don't see any
trouble at all, but if this is something we are
building manually somewhere than I could see that
line:

unshift(@bind, @$extra_bind) if $extra_bind;

causing trouble if @bind is formatted one way and
$extra_bind another.

Also, as a side thing, are people comfortable with
this being a connect time option?  TO be honest I
can't see a good way around that, since if we made it
a per table thing then we'd need to jump a lot of
hoops to the case where we did a join between table
tables with one using bindtype=columns and the other
wasn't.

Some illumination is appreciated.

--john
> 
> 
> -- 
>       Matt S Trout       Offering custom
> development, consultancy and support
>    Technical Director    contracts for Catalyst,
> DBIx::Class and BAST. Contact
> Shadowcat Systems Ltd.  mst (at)
> shadowcatsystems.co.uk for more information
> 
> + Help us build a better perl ORM:
> http://dbix-class.shadowcatsystems.co.uk/ +
> 
> _______________________________________________
> List:
>
http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN:
>
http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> Searchable Archive:
>
http://www.mail-archive.com/dbix-class@lists.rawmode.org/
> 



 
____________________________________________________________________________________
The all-new Yahoo! Mail beta
Fire up a more powerful email and get things done faster. 
http://new.mail.yahoo.com




More information about the Dbix-class mailing list