Adding Image to a database in Java

Question

I am trying to add an image to a BLOB field in a mysql database. The image is going to be less then 100kb in size. However I am running into problems and was wondering what would be a better way to add this data to the database?

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'Data' at row 1

PreparedStatement addImage = conn.prepareStatement("INSERT INTO Images (Width, Height, Data) VALUES (?,?,?)",Statement.RETURN_GENERATED_KEYS);

Below is the method that I am using to add the image into the database.

public int addImage(Image image) throws SQLException, IllegalArgumentException
{
    this.addImage.clearParameters();
    byte[] imageData = ImageConverter.convertToBytes(image);
    int width = image.getWidth(null);
    int height = image.getHeight(null);
    if (width == -1 || height == -1)
    {
        throw new IllegalArgumentException("You must load the image first.");
    }



    this.addImage.setInt(1, width);
    this.addImage.setInt(2, height);
    this.addImage.setBytes(3, imageData);
    this.addImage.executeUpdate();

    ResultSet rs = this.addImage.getGeneratedKeys();
    rs.next();

    return rs.getInt(1);
}

SQL Definition for the table

After Changing the datafield type to a Mediumblob and attempting to place a 140kb image file into the database i received a different error.

com.mysql.jdbc.PacketTooBigException: Packet for query is too large

Is the problem the way in which i am trying to add the data to the database. Should I take a different approach? If so which way?

It may be worth posting the definition of the table you're trying to add the image to. — David Webb
– David Webb, Commented Mar 8, 2009 at 21:40

Luke Woodward · Accepted Answer · 2009-03-08 21:54:07Z

6

Try using a MEDIUMBLOB instead of a BLOB. BLOB is limited to 64KB whereas a MEDIUMBLOB column can hold 16MB.

See the 'Storage Requirements for String Types' section of this page.

answered Mar 8, 2009 at 21:54

Luke Woodward

65.3k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matthew Flaschen · Accepted Answer · 2009-03-08 23:39:09Z

2

To deal with PacketTooBigException, it looks like you need to tweak a config variable, max_allowed_packet. See http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html . However, by default MySQL 5.1 should give you no problems with up to 1MB . What version of MySQL are you using (both server and client).

answered Mar 8, 2009 at 23:39

Matthew Flaschen

286k53 gold badges523 silver badges554 bronze badges

Comments

Maurice Perry · Accepted Answer · 2009-03-08 21:51:29Z

1

Try setBlob instead of setBytes

answered Mar 8, 2009 at 21:51

Maurice Perry

32.8k9 gold badges72 silver badges97 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:27:42Z

0

Just execute below query at the start of the project...

SET GLOBAL max_allowed_packet = 1024*1024*number of MB

i.e.

SET GLOBAL max_allowed_packet = 1024*1024*14

Also read this.

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Jul 3, 2012 at 23:10

Fahim Parkar

31.8k46 gold badges168 silver badges285 bronze badges

Collectives™ on Stack Overflow

Adding Image to a database in Java

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related