[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6655) trim function on DB2 is broken

[Steve Ebersole (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6655?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=43514#comment-43514 ] 

Steve Ebersole commented on HHH-6655:
-------------------------------------

This is not "broken".  The error you are getting is related to an invalid use of bind variables (parameters) according to db2.

>From http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/rsql0400.htm :
{quote}
SQL0418N   A statement contains a use of a parameter marker that is not valid.
Explanation:

Untyped parameter markers cannot be used:

    in a SELECT list
    as the sole argument of a datetime arithmetic operation
    in some cases as the sole argument of a scalar function
    as a sort key in an ORDER BY clause 

Parameter markers can never be used:

    in a statement that is not a prepared statement
    in the fullselect of a CREATE VIEW statement
    in the triggered action of a CREATE TRIGGER statement
    in a query captured by DB2 Query Patroller 
{quote}

> trim function on DB2 is broken
> ------------------------------
>
>                 Key: HHH-6655
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6655
>             Project: Hibernate Core
>          Issue Type: Bug
>    Affects Versions: 4.0.0.CR2
>            Reporter: Strong Liu
>            Assignee: Steve Ebersole
>             Fix For: 4.0.0.CR3
>
>
> For DB2 a warning message is generated by TempateRenderer which is not seen for other databases:
> WARN: HHH00174:Function template anticipated 3 arguments, but 2 arguments encountered
> see test _org.hibernate.ejb.criteria.CriteriaCompilingTest#testTrim_ for more details
> Also the query created is different in case of DB2:
> Hibernate: select replace(replace(ltrim(rtrim(replace(replace(customer0_.NAME,' ','${space}$'),?,' '))),' ',?),'${space}$',' ') as col_0_0_ from CUSTOMER_TABLE customer0_ where customer0_.NAME=? fetch first 2 rows only
> and finally it fails because of above warning:
> javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: DB2 SQL Error: SQLCODE=-313, SQLSTATE=07001
> For other DBs same code generates following query and test passes:
> Hibernate: select trim(BOTH ? from customer0_.NAME) as col_0_0_ from CUSTOMER_TABLE customer0_ where customer0_.NAME=? limit ?

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira