DBI, BLOBS, MySQL

[Paul Makepeace]

What does 'show table files;' give you? You might have the wrong type
of blob/text.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

Below is ad-hoc commentary...

On Thu, Jan 8, 2009 at 11:02 PM, Christopher Jones <c.jones at ucl.ac.uk> wrote:
>
> I'm having issues inserting files into a BLOB field in MySQL, using something similar to this;
>
>
> binmode($filehandle);
> my $file_str;
> {
>  local( $/, undef ) ;

I don't think this is doing what you think it is... local $/; is all
you need there.

>  $file_str = <$filehandle>;
> }
> my $statement = "

Consider using q{...} non-interpolating since you don't have any $vars.

>  INSERT INTO 'files' (file_name, file)
>  VALUES (?,?,?)

You only need two ? given two bound variables

> ";
> my $sth = $dbh->prepare($statement);
> $sth->execute('my.pdf',$file_str);
>
>
> The problem is that files bigger than 64K are being truncated on their insert into the database (i.e. the maximum BLOB length in the database is 64K).

> The global max_allowed_packet variable is set to 16776192, so that doesn't seem to be the culprit, and I don't know of any other limitations in MySQL that would limit the size of the insert.

Yes, this is just for client-server communication, not to do with your
underlying schema.

>
> I'm tearing my hair out with this, and after wading through countless dead-ends I've pretty much given up!
>
> Is it possible this is a DBI problem, or is it bound to be a MySQL issue? Any clues, from any direction, would be very gratefully received.
>
>
>
> Cheers,
>
>
> Chris
>
>
> PS Perl 5.8.8, DBI v1.607, MySQL 5.1, if that makes any difference
>