[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3551) Boolean substitution in informix

[Rouvignac (JIRA)]

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

Rouvignac commented on HHH-3551:
--------------------------------

Maybe I mistaken with the update in the driver.
As i remember our problems disappeared with the use of 3.50.JC5 driver.
We still use that one with Informix 11.50.

In order to give some more help, here is the latest custom dialect we use :


import java.sql.Types;

import org.hibernate.Hibernate;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.NvlFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;

public class InformixDialect extends org.hibernate.dialect.InformixDialect {

    private static final int CLOB_MAXIMUM_LENGTH = 32739;

    public InformixDialect() {
        super();

        this.registerColumnType(Types.CLOB, InformixDialect.CLOB_MAXIMUM_LENGTH, "lvarchar($l)");

        this.registerFunction("current_date", new NoArgSQLFunction("today", Hibernate.DATE, false));
        this.registerFunction("coalesce", new NvlFunction());

        this.registerFunction("year", new SQLFunctionTemplate(Hibernate.INTEGER, "year(?1)"));
        this.registerFunction("month", new SQLFunctionTemplate(Hibernate.INTEGER, "month(?1)"));
        this.registerFunction("day", new SQLFunctionTemplate(Hibernate.INTEGER, "day(?1)"));

        this.registerFunction("extract", new SQLFunctionTemplate(Hibernate.INTEGER, "?1(?3)"));

    }

    @Override
    public String toBooleanValueString(boolean bool) {
        return bool ? "t" : "f";
    }

    @Override
    public boolean supportsTemporaryTables() {
        return true;
    }

}


In the persistence.xml we defined :
         <property name="hibernate.query.substitutions" value="'true'='t', 'false'='f'" />

This is for queries which tests: "something='true'".
In source code we use "something=?" but we have a query builder for our customers were they write "something='true'" in queries.




> Boolean substitution in informix
> --------------------------------
>
>                 Key: HHH-3551
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3551
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-sql
>    Affects Versions: 3.3.1
>         Environment: Hibernate 3.2
> Informix 9.40
>            Reporter: Rouvignac
>   Original Estimate: 2h
>  Remaining Estimate: 2h
>
> HQL Query :
> select order from Order order where order.printed = ? 
> Parameter : 
> true, false, Boolean.TRUE or Boolean.FALSE
> When the request is executed we get the following error :
> SQLSTATE: IX000
> SQL CODE: -674
>  674: Routine (equal) can not be resolved. 
> If as parameter we use "t" ot "f" everything works fine but it will not work with other DB.
> As a workaround we can use  :
> property name="hibernate.query.substitutions">true t, false f</property>
> I investigated in Dialects :
> Dialect.java
> 	public String toBooleanValueString(boolean bool) {
> 		return bool ? "1" : "0";
> 	}
> PostgreSQLDialect.java
> 	public String toBooleanValueString(boolean bool) {
> 		return bool ? "true" : "false";
> 	}
> InformixDialect.java uses Dialect.java toBooleanValueString method.
> In my mind toBooleanValueString  should be added to InformixDialect.java as follow :
> 	public String toBooleanValueString(boolean bool) {
> 		return bool ? "t" : "f";
> 	}

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira