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;

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();
    }

  }
}

---

Thanks i will give it try

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

Rest assured that I would never have posted it if I wasn't sure that it worked :cool:

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();
    }
  }
}

---

Very good,I also need it.
Most onlie HTML editor only support upload the images into directory,don't support insert them into database