Link to home
Start Free TrialLog in
Avatar of npollock
npollock

asked on

Mysql load data problem. Can't get replace to work inside statement.

I am trying to load two binary objects into a database based on a list, but the load fails when I try to create the correct path to load the second file.

Here is the db schema:
create table images (
    part_num        varchar(15)  not null default '',
    primary key(part_num),
    imgpath        varchar(70)  not null default '',
    zoomimg           longblob        not null,
    thumbimg          longblob        not null
);

Here is sample data from table /tmp/thumnlst.out used in the query:
/var/www/html/bigtmp1/img/item/thumb/17/10/1710-0078.jpg
/var/www/html/bigtmp1/img/item/thumb/17/10/1710-0126.jpg

I am able to extract the part number and load the thumbnail image path with no issue, but when I try to change the imgpath to change the word thumb to zoom, it blows up.  I tried using both the substring and concat functions and the replace function and cannot get it to work.
LOAD DATA INFILE '/tmp/thumblst.out' INTO TABLE images
  LINES TERMINATED BY '\n'
  (imgpath)
  SET part_num = substring(imgpath,44,9),
      thumbimg = load_file(imgpath)
      zoomimg = load_file(concat(substring(imgpath,1,31), 'zoom',substring(imgpath,37,21)));

error produced by above code:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'zoomimg = load_file(concat(substring(imgpath,1,31), 'zoom',substring(imgpath,37,' at line 6

I also tried:
LOAD DATA INFILE '/tmp/thumblst.out' INTO TABLE images
  LINES TERMINATED BY '\n'
  (imgpath)
  SET part_num = substring(imgpath,44,9),
      thumbimg = load_file(imgpath)
      zoomimg = load_file(replace(imgpath,'thumb','zoom' ));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'zoomimg = load_file(replace(imgpath,'thumb','zoom' ))' at line 6

Please let me know what I am doing wrong.

Thanks

Norm
ASKER CERTIFIED SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of npollock
npollock

ASKER

Wow, shows you how when you are looking at something too closely, you miss the forest for the trees.  Thanks for your help.
Happens to all of us. Glad to help  :)