Why do I have to reaccess the database for Array, Blob, and Clob data?

Joe Sam Shirah

Most DBMS vendors have implemented these types via the SQL3 Locator type ( see What is an SQL Locator? and the JDBC 2.1 core API specifies "By default, a JDBC driver should implement the Blob and Clob interfaces using the appropriate locator type." The same is true for the Array interface.

Some rationales for using Locators rather than directly returning the data can be seen most clearly with the Blob type. By definition, a Blob is an arbitrary set of binary data. It could be anything; the DBMS has no knowledge of what the data represents. Notice that this effectively demolishes data independence, because applications must now be aware of what the Blob data actually represents. Let's assume an employee table that includes employee images as Blobs.

Say we have an inquiry program that presents multiple employees with department and identification information. To see all of the data for a specific employee, including the image, the summary row is selected and another screen appears. It is only at this pont that the application needs the specific image. It would be very wasteful and time consuming to bring down an entire employee page of images when only a few would ever be selected in a given run.

Now assume a general interactive SQL application. A query is issued against the employee table. Because the image is a Blob, the application has no idea what to do with the data, so why bring it down, killing performance along the way, in a long running operation?

Clearly this is not helpful in those applications that need the data everytime, but these and other considerations have made the most general sense to DBMS vendors.