On 5-10-2015 13:14, Tim van der Leeuw tnleeuw@... [Firebird-Java]
wrote:

> I'm using Hibernate for database access. I have a Hibernate query which
> appears valid, and appears to generate valid SQL.
>
> When trying to execute the query, I get an exception:
>
> Dynamic SQL Error
> SQL error code = -804
> Data type unknown. Stacktrace follows:
> Message: Dynamic SQL Error
> SQL error code = -804
> "Data type unknown"
> Line | Method
> ->> 2092 | readStatusVector in
> org.firebirdsql.gds.impl.wire.AbstractJavaGDSImpl
>
> Tracing back to the call:
>
> result = conn.prepareStatement(sql);
>
> The Hibernate query is:
>
> select distinct p.id <http://p.id> from ProcessingPersonalData p where
> :org in elements(p.executingOrganisations)
>
>
> And the generated SQL is:
>
> select distinct processing0_.id as col_0_0_
> from processing_personal_data processing0_
> inner join process processing0_1_ on processing0_.id=processing0_1_.id
> where ? in (select executingo1_.organisation_id from ppd_exec_org
> executingo1_ where processing0_.id=executingo1_.ppd_id)
>

The problem is: ? IN (....)

This construct (with a parameter in the left hand side of an IN is not
supported in Firebird 2.5 and earlier as Firebird is unable to infer the
datatype of the parameter. Support will be added in Firebird 3, see
http://tracker.firebirdsql.org/browse/CORE-2697

If this was direct SQL, you could work around it by explicitly casting
the parameter: CAST(? AS BIGINT) IN (....). I am not sure if there is an
equivalent in Hibernate. It might be easier to just join the two tables.

> I can execute the statement in another query tool, and filling in a
> value for the '?' I get two rows of output, which matches my dataset -
> so the query itself does work.

...

> The Hibernate version, in case it's relevant, is 3.6.10. Firebird
> version 2.5.4 for MacOS, JDBC driver version is 2.2.8 JDK8.
> All the columns in this query have data type java.lang.Long in Java,
> Numeric(18,0) in the database.

Out of curiosity: why are you using NUMBER(18,0) instead of BIGINT?

Mark
--
Mark Rotteveel