William Miller <[EMAIL PROTECTED]> wrote:
> I am new to MySQL and I was trying to find out how to setup a record
> with a datatype as a image ....jpg etc...
I have recently done just that. Here is my table structure:
CREATE TABLE IF NOT EXISTS Item_binary (
entry int(11) NOT NULL,
vidx smallint(6) NOT NULL,
content_type varchar(255) NOT NULL,
value blob,
UNIQUE KEY (entry, vidx),
KEY (content_type)
) TYPE=MyISAM PACK_KEYS=1 CHECKSUM=1;
More generically, you might want something like:
CREATE TABLE IF NOT EXISTS Image (
id int(11) NOT NULL AUTO_INCREMENT,
content_type varchar(255) NOT NULL,
value blob,
UNIQUE KEY (id),
KEY (content_type)
) TYPE=MyISAM PACK_KEYS=1 CHECKSUM=1;
You will need a few tricks in order to fetch and store binary data.
Here is the Perl code I use for fetching:
# Make sure that LongReadLen is set properly, otherwise the data will
# be truncated by DBI.pm.
my $save_len = $LDB::dbh->{LongReadLen};
$LDB::dbh->{LongReadLen} = 128 * 1024;
my $sth = $LDB::dbh->prepare("SELECT value FROM Item_binary
WHERE entry = $qid AND vidx = $qvidx");
$LDB::dbh->{LongReadLen} = $save_len;
$sth->execute;
croak $DBI::errstr if $DBI::errstr;
my ($data) = $sth->fetchrow_array();
print STDOUT $data;
return;
Here is a simplification of the code that I use for storing:
# Here we assume that $buf is a string containing packed binary data.
my $qtype = $dbh->quote( $content_type || 'image/jpeg' );
my $sth = $LDB::dbh->prepare("INSERT INTO Item_binary
(entry, vidx, content_type, value)
VALUES ($id, 1, $qtype, ?)");
$sth->bind_param(1, $buf);
$sth->execute();
If you are using a different API, you're on your own...
---------------------------------------
Dr. Michael McClennen [EMAIL PROTECTED]
Internet Public Library http://www.ipl.org/
University of Michigan +1 734 764 3417
550 E. University, Rm. 304
Ann Arbor, MI 48109-1092 USA
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
