On 26 Feb 2015, at 18:14, David Cantrell <da...@cantrell.org.uk> wrote:
> On Thu, Feb 26, 2015 at 04:55:10PM +0100, Peter Rabbitson wrote:
[...]
>> Also (as noted in that RT) - this will badly
>> break BLOB operations.
>
> Good point. That prompted me to look over our code. We had two BLOB
> fields, one of which can be replaced with a TEXT. The other really does
> contain binary data, but it seems that it's getting corrupted anyway
> even without my monkey-patch. Yuck.
The problem appeared to be encoding issues between DBI and mysql. Delving into
DBIx::Class::Storage::DBI:_dbh_execute I found that we were correctly passing a
byte-encoded string from DBIx::Class, but it ended up corrupted in the database:
> DB<11> x $sql
>
> 0 'INSERT INTO customer_static_file ( bytes, customer_id, date_created,
> date_updated, filename, user_id) VALUES ( ?, ?, ?, ?, ?, ? )'
> DB<12> x $bind
>
> 0 ARRAY(0x122bf2b8)
> 0 ARRAY(0x122bef70)
> 0 HASH(0x122780a8)
> 'dbic_colname' => 'bytes'
> 'sqlt_datatype' => 'LONGBLOB'
> 1 "?PNG\cM\cJ\cZ\cJ??Stuff🝖"
DBI appears to know that this is a binary column, but that's not helping.
The solution I arrived at after some googling was as follows:
> __PACKAGE__->load_components('FilterColumn');
> __PACKAGE__->filter_column(
> 'bytes',
> {
> filter_from_storage => sub { pop },
> filter_to_storage => sub {
> my ($resultset, $binary_data) = @_;
> my $encoded_string = sprintf(
> "x'%s'",
> join('',
> map { sprintf('%02x', ord($_)) } split('', $binary_data))
> );
> return \$encoded_string;
> },
> }
> );
which uses MySQL's preferred way of encoding binary data on the command line.
Is this the best way of handling this, or is there a simpler or purer way I've
overlooked?
Sam
--
Website: http://www.illuminated.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
