Determine the Height and the Width of a BLOB

Question

I realized that the images files of the some people are directed landscape, whereas all of them should be portrait, in our database. So, I need to determine which file has width more than its height.

I wonder if there is any method to get the height and width of BLOB type columns such like dbms_lob.getlength function which returns the number of characters (bytes) in the CLOB/BLOB column.

Answer 1

A BLOB is binary data - it does not intrinsically have a format (such as JPEG/PNG/BMP) and as such is not implicitly an image and asking what its width/height is does not make sense.

What you need to do is take the binary data (a BLOB) to from its (unknown) binary format (i.e. JPG/PNG/BMP/etc.) and use an Image reader to read the dimensions from the file's meta-data (so you don't have to load the entire file).

You could write a Java class that has a function that takes a BLOB/binary stream and the image format and then uses ImageIO or ImageReader & ImageInputStream (for example, as the first hits I found on reading images from binary data; there will be other solutions/libraries) extract the dimensions from the header [1, 2] and return it.

Then, to load that class into the Oracle database, use the loadjava utility or CREATE OR REPLACE AND COMPILE JAVA SOURCE (example for uncompressing zipped strings stored in an Oracle BLOB).

Then write an SQL function to wrap the Java implementation so that it passes the BLOB to the Java function and returns the width or the height (or a struct containing both values).

---

Java Code:

import java.io.IOException;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.Iterator;
import javax.imageio.ImageIO;
import javax.imageio.ImageReader;
import javax.imageio.stream.ImageInputStream;
import javax.imageio.stream.MemoryCacheImageInputStream;

public class ImageMetaDataReader {
    public static Integer getHeight(
            final Blob   blob,
            final String fileType
    ) throws SQLException
    {
        Iterator<ImageReader> iter = ImageIO.getImageReadersBySuffix( fileType );
        while(iter.hasNext())
        {
            ImageReader reader = iter.next();
            try
            {
                ImageInputStream stream = new MemoryCacheImageInputStream( blob.getBinaryStream() );
                reader.setInput(stream);
                return reader.getHeight(reader.getMinIndex());
            } catch ( IOException e ) {
            } finally {
                reader.dispose();
            }
        }
        return null;
    }

    public static Integer getWidth(
            final Blob   blob,
            final String fileType
    ) throws SQLException
    {
        Iterator<ImageReader> iter = ImageIO.getImageReadersBySuffix( fileType );
        while(iter.hasNext())
        {
            ImageReader reader = iter.next();
            try
            {
                ImageInputStream stream = new MemoryCacheImageInputStream( blob.getBinaryStream() );
                reader.setInput(stream);
                return reader.getWidth(reader.getMinIndex());
            } catch ( IOException e ) {
            } finally {
                reader.dispose();
            }
        }
        return null;
    }
}

Testing:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.SQLException;

public class MockBlob implements Blob {
    private final File file;

    public MockBlob(
            final File file
    )
    {
        this.file = file;
    }

    @Override
    public long length() throws SQLException {
        return file.length();
    }

    @Override
    public InputStream getBinaryStream() throws SQLException {
        try
        {
            return new FileInputStream( this.file );
        }
        catch( FileNotFoundException e )
        {
            throw new SQLException( e.getMessage() );
        }
    }

    @Override public byte[] getBytes(long pos, int length) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); }
    @Override public long position(byte[] pattern, long start) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); }
    @Override public long position(Blob pattern, long start) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); }
    @Override public int setBytes(long pos, byte[] bytes) throws SQLException { throw new UnsupportedOperationException("Not supported yet.");  }
    @Override public int setBytes(long pos, byte[] bytes, int offset, int len) throws SQLException { throw new UnsupportedOperationException("Not supported yet.");  }
    @Override public OutputStream setBinaryStream(long pos) throws SQLException { throw new UnsupportedOperationException("Not supported yet.");  }
    @Override public void truncate(long len) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); }
    @Override public void free() throws SQLException { throw new UnsupportedOperationException("Not supported yet."); }
    @Override public InputStream getBinaryStream(long pos, long length) throws SQLException { throw new UnsupportedOperationException("Not supported yet."); }
}

import java.io.File;
import java.sql.Blob;
import java.sql.SQLException;

public class ImageTest {
    public static void main(
            final String[] args
    ) throws SQLException
    {
        File file = new File( "/path/to/test.png" );
        Blob blob = new MockBlob( file );
        System.out.println(
                "height: "
                + ImageMetaDataReader.getHeight( blob, "png" )
        );
        System.out.println(
                "width: "
                + ImageMetaDataReader.getWidth( blob, "png" )
        );
    }
}

SQL:

CREATE AND COMPILE JAVA SOURCE NAMED "ImageMetaDataReader" AS
<the java code from above>
/

CREATE FUNCTION getImageHeight(
  file     IN BLOB,
  fileType IN VARCHAR2
) RETURN NUMBER
AS LANGUAGE JAVA
name 'ImageMetaDataReader.getHeight( java.sql.Blob, String) return Integer';
/

CREATE FUNCTION getImageWidth(
  file     IN BLOB,
  fileType IN VARCHAR2
) RETURN NUMBER
AS LANGUAGE JAVA
name 'ImageMetaDataReader.getWidth( java.sql.Blob, String) return Integer';
/

(The code is untested in an Oracle database as I don't have an instance to hand at the moment.)