Hi,
I need to use Hibernate with the SQLite database.
The database schema makes use of composite ids.
Because there is no SQLite dialect I use the GenericDialect.
The problem is that Hibernate produces SQL which SQLite cannot execute.
The HQL query looks like:
Code:
select
link
from
Link as link,
Task as task
join link.source as content
join task.object as object
where
content.object=object
And the resulting SQL is:
Code:
select
link0_.CLIENT as CLIENT,
link0_.OBJECT_NUMBER as OBJECT2_,
link0_.CONTENT_NUMBER as CONTENT3_,
link0_.LINK_NUMBER as LINK4_,
link0_.URL as URL0_,
link0_.IS_BROKEN as IS6_0_,
link0_.BROKEN_SINCE as BROKEN7_0_,
link0_.BROKEN_BY as BROKEN8_0_,
link0_.SUBST_ANNOTATION as SUBST9_0_,
link0_.SUBST_DESTINATION as SUBST10_0_,
link0_.DESTINATION_OBJ_CLIENT as DESTINA11_0_,
link0_.DESTINATION_OBJ_NUMBER as DESTINA12_0_,
link0_.CLIENT as formula0_,
link0_.OBJECT_NUMBER as formula1_,
link0_.CONTENT_NUMBER as formula2_
from LINKS
link0_ inner join CONTENTS content2_ on
link0_.CLIENT=content2_.CLIENT and
link0_.OBJECT_NUMBER=content2_.OBJECT_NUMBER and
link0_.CONTENT_NUMBER=content2_.CONTENT_NUMBER,
TASKS task1_ inner join OBJECTS objecti3_ on
task1_.CLIENT=objecti3_.CLIENT and
task1_.OBJECT_NUMBER=objecti3_.OBJECT_NUMBER
where
(content2_.CLIENT, content2_.OBJECT_NUMBER)=(objecti3_.CLIENT, objecti3_.OBJECT_NUMBER)
If I modify the SQL query as follows and run it on the SQLite command line it works:
Code:
...
where
content2_.CLIENT=objecti3_.CLIENT and
content2_.OBJECT_NUMBER=objecti3_.OBJECT_NUMBER
Stacktrace:
Code:
2005-06-30 11:38:28,777 [Trifork J2EE:1] DEBUG org.hibernate.util.JDBCExceptionReporter - could not execute query <here_comes_the_sql_query_from_above>
java.sql.SQLException: SQLite.Exception: error in prepare
at SQLite.JDBC2x.JDBCStatement.executeQuery(JDBCStatement.java:121)
at SQLite.JDBC2x.JDBCPreparedStatement.executeQuery(JDBCPreparedStatement.java:71)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:775)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:312)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedParam(HibernateTemplate.java:766)
at org.springframework.orm.hibernate3.HibernateTemplate.findByNamedParam(HibernateTemplate.java:758)
- Is there any way to change the produced SQL?
- Can I use some other Dialect or where can I start to have my own SQLiteDialect generate SQL which SQLite can execute?
- Where in a Dialect can I hook in to generate different SQL for where clauses.
- Or is this all the wrong place and I need to hack the antlr grammar?
Thomas
Hibernate version: 3.0.5
Name and version of the database you are using: SQLite 3.2.1
Login
FAQ
Search