teads has asked for the wisdom of the Perl Monks concerning the following question:

Hi-

I'm working with DBI to set up a suite to test Oracle datatypes. My scripts for BLOB's/CLOB's work fine, but I'm running into problems with RAW datatypes. I'm trying to insert 5-6 .GIF files. I think they insert o.k., but when I select them back out, they have different file sizes and appear to have been corrupted somehow. How should I specify a RAW datatype within my scripts, whether select or insert?

***** insert snippet:

my $max_len = 20000;

#
#Load Filenames into Scalar
#

opendir(DIR, "/RAWdata") || die "Cannot open gif:$!\n";
my @filenames = grep !/^\./, readdir(DIR);
close(DIR);

my $file;

#
#Go through list of filenames in a directory
#Add the name, length, and binary data to the table 
#

foreach my $title (@filenames) {

    open(FILE, "/RAWdata/$title") || die "Cannot open $title : $!\n";
    binmode FILE;
    my $file_len = read FILE, $file, $max_len;   

    #Prepare Insert Statement
       my $sth = $dbh->prepare("INSERT into RAH values (?,?,?)" );
   
    #Bind Variables to columns.
    $sth->bind_param(1,$title);
    $sth->bind_param(2,$file_len);
    $sth->bind_param(3,$file, {ora_type => ORA_RAW} );
   
    #Insert.
    my $rv = $sth->execute();   
    
    close(FILE);
}

[download]
***** select snippet:

$dbh->{LongReadLen} = '20000';

opendir(DIR, "/RAWdata") || die "Cannot open gif:$!\n";
my @filenames = grep !/^\./, readdir(DIR);
close(DIR);

my $title;

#
#Select out RAW's based on their filename - R_NAME
#

foreach my $title (@filenames) {

    my $sth = $dbh->prepare("SELECT R_RAW from RAH where R_NAME='$t
+itle'");

    $sth->execute();

#
#Put selected RAW into an array, write it to a file using the filename
+ you
#got from the RAWdata directory.
#
    my @jpg = $sth->fetchrow_array();
    open(FILE, ">$title") || die "Cannot open file for writing: $!\
+n";
    print FILE @jpg;
    close(FILE);
    $sth->finish();

[download]

Edit kudra, 2001-10-19 Replaced br with code

Replies are listed 'Best First'.
Re: DBD::Oracle - RAW datatypes
by cjensen (Sexton) on Oct 19, 2001 at 01:20 UTC
    I always used ORA_BLOB for images, and I've never used the ORA_RAW datatype, but I'll take a stab since nobody else has yet.

    ORA_RAW, I think, is limited to 255 bytes column size, and ORA_LONGRAW is much bigger (2 gigs?). You can't fit a 20K gif in 255 bytes. Try using ORA_LONGRAW instead.

    There could be other problems, but that seems like the most obvious potential issue. It might explain why your images seem truncated/corrupted when you select them out.

[reply]
      <html> <head> <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1"> </head> <body> Thanks for your response. The Oracle documentation states that RAW datatypes can be up to 2KB
      in size. The .GIF's I'm trying to insert are:

      IPP_0002.GIF 1518b
      IPP_0003.GIF 899b
      IPP_0004.GIF 895b
      IPP_0005.GIF 255b
      IPP_0012.GIF 1265b
      IPP_0015.GIF 902b

      When I select them out of the database and write them to my cwd, I get:

      IPP_0002.GIF 3036b
      IPP_0003.GIF 1798b
      IPP_0004.GIF 1790b
      IPP_0005.GIF 510b
      IPP_0012.GIF 2530b
      IPP_0015.GIF 1804b

      The gif's aren't being truncated, they're expanding!... ?

      I can successfully insert/select BLOB's, CLOB's, LONG's. But I also need to be able to do
      RAW's. I work in a test environment and my current task is to provide test cases for all of the
      datatypes for the product my company is working on...

      </body> </html>
[reply]
        Oh, that's weird. You're getting exactly double the number of bytes. What does your table definition look like? Do you have more than one binary object in the table? Have you tried using bind_param_inout and specifying ora_field along with ora_type?

[reply]
Re: DBD::Oracle - RAW datatypes
by wyrickre (Initiate) on Feb 24, 2005 at 19:47 UTC
    I know this is an old thread, but I'm including my solution below in hopes that it will help somebody out there.... Call the subroutine below with the RAW you get from the query... This will "fix" the raw. 
    sub fixRaw(\$)
{
    my $raw = shift;
    return undef unless defined $raw and defined $$raw;

    ##################################################################
    # Split the raw into 2 byte chunks, interpret each chunk as hex, 
    # then pack it!
    ##################################################################
    return join('',map(pack('C', hex($_)), $$raw =~ /../sg));
    # The below also works... I don't know which is fastest.
    # return join('',map(pack('C', hex($_)), unpack("a2"x(length($$raw
+)/2),$$raw)));
}

    [download]
[reply]
[d/l]
      Thanks wyrickre. Exactly what I needed!
[reply]

Back to Seekers of Perl Wisdom