Error when using the jdbcType NVARCHAR with H2


mwhesse@...
 

When mapping a column with java type String and using jdbcType="NVARCHAR" in the @Column annotation we receive an error that the type is not supported by our H2 datastore.

We looked at the H2Adapter and it does register NVARCHAR as a supported JDBCType, but when DN queries the store metadata and retrieves the supported JDBCTypeInfos NVARCHAR is not returned by the H2 driver and subsequently DN unregisters the type from the supported types. Yet H2 claims to support NVARCHAR.

How can we use the JDBC type NVARCHAR with H2 and DN?

Using the latest DN 5.2 version and H2 1.4.200. 


Andy
 

So does H2 actually claim to support "NVARCHAR" ? By that I mean in its response to DatabaseMetadata.getTypeInfo()?
You can easily run DataNucleus SchemaTool in "dbinfo" mode, and get output like the examples in this folder.

If it doesn't actually list it, but would allow a table to be defined using it, then it would be possible to artificially add it to the DataNucleus H2 adapter (see here for how we do it for UUID and GEOMETRY types). If it doesn't return it itself you could get the code and contribute something like that perhaps, via a Pull Request


mwhesse@...
 

Yes H2 does indeed claim support for NVARCHAR:

https://h2database.com/html/datatypes.html?highlight=NVARCHAR&search=NVARCHAR#varchar_type

But it fails to expose it via it's JDBC driver as supported JDBC Type.

Ok thanks on the hint with the custom adapter, we will try that approach and if it works we will submit a PR.


mwhesse@...
 

A committer on the H2 project suggested that "Actually JDBC metadata is not a reliable source of information in all or almost all drivers, don't trust it.". [1]

Maybe the person is right and we should just do what we think H2 can do, not what H2 actually tells us? In the case of H2 that would mean to not ask the driver I guess?

[1] https://github.com/h2database/h2database/issues/3050


mwhesse@...
 

Thanks for your support, below works for us.

SQLTypeInfo sqlType = new H2TypeInfo(
        "NVARCHAR", // String typeName
        (short)Types.NVARCHAR, // short dataType
        2147483647, // int precision
        "'", // String literalPrefix
        "'", // String literalSuffix
        "LENGTH", // String createParams
        1, // int nullable
        true, // boolean caseSensitive
        (short)3, // short searchable 
        false, // boolean unsignedAttribute
        false, // boolean fixedPrecScale
        false, // boolean autoIncrement
        "NVARCHAR", // String localTypeName
        (short)0, // short minimumScale
        (short)0, // short maximumScale
        0 // int numPrecRadix
);
addSQLTypeForJDBCType(handler, mconn, (short)Types.NVARCHAR, sqlType, true);    


mwhesse@...
 

Interesting outcome in the discussion with Evgenij, H2 accepts NVARCHAR but doesn't directly support it. You can use it in H2 DDL though, will just be converted to VARCHAR.

Wondering if it would be better to use "VARCHAR" as local type name in our custom JDBC type above?


Andy
 
Edited

"Actually JDBC metadata is not a reliable source of information in all or almost all drivers, don't trust it."

Says much, about the attitude in the "database community" to a standard that was intended to limit the amount of hardcoding of database specific nonsense in downstream software ("some other JDBC driver churns out untrustworthy results from this so we don't have to"). As do his later comments regarding "good frameworks" (attempt to distract attention from the output from JDBC and whether JDBC provides what is required to express his databases support) ... aka unconstructive. Needless to say I won't be having anything to do with such crap.


If they simply treat NVARCHAR as VARCHAR and seemingly have no plan of ever supporting it, then you should use VARCHAR.


mwhesse@...
 

Ok, thanks Andy!

Will change the local type as suggested.