Inserting image into mySQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Inserting image into mySQL

  1. #1
    Join Date
    Nov 2005
    Posts
    18

    Inserting image into mySQL

    Hi,
    I wonder if any one might be able to help, i am trying to insert a .jpg into a mySQL data base, so far i have tried inserting the file as byte[], i keep on recieveing an error message, stating that the syntax i have used is incorrect,
    is nay one able to help me? i have provided a copy of the create table SQL used as well,

    Thanks in advance.

    public void putFile(int FoodID, File infile){
    try{
    System.out.println(infile.getName());
    FileInputStream is=new FileInputStream(infile);
    byte[] b=new byte[(int)infile.length()];
    is.read(b);
    bt=b;
    //is.close();
    Statement stmt = con.createStatement();
    stmt.executeUpdate("INSERT INTO photo VALUES("+FoodID+",'"+infile.getName()+"',"+bt+")");
    stmt.close();
    con.commit();
    }catch (Exception e){
    System.out.println("error: " + e);
    }
    }


    Create Table Photo(
    Foodin_id INT NOT NULL,
    photoName VARCHAR(150) NOT NULL,
    PRIMARY KEY(Foodin_id, photoName),
    photo MEDIUMBLOB NOT NULL,
    Foreign Key (Foodin_id)
    References Food_Inspect(Foodin_id)) TYPE=INNODB;

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    1,560

    Blobs must be treated as streams

    Check this code, it inserts an image into a mySql db like.

    Here is the table definition
    Code:
    create table image_tab
    (
        image_name     VARCHAR(42)           not null,
        image_content  LONG VARBINARY                ,
        primary key (image_name)
    );
    here is the java code
    Code:
    import java.sql.*;
    import java.io.*;
    
    public class StoreBinary {
      private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
      private Statement stmt = null;
      private Connection conn = null;
      public StoreBinary() {
      }
    
      public void storeImageFile(String fileName) throws Exception {
        if (!connect("test", "root", "")) {
          return;
        }
    
        FileInputStream in = new FileInputStream(fileName);
        int len=in.available();
       
        PreparedStatement pStmt = conn.prepareStatement("insert into image_tab values (?,?)");
        pStmt.setString(1, fileName);
        pStmt.setBinaryStream(2, in, len);
        pStmt.executeUpdate();
        in.close();
        System.out.println("Stored: "+fileName+", length: "+len);
      }
    
      public boolean connect(String dbName, String dbUser, String dbPassword) {
        try {
          Class.forName(driverName);
        }
        catch (ClassNotFoundException ex) {
          ex.printStackTrace();
          return false;
        }
        try {
          conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
                                             dbUser,
                                             dbPassword);
          //stmt = conn.createStatement();
        }
        catch (SQLException ex1) {
          ex1.printStackTrace();
          return false;
        }
        return true;
      }
    
      /**
       * MAIN ***********************
       */
      public static void main(String[] args) {
    
        StoreBinary sb = new StoreBinary();
        try {
          sb.storeImageFile("c:\\tmp\\f128.jpg");
        }
        catch (Exception ex) {
          ex.printStackTrace();
        }
    
      }
    }
    eschew obfuscation

  3. #3
    Join Date
    Nov 2005
    Posts
    18
    Thanks i will give it try

  4. #4
    Join Date
    Nov 2005
    Posts
    18
    Hi

    Thanks for the code it worked!i wownder if you might have similar code to retrieve the image from the db? in any case thanks for your help

  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    1,560
    Rest assured that I would never have posted it if I wasn't sure that it worked

    Here it is, I have made some minor modifications; the table now have three columns, the name, the length and the blob, and I only store the base filename.

    Code:
    import java.sql.*;
    import java.io.*;
    import java.awt.*;
    import java.awt.Image;
    
    
    public class StoreBinary {
      private static String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
      private Statement stmt = null;
      private Connection conn = null;
      public StoreBinary() {
      }
      public static String getBaseName(String fileName) {
        int ix=fileName.lastIndexOf("\\");
        if (ix < 0) return fileName;
        return fileName.substring(ix+1);
      }
      public boolean storeImageFile(String fileName) throws Exception {
        if (!connect("test", "root", "")) {
          return false;
        }
    
        FileInputStream in = new FileInputStream(fileName);
        int len=in.available();
        String baseName=StoreBinary.getBaseName(fileName);
        PreparedStatement pStmt = conn.prepareStatement("insert into image_tab values (?,?,?)");
        pStmt.setString(1, baseName);
        pStmt.setInt(2,len);
        pStmt.setBinaryStream(3, in, len);
        pStmt.executeUpdate();
        in.close();
        System.out.println("Stored: "+baseName+", length: "+len);
        return true;
      }
      public Image getImageFile(String fileName) throws Exception {
        String baseName=StoreBinary.getBaseName(fileName);
        ResultSet rs=stmt.executeQuery("select * from image_tab where image_name='"+baseName+"'");
        if (!rs.next()) {
          System.out.println("Image:"+baseName+" not found");
          return null;
        }
        int len=rs.getInt(2);
        byte [] b=new byte[len];
        InputStream in = rs.getBinaryStream(3);
        in.read(b);
        in.close();
        Image img=Toolkit.getDefaultToolkit().createImage(b);
        System.out.println("Image: "+baseName+" retrieved ok, size: "+len);
        return img;
      }
    
      public boolean connect(String dbName, String dbUser, String dbPassword) {
        try {
          Class.forName(driverName);
        }
        catch (ClassNotFoundException ex) {
          ex.printStackTrace();
          return false;
        }
        try {
          conn = DriverManager.getConnection("jdbc:odbc:" + dbName,
                                             dbUser,
                                             dbPassword);
          stmt = conn.createStatement();
        }
        catch (SQLException ex1) {
          ex1.printStackTrace();
          return false;
        }
        return true;
      }
    
      /**
       * MAIN ***********************
       */
      public static void main(String[] args) {
        String fileName="c:\\tmp\\f128.jpg";
        StoreBinary sb = new StoreBinary();
        try {
          if (sb.storeImageFile(fileName)) {
            // stored ok, now get it back again
            Image img=sb.getImageFile(fileName);
          }
        }
        catch (Exception ex) {
          ex.printStackTrace();
        }
      }
    }
    eschew obfuscation

  6. #6
    Join Date
    Dec 2005
    Posts
    48
    Very good,I also need it.
    Most onlie HTML editor only support upload the images into directory,don't support insert them into database

Similar Threads

  1. Replies: 7
    Last Post: 07-17-2013, 12:47 AM
  2. help regarding image processing
    By laxmi in forum Java
    Replies: 6
    Last Post: 02-03-2006, 01:55 PM
  3. Inserting image
    By raihanul in forum VB Classic
    Replies: 1
    Last Post: 01-04-2006, 08:38 PM
  4. Script for scrolling
    By Mark in forum Web
    Replies: 3
    Last Post: 08-30-2001, 12:45 PM
  5. Inserting Image in access97
    By Mahyar in forum VB Classic
    Replies: 0
    Last Post: 09-12-2000, 02:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center