Updating BLOB from MySQL Workbench

Question

I have a problem with one SQL statement. I am trying to update something in a table which is a Blob field with a pdf. And it's not working.

UPDATE employees 
SET resume = LOAD_FILE('C:\Users\gaby\Desktop\sample_resume.pdf') 
WHERE id = 1;

If I use the above command the result is null. If I use the below command the result is not null, there is a pdf but it is empty.

UPDATE employees 
SET resume = 'C:\Users\gaby\Desktop\sample_resume.pdf'
WHERE id = 1;

I can do this update using JDBC, it works perfect. But I want to do it from workbench too.

Thanks in advance!

score 2 · Answer 1 · answered Feb 21 '19 at 06:32

I was able to get it done by moving the image(fileName.jpg) file first in to below folder(in my case) C:\ProgramData\MySQL\MySQL Server 5.7\Uploads and then I executed below command and it works for me,

update employees set file=LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/fileName.jpg') where id = 1;

Hope this helps.

score 0 · Answer 2 · answered May 28 '18 at 12:48

Check if all conditions described in the manual are given.

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

load_file() returns NULL, (at least) one of the conditions must fail.

Updating BLOB from MySQL Workbench

2 Answers2