0 Replies Latest reply on Dec 4, 2014 10:32 AM by Lei Ma

    Corrupted sql query to Microsoft SQL server is generated by Hibernate on Jboss as 7.1.1 Final

    Lei Ma Newbie

      I've just upgraded from Jboss 5.1 to Jboss 7.1.1. I have the following ejb named query:

      SELECT g FROM GlobalSetting g WHERE g.company=:co

      It works fine with mysql. However, when I tried running against Microsoft SQL server I got the following error:

      10:27:18,765 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-8087-6) SQL Error: 156, SQLState: S0001
      10:27:18,766 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http--0.0.0.0-8087-6) Incorrect syntax near the keyword 'as'.

      I then turned on the hibernate sql trace and the following query is recorded where the ROW_NUMBER() function is inserted right in the middle of the column name of email_from_address.

       

       

      11:47:31,639 INFO  [stdout] (http--0.0.0.0-8087-6) Hibernate:

      11:47:31,641 INFO  [stdout] (http--0.0.0.0-8087-6)     WITH query AS (select

      11:47:31,642 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.global_setting_id as global1_471_,

      11:47:31,642 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.created_by as created2_471_,

       

      11:47:31,643 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.created_date as created3_471_,

       

      11:47:31,644 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.modified_by as modified4_471_,

       

      11:47:31,644 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.modified_date as modified5_471_,

       

      11:47:31,645 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.status as status471_,

       

      11:47:31,646 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.ad_auto_activate as ad7_471_,

       

      11:47:31,646 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.auto_renew as auto8_471_,

       

      11:47:31,647 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.command_optimization as command9_471_,

       

      11:47:31,648 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.company_id as company25_471_,

       

      11:47:31,649 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.date_format as date10_471_,

       

      11:47:31,649 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.default_page_size as default11_471_,

       

      11:47:31,650 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.dm_auto_activate as dm12_471_,

       

      11:47:31,651 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.email_auth_user as email13_471_,

       

      11:47:31,651 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.email_auth_user_pswd as email14_471_,

       

      11:47:31,653 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.email,

       

      11:47:31,654 INFO  [stdout] (http--0.0.0.0-8087-6)         ROW_NUMBER() OVER (

       

      11:47:31,654 INFO  [stdout] (http--0.0.0.0-8087-6)     ORDER BY

       

      11:47:31,655 INFO  [stdout] (http--0.0.0.0-8087-6)         CURRENT_TIMESTAMP) as __hibernate_row_nr___from_address as email15_471_,

       

      11:47:31,656 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.email_port as email16_471_,

       

      11:47:31,656 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.email_server as email17_471_,

       

      11:47:31,657 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.email_to_address as email18_471_,

       

      11:47:31,658 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.jmx_ip as jmx19_471_,

       

      11:47:31,658 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.jmx_port as jmx20_471_,

       

      11:47:31,659 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.lifetime_license as lifetime21_471_,

       

      11:47:31,660 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.map_license_client_id as map22_471_,

       

      11:47:31,661 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.map_license_signature as map23_471_,

       

      11:47:31,661 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.max_report_age as max24_471_

       

      11:47:31,662 INFO  [stdout] (http--0.0.0.0-8087-6)     from

       

      11:47:31,663 INFO  [stdout] (http--0.0.0.0-8087-6)         n4a_global_setting globalsett0_

       

      11:47:31,663 INFO  [stdout] (http--0.0.0.0-8087-6)     where

       

      11:47:31,664 INFO  [stdout] (http--0.0.0.0-8087-6)         globalsett0_.company_id=?) SELECT

       

      11:47:31,665 INFO  [stdout] (http--0.0.0.0-8087-6)         *

       

      11:47:31,666 INFO  [stdout] (http--0.0.0.0-8087-6)     FROM

       

      11:47:31,666 INFO  [stdout] (http--0.0.0.0-8087-6)         query

       

      11:47:31,667 INFO  [stdout] (http--0.0.0.0-8087-6)     WHERE

       

      11:47:31,668 INFO  [stdout] (http--0.0.0.0-8087-6)         __hibernate_row_nr__ >= ?

       

      11:47:31,668 INFO  [stdout] (http--0.0.0.0-8087-6)         AND __hibernate_row_nr__ < ?

       

      Please help.