These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
lil123
 Post subject: Invalid SQL generated for SqlServer
PostPosted: Tue Dec 04, 2007 4:21 pm 
Newbie

Joined: Tue Dec 04, 2007 4:09 pm
Posts: 2
Hibernate version:
3.2.5.ga

Mapping documents:
Code:
<hibernate-mapping>
  <class name="org.test.htest.SuperClass" table="SuperClass">
   <composite-id>
      <key-property name="pseudoId" type="integer" column="pseudoId" />
      <key-property name="createdDate" type="java.util.Date" column="createdDate" />
    </composite-id>

    <property name="name" type="string" column="name" />

    <many-to-one name="superceededBy" foreign-key="fk_SuperClassSuperceededBy_SuperClassPseudoIdCreatedDate">
      <column name="superceededByPseudoId"/>
      <column name="superceededByCreatedDate"/>
    </many-to-one>
  </class>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():
Code:
   public static void main(String[] args)
   {
      Configuration cfg = new Configuration();
      cfg.configure("/hibernate.cfg.xml");
      SessionFactory sessionFactory = cfg.buildSessionFactory();
      Session session = sessionFactory.getCurrentSession();

      Transaction tx = null;
      try
      {
         tx = session.beginTransaction();
         Query query = session.createQuery("from SuperClass where superceededBy is null");
         List result = query.list();
         tx.commit();

         System.out.println("Got " + result.size() + " results");
         System.out.flush();
      }
      catch(HibernateException e)
      {
         if (tx != null && tx.isActive())
         {
            tx.rollback();
         }
      }

      sessionFactory.close();
   }



Full stack trace of any exception that occurs:
12:06:06,096 ERROR JDBCExceptionReporter:78 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near ','.


Name and version of the database you are using:
SqlServer 2000

The generated SQL (show_sql=true):
Hibernate: select superclass0_.pseudoId as pseudoId0_, superclass0_.createdDate as createdD2_0_, superclass0_.name as name0_, superclass0_.superceededByPseudoId as supercee4_0_, superclass0_.superceededByCreatedDate as supercee5_0_ from SuperClass superclass0_ where (superclass0_.superceededByPseudoId, superclass0_.superceededByCreatedDate) is null


Debug level Hibernate log excerpt:
12:24:01,769 INFO Environment:514 - Hibernate 3.2.5
12:24:01,769 INFO Environment:547 - hibernate.properties not found
12:24:01,769 INFO Environment:681 - Bytecode provider name : cglib
12:24:01,785 INFO Environment:598 - using JDK 1.4 java.sql.Timestamp handling
12:24:01,863 INFO Configuration:1426 - configuring from resource: /hibernate.cfg.xml
12:24:01,863 INFO Configuration:1403 - Configuration resource: /hibernate.cfg.xml
12:24:01,972 INFO Configuration:553 - Reading mappings from resource : org/test/htest/SuperClass.hbm.xml
12:24:02,082 INFO HbmBinder:300 - Mapping class: org.test.htest.SuperClass -> SuperClass
12:24:02,191 INFO Configuration:1541 - Configured SessionFactory: null
12:24:02,238 WARN RootClass:210 - composite-id class does not override equals(): org.test.htest.SuperClass
12:24:02,238 WARN RootClass:215 - composite-id class does not override hashCode(): org.test.htest.SuperClass
12:24:02,253 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
12:24:02,253 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
12:24:02,253 INFO DriverManagerConnectionProvider:45 - autocommit mode: false
12:24:02,253 INFO DriverManagerConnectionProvider:80 - using driver: com.microsoft.jdbc.sqlserver.SQLServerDriver at URL: jdbc:microsoft:sqlserver://myserver:1433;DatabaseName=mydatabase;SelectMethod=Cursor
12:24:02,253 INFO DriverManagerConnectionProvider:86 - connection properties: {user=myuser, password=****}
12:24:02,425 INFO SettingsFactory:89 - RDBMS: Microsoft SQL Server, version: Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

12:24:02,425 INFO SettingsFactory:90 - JDBC driver: SQLServer, version: 2.2.0040
12:24:02,457 INFO Dialect:152 - Using dialect: org.hibernate.dialect.SQLServerDialect
12:24:02,457 INFO TransactionFactoryFactory:31 - Using default transaction strategy (direct JDBC transactions)
12:24:02,472 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
12:24:02,472 INFO SettingsFactory:143 - Automatic flush during beforeCompletion(): disabled
12:24:02,472 INFO SettingsFactory:147 - Automatic session close at end of transaction: disabled
12:24:02,472 INFO SettingsFactory:162 - Scrollable result sets: enabled
12:24:02,472 INFO SettingsFactory:170 - JDBC3 getGeneratedKeys(): disabled
12:24:02,472 INFO SettingsFactory:178 - Connection release mode: auto
12:24:02,472 INFO SettingsFactory:205 - Default batch fetch size: 1
12:24:02,472 INFO SettingsFactory:209 - Generate SQL with comments: disabled
12:24:02,472 INFO SettingsFactory:213 - Order SQL updates by primary key: disabled
12:24:02,472 INFO SettingsFactory:217 - Order SQL inserts for batching: disabled
12:24:02,472 INFO SettingsFactory:386 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
12:24:02,472 INFO ASTQueryTranslatorFactory:24 - Using ASTQueryTranslatorFactory
12:24:02,472 INFO SettingsFactory:225 - Query language substitutions: {}
12:24:02,472 INFO SettingsFactory:230 - JPA-QL strict compliance: disabled
12:24:02,472 INFO SettingsFactory:235 - Second-level cache: enabled
12:24:02,472 INFO SettingsFactory:239 - Query cache: disabled
12:24:02,472 INFO SettingsFactory:373 - Cache provider: org.hibernate.cache.NoCacheProvider
12:24:02,472 INFO SettingsFactory:254 - Optimize cache for minimal puts: disabled
12:24:02,472 INFO SettingsFactory:263 - Structured second-level cache entries: disabled
12:24:02,488 INFO SettingsFactory:283 - Echoing all SQL to stdout
12:24:02,488 INFO SettingsFactory:290 - Statistics: disabled
12:24:02,488 INFO SettingsFactory:294 - Deleted entity synthetic identifier rollback: disabled
12:24:02,488 INFO SettingsFactory:309 - Default entity-mode: pojo
12:24:02,488 INFO SettingsFactory:313 - Named query checking : enabled
12:24:02,535 INFO SessionFactoryImpl:161 - building session factory
12:24:02,863 INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
12:24:03,238 DEBUG SQL:401 - select superclass0_.pseudoId as pseudoId0_, superclass0_.createdDate as createdD2_0_, superclass0_.name as name0_, superclass0_.superceededByPseudoId as supercee4_0_, superclass0_.superceededByCreatedDate as supercee5_0_ from SuperClass superclass0_ where (superclass0_.superceededByPseudoId, superclass0_.superceededByCreatedDate) is null
Hibernate: select superclass0_.pseudoId as pseudoId0_, superclass0_.createdDate as createdD2_0_, superclass0_.name as name0_, superclass0_.superceededByPseudoId as supercee4_0_, superclass0_.superceededByCreatedDate as supercee5_0_ from SuperClass superclass0_ where (superclass0_.superceededByPseudoId, superclass0_.superceededByCreatedDate) is null
12:24:03,300 WARN JDBCExceptionReporter:77 - SQL Error: 170, SQLState: HY000
12:24:03,300 ERROR JDBCExceptionReporter:78 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near ','.
12:24:03,300 WARN JDBCExceptionReporter:77 - SQL Error: 16945, SQLState: HY000
12:24:03,300 ERROR JDBCExceptionReporter:78 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The cursor was not declared.
12:24:03,300 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState:
12:24:03,300 WARN JDBCExceptionReporter:55 - [Microsoft][SQLServer 2000 Driver for JDBC]Database changed to mydatabase
12:24:03,300 WARN JDBCExceptionReporter:54 - SQL Warning: 5701, SQLState: 01000
12:24:03,300 WARN JDBCExceptionReporter:55 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Changed database context to 'mydatabase'.
12:24:03,300 WARN JDBCExceptionReporter:54 - SQL Warning: 0, SQLState:
12:24:03,300 WARN JDBCExceptionReporter:55 - [Microsoft][SQLServer 2000 Driver for JDBC]Language changed to us_english
12:24:03,316 WARN JDBCExceptionReporter:54 - SQL Warning: 5703, SQLState: 01000
12:24:03,316 WARN JDBCExceptionReporter:55 - [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Changed language setting to us_english.
12:24:03,316 INFO SessionFactoryImpl:769 - closing
12:24:03,316 INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:microsoft:sqlserver://myserver:1433;DatabaseName=mydatabase;SelectMethod=Cursor



The Issue:

Hibernate is generating incorrect SQL.

The particular problem is in the where clause:
Code:
where (superclass0_.superceededByPseudoId, superclass0_.superceededByCreatedDate) is null


SqlServer does not understand this kind of where clause. It should be:
Code:
where superclass0_.superceededByPseudoId is null and superclass0_.superceededByCreatedDate is null



Is there anything I can do to fix this? Is it a bug?
Top
 Profile  
 
farzad
 Post subject: Re: Invalid SQL generated for SqlServer
PostPosted: Tue Dec 04, 2007 4:43 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
Worst comes to worst you can define those key properties in your class and set their insert=false and update=false so that hibernate does not get confused. Then you can modify your query to check for nullability of those properties.
Top
 Profile  
 
lil123
 Post subject:
PostPosted: Tue Dec 04, 2007 6:00 pm 
Newbie

Joined: Tue Dec 04, 2007 4:09 pm
Posts: 2
Could you post an example of that? I can't seem to get it to work.
Top
 Profile  
 
farzad
 Post subject:
PostPosted: Tue Dec 04, 2007 8:37 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
lil123 wrote:
Could you post an example of that? I can't seem to get it to work.


Add these to your mapping and see if it works:

Code:
    <property name="superceededByPseudoId" column="superceededByPseudoId" insert="false" update="false"/>

    <property name="superceededByCreatedDate" column="superceededByCreatedDate" insert="false" update="false"/>



and the query should look like:

Code:
from SuperClass where superceededByCreatedDate is null and superceededByPseudoId is null
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.