how to import a binary file into the MYSQL tables.

[Jani Tolonen]

Mike Almogy writes:
 > Hi ALL.
 > does anyone know how i can insert a binary file into a table.
 > lets assume that i have a table called img with pic as long blob and
 > name as varchar(50).
 > Do i just need to use the LOAD DATA INFILE "/some/dir/on/server/pic.jpg"
 > INTO TABLE img;  ?
 > 
 > Or i need to do something else ?
 > 
 > Thanks .
 > 
 > 
 > Mike Almogy
 > Mofet System Administrator.
 > Pho :972-03-6901417
 > Cel :972-052-562237
 > Fax :[phone]
 > 

Hello Mike,

Here is a small perl script which should be very helpful to you...

--------------------------

#!/usr/local/bin/perl
$^W = 1;
use strict;

use Mysql;

my ($image_file) = @ARGV;

$image_file =~ m/(.*)\.(.*)/;

my $base_name = $1;
my $extension = $2;

my $image_copy = $base_name . "_copy." . $extension;


my $dbh = Mysql->connect('', 'test') || die "Didn't connect";

open IMAGE, $image_file;
my $image;
{
    local $/ = undef;
    $image = <IMAGE>;
}
close IMAGE;

$image = escape_chars($image);

my $sth = $dbh->query('drop table image_test') || warn $dbh->errmsg;
$sth = $dbh->query('create table image_test(image blob)') || warn $dbh->errmsg;

$sth = $dbh->query("insert into image_test values('$image')") || warn
$dbh->errmsg;

$sth = $dbh->query('select image from image_test') || warn $dbh->errmsg;

my ($retrieved_image) = $sth->fetchrow();

open IMAGE_COPY, ">$image_copy";
print IMAGE_COPY $retrieved_image;
close IMAGE_COPY;


sub escape_chars {

    my $image = shift;

    $image =~ s/\\/\\\\/g;
    $image =~ s/\'/\\\'/g;
    $image =~ s/\"/\\\"/g;
    $image =~ s/\000/\\0/g;

    return $image;

}

---------------------------------

Regards,

- Jani

-- 
+---------------------------------------------------------------------+
| TcX  ____  __     _____   _____  ___                                |
|     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|       Jani Tolonen             |
|    /*/ /*/ /*/   \*\_   |*|   |*||*|       mailto: jani@stripped   |
|   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|       Helsinki                 |
|  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|_____  Finland                  |
|  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                           |
|             /*/             \*\                   Developers Team   |
+---------------------------------------------------------------------+