View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0006605SymmetricDSNew Featurepublic2024-10-01 23:112024-10-01 23:14
Reporterelong Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.16.0 
Target Version3.16.0 
Summary0006605: Support for cross platform BIT data type size > 1 (bit varying, varbit, bit string, bit array)
DescriptionAdd cross platform support for BIT array style data types with a size greater than 1:

   PostgreSQL - BIT(n) and BIT VARYING(n), unlimited length, unspecified is unlimited, string literal b'101'
   https://www.postgresql.org/docs/current/datatype-bit.html

   MySQL / MariaDB - BIT(n), range of 1 to 64, unspecified is 1, string literal b'101'
   https://dev.mysql.com/doc/refman/8.4/en/bit-type.html

   SQL Anywhere - VARBIT(n), range of 1 to 32767, unspecified is 1, cast( '101' as VARBIT )
   https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.0/dbreference/datatypes-s-3941580.html

Each platform behaves a little differently. MySQL will accept byte[] or an Integer or BigDecimal, but String causes each character's hex code to be encoded -- String will work as expected if the prepared statement uses vendor-specific "b?" syntax in the SQL for the parameter. Postgres will accept byte[] or String. SQL Anywhere accepts at least byte[], other types weren't tested.

Other platforms have BIT, but the size is 1, so it gets mapped as Boolean in JDBC. It might be useful to be backwards compatible so that a BIT(1) acts like a Boolean, but BIT(n) where n > 1 is treated differently.

If hex encoding is chosen as the capture and transport format, then the following code changes would make sense. In the DdlReader for each platform, the mapUnknownJdbcTypeForColumn() can be used to override the type mapping to affect triggers and loading:

} else if ("BIT".equals(typeName)) {
    String columnSize = (String) values.get(getName("COLUMN_SIZE"));
    int size = StringUtils.isNotBlank(columnSize) ? Integer.parseInt(columnSize) : 1; // specified or unspecified size
    if (size > 1) {
        return Types.BINARY;
    } else {
        return super.mapUnknownJdbcTypeForColumn(values);
    }
}

In AbstractDatabasePlatform.getObjectValue(), make sure Hex string has even number of characters, otherwise it should be preceded with a zero (the MySQL hex() function is an example of sourcing odd number of characters):

} else if (encoding == BinaryEncoding.HEX) {
    if (value.length() % 2 == 1) {
        value = "0" + value;
    }
    objectValue = Hex.decodeHex(value.toCharArray());
}

For testing on MySQL, the export_set(bits, on, off, separator, num_bits) function is handy:

select id, export_set(mybits,'1', '0', '', 16) from sites;
Tagsdata type, dialect: mysql/mariadb, dialect: postgresql, dialect: sql anywhere

Relationships

related to 0005949 new Sybase2Postres - BIT VARYING Error 

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2024-10-01 23:11 elong New Issue
2024-10-01 23:11 elong Tag Attached: data type
2024-10-01 23:11 elong Tag Attached: dialect: mysql/mariadb
2024-10-01 23:11 elong Tag Attached: dialect: postgresql
2024-10-01 23:11 elong Tag Attached: dialect: sql anywhere
2024-10-01 23:11 elong Relationship added related to 0005949
2024-10-01 23:14 elong Description Updated View Revisions