Re: IMAGE vs TEXT using Sybase storage difference

[Jamsheed]

Thanks a lot Michael for your help.
It works finally. This is what i did.
Snippet
---------------------------
my $fh = $cgi->upload('data');
my $data;
local $/;
$data = <$fh>;
$data = unpack("H*",$data); 
(for some reason i was not able to put the file as is , withought converting to hexa first)

my $sth = $dbh->prepare("insert into attachment (attach_id, data)
values($id, 0x$data);
(Note:  prefixed  "0x" when i insert into that database.)
And when i query to display , i set the
dbh->{syb_binary_images} = 1; 
where i got the attachment in binary format to display.
-------------------------




[EMAIL PROTECTED] wrote:
On Sat, 2004-06-26 at 02:39, Jamsheed wrote:
 

Michael Peppler wrote:
   

On 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.
     

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 in
extracting it  back from the database.  Correct me if  I am wrong 
here.
   

 

Attaching my code snippet:
===============================================================================================
Insert
---------------------
my $fh = $cgi->upload('data');
my $data;
      local $/;
     $data = <$fh>;
     $data = unpack("H*",$data);
     $data = $dbh->quote($data);
   

Don't quote your data here!
 

     my $sth = $dbh->prepare("INSERT INTO attachment (attach_id, data) 
values ($id, $data);
   

my $sth = $dbh->prepare("insert into attachment (attach_id, data)
values($id, 0x$data);
will get the data in binary form into the attachment table.
 

Fetch
--------------
$dbh->prepare("SELECT data FROM attachments WHERE attach_id = $attach_id");
my $vdata = sth->fetchrow_array;
$vdata =~ s/^0x//;
$vdata = pack("H*", $vdata);
   

The problem is that due to the incorrect quoting in the insert you've
now actually stored the hex string, instead of the binary data.
See also the syb_binary_images attribute - set it to true to get IMAGE
columns returned in binary format.
Michael