Inserting image into mySQL

[Bennettd02]

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;

[sjalle]

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

Here is the table definition

create table image_tab
(
    image_name     VARCHAR(42)           not null,
    image_content  LONG VARBINARY                ,
    primary key (image_name)
);

here is the java 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();
    }

  }
}

[Bennettd02]

Thanks i will give it try

[Bennettd02]

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

[sjalle]

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.

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

[jetbrains]

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