Filter on ListGrid not working

[susanths]

Hi,
I have a Listgrid with

showFilterEditor: true,
filterOnKeypress: true,

The filter works when I am using "contains (default)" clause. However when I use "does not contain" clause in the filter, the query doesnt seem to work. Going through the server logs, I found that, once I change the criteria to "does not contain", the query string changes from uppercase to lower case.

The query changes from "SITE LIKE '%IRVINE%' " to "SITE LIKE '%irvine%' ".

I use Mysql database and Smartclient12.0 Evaluation version.

How can I get a consistent filter response.

Thanks,
SS

[Isomorphic]

This is not reproducible on our side. Could you please share more details, like complete ds.xml and ListGrid creation code?

[Isomorphic]

If you want to set "does not contain" programmatically as the filter for a field, you can set the ListGridField.filterOperator property when creating the grid. Otherwise, provide some sample code - perhaps a modified version of the Live Filter sample - so we can see what you're doing.

[susanths]

Hi,
Thanks for the reply. I had tried the Live Filter in the example section before posting the question here, which seemed to work fine.
Here is the main portion of the listgrid

    this.ddList = isc.ListGrid.create({
        ID: "ddList",
        width: "100%",
        height: "100%", margin: 1,
        dataSource:"dd_view",
        alternateRecordStyles:true,
        autoDraw: false,

        fetchDelay: 500,
        //dataFetchMode: "paged",
        showFilterEditor: true,
        filterOnKeypress: true,
        canPickFields:true,
        canMultiSort: true,
        canSelectCells: true,
        useCopyPasteShortcuts: true,
        useAdvancedFieldPicker: true,
        canSelectCellGroups: true,
        recordCanSelectProperty: true,
        //showGridSummary: true,
        fields: [
        {
            name:"SITE",
            length:15,
            columnCode:"98defd6ee70dfb1dea416cecdf391f58",
            type:"text",
            validators:[
            ]
        }]
 });

and here is the relevant part of the ds.xml

<DataSource 
    serverType="sql"
    dbName="****"
    ID="dd_view"
    generatedBy="v12.0p_2018-11-20/EVAL Deployment 2018-11-20"
    dataSourceVersion="1"
    tableName="dd_view"
>
    <fields>
    <field sqlType="varchar" sqlLength="15" name="SITE" length="15" type="text"></field>
    </fields>
</DataSource>

I have not put the rest of the fields. I hope the above snippet has enough data.
I was wondering whether it is to do anything with MySql driver. I added com.mysql.jdbc_5.1.5.jar in the WEB-INF/lib folder to add mysql support.

Thanks,
Sushanth

[Isomorphic]

We do see the case difference you mention in the sql queries, but according to the SQL docs, such queries are by default case insensitive and our own experimentation confirms that. Using your example code above and some sample data, together with the 5.1.47 connector, we see everything behaving as expected.

Can you provide:
- A screen shot of your database configuration page, typically located at http://localhost:8080/tools/adminConsole.jsp?
- Complete SmartClient server logs that accompany your query and logs from the RPC tab of the developer console?
- Test data to accompany your ds.xml file?

Otherwise, there's not much we can do.

[susanths]

Hello,
Thank you for the response. I think I found the issue :). The collate set in the database is latin1_bin, which is case sensitive. This causes the lowercase query to return the uppercase results. Is there a way to make sure the filter passes the parameters in the correct case to the backend.

Thanks,
-S

[Isomorphic]

If your DB is going to force case sensitivity in this situation, then you should use the case sensitive operator here. Otherwise client and server filtering will not match.

[Isomorphic]

Actually, we're not even clear what you're saying here: you just said it's case sensitive and then immediately talked about lowercase filters matching uppercase results, which would generally mean non-case-sensitive. So which is it?

[susanths]

As per the documentation Mysql doc link you had mentioned https://dev.mysql.com/doc/refman/5.7...nsitivity.html earlier, the default collation is latin1_swedish_ci which is case-insensitive. The listgrid filter works great in this situation, because '%ABC%' is the same as '%abc%'. However in my case the collation at the database is set to latin1_bin/latin1_general_cs which is case sensitive to the queries. In the case of the example mentioned, Smartclient changes the case from upper case to lower-case before the query is sent to the database. Since my database is _cs, it returns unexpected values.

I believe it is a bug in the isc library where the user types upper-case into the listgrid filter, and isc converts the upper case to lower case without checking the collation in the database.

Is there a work-around to make sure isc sends the same 'case' whatever the user enters in the filter, to the database.

[Isomorphic]

Yes: as we just said, use a case sensitive filter operator. We don't change the case if you specify case sensitive filtering.

Basically, your collation setting removes our ability to do case insensitive filtering, so stop telling the framework to attempt it.

[susanths]

I set

       autoFetchTextMatchStyle: "exactCase",

in the ListGrid.
It still seem to pass the filter criteria as lower case.

I am not able to find the documentation for case sensitive filter operator. Could you point me to the "case sensitive filter operator" example or documentation?

[Isomorphic]

Our tests do not show that behavior.

The docs explaining the use of operators and criteria are under Operator and Criteria/AdvancedCriteria.