When testing EJQBL query on PostgreSQL, I noticed that the result padded CHAR columns are not trimmed. A DbAdapter would install an ExtendedType that implements column trimming behavior appropriate for the target DB. In case of EJBQL query (that is internally translated to SQLTemplate), the ExtendedType didn't have enough information about the column to make a trim/no-trim decision. The missing piece is JDBC type.
Column information is passed to Cayenne via #result directive in SQLTemplate... It has java class parameter, but not JDBC type parameter. I am thinking of redesigning the #result directive into a more usable variety, that addresses this concern, and other #result limitations. To address backwards compatibility concerns, instead of redefining #result, I would suggest a new directive called #column:
#column(index javaType jdbcType)
#column(index javaType jdbcType rowLabel)
SELECT t0.ARTIST_NAME as C1, t0.DATE_OF_BIRTH as C2 #column(2 'java.sql.Timestamp' 'TIMESTAMP')
- It solves the main limitation of the #result - the need to tag ALL columns (so it will require backend support for partial result set descriptor that is filled in from metadata on the fly),
- it doesn't require the SQL column alias to be teh same as DataRow value key.
- It can appear anywhere in the SQL, not necessarily tied to the SQL column declaration, so "SELECT * #column(3 'String') FROM..." is valid.
- It supports JDBC type parameter, letting adapter to make the right decision on the value handling (the original motivation for the new directive).