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/171 0-0078.jpg
/var/www/html/bigtmp1/img/ item/thumb /17/10/171 0-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,3 7,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,3 7,' 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','z oom' ));
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','z oom' ))' at line 6
Please let me know what I am doing wrong.
Thanks
Norm
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/
/var/www/html/bigtmp1/img/
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
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
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,
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,
Please let me know what I am doing wrong.
Thanks
Norm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Happens to all of us. Glad to help :)
ASKER