0013153: DAL queries don't work with CLOB (oracle)

Description

If a column in (ora) DB is defined as CLOB type, it is not possible to use in DAL queries.

I haven't tested the behavior in PostgreSQL where there CLOB is converted into TEXT type.

Note that the java object is correctly composed and populated.

This means this code doesn't work:

     OBCriteria<Preference> qPref = OBDal.getInstance().createCriteria(Preference.class);
     qPref.add(Expression.eq(Preference.PROPERTY_ATTRIBUTE, "testProperty"));
     qPref.add(Expression.eq(Preference.PROPERTY_SEARCHKEY, "anotherValue"));

     Preference newPref = qPref.list().get(0);

whereas this other one does work:

Preference newPref = null;
    OBCriteria<Preference> qPref = OBDal.getInstance().createCriteria(Preference.class);
    qPref.add(Expression.eq(Preference.PROPERTY_ATTRIBUTE, "testProperty"));
    for (Preference p : qPref.list()) {
      if (p.getSearchKey().equals("anotherValue")) {
        newPref = p;
      }
    }

The following exception is raised for queries:

org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.loader.Loader.doList(Loader.java:2223)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
    at org.hibernate.loader.Loader.list(Loader.java:2099)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
    at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
    at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
    at org.openbravo.dal.service.OBCriteria.list(OBCriteria.java:89)
...

Steps To Reproduce

-Create a CLOB column in DB and in AD
-Use it in DAL queries.

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

related to	defect	0015555		closed	alostale	In Oracle, Enterprise Module Management window shows an error message on open
has duplicate	defect	0015556		closed	mtaal	OBCriteria is not able to filter Oracle's CLOB datatype

Notes
(0026960) shuehner (administrator) 2010-05-05 17:17	After resolving this issue an reintegrating the pi-preference branch: the junit test PreferenceTest should be updated (search for 0013153 in it)

(0038545) shuehner (administrator) 2011-06-27 17:44	Just retested that on latest pi. This bug is still present with the new hibernate version.

(0052497) AugustoMauch (administrator) 2012-09-24 23:35	Effort: 10 Impact: low Plan: mid

(0132173) cberner (viewer) 2021-10-05 18:08	Still happening after updating hibernate to version 5.5.7. Tried running the PreferenceTest with the code reintegrated and got this result: [junit] 18:07:09.893 INFO org.openbravo.test.base.OBBaseTest - *** Starting test case: org.openbravo.test.preference.PreferenceTest.test14SolvedConflict [junit] 18:07:09.932 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-00932: inconsistent datatypes: expected - got CLOB

Issue History
Date Modified	Username	Field	Change
2010-04-29 09:45	alostale	New Issue
2010-04-29 09:45	alostale	Assigned To	=> mtaal
2010-04-29 09:45	alostale	OBNetwork customer	=> No
2010-05-03 08:27	alostale	Status	new => scheduled
2010-05-05 17:17	shuehner	Note Added: 0026960
2010-05-05 17:19	shuehner	Issue Monitored: shuehner
2011-01-05 08:29	alostale	Relationship added	has duplicate 0015556
2011-01-05 08:30	alostale	Relationship added	related to 0015555
2011-04-05 21:27	mtaal	Assigned To	mtaal => alostale
2011-06-27 17:44	shuehner	Note Added: 0038545
2011-11-23 10:41	alostale	Type	defect => design defect
2012-09-24 23:35	AugustoMauch	Note Added: 0052497
2012-09-24 23:35	AugustoMauch	Priority	urgent => normal
2017-03-31 14:36	alostale	Status	scheduled => acknowledged
2017-04-10 14:34	alostale	Assigned To	alostale => platform
2021-10-05 18:08	cberner	Note Added: 0132173
2022-02-01 08:07	alostale	Assigned To	platform => Triage Platform Base