Like query...Char vs. Varchar issue?

["Voorhoeve, Niels {PBG}" <ni...@pepsi.com>]

I've got a problem with the following config below.  

I am testing with a UserCostCenterDept object with the userId set as a
param.  When I pass in an object with the costCenter property set and dept
null, it formulates the query but returns no row.  On the other hand, if the
department is set and the costCenter is null, it returns a row.  The only
difference is that costCenter is a char(3) and department is a varchar(6).
Both rows are in the db, so I'm at a loss.  I tried using = for the
cost_center, but that did not help.  Any ideas?

Thanks,
Niels

    <select id="getUserCostCenterDept" 
        resultClass="UserCostCenterDept" 
        parameterClass="UserCostCenterDept" 
        resultMap="UserCostCenterDept-result"
        cacheModel="UserCostCenter-cache" >
        <![CDATA[
            select
                user_id  ,
                cost_center  ,
                department  ,
                description  ,
                record_date  ,
                last_act_date 
            from
                COST_CENTER_DEPT
            where 
				 user_id = #userId# 
        ]]>
       <dynamic prepend="">
            <isNotEmpty prepend="and" property="costCenter">
            	cost_center like #costCenter# 
            	<!--  cost center is char(3) -->
            </isNotEmpty>
            <!--  costCenter used in testing update -->
            <isNotEmpty prepend="and" property="department">
            	department like #department#
            	<!--  dept is varchar(6) -->
            </isNotEmpty>
        </dynamic>
        
    </select>

SQL Logging shows:


2005-10-28 14:21:51,498 [main] DEBUG java.sql.PreparedStatement -
{pstm-100001} PreparedStatement:                       select
user_id  ,                 cost_center  ,                 department  ,
description  ,                 record_date  ,                 last_act_date
from                 COST_CENTER_DEPT             where       user_id = ?
and              cost_center like ?

2005-10-28 14:21:51,498 [main] DEBUG java.sql.PreparedStatement -
{pstm-100001} Parameters: [CPEPSI, 070]
2005-10-28 14:21:51,920 [main] DEBUG java.sql.Connection - {conn-100003}
Connection
2005-10-28 14:21:51,920 [main] DEBUG java.sql.PreparedStatement -
{pstm-100004} PreparedStatement:                       select
user_id  ,                 cost_center  ,                 department  ,
description  ,                 record_date  ,                 last_act_date
from                 COST_CENTER_DEPT             where       user_id = ?
and              department like ?

2005-10-28 14:21:51,935 [main] DEBUG java.sql.PreparedStatement -
{pstm-100004} Parameters: [ABARVARO, 0152]

Re: Like query...Char vs. Varchar issue?

[Clinton Begin <cl...@gmail.com>]

Try trimming the CHAR field, as it may be padded with whitespace.

Cheers,
Clinton

On 10/28/05, Voorhoeve, Niels {PBG} <ni...@pepsi.com> wrote:
>
> I've got a problem with the following config below.
>
> I am testing with a UserCostCenterDept object with the userId set as a
> param. When I pass in an object with the costCenter property set and dept
> null, it formulates the query but returns no row. On the other hand, if
> the
> department is set and the costCenter is null, it returns a row. The only
> difference is that costCenter is a char(3) and department is a varchar(6).
> Both rows are in the db, so I'm at a loss. I tried using = for the
> cost_center, but that did not help. Any ideas?
>
> Thanks,
> Niels
>
> <select id="getUserCostCenterDept"
> resultClass="UserCostCenterDept"
> parameterClass="UserCostCenterDept"
> resultMap="UserCostCenterDept-result"
> cacheModel="UserCostCenter-cache" >
> <![CDATA[
> select
> user_id ,
> cost_center ,
> department ,
> description ,
> record_date ,
> last_act_date
> from
> COST_CENTER_DEPT
> where
> user_id = #userId#
> ]]>
> <dynamic prepend="">
> <isNotEmpty prepend="and" property="costCenter">
> cost_center like #costCenter#
> <!-- cost center is char(3) -->
> </isNotEmpty>
> <!-- costCenter used in testing update -->
> <isNotEmpty prepend="and" property="department">
> department like #department#
> <!-- dept is varchar(6) -->
> </isNotEmpty>
> </dynamic>
>
> </select>
>
> SQL Logging shows:
>
>
> 2005-10-28 14:21:51,498 [main] DEBUG java.sql.PreparedStatement -
> {pstm-100001} PreparedStatement: select
> user_id , cost_center , department ,
> description , record_date , last_act_date
> from COST_CENTER_DEPT where user_id = ?
> and cost_center like ?
>
> 2005-10-28 14:21:51,498 [main] DEBUG java.sql.PreparedStatement -
> {pstm-100001} Parameters: [CPEPSI, 070]
> 2005-10-28 14:21:51,920 [main] DEBUG java.sql.Connection - {conn-100003}
> Connection
> 2005-10-28 14:21:51,920 [main] DEBUG java.sql.PreparedStatement -
> {pstm-100004} PreparedStatement: select
> user_id , cost_center , department ,
> description , record_date , last_act_date
> from COST_CENTER_DEPT where user_id = ?
> and department like ?
>
> 2005-10-28 14:21:51,935 [main] DEBUG java.sql.PreparedStatement -
> {pstm-100004} Parameters: [ABARVARO, 0152]
>
>