Hello hibernators,
I'm at a loss here. Please allow me to state the issue briefly yet informative.
A straightforward query results in:
Quote:
java.sql.SQLException: Cannot resolve collation conflict for concatenation operation
.
Platform specs:
- sql server 2000 on windows 2000 english sp3
- java hotspot build 1.4.2-b28
- hibernate-2.1.4
- jdbc driver jtds-0.8.1, jtds.sourceforge.net
Mapping:
Code:
<class
name="FormOptionImpl"
table="tblOptie"
>
<id
name="id"
type="java.lang.Integer"
column="OptieID"
>
<generator class="native" />
</id>
<property
name="text"
type="java.lang.String"
column="OptieTekst"
length="256"
/>
</class>
Table (not generated, code must run on already existing db):
Code:
CREATE TABLE [dbo].[tblOptie] (
[OptieID] [int] IDENTITY (1, 1) NOT NULL ,
[OptieTekst] [varchar] (256) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Hibernate query:
Code:
"select distinct option from FormOptionImpl option " +
"where ((:text is null) or (option.text like :text + '%'))";
Problem description:
This query results in the mentioned exception if the database collation is set to 'Latin1_General_CI_AS' (note that this is the same collation as the column that is being queried on, inspection of the column collation in the mssql enterprise manager application results in '<database-default>').
Console trace output:
Quote:
[2004-07-27 21:42:01,481] DEBUG net.sf.hibernate.hql.QueryTranslator 199: - HQL: select distinct option from FormOptionImpl option where ((:text is null) or (option.text like :text + '%'))
[2004-07-27 21:42:01,481] DEBUG net.sf.hibernate.hql.QueryTranslator 200: - SQL: select distinct formoption0_.OptieID as OptieID, formoption
0_.OptieTekst as OptieTekst from tblOptie formoption0_ where (((? is null ))or((formoption0_.OptieTekst like ?+'%' )))
[2004-07-27 21:42:01,481] DEBUG net.sf.hibernate.impl.BatcherImpl 196: - about to open: 0 open PreparedStatements, 0 open ResultSets
[2004-07-27 21:42:01,481] DEBUG net.sf.hibernate.SQL 237: - select distinct formoption0_.OptieID as OptieID, formoption0_.OptieTekst as Opti
eTekst from tblOptie formoption0_ where (((? is null ))or((formoption0_.OptieTekst like ?+'%' )))
[2004-07-27 21:42:01,491] DEBUG net.sf.hibernate.impl.BatcherImpl 241: - preparing statement
[2004-07-27 21:42:01,491] DEBUG net.sf.hibernate.type.StringType 46: - binding 'a' to parameter: 1
[2004-07-27 21:42:01,491] DEBUG net.sf.hibernate.type.StringType 46: - binding 'a' to parameter: 2
[2004-07-27 21:42:01,501] DEBUG net.sf.hibernate.util.JDBCExceptionReporter 36: - SQL Exception
java.sql.SQLException: Cannot resolve collation conflict for concatenation operation.
at net.sourceforge.jtds.jdbc.SqlMessage.toSQLException(SqlMessage.java:392)
at net.sourceforge.jtds.jdbc.SQLWarningChain.addOrReturn(SQLWarningChain.java:91)
at net.sourceforge.jtds.jdbc.Tds.submitProcedure(Tds.java:235)
at net.sourceforge.jtds.jdbc.PreparedStatement_base.submitProcedure(PreparedStatement_base.java:195)
at net.sourceforge.jtds.jdbc.PreparedStatement_base.findOrCreateProcedure(PreparedStatement_base.java:179)
at net.sourceforge.jtds.jdbc.PreparedStatement_base.execute(PreparedStatement_base.java:131)
at net.sourceforge.jtds.jdbc.PreparedStatement_base.executeQuery(PreparedStatement_base.java:262)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at CollationTest.hibernateTest(CollationTest.java:52)
at CollationTest.main(CollationTest.java:66)
P6spy query:
Quote:
select distinct formoption0_.OptieID as OptieID, formoption0_.OptieTekst as OptieTekst from tblOptie formoption0_ where ((('a' is null ))or((formoption0_.OptieTekst like 'a'+'%' )))
Further details:
I've tried some variations w.r.t. collation settings of the database, please not that in all cases
the sql query has been successfully executed using normal jdbc (using the jtds-0.8.1. driver).
For db collation 'SQL_Latin1_General_CP1_CI_AS',
column collation 'Latin1_General_CI_AS' the hibernate query was successfully executed.
For db collation 'SQL_Latin1_General_CP1_CI_AS',
column collation '<database-default>' the hibernate query was also successfully executed.
Appendix test code:
JDBC
Code:
Connection conn = getConnection();
String query = "select distinct formoption0_.OptieID as OptieID, formoption0_.OptieTekst " +
"as OptieTekst from tblOptie formoption0_ where ((('a' is null )) or " +
"((formoption0_.OptieTekst like 'a'+'%' )))";
PreparedStatement ps = conn.prepareStatement(query)
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getString(1) + ", " + rs.getString(2));
}
}
HIBERNATE
Code:
String hql = "select distinct option " +
"from careweb.data.impl.FormOptionImpl option " +
"where ((:text is null) or (option.text like :text + '%'))";
Configuration config = new Configuration();
config.addJar(new File("data.jar"));
SessionFactory sessions = config.buildSessionFactory();
Session session = sessions.openSession(getConnection());
Query query = session.createQuery(hql);
query.setParameter("text", "a");
List list = query.list();
for (Iterator it = list.iterator(); it.hasNext();) {
FormOption option = (FormOption) it.next();
System.out.println(option);
}
Many thanks if you read this,
sleepy steve.
Login
FAQ
Search
