9.3.2 BINARY, VARBINARY, and LONGVARBINARY

The JDBC types BINARY, VARBINARY, and LONGVARBINARY are closely related. BINARY represents a small, fixed-length binary value, VARBINARY represents a small, variable-length binary value, and LONGVARBINARY represents a large, variable-length binary value.;;
Unfortunately, the use of these various BINARY types has not been standardized and support varies considerably among the major databases.

The SQL BINARY type corresponding to JDBC BINARY is a nonstandard SQL extension and is only implemented on some databases. It takes a parameter that specifies the number of binary bytes. Thus BINARY(12) defines a 12-byte binary type. Typically, BINARY values are limited to 254 bytes.

The SQL VARBINARY type corresponding to JDBC VARBINARY is a nonstandard SQL extension and is only implemented on some databases. It takes a parameter that specifies the maximum number of binary bytes. Thus VARBINARY(12) defines a binary type whose length may be up to 12 bytes. Typically, VARBINARY values are limited to 254 bytes. When a binary value is assigned to a VARBINARY variable, the database remembers the length of the assigned value and on a SELECT, it will return the exact original value.

Regrettably, there is no consistent SQL type name corresponding to the JDBC LONGVARBINARY type. All the major databases support some kind of very large variable length binary type supporting up to at least a gigabyte of data, but the SQL type names vary. See the table "JDBC Types Mapped to Database-specific SQL Types" on page 134 for some examples.
BINARY, VARBINARY, and LONGVARBINARY can all be expressed identically as byte arrays in the Java programming language. Since it is possible to read and write SQL statements correctly without knowing the exact BINARY data type that was expected, there is no need for programmers writing code in the Java programming language to distinguish among them.

The method recommended for retrieving BINARY and VARBINARY values is ResultSet.getBytes. If a column of type JDBC LONGVARBINARY stores a byte array that is many megabytes long, however, the method getBinaryStream is recommended. Similar to the situation with LONGVARCHAR, this method allows a programmer to retrieve a LONGVARBINARY value as a Java input stream that can be read later in smaller chunks.
The SQL99 BLOB data type, which is described later in this chapter, provides an alternate means of representing large amounts of binary data.