Hello Tim

How do you setup parameter in Hibernate query?

Does next query works for you?

select distinct p.id from ProcessingPersonalData p join p.executingOrganisations orgs where :org.id=orgs.id

Thanks, Eugene Putilin.

Понедельник, 5 октября 2015, 11:14 UTC от "Tim van der Leeuw tnleeuw@... [Firebird-Java]" <Firebird-Java@yahoogroups.com>:

 

Hi,

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

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.

For the query, I left out most result fields not relevant for testing the problem. There's still a JOIN which doesn't make sense in this dressed-down version of the query, but which is introduced by Hibernate.

For the purpose of this query, the tables and relevant fields are (in my opinion):

- Organisation (id: PK)

- Process(id: PK)

- ProcessingPersonalData(id: PK & FK to Process(id) ) - extension table of Process

- PPD_Exec_Org(PPD_ID: FK to ProcessingPersonalData(id), Organisation_ID: FK to Organisation(id) ) - link table to express m:n relation between entities Organisation & ProcessingPersonalData

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.

I can probably make a workaround using a direct SQL query, but I'd appreciate if I could work with Hibernate.

I would appreciate if anyone can give some advice on what is wrong, if this is a bug in the driver, or elsewhere.

If more information is required I'd be happy to provide it.

Kind regards,

--Tim van der Leeuw

The full stack trace is:

2015-10-02 19:24:12,010 [http-bio-8080-exec-10] WARN  util.JDBCExceptionReporter  - SQL Error: 335544569, SQLState: 42000

| Error 2015-10-02 19:24:12,010 [http-bio-8080-exec-10] ERROR util.JDBCExceptionReporter  - GDS Exception. 335544569. Dynamic SQL Error

SQL error code = -804

Data type unknown

| Error 2015-10-02 19:24:12,047 [http-bio-8080-exec-10] ERROR errors.GrailsExceptionResolver  - GDSException occurred when processing request: [GET] /pp-app/externalOrganisation/show/17 - parameters:

role: PROCESSOR

_from: 39-45

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

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

|   2042 | receiveResponse                        in     ''

|   1465 | iscDsqlPrepare . . . . . . . . . . . . in     ''

|    197 | prepareStatement                       in org.firebirdsql.gds.impl.GDSHelper

|   1434 | prepareFixedStatement . . . . . . . .  in org.firebirdsql.jdbc.AbstractStatement

|   1283 | prepareFixedStatement                  in org.firebirdsql.jdbc.AbstractPreparedStatement

|    135 | <init> . . . . . . . . . . . . . . . . in     ''

|     45 | <init>                                 in org.firebirdsql.jdbc.FBPreparedStatement

|     -1 | newInstance . . . . . . . . . . . . .  in sun.reflect.GeneratedConstructorAccessor133

|     45 | newInstance                            in sun.reflect.DelegatingConstructorAccessorImpl

|    422 | newInstance . . . . . . . . . . . . .  in java.lang.reflect.Constructor

|   1075 | jlrConstructorNewInstance              in org.springsource.loaded.ri.ReflectiveInterceptor

|     89 | createPreparedStatement . . . . . . .  in org.firebirdsql.jdbc.FBStatementFactory

|   1186 | prepareStatement                       in org.firebirdsql.jdbc.AbstractConnection

|    941 | prepareStatement . . . . . . . . . . . in     ''

|    895 | prepareStatement                       in     ''

|    322 | prepareStatement . . . . . . . . . . . in     ''

|    534 | getPreparedStatement                   in org.hibernate.jdbc.AbstractBatcher

|    452 | getPreparedStatement . . . . . . . . . in     ''

|    161 | prepareQueryStatement                  in     ''

|   1700 | prepareQueryStatement . . . . . . . .  in org.hibernate.loader.Loader

|    801 | doQuery                                in     ''

|    274 | doQueryAndInitializeNonLazyCollections in     ''

|   2542 | doList                                 in     ''

|   2276 | listIgnoreQueryCache . . . . . . . . . in     ''

|   2271 | list                                   in     ''

|    459 | list . . . . . . . . . . . . . . . . . in org.hibernate.loader.hql.QueryLoader

|    365 | list                                   in org.hibernate.hql.ast.QueryTranslatorImpl

|    196 | performList . . . . . . . . . . . . .  in org.hibernate.engine.query.HQLQueryPlan

|   1268 | list                                   in org.hibernate.impl.SessionImpl

|    102 | list . . . . . . . . . . . . . . . . . in org.hibernate.impl.QueryImpl

WBR Evgeny Putilin