getObject() returns byte[] instead of String for BLOB SUB_TYPE 1 when using octetsAsBytes [JDBC431]

[firebird-automations]

Submitted by: Vjacheslav Borisov (slavb18)

Cannot read BLOB SUB_TYPE 1 columns in eclipselink, getting HEX string of bytes istead of value
Tested on jaybird 2.2.10

Traced problem
1. Intro:
FBResultSetMetaData.getColumnType for BLOB SUB_TYPE 1 returning Types.LONGVARCHAR

        case ISCConstants\.SQL\_BLOB:
            if \(sqlsubtype < 0\)
                return Types\.BLOB;
            else if \(sqlsubtype == 0 \|\| sqlsubtype \> 1\)
                return Types\.LONGVARBINARY;
            else if \(sqlsubtype == 1\)
                return Types\.LONGVARCHAR;

And java.sql.ResultSet.getObject returns value of type byte[]

2. org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject treats Types.LONGVARCHAR as "CLOB"
and tries to get String value from byte[] value returned by ResultSet.getObject

see http://grepcode.com/file/repo1.maven.org/maven2/org.eclipse.persistence/eclipselink/2.6.0/org/eclipse/persistence/internal/databaseaccess/DatabaseAccessor.java

                \} else if \(isClob\(type\)\) \{
                    // EL Bug 294578 \- Store previous value of CLOB so that temporary objects can be freed after conversion
                    Object originalValue = value;
                    value = platform\.convertObject\(value, ClassConstants\.STRING\);

where isClob is:
public static boolean isClob(int type) {
return (type == Types.CLOB) || (type == Types.LONGVARCHAR) || (type == DatabasePlatform.Types_NCLOB) || (type == Types.LONGNVARCHAR);
}

where platform is of type org.eclipse.persistence.platform.database.FirebirdPlatform

Step 3: platform.convertObject traced to org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToString:
Stack trace is:
at org.eclipse.persistence.internal.helper.ConversionManager.convertObjectToString(ConversionManager.java:677)
at org.eclipse.persistence.internal.helper.ConversionManager.convertObject(ConversionManager.java:104)
at org.eclipse.persistence.internal.databaseaccess.DatasourcePlatform.convertObject(DatasourcePlatform.java:179)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject(DatabaseAccessor.java:1313)

see http://grepcode.com/file/repo1.maven.org/maven2/org.eclipse.persistence/org.eclipse.persistence.core/2.6.0/org/eclipse/persistence/internal/helper/ConversionManager.java

    \} else if \(sourceObjectClass == ClassConstants\.APBYTE\) \{
        return Helper\.buildHexStringFromBytes\(\(byte\[\]\)sourceObject\);    

So, in case when columnType==Types.LONGVARCHAR and source object is of type byte[], it is converted as hexdecimal representation of bytes, and
istead of text value stored in blob, i am getting string like D09FD180D0B8D0BDD18F..., and this is the problem

Possible solution is to make FBResultSetMetaData.getColumnType return Types.LONGVARBINARY (or ISCConstants.SQL_BLOB) for BLOB SUB_TYPE 1

If FBResultSetMetaData.getColumnType will return Types.LONGVARBINARY (or ISCConstants.SQL_BLOB), then
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject will go to this branch

                if \(isBlob\(type\)\) \{
                    // EL Bug 294578 \- Store previous value of BLOB so that temporary objects can be freed after conversion
                    Object originalValue = value;
                    value = platform\.convertObject\(value, ClassConstants\.APBYTE\);

where isBlob is:
public static boolean isBlob(int type) {
return (type == Types.BLOB) || (type == Types.LONGVARBINARY);
}

And value returned (byte[]) will be returned as byte[] without string hexdecimal conversion,

and I can write my own @AttributeConverter which will do right conversion:
@OverRide
public String convertToEntityAttribute(byte[] dbData) {
return dbData==null?null:new String(dbData, java.nio.charset.StandardCharsets.UTF_8);
}

To achive this we could have option like octetsAsBytes, but which will return Types.BLOB (or Types.LONGVARBINARY) for BLOB SUB_TYPE 1,

Commits: f682b82 96d62cc

[firebird-automations]

Commented by: @mrotteveel

ResultSet.getObject should return String for a BLOB SUB_TYPE 1 / LONGVARCHAR, not byte[]. I'll see if I can reproduce.

[firebird-automations]

Commented by: @mrotteveel

The problem is in FBField.getObject:

    switch \(requiredType\) \{
        case Types\.CHAR :
        case Types\.VARCHAR :
        case Types\.LONGVARCHAR :
            // check whether OCTETS should be returned as byte\[\]
            if \(isOctetsAsBytes\(\) && field\.sqlsubtype == 1\)
                return getBytes\(\);
            else
                return getString\(\);

For a BLOB SUB_TYPE 1 it should check the scale, not the subtype (for CHAR/VARCHAR subtype is the character set (and 1 is OCTETS), but for BLOB SUB_TYPE 1, the character set is in scale).

[firebird-automations]

Modified by: @mrotteveel

Fix Version: Jaybird 2.2.11 [ 10751 ]

[firebird-automations]

Modified by: @mrotteveel

summary: Cannot read BLOB SUB_TYPE 1 columns in eclipselink => getObject() returns byte[] instead of String for BLOB SUB_TYPE 1 when using octetsAsBytes

[firebird-automations]

Commented by: @mrotteveel

Problem was already fixed in Jaybird 3 by JDBC240. I fixed this for Jaybird 2.2.11, by always returning String for LONGVARCHAR. This means that BLOB SUB_TYPE TEXT CHARACTER SET OCTETS will not be considered for the octetsAsBytes property (but people should use BLOB SUB_TYPE BINARY for that anyway).

[firebird-automations]

Modified by: @mrotteveel

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

[firebird-automations]

Modified by: @mrotteveel

status: Resolved [ 5 ] => Closed [ 6 ]