On Mon, Oct 5, 2015 at 8:40 PM Mark Rotteveel mark@... [Firebird-Java] <Firebird-Java@yahoogroups.com> wrote:

 

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"> 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 (....)

So that means that when I tested the query with another query tool, it already substituted the parameter before passing it to the database, thus avoiding the issue...

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.

Since the link table is not known in the Java domain model I cannot use it in a JOIN, and another JOIN I couldn't easily think up either.

However, I made it 2 queries: 1 direct SQL query on just the join-table, to get the unique IDs, which I know will be a limited number so I can keep them in memory.

Then I use that to query the Process table.

Not as nice, but it works, for now.

> 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?

Probably out of habit?

The original schema was not designed by me, but I've seen this in many cases where a java.lang.Long was mapped to the database, as a Number(18,0) or Number(19,0).

 

Mark
--
Mark Rotteveel

Met groeten,

--Tim