JPQL SELECT Distinct Query with clob Column works fine on WAS7, but WAS9 doesn't

Question

I'm Using Spring 3.0 based JPA2.0

@Entity
@Table(name = "MY_TABLE", schema = "DB2XXXX")
public class MyTable  implements java.io.Serializable,Comparable<MyTable> {
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "MY_TABLE_ID")
    private BigInteger myTableId;
    
    @Column(name = "FREETEXT")
    private String freetext;
    
    public MyTable() {
    }
    
    public MyTable(BigInteger myTableId,String freetext) {
        super();
        this.myTableId = myTableId;
        this.freetext = freetext
    }
    
    public BigInteger getMyTableId() {
        return myTableId;
    }
    public void setMyTableId(myTableId) {
        this.myTableId = myTableId;
    }
    public String getFreetext() {
        return freetext;
    }
    public void setFreetext(String freetext) {
        this.freetext = freetext;
    }
}

freetext is clob column

    public List<MyTable> getData(BigInteger myTableId) throws Exception {
        StringBuilder jpql = new StringBuilder();
        List<MyTable> resultList = null;
        try {
            jpql.append("SELECT distinct data FROM MyTable data ")
            resultList = findWithJpql(entityManager, jpql.toString(), null,false, 0, 0);
        } catch (Exception e) {
        }
        return resultList;
    }

    @TransactionAttribute(value = TransactionAttributeType.REQUIRED)
    @SuppressWarnings("unchecked")
    public <T> T findWithJpql(EntityManager em, String jpql, Map<String, Object> conditions, boolean singleResult, int firstResult, int maxResult) {
        Query query = em.createQuery(jpql);
        if(firstResult > 0){
            query.setFirstResult(firstResult);
        }
        if(maxResult > 0){
            query.setMaxResults(maxResult);
        }

        if(conditions != null && conditions.size() > 0){
            for(String condition : conditions.keySet()){
                query.setParameter(condition, conditions.get(condition));
            }
        }
        if(singleResult){
            try{
                return (T)query.getSingleResult();
            }catch(NoResultException nrex){
                return null;
            }
        }else{
            T a = (T)query.getResultList();
            return a;
        }
    }

when call query method in WAS9 Db2 error code -134 : A string value with a length attribute greater than 255 bytes is not allowed in a SELECT list that also specifies DISTINCT. **But this query works fine on WAS7 **

[21/12/2564, 10:28:10:731 ICT] 0000018e eclipselink W Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.8.WAS-v20181218-0accd7f): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-134, SQLSTATE=42907, SQLERRMC=REMARK, DRIVER=4.25.13 Error Code: -134

Internal Exception: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-134, SQLSTATE=42907, SQLERRMC=REMARK, DRIVER=4.25.13 Error Code: -134

Answer 1

If freetext is a Clob column, try to annotate it properly with @Lob . Check this post Using EclipseLink JPA to bind Strings larger than 255 characters .

The error might be related to the JPA provider change between WAS7 and 9 (previously it was OpenJPA). If you dont care abouth JPA 2.1, you can switch back to 2.0 OpenJPA, for example like this using wsadmin:

wsadmin>AdminTask.modifyJPASpecLevel('[-node myNode -server server1 -specLevel 2.0]')  
CWWJP8813I: The JPA specification compliance level for Server server1 has been successfully set to JPA 2.0.

See the following link for details: https://www.ibm.com/docs/en/was/9.0.5?topic=applications-identifying-modifying-jpa-specification-level