LOAD_FUNCTION in MySQL shows an invalid identifier

Question

I created a table namely pictures to insert images into the table. After using the insert command and load my picture with load_function it's showing an invalid identifier.

CREATE table pics(id number(10),pics1 blob);

INSERT INTO pics 
values(1,LOAD_FUNCTION('C:\Users\Vandana\Desktop\face\my_image.jpg'));

Answer 1

Your issue is caused by your CREATE TABLE statement.

number is not a valid MySQL column data type, you should use INTEGER(10) SIGNED [sic].

CREATE TABLE pics(id INTEGER(10) SIGNED, pics1 BLOB);

However NUMBER is a valid data type for Oracle Database, which is very much so different from MySQL.

LOAD_FUNCTION is not a valid MySQL function [sic].

I believe you are wanting LOAD_FILE, which will retrieve the file's contents for the blob column.

You should also use double backslashes when referencing Windows file paths, to avoid escape sequence characters like \n.

INSERT INTO pics(id, pics1) 
VALUES(1, LOAD_FILE('C:\\Users\\Vandana\\Desktop\\face\\my_image.jpg'));

Please keep in mind that LOAD_FILE only has access to the files located on the system and that the user/group running the database service has read permissions for.
See secure_file_priv for more details.

Answer 2

In MySQL is better to use LOAD_FILE command:

MySQL LOAD_FILE() reads the file and returns the file contents as a string, which in your case will be a blob

CREATE table pics(id number(10),pics1 blob); 

INSERT INTO pics 
values(1, LOAD_FILE('C:\Users\Vandana\Desktop\face\my_image.jpg'));

Remembering: You need the right read privileges on your file.

You can refer: More complete answer with load_file()