[Dbix-class] Branch for supporting column attributes checked in

John Napiorkowski jjn1056 at yahoo.com
Sat Nov 18 01:12:21 GMT 2006


Hi,

I checked in a branch off of current under
branches/DBIx-Class/param_bind which alters how SQL
statements are executed so that you can specify bind
parameters in your schema classes.

This is sorely needed by people trying to get
Postgresql BYTEA and Oracle BLOB columns to work
without having to hack around.

The summary of the problem is that typically when you
bind a value to a placeholder in your SQL statement,
that value gets treated like a VARCHAR.  For some
database and column types this is a problem,
particularly if the column is for a binary type. 
Typical issues are that the value will get truncated
at some length, or the sql statement will just fail.

When using straight DBI, people with these kinds of
problems can bind the column type as a hashref:

$sth->bind_param(1, 'value', { pg_type =>
DBD::Pg::PG_BYTEA });

This branch makes some key alterations to support this
in DBIx::Class.  You can add the required attributes
in you Schema:

__PACKAGE__->add_columns(
  "media", { 
  
    data_type => "bytea", 
    default_value => undef, 
    is_nullable => 1, 
    bind_attributes => { 
      pg_type => DBD::Pg::PG_BYTEA },
  },
);

If you have need for this please check it out and see
if it works for you.  This currently solves my
particular itch as well as passed all my other tests,
but I am hoping to get feedback on the best way to
make this suitable for wider consumption.

The biggest change I've introduced is that
SQL::Abstract is initialised with
"bindtype=>'columns'", which means that the array of
binds returned will be an arrayref of the column name
and the value instead of just an array of values.  So
if you are using the SQL::Abstract inside of
..Storage::DBI then this change will affect you.  I
think I got all the places but I might have missed
something.

In order to get the actual bind attributes into the
storage object I had to mess with ..ResultSet->update
and ..Row->update|insert.  It's possible people might
prefer to keep these clean, if so I could easily
isolate those changes into a component that you'd have
to load for the table that needed it.

I see no reason to pass the parameter bind attributes
for deletes and selects, but please correct me if I am
wrong.

Any, I know there are a few of you out there that need
something like this (this issue has come up on the
mailing list several times) so please check it out and
kick the tires.

Thanks!

--John Napiorkowski


 
____________________________________________________________________________________
Sponsored Link

Mortgage rates near 39yr lows. 
$420k for $1,399/mo. Calculate new payment! 
www.LowerMyBills.com/lre



More information about the Dbix-class mailing list