FilterBuilder, datetime & Today

[tgilbert]

Hi,

We are using the FilterBuilder to create a criteria which is then passed to a ListGrid to query data from the database.

Our problem is that when we select the "equals / today" criteria on a datetime SQLServer columnn, the generated query contains a condition like this "CREATED = '2014-02-10T00:00:00'" which will not work as it's a datetime column.

Here is the issued DSRequest:

{
    dataSource:"businessUnit", 
    operationType:"fetch", 
    componentId:"isc_FircoListGrid_0", 
    data:{
        operator:"and", 
        criteria:[
            {
                fieldName:"CREATED", 
                operator:"equals", 
                value:"2014-02-09T23:00:00"
            }
        ]
    }, 
    startRow:0, 
    endRow:75, 
    textMatchStyle:"exact", 
    resultSet:[ResultSet ID:isc_ResultSet_0 (created by: isc_FircoListGrid_0)], 
    callback:{
        caller:[ResultSet ID:isc_ResultSet_0 (created by: isc_FircoListGrid_0)], 
        methodName:"fetchRemoteDataReply"
    }, 
    willHandleError:true, 
    showPrompt:true, 
    prompt:"Finding Records that match your criteria...", 
    oldValues:{
        operator:"and", 
        criteria:[
            {
                fieldName:"CREATED", 
                operator:"equals", 
                value:"2014-02-09T23:00:00"
            }
        ]
    }, 
    requestId:"businessUnit$62730", 
    internalClientContext:{
        requestIndex:13
    }, 
    useStrictJSON:true, 
    fallbackToEval:true, 
    lastClientEventThreadCode:"TMR0", 
    bypassCache:true
}

(I also attached a screen of the selected builder criteria)

Please also note that if, for any reason, SmartGWT decides to NOT fetch the data (willfetchdata returns false), the criteria is correctly applied... so there seem to be a mismatch between the JavaScript and the SQL logic there ?

Many thanks for your help,

Thomas

PS: SmartGWT 4.1d

[Isomorphic]

The problem is that the date value in the criteria is being submitted is a String. That's not the normal behavior of a FilterBuilder - the criteria it produces would be a true Date value. Can you show how to configure a FilterBuilder such that it would produce a String value for a datetime field?

Also, please remember to post your full version (not just 4.1d) and to test with the latest if you suspect a bug.

[stonebranch1]

Any follow ups on this one?

I am seeing my DateTime fields coming over to the server as a String value as well!!!

(4.1p dated 2014-04-01)

[Isomorphic]

No follow-ups will be coming from Isomorphic, because this functionality works as expected in our tests, and this appears to be bad usage.

As we said:

Can you show how to configure a FilterBuilder such that it would produce a String value for a datetime field?

[stonebranch1]

I don't do any special customization of the FilterBuilder other than just the following:

        setTopOperator(LogicalOperator.AND);
        setAllowEmpty(Boolean.TRUE);

I thought it was because of my call to format the DateTime items in a certain format using

DateUtil.setNormalDatetimeDisplayFormatter(...)

Which formats DateTimes via ""yyyy-MM-dd HH:mm:ss".
However, removing the call to DateUtil I see the default formatting (so I know it took) and yet I still see Strings on the Server side:

AdvancedCriteria:[and:[{sysCreatedOn lessThan 04-01-2014 15:59}]]

And if I pull the value out of the map to check it:
sysCreatedOn < value (java.lang.String) : 04-01-2014 15:59

[Isomorphic]

We seem to need to remind you of this a lot but.. we show this working fine, and details matter, so we need to see complete running code, not a guided description of the parts you think are already correct.

[stonebranch1]

We are building a complex application have our own server side code that handle the datasource DMI invocations so it is never an easy task to try and produce a complete working example.

I was hoping you would have tests for this that you could verify, and in fact you stated your tests run fine.

Any chance you can give me a test or working example of this working as expected :)

[Isomorphic]

There are several working samples of the FilterBuilder in the Showcase.

Pick any sample and modify it to introduce elements of your application, or, temporarily disable elements of your application until the problem disappears and isolate from there.

This is just standard problem isolation procedure.

[stonebranch1]

I will see what I can do. Thanks.

By the way, if I do set the date format for DateTime using the DateUtil mechanism, are there other "things" I should be doing as well to complete that solution? That is, is there some parsing logic that goes the other way that I need to take care of as well?

Just wanna make sure I am following best practice (like Java equals/hashcode).

[Isomorphic]

Yes. If you set a formatter, your parser must be able to parse the format.

[stonebranch1]

I added the code below but it never seems to get called. Is there something more I need to do?

        DateUtil.setDateParser(new DateParser() {
            @Override
            public Date parse(String dateString) {
                final DateTimeFormat format = DateTimeFormat.getFormat(SharedConstants.DATE_TIME_FORMAT_Z);
                Date date = format.parse(dateString);
                return date;
            }
        });

The above parsing matches the formatting being used.

Code Level: SmartClient Version: v9.1p_2014-04-29/Pro Deployment (built 2014-04-29)

[Isomorphic]

Well, we show no issues - the date parser is called at all expected times, and countless other developers have successfully used this API.

Let us know if you can put together complete, runnable code that demonstrates a framework issue.

[stonebranch1]

I got it working now. The problem was the ordering/sequence of the calls. I am not sure why as I did not see anything documented about it but thought I would mention it in case anyone else runs into the same thing.

When I had the line:

        DateUtil.setDateParser(ClientUtils.getDateParser());

before the code below it did not work, but when I moved it to after the code below it started working as expected. After it was working I tried moving it above again just to confirm and sure enough that prevented it from working.

        DateUtil.setDefaultDateSeparator("-");
        DateUtil.setDateInputFormat("YMD");
        DateFormatStringFormatter dateFormatter = new DateFormatStringFormatter(SharedConstants.DATE_FORMAT);
        DateUtil.setShortDateDisplayFormatter(dateFormatter);
        DateUtil.setNormalDateDisplayFormatter(dateFormatter);

Thanks for you assistance on it.

[Isomorphic]

System-wide formatter and parser settings must be set before the affected components are created, but there is no other order dependency that we know of.

Let us know if you can produce a test case showing an unexpected order dependency.

[stonebranch1]

This above code is right at the very beginning of our application before anything is done. All that code is together.

Basically this works:

        DateUtil.setNormalDatetimeDisplayFormatter(ClientUtils.getDateTimeFormatter());
        DateUtil.setShortDatetimeDisplayFormatter(ClientUtils.getDateTimeFormatter());
        DateUtil.setDefaultDateSeparator("-");
        DateUtil.setDateInputFormat("YMD");
        DateFormatStringFormatter dateFormatter = new DateFormatStringFormatter(SharedConstants.DATE_FORMAT);
        DateUtil.setShortDateDisplayFormatter(dateFormatter);
        DateUtil.setNormalDateDisplayFormatter(dateFormatter);
        DateUtil.setDateParser(ClientUtils.getDateParser());

but this did not

        DateUtil.setNormalDatetimeDisplayFormatter(ClientUtils.getDateTimeFormatter());
        DateUtil.setShortDatetimeDisplayFormatter(ClientUtils.getDateTimeFormatter());
        DateUtil.setDateParser(ClientUtils.getDateParser());
        DateUtil.setDefaultDateSeparator("-");
        DateUtil.setDateInputFormat("YMD");
        DateFormatStringFormatter dateFormatter = new DateFormatStringFormatter(SharedConstants.DATE_FORMAT);
        DateUtil.setShortDateDisplayFormatter(dateFormatter);
        DateUtil.setNormalDateDisplayFormatter(dateFormatter);

where the only change is the order of where setDateParser is.

I have it working so all good, I just thought you and possibly others might wanna be aware of it.