Getting error while inserting blob datatype value into mysql table

Question

I am trying to enter data into mysql table Fruitbox .But while entering data I am getting error Fruitpdf cannot be null. I created fruitbox table as follows:

Fruitname(varchar(100), Fruitpdf(blob), Fruitprice(int)

Fruitpdf column contains pdf files of fruit. My file mango.pdf in desktop is size of 190 KB.

I wrote following query but getting error "Fruitpdf cannot be null" while executing sql statement.

INSERT INTO Fruitbox (Fruitname, Fruitpdf, Fruitprice) VALUES ('Mango', LOAD_FILE('C:\Users\Tom\Desktop\mango.pdf'), '100');

Where I am wrong ? Any help is much appreciated. Thanking you

You should start by reading the requirements for LOAD_FILE()... "If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL" — Phil
– Phil, Commented Jul 16, 2019 at 3:41
ok I went through this link but could not find solution stackoverflow.com/questions/23575836/… — Tom
– Tom, Commented Jul 16, 2019 at 3:43

Mark Willis · Accepted Answer · 2019-07-17 06:59:53Z

1

MySQL treats a backslash in a string as an escape character. You need to use a double backslash :

LOAD_FILE('C:\\Users\\Tom\\Desktop\\mango.pdf')

See https://dev.mysql.com/doc/refman/8.0/en/string-literals.html

Edit : If it still doesn't work :

Is the file on the MySQL server ?
Is the 'secure_file_priv' variable set ? (SHOW VARIABLES LIKE 'secure_file_priv';) If so, you will need to place the file under that directory.
Does your user have the FILE privilege ?

edited Jul 17, 2019 at 6:59

answered Jul 16, 2019 at 7:15

Mark Willis

86712 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tom Over a year ago

I am getting that error still...is there any wrong in my syntax ?

Mark Willis Over a year ago

@Tom, see update to answer. The syntax is ok, but it would be better to change the '100' string to 100 as the target column wants an int.

Collectives™ on Stack Overflow

Getting error while inserting blob datatype value into mysql table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related