Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

  1. 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.
like image 404
xxx Avatar asked Dec 17 '15 07:12

xxx


People also ask

How do I insert an image into a SQL table?

1) Bring up SQL Server Management Studio. 2) Connect, and open the database you want to add the image to. 3) Expand the tables, and either add a new table with an appropriate index field, or right click teh table and select design. 4) Add a field called "myImage", and make its datatype "image".

Can we add images in MySQL?

1 Answer. You can try the below code for inserting an image: INSERT INTO xx_BLOB(ID,IMAGE) VALUES(1,LOAD_FILE('E:/Images/jack. jpg'));


2 Answers

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

like image 110
aceraven777 Avatar answered Oct 06 '22 03:10

aceraven777


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

  1. Right click on the value of the (blob)column in the table and select "Load value from file".
  2. Then we can provide the image path in the system.
  3. Then it will converted into byte array and stored it automatically.
  4. finally save the changes of the table.
like image 40
Kona Suresh Avatar answered Oct 06 '22 04:10

Kona Suresh