Remove quotes from table name in queries on IBMi

Hibernate ORM
1

Hello,

Our IBMi doesn’t like table names wrapped in quotes. Is there a way to remove the quotes from the table name? Example:

Hibernate: select t1_0.codigo,t1_0.descricao,t1_0.englobamento from "i#shared.tiporend" t1_0 where t1_0.codigo=?

This query generates an error on the IBMi side.

Also, if we do not specify the dialect on persistence.xml, the selected dialect is DB2Dialect, which prompts the db version warning.

[org.hibernate.dialect.Dialect] (ServerService Thread Pool -- 118) HHH000511: The 7.4.0 version for [org.hibernate.dialect.DB2Dialect] is no longer supported, hence certain features may not work properly. The minimum supported version is 10.5.0. Check the community dialects project for available legacy versions.

But since we are on IBMi the version is in fact v7r4

However, if we specify manually the DB2iDialect (which does not prompt the db version error), we get the following error:

Caused by: java.lang.NullPointerException: Cannot invoke "String.isEmpty()" because "rowId" is null
        at org.hibernate@6.2.13.Final//org.hibernate.dialect.DB2iDialect.rowId(DB2iDialect.java:161)
        at org.hibernate@6.2.13.Final//org.hibernate.dialect.Dialect.registerColumnTypes(Dialect.java:444)
        at org.hibernate@6.2.13.Final//org.hibernate.dialect.DB2Dialect.registerColumnTypes(DB2Dialect.java:209)
        at org.hibernate@6.2.13.Final//org.hibernate.dialect.Dialect.contributeTypes(Dialect.java:1580)
        at org.hibernate@6.2.13.Final//org.hibernate.dialect.DB2Dialect.contributeTypes(DB2Dialect.java:900)
        at org.hibernate@6.2.13.Final//org.hibernate.dialect.Dialect.contribute(Dialect.java:1569)
        at org.hibernate@6.2.13.Final//org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:630)
        at org.hibernate@6.2.13.Final//org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:174)
        at org.hibernate@6.2.13.Final//org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:1383)
        at org.hibernate@6.2.13.Final//org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1454)
        at org.hibernate.jipijapa-hibernate6@30.0.0.Final//org.jboss.as.jpa.hibernate.TwoPhaseBootstrapImpl.build(TwoPhaseBootstrapImpl.java:32)
        at org.jboss.as.jpa@30.0.0.Final//org.jboss.as.jpa.service.PersistenceUnitServiceImpl$1$1.run(PersistenceUnitServiceImpl.java:159)
        ... 10 more

We are currently testing on Wildfly 30 (Hibernate 6.2.13).

On our Wildfly 15 (Hibernate 5.3.7) installation everything works just fine with the defaults.

2

This NPE was fixed with [HHH-17344] - Hibernate JIRA in Hibernate ORM 6.4. You will have to upgrade if you want this fix. Also, note that DB2 for iSeries is not “really” supported. We try to provide a best effort support because it’s an IBM product, but we do not have any means to test against that database, so whenever DB2 for iSeries deviates from DB2 for LUW behavior, there will be problems.

I don’t know why the quoting with " doesn’t work in your case. According to the documentation, this is the proper way to quote identifiers that contain special characters like a #.

3

Maybe your @Table mapping is wrong. You have to separate the schema/catalog name from table name e.g. @Table(name = "tiporend", schema = "i#shared")

4

Thank you for your reply, and your team efforts on supporting IBMi. Your @Table mapping suggestion results in the following query string:

[select t1_0.codigo,t1_0.descricao,t1_0.englobamento from "i#shared".tiporend t1_0 where t1_0.codigo=?]

The db server still complains. Even on IBMi system console, using interactive query, the quoted names are not accepted. I’ll try to investigate on this matter.

However I was able to apparently circumvent que issue by extending the DB2Dialect and overriding quoteOpen and quoteClose methods. I’ll keep testing and adding more complex queries and operations to see how the library handles them.

1 Like