[nhibernate-issues] [NHJIRA] Updated: (NH-2546) MSSQL Like criteria doesn't work if expression length is more then column length

[Julian M. (JIRA) <nh...@gm...>]

     [ http://216.121.112.228/browse/NH-2546?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Julian Maughan updated NH-2546:
-------------------------------

    Attachment: NH-2546.patch

An easier/quicker/better(?) solution is a configuration setting that can turn on or off the setting of parameter sizes in the driver. The 'prepare_sql' setting used to serve this purpose prior to being commented out in r4915.

I've attached a quick implementation (a code spike).

Before:
2.x: prepare_sql = false (default) -> parameter sizes not set (ADO.NET infers the size from the parameter value) and IDbCommand.Prepare not called
2.x: prepare_sql = true -> parameter sizes are set and IDbCommand.Prepare called

After:
3.1: adonet.set_parameter_sizes = true (default) -> parameter sizes are set
3.1: adonet.set_parameter_sizes = false -> parameter sizes not set (ADO.NET infers the size from the parameter value)
3.1: prepare_sql = false (default): IDbCommand.Prepare is not called
3.1: prepare_sql = true: IDbCommand.Prepare is called

Note that allowing ADO.NET to infer the size of parameter can lead to sub-optimal performance because of reduced query-plan reuse: queries with command parameters of different size are regarded as different queries, causing a new query plan to be calculated rather than using a cached one. This applies to SQL Server, not sure about other RDBMSs. I believe this was the reason for the r4915 change (see NH-2484).

> MSSQL Like criteria doesn't work if expression length is more then column length
> --------------------------------------------------------------------------------
>
>                 Key: NH-2546
>                 URL: http://216.121.112.228/browse/NH-2546
>             Project:  NHibernate
>          Issue Type: Bug
>          Components: DataProviders / Dialects
>    Affects Versions: 3.0.0.GA
>            Reporter: Denis
>            Priority: Minor
>         Attachments: NH-2546.patch
>
>
> Consider the following situation:
> mapping file contains the following property
> <property name="PostalCode" column="postal_code" type="String(10)"
> not-null="true"/>
> When I apply the following restriction 
> Restrictions.Like("PostalCode", "%9108[ -]MC%")
> it doesn't work as expected.
> Generated query looks like this:
> SELECT count(*) as y0_ FROM PostalCodes this_ WHERE this_.postal_code like @p0;@p0 = '%9108[ -]MC%' [Type: String (10)] 
> Parameter value length ('%9108[ -]MC%') is 12 symbols and it is correct expression but the value is cutted to '%9108[ -]M' which is not correct. 

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