PostgreSQL getting image from database

Question

I have some Java code which get's an image from the database and saves it out to a file, however, when I open it in all programs but Photoshop I get an error similar to:

"It may be damaged or use a file format that Preview doesn’t recognize." - This paticular one is from preview on MAC. The database i'm pulling from is PostgreSQL and the column is a bytea.

My code is as follows:

public static void main(String[] args) throws Exception {
        Connection conn = null;
        try {
            conn = ConnectionManager.getConnection();

            String sql = "SELECT content from asset ";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet resultSet = stmt.executeQuery();
            while (resultSet.next()) {

                File image = new File("/Users/esklein/testing"+UUID.randomUUID()+".jpg");
                FileOutputStream fos = new FileOutputStream(image);

                byte[] buffer = new byte[256];

                //
                // Get the binary stream of our BLOB data
                //
                InputStream is = resultSet.getBinaryStream("content");
                while (is.read(buffer) > 0) {
                    fos.write(buffer);
                }

                fos.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        }
    }

Any ideas why this isn't spitting out an image which can be opened in any file? Thanks

Are you positive the image is a jpg? Photoshop might open it because it's smart enough to check the file format for type rather than relying on the extension like most image viewers. Other than that you might be getting garbage at the end of your file if you don't clear your buffer after each write. What happens when the last iterations reads less than 256 bytes? The leftover bytes from the previous read might still be in the buffer and write out as garbage at the end of the file. Again photoshop might be smart enough to ignore that where other viewers may not. — Jberg
– Jberg, Commented Apr 28, 2011 at 18:16
1. Are you sure it's a JPEG file ? look into the first bytes, it should contain a "JFIF" string 2. Can you test if the size of the file is the same as the data length in the db ? select octet_length(...) — leonbloy
– leonbloy, Commented Apr 28, 2011 at 18:17

Mike Baranczak · Accepted Answer · 2011-04-28 18:18:48Z

3

while (is.read(buffer) > 0) {

You're assuming that the read operation always fills the whole buffer. But if the data size is 1001 bytes, and the buffer size is 1000 bytes, then the last read will only get one byte. You need to check the number of bytes that was actually read, and then

fos.write(buffer, 0, bytesRead)

answered Apr 28, 2011 at 18:18

Mike Baranczak

8,4158 gold badges52 silver badges73 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL getting image from database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related