Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using: Sybase 12.5

The generated SQL (show_sql=true):

Hibernate: select registryhi0_.T3381_RGY_NBR as T1_0_, shipprofil1_.REGISTERED_USER_ID as REGISTERED1_1_, shipprofil1_.PROFILE_NAME as PROFILE2_1_, registryhi0_.T3391_RGY_EVN_CD as T2_48_0_, registryhi0_.RGY_EVN_NME as RGY3_48_0_, registryhi0_.RGY_EVN_DM as RGY4_48_0_, registryhi0_.RGY_ORG_LCT_NBR as RGY5_48_0_, registryhi0_.T3392_DCO_STY_CD as T6_48_0_, registryhi0_.RGY_PAL_CMT_1_TXT as RGY7_48_0_, registryhi0_.RGY_PAL_CMT_2_TXT as RGY8_48_0_, registryhi0_.RGY_PAL_CMT_3_TXT as RGY9_48_0_, registryhi0_.SHP_PRL_NME as SHP10_48_0_, registryhi0_.T3387_RGY_SRC_CD as T11_48_0_, registryhi0_.RGY_PVT_IDC as RGY12_48_0_, registryhi0_.RGY_DCR_NFN_IDC as RGY13_48_0_, registryhi0_.RGY_LOW_NFN_IDC as RGY14_48_0_, registryhi0_.T3386_RGY_STS_CD as T15_48_0_, registryhi0_.RGY_PRL_ARC_DM as RGY16_48_0_, registryhi0_.ADD_DM as ADD17_48_0_, registryhi0_.UPD_DM as UPD18_48_0_, registryhi0_.UPD_ID as UPD19_48_0_, registryhi0_.RGY_GST_PSW_TXT as RGY20_48_0_, registryhi0_.T3620_QST_PSW_CD as T21_48_0_, registryhi0_.RGY_ANS_PSW_TXT as RGY22_48_0_, registryhi0_.RGY_CTC_PRL_NME as RGY23_48_0_, registryhi0_.RGY_CRE_DM as RGY24_48_0_, registryhi0_.T3380_RGY_RGS_NBR as T25_48_0_, shipprofil1_.PREFIX as PREFIX51_1_, shipprofil1_.SHIP_TO_FIRST_NAME as SHIP4_51_1_, shipprofil1_.SHIP_TO_MI as SHIP5_51_1_, shipprofil1_.SHIP_TO_LAST_NAME as SHIP6_51_1_, shipprofil1_.SUFFIX as SUFFIX51_1_, shipprofil1_.SHIP_TO_ADDRESS_LINE1 as SHIP8_51_1_, shipprofil1_.SHIP_TO_ADDRESS_LINE2 as SHIP9_51_1_, shipprofil1_.SHIP_TO_ADDRESS_LINE3 as SHIP10_51_1_, shipprofil1_.SHIP_TO_CITY as SHIP11_51_1_, shipprofil1_.SHIP_TO_STATE as SHIP12_51_1_, shipprofil1_.SHIP_TO_COUNTRY as SHIP13_51_1_, shipprofil1_.SHIP_TO_POSTAL_CODE as SHIP14_51_1_, shipprofil1_.HOME_PHONE as HOME15_51_1_, shipprofil1_.WORK_PHONE as WORK16_51_1_, shipprofil1_.CREATED_BY_ID as CREATED17_51_1_, shipprofil1_.CREATED_DATE as CREATED18_51_1_, shipprofil1_.UPDATED_BY_ID as UPDATED19_51_1_, shipprofil1_.UPDATED_DATE as UPDATED20_51_1_, shipprofil1_.FGN_ST_PVN_ADR as FGN21_51_1_, shipprofil1_.T1989_CRY_CD as T22_51_1_ from T3381_RGY_PRL registryhi0_, SHIP_PROFILE shipprofil1_, T3380_RGY_RGS_PRL registryre2_ where registryhi0_.T3380_RGY_RGS_NBR=registryre2_.T3380_RGY_RGS_NBR and registryhi0_.T3387_RGY_SRC_CD<>? and registryre2_.RGS_USE_ID<>? and registryre2_.RGS_USE_ID=shipprofil1_.REGISTERED_USER_ID and registryhi0_.RGY_CTC_PRL_NME=shipprofil1_.PROFILE_NAME and (lower(shipprofil1_.SHIP_TO_LAST_NAME)like ?)

Debug level Hibernate log excerpt:


The above line executes the following query in the database causing millions of records coming back and therefore bringing down database:

select ... from SHIP_PROFILE where REGISTERED_USER_ID<>"coreg"

In the where clause in the log file, the following params are passed for ?

registryre2_.RGS_USE_ID<>? ---> ?="coreg"

If the above query is being broken down, how come it is not being printed in the log file.

Thanks so much for you help

What are you talking about?
What's the relation with EJB3?

manuel, sorry, I have no credit and desperate to figure this out. Here was the original query.

sbQuery.append("select registry, shipAddress from com.lowes.gr.dataObjects.RegistryHib as registry, com.lowes.gr.dataObjects.ShipProfile as shipAddress where registry.sourceCode!=? and registry.registrant.userIdentifier!=? and registry.registrant.userIdentifier=shipAddress.registeredUserId and registry.registryCTCProfileName=shipAddress.profileName");

Query query=session.createQuery(sbQuery.toString());

int counter=-1;

query.setInteger(++counter, REGISTRY_INACTIVE);

query.setInteger(++counter, REGISTRY_INACTIVE);
query.setString(++counter, "coreg");

------------------------------------------------------------------------
shipAddress maps to SHIP_PROFILE table.


Somehow the above ends up calling

SQL Text: SELECT "REGISTERED_USER_ID", "PROFILE_NAME", "PREFIX", "SHIP_TO_FIRST_NAME", "SHIP_TO_MI", "SHIP_TO_LAST_NAME", "SUFFIX", "SHIP_TO_ADDRESS_LINE1", "SHIP_TO_ADDRESS_LINE2", "SHIP_TO_ADDRESS_LINE3", "SHIP_TO_CITY", "SHIP_TO_STATE", "SHIP_TO_COUNTRY", "SHIP_TO_POSTAL_CODE", "HOME_PHONE", "WORK_PHONE", "CREATED_BY_ID", "CREATED_DATE", "UPDATED_BY_ID", "UPDATED_DATE", "FGN_ST_PVN_ADR", "T1989_CRY_CD" FROM DBNAME.dbo."SHIP_PROFILE" WHERE "REGISTERED_USER_ID" <> 'coreg'

which hangs the sybase database. This is from monitoring Sybase.

How come the above query does not print in the Hiberante log file? Does Hibernate still optimize the quries further than the ones it prints in the log file?

Thanks so much for your help.