Adding a JPEG file to database

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

Hi There, I would like to insert/update a jpeg file to a table in oracle. The table has three columns,: id (number) , Plot( BLOB), contamination(number). I need to update the plots column with a JPEG file.

my ($conn);
my($sample_name, $blob_file);



&GetOptions("n=s" =>\$sample_name,
            "f=s" =>\$blob_file,
       );
       
       
       
my $db_type = 'test'; # or live
my $team = 78;

{
  #conn_setup();
  
  update();
}



sub update {
 my $conn;
 open(BLOB, $blob_file) ||die "Cannot open the blob file\n";
 my $bytes = 0;
 my $buf;
 $bytes = read(BLOB, $buf, 500000);
 print STDERR "Read $bytes bytes\n";
 close(BLOB); 
  eval {
   $conn = Database::Conn->new('live');#module to connect to DB
   $conn->addConnection(DBI->connect('dbi:Oracle:CANT.world', 'nst_own
+er', 'NST_OWNER', {RaiseError => 1, AutoCommit => 0}),'nst');
   my $sample_id = $conn->execute('nst::Copynumber::GetCosmicSampleId'
+, $sample_name);
   #print $sample_id;
   $conn->execute('nst::Copynumber::InsertContourPlots',$buf, $sample_
+id); 
   
   };if($@){
    warn "ERROR: ".$@;
    $conn->rollback;
    exit;
    } else {
       
       $conn->commit;
       
    }
}
[download]

I have tried reading the file in to a string, using 'read' but that got me this error and some pages of non readable errors :(
DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only for insert into a LONG column

The JPEG files are usually 30KB - 60KB.
Any directions to how to achieve would be very much appreciated.
Thanks in advance.

Comment on Adding a JPEG file to database Download Code

Replies are listed 'Best First'.
Re: Adding a JPEG file to database by Eliya (Vicar) on Feb 21, 2011 at 19:03 UTC
Just a wild guess... but maybe you could try binding the parameters explicitly using bind_param, which would allow you to specify the type in `\%attr`, such as `{ ora_type => ORA_BLOB }` (for this, you'll also have to import the type constants: `use DBD::Oracle qw{:ora_types};`). When you simply pass a list of parameters to `execute`, the driver does some guessing to determine the type for the binding that's done behind the scenes, but this often goes wrong for anything but simple types. See also Placeholder Binding Attributes.	[reply] [d/l] [select]
Re: Adding a JPEG file to database by kejohm (Hermit) on Feb 22, 2011 at 04:15 UTC
You could try encoding the JPEG files as something like Base64 and storing them as text in the database. The downside with this approach is that it can add to the size of the data by about 30%. Here is an example: `use MIME::Base64; open my $fh, '<', 'file' or die $!; binmode $fh; my( $bytes, $data ); do { $bytes = read $fh, my $buf, 1000; die $! unless defined $bytes; $data .= $buf; } while $bytes != 0; close $fh; print encode_base64($data, '');` [download] Also, instead of using the `read` function, you could just slurp the entire file into memory, like this: `open my $fh, '<', 'file' or die $!; binmode $fh; local $/ = undef; print encode_base64(<$fh>, '');` [download]	[reply] [d/l] [select]
Re: Adding a JPEG file to database by chrestomanci (Priest) on Feb 21, 2011 at 20:26 UTC
Eliya ++ Good point about binding parameters, and specifying their types. The other thing that occurs to me is that one call to `read` is not guaranteed to read the whole file. My understanding is that do to that you have to call read (or sysread) repeatedly until it returns zero. eg: `open(BLOB, $blob_file) \|\|die "Cannot open the blob file\n"; binmode BLOB; my $bytes = 0; my $offset = 0; my $buf; while( $bytes != 0 ) { $bytes = read(BLOB, $buf, 500000, $offset); die "Error reading $blob_file $!" unless defined $bytes } close BLOB; # $buf now contains all the bytes from $blob_file` [download] See read on perldoc	[reply] [d/l] [select]
Re: Die silently? by trwww (Priest) on Feb 21, 2011 at 20:01 UTC
Any directions to how to achieve would be very much appreciated. The generally accepted way of referring to images from a database is to store the file somewhere in the filesystem and then store the image's location in the database field. It seems a bit counter intuitive, but you don't really want to be wasting precious database cycles on large buffers like this.	[reply]