How to insert images in blob in mysql table using only sql syntax (without PHP)?

Question

Hi,I am new to SQL and I wanted to store images in the database.I already created a column with blob data type and tried to execute the following statement as given here

INSERT INTO `abc`
    (`img`)
    SELECT  
        BulkColumn FROM OPENROWSET(
            Bulk 'C:\Users\adity\Desktop\New folder\a.png', SINGLE_BLOB) AS BLOB

which gives error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( Bulk C:\Users\name\Desktop\New folder\a.png, SINGLE_BLOB) AS BLOB' at line 4

I also tried following code as given here

insert into table `abc`(`img`) values('C:\Users\name\Desktop\New folder\an.jpg') where id=1;

which gives the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table abc(img) values('C:\Users\adity\Desktop\New folder\an.jpg') where id=1' at line 1

So please suggest me how to store images in a blob without using php,etc and simply using simple sql insert statement.I am using wamp server for my database.

I know that I should use file system for images instead of using database.But what does a file system actually mean.Does it mean a file or image hosting site whose address will be stored in database.

please don't tag sql-server when the question is only about mysql! — user4622594
– user4622594, Commented Dec 17, 2015 at 7:19
Possible duplicate of Upload image directly through mySQL Command Line — Gerald Schneider
– Gerald Schneider, Commented Dec 17, 2015 at 7:30

aceraven777 · Accepted Answer · 2015-12-17 07:25:35Z

10

I think that command is a MSSQL syntax. Try this command:

INSERT INTO `abc`
(`img`)
VALUES
(LOAD_FILE('C:/Users/adity/Desktop/New folder/a.png'))

This command stores image as a BLOB

answered Dec 17, 2015 at 7:25

aceraven777

4,5864 gold badges39 silver badges60 bronze badges

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

2 Comments

xxx Over a year ago

The statement executed successfully and now table looks something like this.Is it correct and what format is it?

aceraven777 Over a year ago

Yes that's the correct format of the BLOB. Are you expecting an image will show up there? That's not how it works. You can use this BLOB to display the image. You can refer here how to display BLOB using PHP. stackoverflow.com/questions/20556773/…

Kona Suresh · Accepted Answer · 2018-03-23 11:10:47Z

4

Through Mysql workbench, its very easy to load images into database using the following steps.

Right click on the value of the (blob)column in the table and select "Load value from file".
Then we can provide the image path in the system.
Then it will converted into byte array and stored it automatically.
finally save the changes of the table.

answered Mar 23, 2018 at 11:10

Kona Suresh

1,8541 gold badge16 silver badges27 bronze badges

Comments

tk_ · Accepted Answer · 2019-12-12 15:45:53Z

0

Below works for me,

However, 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,

INSERT INTO `abc`
(`img`)
VALUES
(LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/an.jpg'));

Hope this helps.

edited Dec 12, 2019 at 15:45

answered Oct 1, 2018 at 2:59

tk_

17.5k9 gold badges88 silver badges90 bronze badges

3 Comments

RiggsFolly Over a year ago

WHERE id=1 ??

tk_ Over a year ago

Im was answering the question. :D

RiggsFolly Over a year ago

An INSERT query does NOT have a WHERE clause

Collectives™ on Stack Overflow

How to insert images in blob in mysql table using only sql syntax (without PHP)?

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related