Jump to content

How to insert image directly in mysql using command?


prohor

Recommended Posts

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.