troubleshooting Question

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

Avatar of npollock
npollock asked on
MySQL ServerSQL
3 Comments1 Solution415 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros