Directly from the DBI documentation:
'There is no need to quote values being used with Placeholders and Bind Values.'
So the driver is flawed if you need to be doing this. If it works and you can live with the untidyness though, stick with it. I suggest you report your bug as well (http://rt.cpan.org). Perhaps we could work on a fix if the maintainer is unresponsive.
Cheers,
Laird
From: "Aigars Grins" <[EMAIL PROTECTED]>
To: "Laird Shaw" <[EMAIL PROTECTED]>
CC: <[EMAIL PROTECTED]>
Subject: RE: FW: Question about the DBD::mysqlPP DBI driver
Date: Wed, 22 Jan 2003 14:02:58 +0100
Hi Laird,
You're right. The typos where unnecessary, but as you say: they don't affect
the real problem.
The code I refer to is in mysqlPP.pm. The most interesting part is on line
312 (for version 0.03):
$statement =~ s/\?/$quoted_param/e;
This clearly shows that if the $quoted_param does hold '?'-chars this will
go wrong. The $quoted_param is quoted using $dbh->quote(), but that wont
help in this case. Besides using the dirty trick with doing a
$sth->STORE("NUM_OF_PARAMS" => 0) I also had to create a quote() function of
my own.. Just FYI, I made it similar to:
sub quote_blob
{
my ($self, $val) = @_;
if (!defined($val)) { return "NULL"; }
# the following should be enough for MySQL
$val =~ s/\\/\\\\/gos;
$val =~ s/\'/\\\'/gos;
$val =~ s/\"/\\\"/gos;
$val =~ s/\n/\\n/gos;
$val =~ s/\r/\\r/gos;
$val =~ s/\t/\\t/gos;
$val =~ s/\0/\\0/gos;
return "'".$val."'";
}
Another problem with the driver is that under OpenBSD 3.2 the default
installation of perl doesn't have support for 64-bit integers, i.e. long
long or quad, and the code doesn't take this into account. This means that
unpack('Q', ..) wont work (which is needed in Net::MySQL version 0.07 line
563). That is a problem that can be dirtily "solved" by making sure the
queries sent to MySQL doesn't yield large results. For BLOBs this means
retrieving the BLOB data in small (< 2k) chunks.
I agree that it would be easiest to just stay away from the driver, but
since it's the one you get when installing a "fresh" OpenBSD 3.2-STABLE I
was looking for a way to at least try to use it.
--
Aigars
-----Original Message-----
From: Laird Shaw [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 22, 2003 12:19
To: [EMAIL PROTECTED]
Subject: Re: FW: Question about the DBD::mysqlPP DBI driver
Hi Aigars,
Firstly you have a couple of typos:
*in the table metadata - should be "primary key" not "primary_key".
*in the script,
$sth->execute($id, $data); should be
$sth->execute($data, $id);
*in the SQL: "INSERT INTO test_blobs" is not valid; should be
"INSERT INTO test_blobs (data) values ('')"
Anyhow I'm presuming those aren't the cause of your problems.
Both of your approaches work under the mysql driver; I don't know why your
original approach doesn't work under mysqlPP (I haven't read the code you
refer to - where is it?) - seems like another bug to me.
I have also had problems with '\'s in text data and truncation of binary
data. I've reported these bugs (http://rt.cpan.org/NoAuth/Bug.html?id=1956
and
http://rt.cpan.org/NoAuth/Bug.html?id=1957) and have discontinued the use of
mysqlPP. I use Windows 95 and ActiveState Perl; I don't know if you will
have the same problems under OpenBSD but my guess is you will.
I advise you to stop using mysqlPP and get a copy of the mysql driver until
the (seemingly many) bugs relating to blobs are worked out.
Regards,
Laird
P.S. If you are inserting data for the first time, this approach is probably
better:
$sth = $dbh->prepare("INSERT INTO test_blobs (data) values (?)");
$sth->execute($data);
$id = $dbh->{"mysql_insertid"}; #only if you need the ID value
$sth->finish();
>From: "Aigars Grins" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Subject: FW: Question about the DBD::mysqlPP DBI driver
>Date: Wed, 22 Jan 2003 10:03:16 +0100
>
>Hi,
>
>Since I've gotten no answer from the maintainer of the DBD::mysqlPP driver,
>I thought I'd forward my question here, so that if anyone here has an
>answer
>or hint they might tell me.
>
>TIA
>
>--
>Aigars Grins
>
>-----Original Message-----
>From: Aigars Grins [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, January 08, 2003 15:50
>To: [EMAIL PROTECTED]
>Subject: Question about the DBD::mysqlPP DBI driver
>
>
>Hi,
>
>I've just installed a new version of OpenBSD 3.2 and noticed that the
>customary DBD::mysql driver in the ports tree has been replacted by the
>DBD::mysqlPP driver. Ok, I thought, I don't mind. Now I'm trying to figure
>out the differences. I've found one problem situation and wonder if you
>might want to help me on the way of understanding how to solve it.
>
>
>I want to insert a BLOB into a MySQL column. If we assume that this is how
>it looks:
>
>CREATE TABLE test_blobs (
> id INT NOT NULL PRIMARY_KEY AUTO_INCREMENT,
> data BLOB
>);
>
>Then I thought about doing something like:
>
>...
>$sth = $dbh->prepare("INSERT INTO test_blobs");
>$sth->execute();
>$id = $dbh->{"mysql_insertid"};
>$sth = $dbh->prepare("UPDATE test_blobs SET data = ? WHERE id = ?");
>$sth->execute($id, $data);
>...
>
>But I don't get this to work, if the $data variable contains "?"
>characters.
>I've looked at your code and understand why it doesn't, but I don't
>understand of how to solve this problem properly.
>
>The only work-around that I can figure out is something like:
>
>...
>$sth = $dbh->prepare("INSERT INTO test_blobs");
>$sth->execute();
>$id = $dbh->{"mysql_insertid"};
>$sth = $dbh->prepare("UPDATE test_blobs SET data = ".$dbh->quote($data)."
>WHERE id = ".$id);
>$sth->STORE("NUM_OF_PARAMS" => 0);
>$sth->execute();
>...
>
>Although this seems to work it's _very_ ugly..
>
>Can you help me out?
>
>TIA
>
>--
>Aigars Grins
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
