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

Hibernate version:
2.1.8
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:
oracle 9i
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

I believe hibernate is generating invalid sql for my oracle database select statements. My unit tests work fine against a mssql and db2 database but this select statement returns no rows when it should return 32. Hand writing this select statement in a sql editor for oracle also returns the correct number of results.



if I delete the jql.servername=:servername this query returns values, or even if I hardcode it to be jql.servername='somevalue' it works correctly.

snippets of the hbm file


snippets of the calling test


snippets of JobQueueList object



the data for the database is populated by dbunit, the hbm files generated by middlegen (run against the mssql server)

any help would be greatly appreciated

oh right, the sql


Hibernate: select jobqueueli0_.instidq as instidq, jobqueueli0_.instid as instid, jobqueueli0_.instcd as instcd, jobqueueli0_.jobid as jobid, jobqueueli0_.userid as userid, jobqueueli0_.status as status, jobqueueli0_.priority as priority, jobqueueli0_.startdate as startdate, jobqueueli0_.starttime as starttime, jobqueueli0_.external_eval as externa10_, jobqueueli0_.read_sysin as read_sysin, jobqueueli0_.servername as servername, jobqueueli0_.last_mod_user as last_mo13_, jobqueueli0_.last_mod_date as last_mo14_ from job_queue_list jobqueueli0_ where (jobqueueli0_.status=? )and(jobqueueli0_.servername=? )and((jobqueueli0_.startdate<=? )or(jobqueueli0_.startdate is null ))and((jobqueueli0_.starttime<=? )or(jobqueueli0_.starttime is null )) order by jobqueueli0_.priority , jobqueueli0_.jobid

.. and error and log are ...

-net.sf.hibernate.hql.QueryTranslator - HQL: from dars.apis.audit.JobQueueList jql where jql.status=:status and jql.servername=:servername and (jql.startdate <=:startdate or jql.startdate is null) and (jql.starttime <=:starttime or jql.starttime is null) order by jql.priority, jql.comp_id.jobid

-net.sf.hibernate.hql.QueryTranslator - SQL: select jobqueueli0_.instidq as instidq, jobqueueli0_.instid as instid, jobqueueli0_.instcd as instcd, jobqueueli0_.jobid as jobid, jobqueueli0_.userid as userid, jobqueueli0_.status as status, jobqueueli0_.priority as priority, jobqueueli0_.startdate as startdate, jobqueueli0_.starttime as starttime, jobqueueli0_.external_eval as externa10_, jobqueueli0_.read_sysin as read_sysin, jobqueueli0_.servername as servername, jobqueueli0_.last_mod_user as last_mo13_, jobqueueli0_.last_mod_date as last_mo14_ from job_queue_list jobqueueli0_ where (jobqueueli0_.status=? )and(jobqueueli0_.servername=? )and((jobqueueli0_.startdate<=? )or(jobqueueli0_.startdate is null ))and((jobqueueli0_.starttime<=? )or(jobqueueli0_.starttime is null )) order by jobqueueli0_.priority , jobqueueli0_.jobid
net.sf.hibernate.impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC connection to [jdbc:oracle:thin:@MYDATABASE]

-net.sf.hibernate.SQL - select jobqueueli0_.instidq as instidq, jobqueueli0_.instid as instid, jobqueueli0_.instcd as instcd, jobqueueli0_.jobid as jobid, jobqueueli0_.userid as userid, jobqueueli0_.status as status, jobqueueli0_.priority as priority, jobqueueli0_.startdate as startdate, jobqueueli0_.starttime as starttime, jobqueueli0_.external_eval as externa10_, jobqueueli0_.read_sysin as read_sysin, jobqueueli0_.servername as servername, jobqueueli0_.last_mod_user as last_mo13_, jobqueueli0_.last_mod_date as last_mo14_ from job_queue_list jobqueueli0_ where (jobqueueli0_.status=? )and(jobqueueli0_.servername=? )and((jobqueueli0_.startdate<=? )or(jobqueueli0_.startdate is null ))and((jobqueueli0_.starttime<=? )or(jobqueueli0_.starttime is null )) order by jobqueueli0_.priority , jobqueueli0_.jobid

-net.sf.hibernate.impl.BatcherImpl - preparing statement
-net.sf.hibernate.type.StringType - binding '2005/02/11' to parameter: 3
-net.sf.hibernate.type.StringType - binding 'dars1' to parameter: 2
-net.sf.hibernate.type.StringType - binding '14:28' to parameter: 4
-net.sf.hibernate.type.StringType - binding 'D' to parameter: 1
-net.sf.hibernate.loader.Loader - processing result set
-net.sf.hibernate.loader.Loader - done processing result set (0 rows)
-net.sf.hibernate.impl.BatcherImpl - done closing: 0 open PreparedStatements, 0 open ResultSets
-net.sf.hibernate.impl.BatcherImpl - closing statement
-net.sf.hibernate.loader.Loader - total objects hydrated: 0
-net.sf.hibernate.impl.SessionImpl - initializing non-lazy collections
-org.springframework.orm.hibernate.HibernateTemplate - Eagerly flushing Hibernate session
-net.sf.hibernate.impl.SessionImpl - flushing session
-net.sf.hibernate.impl.SessionImpl - Flushing entities and processing referenced collections
-net.sf.hibernate.impl.SessionImpl - Processing unreferenced collections
-net.sf.hibernate.impl.SessionImpl - Scheduling collection removes/(re)creates/updates
-net.sf.hibernate.impl.SessionImpl - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
-net.sf.hibernate.impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
-net.sf.hibernate.impl.SessionImpl - executing flush
-net.sf.hibernate.impl.SessionImpl - post flush
-org.springframework.orm.hibernate.SessionFactoryUtils - Closing Hibernate session
-net.sf.hibernate.impl.SessionImpl - closing session
-net.sf.hibernate.impl.SessionImpl - disconnecting session

is servername define like CHAR in database ?

Name Null? Type
----------------------------------------- -------- ----------------------------
INSTIDQ NOT NULL CHAR(2)
INSTID NOT NULL CHAR(8)
INSTCD NOT NULL CHAR(3)
JOBID NOT NULL CHAR(16)
USERID NOT NULL CHAR(30)
STATUS CHAR(1)
PRIORITY NUMBER(38)
STARTDATE CHAR(10)
STARTTIME CHAR(5)
EXTERNAL_EVAL CHAR(1)
READ_SYSIN CHAR(1)
SERVERNAME CHAR(10)
LAST_MOD_USER VARCHAR2(30)
LAST_MOD_DATE DATE

CHAR in oracle have padded blank chars (length is always 10)
for example, if you save 'dars1', oracle add blank and in column is 'dars1 ' (5 blanks)

try this from sqlplus

select servername,length(servername)
from <your_table_name>

YAY!! Thanks for the help. You have been given one credit.

Yeah, as you can see we did try to remedy that problem with our RightTrimmedStringUserType10 class... but it is malfunctioning because we tried to update the hibernate jar file to the newest version (but turns out we're going to do that later so we went back to 2.1.8) and somehow our stuff got broken.

I ended up doing
(adding 5 spaces after the dars1)

to get the query to work, so now I know what to fix.

Thanks again

don't use char (if you can) - use varchar2 or nvarchar2