Issue Details (XML | Word | Printable)

Key: JDBC-431
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Vjacheslav Borisov
Votes: 0
Watchers: 0

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

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

Created: 14/Apr/16 06:58 AM   Updated: 20/Aug/16 01:13 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.2.10
Fix Version/s: Jaybird 2.2.11

 Description  « Hide
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

                    } 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(
at org.eclipse.persistence.internal.helper.ConversionManager.convertObject(
at org.eclipse.persistence.internal.databaseaccess.DatasourcePlatform.convertObject(
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.getObject(


        } 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:
    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,

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 14/Apr/16 08:29 AM
ResultSet.getObject should return String for a BLOB SUB_TYPE 1 / LONGVARCHAR, not byte[]. I'll see if I can reproduce.

Mark Rotteveel added a comment - 15/Apr/16 01:37 PM
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();
                    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).

Mark Rotteveel added a comment - 16/Apr/16 12:29 PM
Problem was already fixed in Jaybird 3 by JDBC-240. 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).