Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Adding a JPEG file to database

by Anonymous Monk
on Feb 21, 2011 at 17:03 UTC ( #889422=perlquestion: print w/replies, xml ) Need Help??

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('', '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; } }
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.

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.

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, '');

    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>, '');
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

    See read on perldoc

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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://889422]
Approved by ww
Front-paged by Arunbear
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (11)
As of 2019-11-18 16:31 GMT
Find Nodes?
    Voting Booth?
    Strict and warnings: which comes first?

    Results (90 votes). Check out past polls.