Inserting image to MySQL Database

[Aravind Prasad]

Dear Ranchers,

I have a mysql database table called hrms_photo. The table details are shows below

--------------------------
column name | datatype   |
--------------------------
emp_id      | varchar(10)|
emp_photo   |BLOB        |
--------------------------

I have the following program for uploading the image to MySQL database.

public int imageInsert(String filename,String emp_id)
{int n=0;
try
        {   ImageIcon icon = new ImageIcon(filename);
        Image image = icon.getImage();
BufferedImage bImage = new BufferedImage(image.getWidth(null),image.getHeight(null),BufferedImage.TYPE_INT_RGB);
            Graphics bg = bImage.getGraphics();
            bg.drawImage(image,0,0,null);
            bg.dispose();
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            ImageIO.write(bImage,"jpeg",out);
            byte[] buf = out.toByteArray();
            // setup stream for blob
            ByteArrayInputStream inStream = new ByteArrayInputStream(buf);
            // get or create a connection here
            System.out.println("***********File Name **************" + filename);
            System.out.println("***********Employee ID ************" + emp_id);
            System.out.println(inStream);
try
{createConnection();
String query = "insert into hrms_photo (emp_id,emp_photo) values (?, ?)";
            PreparedStatement ps = con.prepareStatement(query);
            ps.setString(1,emp_id);
            ps.setBinaryStream(2,inStream,inStream.available());
            n = ps.executeUpdate();
            close();
            //Call delete function to delte the files which is uploaded
deleteImage(filename);
}
catch(Exception e)
{System.out.println(e);
}
        }
        catch (Exception exc)
        {  // process error
        System.out.println("process error" + exc);
        }
        return n;
}

When I run the program, I cannot insert the image which is larger than 50KB. In the log file, I'm getting the error like this

***********File Name **************d:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\roadFreighters\emp_photos\201107043819Winter.jpg
***********Employee ID ************0000003
java.io.ByteArrayInputStream@842e74
[B]com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'emp_photo' at row 1
[/B]

Please help me to sort this issue Ranchers..

Thanks/Regards

[Paul Sturrock]

YOu probably want to swap to a larger BLOB data type if you are hitting the limit. MySQL also has mediumblob and largeblob.

[Aravind Prasad]

Thankyou Mr.Paul Sturrock ,

I have put mediumblob and it worked fine. But I couldn't find LargeBlob.. Please advise.

Thanks/Regards

[Paul Sturrock]

Sorry, my mistake. Its called longblob.

[Aravind Prasad]

Thankyou very much!!

Regards

[Rob Spoor]

This is also a MySQL setting. I tried your code, but a) it's limit is 64KB on my machine, and b) it just truncated without aborting.