BLOB data type

A BLOB (binary large object) is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.

The length is given in bytes for BLOB unless one of the suffixes K, M, or G is given, relating to the multiples of 1024, 1024*1024, and 1024*1024*1024 respectively.

Note: Length is specified in bytes for BLOB.

Syntax


{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]

Default

A BLOB without a specified length is defaulted to two gigabytes (2,147,483,647).

Corresponding compile-time Java type

java.sql.Blob

JDBC metadata type (java.sql.Types)

BLOB

Use the getBlob method on the java.sql.ResultSet to retrieve a BLOB handle to the underlying data.

Related information

See Mapping of java.sql.Blob and java.sql.Clob interfaces.

Examples

create table pictures(name varchar(32) not null primary key, pic blob(16M));

-- find all logotype pictures
select length(pic), name from pictures where name like '%logo%';

-- find all image doubles (blob comparisons)
select a.name as double_one, b.name as double_two
from pictures as a, pictures as b
where a.name < b.name
and a.pic = b.pic
order by 1,2;

Using an INSERT statement to put BLOB data into a table has some limitations if you need to cast a long string constant to a BLOB. (See String limitations.) You may be better off using a binary stream, as in the following code fragment.

        String url = "jdbc:derby:blobby;create=true";
        Connection conn = DriverManager.getConnection(url);

        Statement s = conn.createStatement();
        s.executeUpdate(
            "CREATE TABLE images (id INT, img BLOB)");

        // - first, create an input stream
        InputStream fin = new FileInputStream("image.jpg");

        PreparedStatement ps = conn.prepareStatement(
            "INSERT INTO images VALUES (?, ?)");
        ps.setInt(1, 1477);

        // - set the value of the input parameter to the input stream
        ps.setBinaryStream(2, fin);
        ps.execute();

        // --- reading the columns
        ResultSet rs = s.executeQuery(
            "SELECT img FROM images WHERE id = 1477");
        byte buff[] = new byte[1024];

        while (rs.next()) {
            Blob ablob = rs.getBlob(1);
            File newfile = new File("newimage.jpg");

            InputStream is = ablob.getBinaryStream();

            FileOutputStream fos = 
                new FileOutputStream(newfile);

            for (int b = is.read(buff); b != -1; b = is.read(buff)) {
                fos.write(buff, 0, b);
            }

            is.close();
            fos.close();
        }
        s.close();
        ps.close();
        rs.close();
        conn.close();