Michael Peppler wrote:
I am using DBD::Sybase 1.02 and trying to insert image (as is , withou any conversion) into the IMAGE column in the database and it is giving me SQL error. The only way i am able to insert image/text data into the IMAGE type column in the database is by converting them into hexa , and if i do this way i am having problem inOn Fri, 2004-06-25 at 20:30, Anima wrote:
I am using Syabse::DBD 1.02. Currently we are storing images and text files in a Sybase TEXT column after converting to Hexadecimal. The data is doubled in storage size due to the conversion. We are having db space issues due to that.
IMAGE and TEXT use the same underlying storage mechanism. The difference is that IMAGE is a binary storage - no character set conversion of any sort happens during either inserts or fetches.
I would definitely store images on IMAGE columns - and you should be
able to use DBD::Sybase 1.02 (or later - 1.04 is the current version) to
do this.
extracting it back from the database. Correct me if I am wrong here. (Am i missing any conversion here?)
It would be great if some one can send some sample code to take care of this issue using DBD::Sybase.pm
Attaching my code snippet:
===============================================================================================
Insert
---------------------
my $fh = $cgi->upload('data');
my $data;
local $/;
$data = <$fh>;
$data = unpack("H*",$data);
$data = $dbh->quote($data);
my $sth = $dbh->prepare("INSERT INTO attachment (attach_id, data) values ($id, $data);
Fetch -------------- $dbh->prepare("SELECT data FROM attachments WHERE attach_id = $attach_id"); my $vdata = sth->fetchrow_array; $vdata =~ s/^0x//; $vdata = pack("H*", $vdata); . =============================================================================================
Even if you perform an insert with a hex string for the image (i.e. something like "insert foo (id, data) values(1, 0xdeadbeef)") the actual data stored wouldn't be the hex string, but the binary representation.
You can also use the BLOB write API (ct_send_data, and friends) to send the binary data to the server without converting to a hex string first.
That said - IMAGE and TEXT columns are NOT space efficient, as each data item is stored on its own page chain. Assuming a server with 2k pages (the default) a row with one IMAGE column that contains a single byte will take up 2k of storage (one page for the IMAGE column, plus some storage on a different page for the rest of the row).
Michael
