MySQL Bugs: #24594: BlobFromLocator's blobColumnName is derived incorrecly

Bug #24594	BlobFromLocator's blobColumnName is derived incorrecly
Submitted:	26 Nov 2006 14:56	Modified:	19 Dec 2007 14:28
Reporter:	Timo Westkämper	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S4 (Feature request)
Version:	5.0.4	OS:	Any
Assigned to:		CPU Architecture:	Any
Triage:	D5 (Feature request)

Description:
The blobColumnName is derived currently in the following way :

this.blobColumnName = quotedId + this.creatorResultSet.getString(blobColumnIndex) + quotedId;
        
Instead of getting the value of the column the name should be used. The suggested replacement works for cases where the original column name is used in SELECT and also for some alias usage.
        

How to repeat:
Blob usage with emulateLocators on. I got the problem with Hibernate 3.2.0 and Opensymphony Compass 1.0.1.

With Opensymphony Compass there were also alias problems when the original column was quoted in the SELECT clause :

e.g. SELECT 'a' as x

In this case the original column name was not accessible from the result set metadata

Suggested fix:
One correct way would be

this.blobColumnName = quotedId + creatorResultSet.fields[blobColumnIndex - 1].getOriginalName() + quotedId;

I just realized that e.g. Opensymphony Compass 1.0.1 (JDBC based Lucene) is dependent on the bug and that Hibernate 3.2.0 depends on the fixed behaviour.

A behaviour satisfying both cases would be to

* use the original name of the blob column, if the original name is available

this.blobColumnName = quotedId + creatorResultSet.fields[blobColumnIndex -
1].getOriginalName() + quotedId;  

* use the string value of the blob column, if not

this.blobColumnName = quotedId +
this.creatorResultSet.getString(blobColumnIndex) + quotedId;

Hi Timo and thanks for your problem report. It seems to me that your test violates restrictions noted in the manual:

       JDBC API Implementation Notes
This section gives details on a interface-by-interface level about how certain implementation decisions may affect how you use MySQL Connector/J.
• Blob
The Blob implementation does not allow in-place modification (they are copies, as reported by the DatabaseMetaData.locatorsUpdateCopies() method). Because of this, you should use the corresponding PreparedStatement.setBlob() or ResultSet.updateBlob() (in the case of updatable result sets) methods to save changes back to the database. Starting with Connector/J version 3.1.0, you can emulate Blobs with locators by adding the property 'emulateLocators=true' to your JDBC URL. 

* You must then use a column alias with the value of the column set to the actual name of the Blob column in the SELECT that you write to retrieve the Blob.* The SELECT must also reference only one table, the table must have a primary key, and the SELECT must cover all columns that make up the primary key.

The driver will then delay loading the actual Blob data until you retrieve the Blob and call retrieval methods (getInputStream(), getBytes(), and so forth) on it.

Concerning the following requirement

"* You must then use a column alias with the value of the column set to the
actual name of the Blob column in the SELECT that you write to retrieve the
Blob.* The SELECT must also reference only one table, the table must have a
primary key, and the SELECT must cover all columns that make up the primary
key."

I just don't understand what for this strange behaviour? I think the SQL queries should be the same independent of the Blog usage type.

I guess the strange alias usage was a way to supply the blob column name to the BlobFromLocator implementation. Instead of supplying the string, the column name can be obtained from the ResultSet metadata.

More of a feature request after all.

Timo, I can agree with S4 (feature request) but will have to consult with colleagues.

The entire reason this feature requires the column name is so that the result set *doesn't* return the blob itself. It's working as intended. It's a hack to support those who want to "stream" a blob back to the client, even though the server doesn't support it.