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-- SQL Error: 156, SQLState: S0001
      10:27:18,766 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-- 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-- Hibernate:

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

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

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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


      11:47:31,654 INFO  [stdout] (http--     ORDER BY


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


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


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


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


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


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


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


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


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


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


      11:47:31,662 INFO  [stdout] (http--     from


      11:47:31,663 INFO  [stdout] (http--         n4a_global_setting globalsett0_


      11:47:31,663 INFO  [stdout] (http--     where


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


      11:47:31,665 INFO  [stdout] (http--         *


      11:47:31,666 INFO  [stdout] (http--     FROM


      11:47:31,666 INFO  [stdout] (http--         query


      11:47:31,667 INFO  [stdout] (http--     WHERE


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


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


      Please help.