# 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();

Parent topic: Data types