Sunday, March 20, 2011

How do I store a picture in MySQL?

I want to store an image in a MySQL database. I have created a table with a BLOB datatype, but now how do I store the image in this table?

From stackoverflow
  • read the content of the file (BINARY) and insert it in insert \ update MYSQL query

    a lot of example in google :

    http://www.java2s.com/Code/Java/Database-SQL-JDBC/InsertpicturetoMySQL.htm

    http://www.roseindia.net/jdbc/save%5Fimage.shtml

    http://www.jguru.com/faq/view.jsp?EID=1278882

  • You may want to check out the following example:

    From java2s.com: Insert picture to MySQL:

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class InsertPictureToMySql {
      public static void main(String[] args) throws Exception, IOException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/databaseName", "root", "root");
        String INSERT_PICTURE = "INSERT INTO MyPictures (photo) VALUES (?)";
    
        FileInputStream fis = null;
        PreparedStatement ps = null;
        try {
          conn.setAutoCommit(false);
          File file = new File("/tmp/photo.png");
          fis = new FileInputStream(file);
          ps = conn.prepareStatement(INSERT_PICTURE);
          ps.setBinaryStream(1, fis, (int) file.length());
          ps.executeUpdate();
          conn.commit();
        } finally {
          ps.close();
          fis.close();
        }
      }
    }
    

    MySQL Table:

    CREATE TABLE MyPictures (
       photo  BLOB
    );
    

    If the image is located on your MySQL server host, you could use the LOAD_FILE() command from a MySQL client:

    INSERT INTO MyPictures (photo) VALUES(LOAD_FILE('/tmp/photo.png'));
    
    Karthik.m : Can we insert the image using simple query language?
    Daniel Vassallo : Yes, you may want to try using the `LOAD_FILE()` function: `INSERT INTO mytbl (image_data) VALUES(LOAD_FILE('/tmp/myimage.png'));` (http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-CHP-17-SECT-7.html)
    Karthik.m : Its not working mate....
    Daniel Vassallo : Karthik, what is not working?... The `LOAD_FILE()` method? If yes, make sure that the file is readable by mysql, and also make sure that your mysql user has the `FILE` privilege.
    Daniel Vassallo : To grant the `FILE` privilege, log-in as root and execute `GRANT FILE ON *.* TO 'mysql_user'@'localhost'`.
    Hari : @Daniel : how to check whether my MySQl has File privilege, if i am not having it how to give the privilege and also how to store the image file in server host?
    Daniel Vassallo : @Hari: To check the privileges of the current user, log-in with your user in mysql `mysql -h localhost -u user -p` and execute `SHOW GRANTS;`. To grant the `FILE` privilege, please check my previous comment.
    Hari : @Daniel : how to put the image file into the server host?
    Daniel Vassallo : @Hari: To put the image on the server host, you will have to upload it yourself (or through a script) on your server's file system. In fact, this is not the ideal method to insert images from a web page, since the uploading to the file system is useless.
    Hari : @Daniel : so is it better to add a image into server rather than a adding it into the database right......
    Daniel Vassallo : @Karthik: There is an issue on the MySQL bug-tracker http://bugs.mysql.com/bug.php?id=38403, which is reporting the same behaviour that you are experiencing. Apparently one user solved it by stopping apparmor (which is a security service like selinux).
    Daniel Vassallo : @Hari: You may want to check out this Stack Overflow post: http://stackoverflow.com/questions/3748/
    Hari : @Daniel :ya thanks .. i saw the same post before coming to this one... i thought this post is also having some similarities to that one and also it that one no one told how to insert a image through a query as u have mentioned...
    Hari : @Down voter: Canu post the reason for Downvoting!!!!!!!!!!!
    Daniel Vassallo : @Hari: It doesn't look like the question or any of the answers were down-voted :)
    Hari : @Daniel :actually the number of up votes was 5 before...and now its 4..just wanted to know that whether any other posible answer is also there...
    Hari : and now its 3...
    Daniel Vassallo : @Hari: Someone undid the upvote then. (Not a down-vote)
    Hari : okie.... thanks for the information provided...it very helpful to me...

0 comments:

Post a Comment