[CONJ-28] native query returns strange object instead of String Created: 2013-03-07 Updated: 2013-03-08 Resolved: 2013-03-08 |
|
| Status: | Closed |
| Project: | MariaDB Connector/J |
| Component/s: | None |
| Affects Version/s: | 1.1.0 |
| Fix Version/s: | 1.1.2 |
| Type: | Bug | Priority: | Major |
| Reporter: | Tomas Gustavsson | Assignee: | Vladislav Vaintroub |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Environment: |
Ubuntu 12.04, JBoss Application Server EAP 5.1.2, MySQL-Galera 5.5.29, OpenJDK 1.6.0_27 |
||
| Attachments: |
|
| Description |
|
MariaDB Java connector fails where MySQL connector, Postgres, Oracle etc works fine. I have a JPA bean with a simple method: public static CertificateInfo findFirstCertificateInfo(EntityManager entityManager, String issuerDN, String serialNumber) { String fingerprint = (String) fields[0]; else { updateTime = ValueExtractor.extractLongValue(fields[11]); } ret = new CertificateInfo(fingerprint, cafp, serialNumber, issuerDN, subjectDN, status, type, expireDate, revocationDate, @SqlResultSetMapping(name = "CertificateInfoSubset2", columns = { @ColumnResult(name = "fingerprint"), @ColumnResult(name = "subjectDN"), @ColumnResult(name = "cAFingerprint"), @ColumnResult(name = "status"), @ColumnResult(name = "type"), @ColumnResult(name = "expireDate"), @ColumnResult(name = "revocationDate"), @ColumnResult(name = "revocationReason"), @ColumnResult(name = "username"), @ColumnResult(name = "tag"), @ColumnResult(name = "certificateProfileId"), @ColumnResult(name = "updateTime") }), The table looks like: CREATE TABLE CertificateData ( Running the query I get: java.lang.ClassCastException: [B cannot be cast to java.lang.String CertificateData.java:760 is the line from above: The System.out.println debug output prints strange things: |
| Comments |
| Comment by Vladislav Vaintroub [ 2013-03-07 ] | ||
|
Would it be correct to say that you expect ResultSet.getObject() to return String for varbinary datatype? (cAFingerprint is VARCHAR(250) BINARY, thus I ask) | ||
| Comment by Tomas Gustavsson [ 2013-03-07 ] | ||
|
Yes that is correct. It is a String value in java code and Hibernate maps that to a VARCHAR(250) BINARY by default in MySQL (a TEXT in Psql and a VARCHAR2(255 byte) in Oracle) | ||
| Comment by Vladislav Vaintroub [ 2013-03-07 ] | ||
|
Also, can you please paste STDOUT of your test when you use MySQL ConnectorJ? Thanks! | ||
| Comment by Tomas Gustavsson [ 2013-03-07 ] | ||
|
Or it might be an old JBoss default mapping from ages ago...not 100% sure about hibernates default actually. Anyhow that is the mapping we use for String and it has worked quite well so far | ||
| Comment by Tomas Gustavsson [ 2013-03-07 ] | ||
|
STDOUT output using mysql-connector-java-5.1.18-bin.jar 16:39:09,584 INFO [STDOUT] object: fa1db241cf20aee34a42a3ea887ebba422aa22e7 | ||
| Comment by Vladislav Vaintroub [ 2013-03-07 ] | ||
|
perhaps you can try 1.1.1 .There was a change related to consistency with ConnectorJ wrt binary types/metadata/getObject(). https://mariadb.atlassian.net/browse/CONJ-20. I'm not sure that helps, but just so we're on the same page Generally, "VARCHAR BINARY" for String does seem strange, it seems great for byte array. If "BINARY" were ommited , yes, VARCHAR is good for string. | ||
| Comment by Tomas Gustavsson [ 2013-03-07 ] | ||
|
Tested with 1.1.1, same result. | ||
| Comment by Tomas Gustavsson [ 2013-03-07 ] | ||
|
Sounds like string in the MySQL docs: According to http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html The BINARY and VARBINARY data types are distinct from the CHAR BINARY and VARCHAR BINARY data types. For the latter types, the BINARY attribute does not cause the column to be treated as a binary string column. Instead, it causes the binary collation for the column character set to be used, and the column itself contains nonbinary character strings rather than binary byte strings. For example, CHAR(5) BINARY is treated as CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin, assuming that the default character set is latin1. This differs from BINARY(5), which stores 5-bytes binary strings that have no character set or collation. For information about differences between nonbinary string binary collations and binary strings, see Section 10.1.7.6, “The _bin and binary Collations”. | ||
| Comment by Vladislav Vaintroub [ 2013-03-07 ] | ||
|
http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-type-conversions.html Table 22.25. MySQL Types to Java Types for ResultSet.getObject() VARCHAR(M) [BINARY] VARCHAR java.lang.String (unless the character set for the column is BINARY, then byte[] is returned. | ||
| Comment by Tomas Gustavsson [ 2013-03-07 ] | ||
|
hehe. Actually my show create table gives:
Looks like string? | ||
| Comment by Vladislav Vaintroub [ 2013-03-07 ] | ||
|
I'll run some experiments and tell my findings. | ||
| Comment by Vladislav Vaintroub [ 2013-03-07 ] | ||
|
Oh I see. MariaDB JDBC handles "varbinary" and "varchar binary" as same type. ConnectorJ handles them differently (though documentation is misleading here) | ||
| Comment by Tomas Gustavsson [ 2013-03-08 ] | ||
|
I'll be more than happy to test a snapshot. | ||
| Comment by Vladislav Vaintroub [ 2013-03-08 ] | ||
|
If you are unable to build yourself (using bzr to get the sources, and maven to build,as described here https://kb.askmonty.org/en/about-the-mariadb-java-client/) , in this case you can used attached file, build by myself. | ||
| Comment by Tomas Gustavsson [ 2013-03-08 ] | ||
|
Tested the new version. It seems to work just fine. Awesome! Thanks, nice to be able to move all Maria. |