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.
Length is specified in bytes for BLOB.
Syntax
{ BLOB | BINARY LARGE OBJECT } [ ( length [{K |M |G }] ) ]
DefaultA BLOB without a specified length is defaulted
to two gigabytes (2,147,483,647).
Corresponding compile-time Java typejava.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 .
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
.) 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();