Informix with Hibernate 6.X anyone?

Since Hibernate 6, the InformixDialect class file has been moved out of Hibernate core to the community provided content. Are there anyone with experience in the combination of Hibernate and Informix here? Have you modified or adapted the InformixDialect class? We are having some problems with Hibernate 6 that may look like something is wrong in the default provided InformixDialect class. ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------

Hi, we have used the version from the community dialects package without any issues, but as far as I know from our development, they are using an inherited / slightly modified dialect anyway. Anyway it always depends on the features being used for the application. We are pretty much using basically standard without any fancy types / very limited usage of stored procedures. What kind of issues are you having with the original version ? MARCUS HAARMANN

SQL Query generated by Hibernate version 5.x.x: select varestrom0_.varestr_kode as varestr_1_641_0_, varestrom0_.beskr as beskr2_641_0_, varestrom0_.edi_varestr_kode as edi_vare3_641_0_ from varestrommer varestrom0_ where varestrom0_.varestr_kode=? Which works completely fine.... SQL Query generated by Hibernate version 6.x.x: select null, v1_0.beskr, v1_0.edi_varestr_kode from varestrommer v1_0 where v1_0.varestr_kode=? Which doesn't work and gives SQLGrammarException: could not prepare statement [A syntax error has occurred.] The primary key here is varestr_kode, this is a char(3) field, and is an assigned key with Hibernate. We are still using the legacy hbm.xml based mappings rather than annotation based mappings. The mapping for this table is as follows (package names changed): <?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"; > <hibernate-mapping> <class name="com.example.dal.tgprod.domain.Varestrom" table="varestrommer" lazy="false" > <meta attribute="class-description" inherit="false"> @hibernate.class table="varestrommer" </meta> <id name="varestrKode" type="com.example.hibernate.type.TrimmedString" column="varestr_kode" > <meta attribute="field-description"> @hibernate.id generator-class="assigned" type="com.example.hibernate.type.TrimmedString" column="varestr_kode" </meta> <generator class="assigned" /> </id> <property name="beskr" type="com.example.hibernate.type.TrimmedString" column="beskr" length="70" > <meta attribute="use-in-tostring">true</meta> <meta attribute="field-description"> @hibernate.property column="beskr" length="70" </meta> </property> <property name="ediVarestrKode" type="com.example.hibernate.type.TrimmedString" column="edi_varestr_kode" length="5" > <meta attribute="use-in-tostring">true</meta> <meta attribute="field-description"> @hibernate.property column="edi_varestr_kode" length="5" </meta> </property> <!-- Associations --> </class> </hibernate-mapping> ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------

Hi Øyvind, apart from the question whether or how these two queries could be considered equivalent, selecting NULL without any type information currently isn't allowed in Informix (whether "syntax error" is the best possible error for this might be a separate question.) Unfortunately we cannot conclude any type information from such NULL's position in the projection clause, as that might be the writer's intention only in some cases. I can see that other RDBMS do handle such syntax, even more complex ones where type info is more relevant, like SELECT 1.234 AS a, NULL AS b FROM dual UNION SELECT NULL AS a, 1234 AS b FROM dual;But it's hard to anticipate in what all contexts this had to be made working, or what implications this might be having on client code. Anyway, a feature request shouldn't hurt! Further thoughts? BR, Andreas ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

Hi Øyvind, this does not look like an dialect specific issue but instead related to hibernate core. The dialect has not such a big impact on the query building process in Hibernate. Which exact version are you using? (I checked in our old system, a quite old hibernate 6 (6.1.6) was used). Since I do not know how this TrimmedString class works, I would give it a try with a standard java.lang.String instead in order to make sure the issue is not related to the type. We have got lots (hundreds) of xml definitions which are used without any issues, defining single ids or composite ids, associations etc.. All working without issues. Our Dialect is derived from the community dialect, mostly adding the following functionality: @Override public void initializeFunctionRegistry(QueryEngine queryEngine) { super .initializeFunctionRegistry(queryEngine); CommonFunctionFactory functionFactory = new CommonFunctionFactory(queryEngine); functionFactory.toCharNumberDateTimestamp(); } But even that should not relate to wrong queries. Another try would be to define the same table structure in a "supported" database like postgres / mysql and check what happens at this target when querying some data by key. Also, hibernate.properties might influence the behaviour. Best, MARCUS HAARMANN

Some new findings: Turns out that it Hibernate 6.X is a bit too imprecise. We are using JBoss EAP 8 (currently 8.0), which bundles hibernate core 6.2.13, and that version seems to have this behavior, while for instance Hibernate 6.5 seems to have fixed this. I don't think it will be easy to do brain surgery on JBoss to change this version. JBoss 8.1 (currently in beta) bundles Hibernate 6.6, so should fix the issue. We may decide to wait for that, if it isn't too far away. ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------

Thank you for your answer, but I think a feature request towards IBM to change this will probably take quite a while, and the chances are probably slim. I don't think we can wait for that. ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------

Hi Øyvind, we are not using JBoss EAP, but I know that one is based on Wildfly. The Wildfly history shows that for a Hibernate Upgrade, they typically only increase the version properties. Normally, the hibernate API is quite stable, so an upgrade to a newer version mostly is done by just changing the jars. That was e.g. done that way for upgrade from 6.2.13 to 6.4.1-Final. https://github.com/wildfly/wildfly/commit/2fd269b7cf27ebe9316e84043fa0d557f2d42c51 I personally would do a test to change the original hibernate_core jar with the never version and modify the module.xml file to load it. (wildfly deploys also hibernate-envers, you should update to the same version). First step would be a test with 6.2.38-Final to stay within the same mayor release. Next, I would try to use 6.4.10-Final, which was in the following release. But this is just what I would try, if you want to stay in the original release because of support from Redhat, you would need to wait (but why not ask them for support, in case you have a subscription). I am a little bit astonished that this specific version would have such a very severe error. But maybe the Redhat guys are deploying a modified version. You should be able to see that by comparing the original hibernate download with the deployed one. MARCUS HAARMANN