Commented by: Edilmar Alves (edilmar)

I tried to use "case" workaround (that works with 2.1.6), but it didn't work:

select case when c.historico = '' then 'teste' else cast(c.historico as varchar(2000)) end as historico from conhecimento c where c.numero = 1

Commented by: @mrotteveel

In Jaybird 2.2 we changed what is reported for the ResultSetMetaData properties columnName and columnLabel for compliance with the JDBC 4.x spec (see JDBC162) The Hibernate class org.hibernate.loader.custom.CustomLoader is using the columnName to find the column instead of the columnLabel as they should.

The solution/workaround is to use the connection property columnLabelForName=true (see JDBC260 and the release notes under "Changes and fixes in Jaybird 2.2.1" and "Compatibility with com.sun.rowset.*")

This property can be added to the connection properties:
* Through a properties object (with value true)
* In the connection URL (columnLabelForName=true)
* On a (Jaybird) DataSource by setting setNonStandardProperty("columnLabelForName", "true") or setNonStandardProperty("columnLabelForName=true");

It seems to be related to Hibernate issues:
https://hibernate.onjira.com/browse/HHH-2094 (closed)
https://hibernate.onjira.com/browse/HHH-4875
https://hibernate.onjira.com/browse/HHH-4956

Commented by: @mrotteveel

A quick look at the code of Hibernate 4.1.9 Final (without actually testing it) suggests it is fixed in that version.

Commented by: @mrotteveel

I am closing the issue as 'Won't fix', but don't hesitate to add a comment (or post on the Firebird-Java mailinglist) if you run into more problems with this.

Commented by: Edilmar Alves (edilmar)

Doubt: how could I set up the persistence.xml for this property?
I tried this without sucess...
<property name="hibernate.connection.columnLabelForName" value="true"/>

Commented by: @mrotteveel

columnLabelForName is a Jaybird connection property, not a Hibernate property. So you need to specify it either in the connection URL or as a datasource property (but only if you are using a Firebird datasource); the datasourceproperty would then be "nonStandardProperty" with value "columnLabelForName=true"

Commented by: @mrotteveel

Also it could be that using <property name="hibernate.connection.columnLabelForName" value="true"/> will work, but I am simply not sure. Let me know if that works too.

Commented by: Edilmar Alves (edilmar)

Unfortunatelly the property setup in persistence.xml doesn't work.
Then, in my way, I have to use the URL property directly in Connection Pool from GlassFish.
But now if I have to test this query without GlassFish, just running a main() method to check up the DAO class, it is not possible for these kind of SQLs with casts.

Commented by: @mrotteveel

If you work without glassfish, how do you configure and get your connection? Just add the property to the connection URL there.

Commented by: Edilmar Alves (edilmar)

In the tests out of GF (with direct main()), I program this way and now it worked fine with hibernate.connection.url:

  EntityManagerFactory emf = Persistence\.createEntityManagerFactory\("sat"\);
  EntityManager emAux = emf\.createEntityManager\(\);
  ConhecimentoDAO dao = new ConhecimentoJpaDAO\(\);
  dao\.setEntityManager\(emAux\);
  List<ConhecimentoImp\> list = dao\.getConhecimentoImp\(idConhecimento\); // the method with SQL Native Query using cast\(\)

Then, Persistence uses persistence.xml like this:

<persistence-unit name="sat" transaction-type="RESOURCE_LOCAL">
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.FirebirdDialect"/>
<property name="hibernate.connection.driver_class" value="org.firebirdsql.jdbc.FBDriver"/>
<property name="hibernate.connection.url" value="jdbc:firebirdsql:localhost/3050:/sistemas/satbd/SATIflecha.GDB?lc_ctype=ISO8859_1&columnLabelForName=true"/>
<property name="hibernate.connection.username" value="SYSDBA" />
<property name="hibernate.connection.password" value="masterkey" />
</properties>
</persistence-unit>

Commented by: @mrotteveel

Good to hear!