Multiple select item on listgrid problem

[azuniga]

Currently using smartgwtee-3.0p-NB-20120516 on RHEL 5.x. I am having problems with the multiple select item on a listgrid. My multiple select item has a datasource. When I select an item from the selectitem, i get no records back. The sql query generated by smartclient puts 2 extra '' around the item i selected. Here is what I grab from the console:

criteria:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"PTNAME",
                operator:"inSet",
                value:[
                    "Water",
                    "Height"
                ]
            },
            {
                fieldName:"START_TIMESTAMP",
                operator:"greaterOrEqual",
                value:new Date(1333324800000)
            }
        ]
    },
    operationConfig:{
        dataSource:"MyDataSource",
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_mytable_0_Table",
    appID:"builtinApplication",
    operation:"MyDataSource_fetch",
    oldValues:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"PTNAME",
                operator:"inSet",
                value:[
                    "Water",
                    "Height"
                ]
            },
            {
                fieldName:"START_TIMESTAMP",
                operator:"greaterOrEqual",
                value:new Date(1333324800000)
            }
        ]
    }
}
=== 2012-06-13 15:07:13,136 [l0-8] INFO  IDACall - Performing 1 operation(s)
=== 2012-06-13 15:07:13,136 [l0-8] DEBUG AppBase - [builtinApplication.MyDataSource_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2012-06-13 15:07:13,136 [l0-8] DEBUG AppBase - [builtinApplication.MyDataSource_fetch] No public zero-argument method named '_MyDataSource_fetch' found, performing generic datasource operation
=== 2012-06-13 15:07:13,137 [l0-8] INFO  SQLDataSource - [builtinApplication.MyDataSource_fetch] Performing fetch operation with
	criteria: {criteria:[{fieldName:"PTNAME",operator:"inSet",value:["'Water'","'Height'"]},{fieldName:"START_TIMESTAMP",operator:"greaterOrEqual",value:new Date(1333324800000)}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{fieldName:"PTNAME",operator:"inSet",value:["'Water'","'Height'"]},{fieldName:"START_TIMESTAMP",operator:"greaterOrEqual",value:new Date(1333324800000)}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2012-06-13 15:07:13,137 [l0-8] INFO  SQLDataSource - [builtinApplication.MyDataSource_fetch] derived query: SELECT $defaultSelectClause FROM 	P, 
            				PAT,
            				PA, 
            				PT
             WHERE 	P.P_ID = PA.P_ID AND
    						PA.PAT_ID = PAT.PAT_ID AND
    						PT.PT_ID = P.PT_ID AND
    						(PA.VALUE > PAT.MAX_THRESHOLD OR 
    						PA.VALUE < PAT.MIN_THRESHOLD) AND
    						($defaultWhereClause)
    		 ORDER BY START_TIMESTAMP ASC
=== 2012-06-13 15:07:13,138 [l0-8] DEBUG SQLDataSource - [builtinApplication.MyDataSource_fetch] Executing row count query: SELECT COUNT(*) FROM 	P, 
            				PAT,
            				PA, 
            				PT
             WHERE 	P.P_ID = PA.P_ID AND
    						PA.PAT_ID = PAT.PAT_ID AND
    						PT.PT_ID = P.PT_ID AND
    						(PA.VALUE > PAT.MAX_THRESHOLD OR 
    						PA.VALUE < PAT.MIN_THRESHOLD) AND
    						($defaultWhereClause)
    		
=== 2012-06-13 15:07:13,138 [l0-8] DEBUG SQLDataSource - [builtinApplication.MyDataSource_fetch] Eval'd row count query: SELECT COUNT(*) FROM 	P, 
            				PAT,
            				PA, 
            				PT
             WHERE 	P.P_ID = PA.P_ID AND
    						PA.PAT_ID = PAT.PAT_ID AND
    						PT.PT_ID = P.PT_ID AND
    						(PA.VALUE > PAT.MAX_THRESHOLD OR 
    						PA.VALUE < PAT.MIN_THRESHOLD) AND
    						((((PT.Name IN ('''Water''', '''Height''')) AND PT.Name IS NOT NULL) AND (P.START_TIMESTAMP >= TO_DATE('2012-04-02 00:00:00','YYYY-MM-DD HH24:MI:SS') AND P.START_TIMESTAMP IS NOT NULL)))
    		
=== 2012-06-13 15:07:13,163 [l0-8] DEBUG PoolableSQLConnectionFactory - [builtinApplication.MyDataSource_fetch] Returning pooled Connection
=== 2012-06-13 15:07:13,164 [l0-8] INFO  SQLDriver - [builtinApplication.MyDataSource_fetch] Executing SQL query on 'Oracle': SELECT COUNT(*) FROM 	P, 
            				PAT,
            				PA, 
            				PT
             WHERE 	P.P_ID = PA.P_ID AND
    						PA.PAT_ID = PAT.PAT_ID AND
    						PT.PT_ID = P.PT_ID AND
    						(PA.VALUE > PAT.MAX_THRESHOLD OR 
    						PA.VALUE < PAT.MIN_THRESHOLD) AND
    						((((PT.Name IN ('''Water''', '''Height''')) AND PT.Name IS NOT NULL) AND (P.START_TIMESTAMP >= TO_DATE('2012-04-02 00:00:00','YYYY-MM-DD HH24:MI:SS') AND P.START_TIMESTAMP IS NOT NULL)))
    		
=== 2012-06-13 15:07:13,325 [0-10] INFO  Download - Returning 304: Not modified on conditional get of: /sc/skins/Enterprise/images/loadingSmall.gif
=== 2012-06-13 15:07:13,618 [l0-8] INFO  DSResponse - [builtinApplication.MyDataSource_fetch] DSResponse: List with 0 items

Here is the code that I write to set the select items

 
ListGridField gridField = theListGrid.getField(aFieldName);
      gridField.setOptionDataSource(DataSource.get(aDataSourceName));
      gridField.setAutoFetchDisplayMap(true);

      // Create the select item that will be used by the list grid field.
      SelectItem selectItem = new SelectItem();

      // allow multiple parameters to be selected for the same field
      selectItem.setMultiple(true);
      selectItem.setAllowEmptyValue(true);
      gridField.setFilterEditorProperties(selectItem);

[azuniga]

I have a couple of other tables but the queries generated by smartclient are different.

((((LOWER(PT.NAME) LIKE '%foo bar%' ESCAPE'\' ) OR

instead of using the IN keyword. I don't know how that is generated and why the difference but any help is appreciated.

[Isomorphic]

Your log shows AdvancedCriteria submitted to the server using the IN_SET operator:

{
fieldName:"PTNAME",
operator:"inSet",
value:[
"Water",
"Height"
]
},

This is coming from your code, possibly a getPickListFilterCriteria override.

The SQL generated from this criteria is correct.

[azuniga]

I don't have anything like that. I have

this.theListGridDataSource.setCriteriaPolicy(CriteriaPolicy.DROPONCHANGE);

I also set the initial criteria but when I comment that out I still get the same error. However both the tables that work and don't work both have this policy. Any other suggestions?

[Isomorphic]

There's nothing in the framework that's going to cause criteria like that to be generated for a SelectItem that has an optionDataSource, so you will need to find whatever it is in your code that's creating this criteria.

[azuniga]

This functionality was working fine previously. I was using the smartgwtpower 3.0, and smartgwtpower2.x and then switched to smartgwtee3.0 nightly. Is there anything in this build that may have been added? There is nothing in my code that would tell the select item or the field to use "inSet". On the server side I override datasource to use custom datasource but all I do there is grab the where clause,

String currentWhereClause = SQLDataSource.getSQLClause(SQLClauseType.Where, request);
DSResponse response = super.executeFetch(request);
theLogger.debug("A datasource request is being handled. The sql clause is : " + currentWhereClause);

When I log the where clause, the where clause is fine, but it somehow gets messed up when it executes fetch.

[Isomorphic]

Are you actually on a 3.1d nightly? (you claimed otherwise above).

[azuniga]

Currently using smartgwtee-3.0p-NB-20120516. Started developing with power 2.5 since the company bought that one. Then moved to 3.0 power to upgrade but haven't purchased yet so currently using smartgwtee-3.0p-NB-20120516 until company pays for power.

[azuniga]

So do we have any information as to why it is putting 3 single quotes around each string in the set? If you remove 2 single quotes from each string this would work. Why is this happening? If I comment out the code

selectItem.setMultiple(true)

then it works fine and the criteria sent to the serve uses equals and is

=== 2012-06-14 21:06:03,929 [0-24] DEBUG RPCManager - Processing 1 requests.
=== 2012-06-14 21:06:03,930 [0-24] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"P_T_N",
                operator:"equals",
                value:"Band"
            },
            {
                fieldName:"S_TIME",
                operator:"greaterOrEqual",
                value:new Date(1339621428000)
            }
        ]
    },

I don't care what operator is used as long as it works. Is there a way to remove those 3 single quotes from each string?

[Isomorphic]

Do you have any code that processes the criteria in any way? Such as a DMI.

Look at these parts of the log you can see that the criteria have been modified in some way after they've arrived at the server and before the SQLDataSource gets them:

** here it's fine
criteria:[
{
fieldName:"PTNAME",
operator:"inSet",
value:[
"Water",
"Height"
]
},
...
** here one level of extra quoting has been introduced
=== 2012-06-13 15:07:13,137 [l0-8] INFO SQLDataSource - [builtinApplication.MyDataSource_fetch] Performing fetch operation with
criteria: {criteria:[{fieldName:"PTNAME",operator:"inSet",value:["'Water'","'Height'"]},{fieldName:"START_TIMESTAMP",operator:"greaterOrEqual",value:new Date(1333324800000)}],operator:"and",_constructor:"AdvancedCriteria"} values: {criteria:[{fieldName:"PTNAME",operator:"inSet",value:["'Water'","'Height'"]},{fieldName:"START_TIMESTAMP",operator:"greaterOrEqual",value:new Date(1333324800000)}],operator:"and",_constructor:"AdvancedCriteria"}

[azuniga]

Do you have any code that processes the criteria in any way? Such as a DMI.

Look at these parts of the log you can see that the criteria have been modified in some way after they've arrived at the server and before the SQLDataSource gets them:

All of my datasources are custom datasources but don't intentionally modify the criteria. All was behaving normally until I upgraded smartgwt from 2.5 to 3.0. With the last reply you left I took a look at the server side code and I had

   @Override
   public DSResponse executeFetch(DSRequest request) throws Exception
   {String currentWhereClause = SQLDataSource.getSQLClause(SQLClauseType.Where, request);
DSResponse response = super.executeFetch(request);

After fetching I add some properties to the response and return it. I reversed the order of these 2 statements and set the execute fetch statement to be the first statement in the executeFetch method. Now the listgrid filters properly but when I grab the current where clause in the 2nd statement, I still get the 3 single quotes.

criteria:{
        operator:"and",
        _constructor:"AdvancedCriteria",
        criteria:[
            {
                fieldName:"PTNAME",
                operator:"inSet",
                value:[
                    "Name1",
                    "Name2"
                ]
            },
            {
                fieldName:"START_TIMESTAMP",
                operator:"greaterOrEqual",
                value:new Date(1334620800000)
            }
        ]
    }, 
DEBUG SQLDataSource - [builtinApplication.PerformanceParameterDataSource_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT PT.COV, PAT.NAME, PT.Name AS PT_NAME, P.START_TIMESTAMP, PA.VALUE FROM 	P, 
    					PAT, 
    					PA, 
    					PT
    	 WHERE 	P.P_ID = PA.P_ID AND
    					PA.PAT_ID = PAT.PAT_ID AND
    					PT.PT_ID = P.PT_ID AND
    					((((PT.Name IN ('NAME1', 'NAME2')) AND PT.Name IS NOT NULL) AND (P.START_TIMESTAMP >= TO_DATE('2012-04-17 00:00:00','YYYY-MM-DD HH24:MI:SS') AND P.START_TIMESTAMP IS NOT NULL)))
    	
=== 2012-06-18 12:15:17,609 [l0-2] INFO  DSResponse - [builtinApplication.PerformanceParameterDataSource_fetch] DSResponse: List with 66 items
12:15:17.610 [btpool0-2] DEBUG MYLogger - A datasource request is being handled. The sql clause is : 	P.P_ID = PA.P_ID AND
    					PA.PAT_ID = PAT.PAT_ID AND
    					PT.PT_ID = P.PT_ID AND
    					((((PT.Name IN ('''NAME1''', '''NAME2''')) AND PT.Name IS NOT NULL) AND (P.START_TIMESTAMP >= TO_DATE('2012-04-17 00:00:00','YYYY-MM-DD HH24:MI:SS') AND P.START_TIMESTAMP IS NOT NULL)))
    	
=== 2012-06-18 12:15:17,612 [l0-2] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2012-06-18 12:15:17,613 [l0-2] DEBUG RPCManager - non-DMI response, dropExtraFields: false

If you look at the statement printed by mylogger you can see that the 3 single quotes are added. I retrieve the where clause in the 2nd line of code. What is happening after execute fetch that the where clause gets those single quotes added?

[Isomorphic]

This was a bug in the specific circumstances of an IN_SET operator in the criteria with values of type "text", and the criteria were presented for escaping twice (because of the call to getSQLClause() and the actual DSRequest execution in the same flow). It is now fixed - please try your case with a nightly build from tomorrow (June 23) or later.

Thanks,
Isomorphic Software Support