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
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
