How to insert image directly in mysql using command?

[prohor]

Hello sir,
I am trying to inset a small image img.png (57.7KB) into mysql database. Table structure is id(int) and image(blob). I directly inserting image can cause problems but I need this for test purpose.

I can insert the image using localhost/phpmyadmin but
when I try command-line using the following command:

insert into image(id,image) values (2,load_file('f:\img.png'));

or

insert into image(id,image) values (2,load_file('f:\\img.png'));
	

error occurs :-
ERROR 1048 (23000): Column ‘image’ cannot be null

I am using windws 10 os and WampServer 3.1.7 64 bit. (mysql 5.7.24)

Please help.

[Barand]

LOAD_FILE

Quote

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.

 

[MPM]

As Barand suggests it looks like you are trying to get a file from a local system to a database.  If that database is remote you will need to get the file to the server (probably using an HTML form).

[prohor]

Thank you sir, @Barand, it is a file less than 20KB when Maximum is 1MB set. And what is or how I can find the  local directory for WAMP server based Mysql server? " 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. " The image is saved in E drive of my local storage under windows 10 Operating system.

 

Thank you once again sir for spending your valuable time to help me.

[prohor]

On 6/26/2019 at 2:35 PM, MPM said:

As Barand suggests it looks like you are trying to get a file from a local system to a database.  If that database is remote you will need to get the file to the server (probably using an HTML form).

Thank you @MPM sir, Yes. The image is saved in E drive of my local storage under windows 10 Operating system. and it is well bellow in size of allowed file size. It is only 20KB. How I can set Mysql to upload from local file storage When My server is running under C drive of the same operating system?

I also do not know how to find the default folder / directory to keep file and upload.
Thank you again sir.

[Barand]

You can see if "secure_file_priv" is set, and what its directory value is (if it is set) with

SELECT @@secure_file_priv;

Is your mysql server on the same pc as that E drive?

(The recommended place for image storage is the server's file system and store its location and other metadata in the database)