Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
Clear All
new posts
Previous template Next
    #1

    FilterBuilder Range Issue

    SmartClient Version: v10.0p_2015-06-23/PowerEdition Deployment (built 2015-06-23)

    Using a FilterBuilder instance in the same manner as http://www.smartclient.com/smartgwt/showcase/#grid_custom_filter_builder (invoking a ListGrid's filterData() method via a FilterBuilder's criteria), but with a DB2 server, the "between (inclusive)" operation is not functioning. The server logs show the criteria are being transformed into a no-op. The following are the logs from a client request asking for integer field DEALNBR between 20 and 21, showing that MultipleFieldCriteriaTransformer is removing mostly everything and the final SQL formed does not contain the proper select clause(s) containing DEALNBR.
    Code:
    === 2015-07-22 13:26:12,842 [c-14] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21
            }
        ]
    },
    operationConfig:{
        dataSource:"IPPEDHR",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    sortBy:[
        "DEALDESC"
    ],
    componentId:"isc_DealsNav_4_0",
    appID:"builtinApplication",
    operation:"IPPEDHR_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21
            }
        ]
    }
}
=== 2015-07-22 13:26:12,842 [c-14] DEBUG IpIDACall - Processing 1 queued RPC operations.
=== 2015-07-22 13:26:12,842 [c-14] WARN  IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2015-07-22 13:26:12,842 [c-14] INFO  IpIDACall - Performing 1 operation(s)
=== 2015-07-22 13:26:12,842 [c-14] INFO  IpIDACall - Processing DataSource IPPEDHR
=== 2015-07-22 13:26:12,843 [c-14] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-07-22 13:26:12,843 [c-14] DEBUG DeclarativeSecurity - DataSource IPPEDHR is not in the pre-checked list, processing...
=== 2015-07-22 13:26:12,843 [c-14] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-07-22 13:26:12,843 [c-14] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No public zero-argument method named '_IPPEDHR_fetch' found, performing generic datasource operation
=== 2015-07-22 13:26:12,844 [c-14] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-22 13:26:12,844 [c-14] DEBUG MultipleFieldCriteriaTransformer - [builtinApplication.IPPEDHR_fetch] Transformed criteria {
    criteria:[
    ],
    operator:"and",
    _constructor:"AdvancedCriteria"
}
=== 2015-07-22 13:26:12,844 [c-14] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-22 13:26:12,844 [c-14] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-22 13:26:12,844 [c-14] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] Performing fetch operation with
	criteria: {criteria:[],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-07-22 13:26:12,844 [c-14] INFO  SQLWhereClause - [builtinApplication.IPPEDHR_fetch] empty condition
=== 2015-07-22 13:26:12,844 [c-14] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] derived query: SELECT 
			 IPPEDHR.*, IPPEDHX.*
			 FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE $defaultWhereClause ORDER BY $defaultOrderClause
=== 2015-07-22 13:26:12,845 [c-14] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Executing row count query: SELECT COUNT(*) FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE $defaultWhereClause
=== 2015-07-22 13:26:12,845 [c-14] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Eval'd row count query: SELECT COUNT(*) FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE ('1'='1')
=== 2015-07-22 13:26:12,845 [c-14] DEBUG SQLConnectionManager - [builtinApplication.IPPEDHR_fetch] Borrowed connection '414477228'
=== 2015-07-22 13:26:12,845 [c-14] DEBUG SQLDriver - [builtinApplication.IPPEDHR_fetch] About to execute SQL query in 'iptsfiln' using connection '414477228'
=== 2015-07-22 13:26:12,845 [c-14] INFO  SQLDriver - [builtinApplication.IPPEDHR_fetch] Executing SQL query on 'iptsfiln': SELECT COUNT(*) FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE ('1'='1')
=== 2015-07-22 13:26:12,892 [c-14] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] SQLTransform (1 rows): 0ms
=== 2015-07-22 13:26:12,905 [c-14] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Counted 139 total rows in result set: 60ms
=== 2015-07-22 13:26:12,905 [c-14] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT 
			 IPPEDHR.*, IPPEDHX.*
			 FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE ('1'='1') ORDER BY DEALDESC
    For a request with an "equals" operator, the request is executed properly, with the following logging for a DEALNBR of 20.
    Code:
    === 2015-07-22 13:50:48,271 [c-13] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"DEALNBR",
                operator:"equals",
                value:20,
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"IPPEDHR",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    sortBy:[
        "DEALDESC"
    ],
    componentId:"isc_DealsNav_4_0",
    appID:"builtinApplication",
    operation:"IPPEDHR_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                fieldName:"DEALNBR",
                operator:"equals",
                value:20,
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}
=== 2015-07-22 13:50:48,271 [c-13] DEBUG IpIDACall - Processing 1 queued RPC operations.
=== 2015-07-22 13:50:48,271 [c-13] WARN  IpAuthenticationManager - Property charSetNormalize not found in server.properties
=== 2015-07-22 13:50:48,271 [c-13] INFO  IpIDACall - Performing 1 operation(s)
=== 2015-07-22 13:50:48,271 [c-13] INFO  IpIDACall - Processing DataSource IPPEDHR
=== 2015-07-22 13:50:48,271 [c-13] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-07-22 13:50:48,271 [c-13] DEBUG DeclarativeSecurity - DataSource IPPEDHR is not in the pre-checked list, processing...
=== 2015-07-22 13:50:48,271 [c-13] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-07-22 13:50:48,271 [c-13] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No public zero-argument method named '_IPPEDHR_fetch' found, performing generic datasource operation
=== 2015-07-22 13:50:48,272 [c-13] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-22 13:50:48,272 [c-13] DEBUG MultipleFieldCriteriaTransformer - [builtinApplication.IPPEDHR_fetch] Transformed criteria {
    criteria:[
        {
            value:20,
            fieldName:"DEALNBR",
            operator:"equals"
        }
    ],
    operator:"and",
    _constructor:"AdvancedCriteria"
}
=== 2015-07-22 13:50:48,272 [c-13] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-22 13:50:48,272 [c-13] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-22 13:50:48,272 [c-13] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] Performing fetch operation with
	criteria: {criteria:[{value:20,fieldName:"DEALNBR",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{value:20,fieldName:"DEALNBR",operator:"equals"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-07-22 13:50:48,272 [c-13] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] derived query: SELECT 
			 IPPEDHR.*, IPPEDHX.*
			 FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE $defaultWhereClause ORDER BY $defaultOrderClause
=== 2015-07-22 13:50:48,272 [c-13] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Executing row count query: SELECT COUNT(*) FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE $defaultWhereClause
=== 2015-07-22 13:50:48,272 [c-13] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Eval'd row count query: SELECT COUNT(*) FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE ((DEALNBR = 20 AND DEALNBR IS NOT NULL))
=== 2015-07-22 13:50:48,272 [c-13] DEBUG SQLConnectionManager - [builtinApplication.IPPEDHR_fetch] Borrowed connection '414477228'
=== 2015-07-22 13:50:48,272 [c-13] DEBUG SQLDriver - [builtinApplication.IPPEDHR_fetch] About to execute SQL query in 'iptsfiln' using connection '414477228'
=== 2015-07-22 13:50:48,272 [c-13] INFO  SQLDriver - [builtinApplication.IPPEDHR_fetch] Executing SQL query on 'iptsfiln': SELECT COUNT(*) FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE ((DEALNBR = 20 AND DEALNBR IS NOT NULL))
=== 2015-07-22 13:50:48,325 [c-13] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] SQLTransform (1 rows): 0ms
=== 2015-07-22 13:50:48,338 [c-13] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Counted 1 total rows in result set: 66ms
=== 2015-07-22 13:50:48,338 [c-13] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT 
			 IPPEDHR.*, IPPEDHX.*
			 FROM 
			 IPPEDHR LEFT JOIN IPPEDHX ON IPPEDHR.DEALNBR=IPPEDHX.XDEALNBR
			 WHERE ((DEALNBR = 20 AND DEALNBR IS NOT NULL)) ORDER BY DEALDESC
    The same behaviour is exhibited for date field and string field ranges.

    Please advise. Thanks.
    Tags: None
    #2
    According to the logs, the criteria have been modified before we ever get into SQL-specific code (let alone database-specific).

    It seems likely that either:

    1. the target DataSource doesn't have a field named "DEALNBR" at all (note: field names are case sensitive)

    OR

    2. you have DMI logic or custom DataSource logic that is modifying the criteria and dropping parts of it

    Comment

      #3
      We think we've located the source of the problem as the <field> "multiple" attribute. Following are the client-side code and .ds.xml (with a small subset of the actual fields and server constructor removed to eliminate the possibility of criteria being modified).
      Code:
      **** Client entry point class ***
public class TheEntryPoint implements EntryPoint 
{
   public void onModuleLoad() 
   {
      VLayout             vLo = new VLayout();
      DataSource          ds = DataSource.get("IPPEDHR");
      final FilterBuilder filterBuilder = new FilterBuilder();
      final ListGrid      grid = new ListGrid();
      IButton             btnFilter = new IButton("Filter"); 
      VStack              vStack = new VStack(10);  
        
      filterBuilder.setDataSource(ds);  
      filterBuilder.setTopOperatorAppearance(TopOperatorAppearance.RADIO);  
      grid.setWidth(500);  
      grid.setHeight(224);  
      grid.setDataSource(ds);  
      grid.setAutoFetchData(true);   
     
      btnFilter.addClickHandler(new ClickHandler() {  
         public void onClick(ClickEvent event) {  
            grid.filterData(filterBuilder.getCriteria());  
                  } 
      });  
     
      vStack.addMember(filterBuilder);  
      vStack.addMember(btnFilter);  
      vStack.addMember(grid);  
      vStack.draw();     
   }
}

**** IPPEDHR.ds.xml ****
<DataSource ID="IPPEDHR" dataFormat="iscServer" serverType="sql" dbName="iptsfiln" 
            sparseUpdates="true" tableName="IPPEDHR" qualifyColumnNames="false">
   <fields>
      <field name="DEALNBR" title="Deal Number" type="integer" length="10" primaryKey="true"/>
      <field name="DEALDESC" title="Deal Description" type="text" length="25" characterCasing="upper"/>     
      <field name="STARTDTE" title="Start Date" type="date" useTextField="true" textAlign="left"/>
      <field name="ENDDTE" title="End Date" type="date" useTextField="true" textAlign="left"/>         
      <field name="couponsAssigned" title="Coupons Map" type="IPPEDDC" multiple="true" hidden="true" customSQL="true"/>
   </fields>
</DataSource>
      With the .ds.xml having the "couponsAssigned" "multiple" attribute in place, after the filter is invoked, the screen appears as in attachment "AfterFiltering-Multiple.png" and the logging appears as follows.
      Code:
      === 2015-07-24 14:57:33,453 [ec-9] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"IPPEDHR",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"IPPEDHR_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}
=== 2015-07-24 14:57:33,453 [ec-9] DEBUG IpIDACall - Processing 1 queued RPC operations.
=== 2015-07-24 14:57:33,453 [ec-9] INFO  IpIDACall - Performing 1 operation(s)
=== 2015-07-24 14:57:33,453 [ec-9] DEBUG DataSource - Adding new default DynamicDSGenerator with hashcode '1222085851'
=== 2015-07-24 14:57:33,453 [ec-9] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-07-24 14:57:33,453 [ec-9] DEBUG DeclarativeSecurity - DataSource IPPEDHR is not in the pre-checked list, processing...
=== 2015-07-24 14:57:33,454 [ec-9] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-07-24 14:57:33,454 [ec-9] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No public zero-argument method named '_IPPEDHR_fetch' found, performing generic datasource operation
=== 2015-07-24 14:57:33,454 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-24 14:57:33,454 [ec-9] DEBUG MultipleFieldCriteriaTransformer - [builtinApplication.IPPEDHR_fetch] Transformed criteria {
    criteria:[
    ],
    operator:"and",
    _constructor:"AdvancedCriteria"
}
=== 2015-07-24 14:57:33,455 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-24 14:57:33,455 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-24 14:57:33,455 [ec-9] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] Performing fetch operation with
	criteria: {criteria:[],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-07-24 14:57:33,455 [ec-9] INFO  SQLWhereClause - [builtinApplication.IPPEDHR_fetch] empty condition
=== 2015-07-24 14:57:33,455 [ec-9] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-24 14:57:33,455 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-24 14:57:33,456 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Eval'd row count query: SELECT COUNT(*) FROM IPPEDHR WHERE ('1'='1')
      When the "multiple" attribute is removed or set "false" in the "couponsAssigned" field, after the filter is invoked, the screen appears as in attachment "AfterFiltering-NoMultiple.png" and the logging appears as follows.
      Code:
      === 2015-07-24 15:09:23,463 [c-10] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"IPPEDHR",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"IPPEDHR_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}
=== 2015-07-24 15:09:23,463 [c-10] DEBUG IpIDACall - Processing 1 queued RPC operations.
=== 2015-07-24 15:09:23,463 [c-10] INFO  IpIDACall - Performing 1 operation(s)
=== 2015-07-24 15:09:23,463 [c-10] DEBUG DataSource - Adding new default DynamicDSGenerator with hashcode '263403783'
=== 2015-07-24 15:09:23,464 [c-10] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-07-24 15:09:23,464 [c-10] DEBUG DeclarativeSecurity - DataSource IPPEDHR is not in the pre-checked list, processing...
=== 2015-07-24 15:09:23,464 [c-10] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-07-24 15:09:23,464 [c-10] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No public zero-argument method named '_IPPEDHR_fetch' found, performing generic datasource operation
=== 2015-07-24 15:09:23,464 [c-10] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-24 15:09:23,464 [c-10] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-24 15:09:23,464 [c-10] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] Performing fetch operation with
	criteria: {criteria:[{operator:"betweenInclusive",fieldName:"DEALNBR",start:20,end:21,_constructor:"AdvancedCriteria"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{operator:"betweenInclusive",fieldName:"DEALNBR",start:20,end:21,_constructor:"AdvancedCriteria"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-07-24 15:09:23,465 [c-10] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-24 15:09:23,465 [c-10] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-24 15:09:23,465 [c-10] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Eval'd row count query: SELECT COUNT(*) FROM IPPEDHR WHERE ((DEALNBR >= 20 AND DEALNBR <= 21 AND DEALNBR IS NOT NULL))
      Notice that the final SQL formed has the proper filtering per the FilterBuilder. My colleague surmises that BasicDataSource calls MultipleFieldCriteriaTransformer.transform on any fetch request when the datasource has at least one multiple=“true” field, where there is probably an issue in that that causes range criteria to be dropped. I tried to make it easier for you by using one of your supplied file DSs (animals.ds.xml together with animals.data.xml), but I apologize I couldn't get around the HSQL driver problems ...
      Code:
      === 2015-07-24 13:35:32,653 [c-11] WARN  RequestContext - dsRequest.execute() failed: 
java.sql.SQLTransientConnectionException: java.net.ConnectException: Connection refused: connect
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
	at org.hsqldb.jdbc.JDBCConnection.<init>(Unknown Source)
	at org.hsqldb.jdbc.JDBCDriver.getConnection(Unknown Source)
	at org.hsqldb.jdbc.JDBCDriver.connect(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at java.sql.DriverManager.getConnection(Unknown Source)
	at com.isomorphic.sql.PoolableSQLConnectionFactory.makeUnpooledObject(PoolableSQLConnectionFactory.java:270)
	at com.isomorphic.sql.PoolableSQLConnectionFactory.makeObject(PoolableSQLConnectionFactory.java:367)
	at com.isomorphic.pool.PoolManager.borrowObject(PoolManager.java:99)
	at com.isomorphic.pool.PoolManager.borrowObject(PoolManager.java:76)
	at com.isomorphic.sql.SQLConnectionManager.getConnection(SQLConnectionManager.java:172)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:583)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:550)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:543)
	at com.isomorphic.sql.SQLDriver.getScalarResult(SQLDriver.java:729)
	at com.isomorphic.sql.SQLDriver.executeScalar(SQLDriver.java:1020)
	at com.isomorphic.sql.SQLDataSource.runRowCountQuery(SQLDataSource.java:2958)
	at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2362)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1909)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:443)
	at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:388)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1922)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:726)
	at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
	at com.isomorphic.application.AppBase.execute(AppBase.java:491)
	at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2548)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:220)
	at com.islandpacific.gui.server.customDataSource.IpIDACall.handleDSRequest(IpIDACall.java:58)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:185)
	at com.islandpacific.gui.server.customDataSource.IpIDACall.processRPCTransaction(IpIDACall.java:40)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
	at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:117)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:76)
      ... unfortunately. But hopefully what I've supplied gives you enough to work with.

      Thanks again.
      Attached Files

      Comment

        #4
        Please retest with the latest build from smartclient.com/builds - a number of fixes have been made in this codepath, when fields are multiple:true, since your current build date.

        Comment

          #5
          SmartClient Version: v10.0p_2015-07-27/PowerEdition Deployment (built 2015-07-27)

          Unfortunately that didn't seem to catch it. With the same code sample I provided and multiple:true on DEALNBR, the logs appear similar as w/"v10.0p_2015-06-23/PowerEdition". Here's the logging from the request payload down to where the final SQL is formed, showing the FilterBuilder criteria aren't used.
          Code:
          === 2015-07-27 10:56:42,829 [ec-9] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"IPPEDHR",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"IPPEDHR_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}
=== 2015-07-27 10:56:42,829 [ec-9] DEBUG IpIDACall - Processing 1 queued RPC operations.
=== 2015-07-27 10:56:42,829 [ec-9] INFO  IpIDACall - Performing 1 operation(s)
=== 2015-07-27 10:56:42,829 [ec-9] DEBUG DataSource - Adding new default DynamicDSGenerator with hashcode '1369330302'
=== 2015-07-27 10:56:42,829 [ec-9] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-07-27 10:56:42,829 [ec-9] DEBUG DeclarativeSecurity - DataSource IPPEDHR is not in the pre-checked list, processing...
=== 2015-07-27 10:56:42,829 [ec-9] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-07-27 10:56:42,829 [ec-9] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No public zero-argument method named '_IPPEDHR_fetch' found, performing generic datasource operation
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG MultipleFieldCriteriaTransformer - [builtinApplication.IPPEDHR_fetch] Transformed criteria {
    criteria:[
    ],
    operator:"and",
    _constructor:"AdvancedCriteria"
}
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-27 10:56:42,830 [ec-9] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] Performing fetch operation with
	criteria: {criteria:[],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-07-27 10:56:42,830 [ec-9] INFO  SQLWhereClause - [builtinApplication.IPPEDHR_fetch] empty condition
=== 2015-07-27 10:56:42,830 [ec-9] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG temp - [builtinApplication.IPPEDHR_fetch] operationBinding config not found for operationType='fetch' and operationId='IPPEDHR_fetch'
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG temp - [builtinApplication.IPPEDHR_fetch] Row count query will be used.
=== 2015-07-27 10:56:42,830 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause
=== 2015-07-27 10:56:42,831 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Eval'd row count query: SELECT COUNT(*) FROM IPPEDHR
=== 2015-07-27 10:56:42,831 [ec-9] DEBUG SQLConnectionManager - [builtinApplication.IPPEDHR_fetch] Borrowed connection '1216436119'
=== 2015-07-27 10:56:42,831 [ec-9] DEBUG SQLDriver - [builtinApplication.IPPEDHR_fetch] About to execute SQL query in 'iptsfiln' using connection '1216436119'
=== 2015-07-27 10:56:42,831 [ec-9] INFO  SQLDriver - [builtinApplication.IPPEDHR_fetch] Executing SQL query on 'iptsfiln': SELECT COUNT(*) FROM IPPEDHR
=== 2015-07-27 10:56:42,878 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] SQLTransform (1 rows): 0ms
=== 2015-07-27 10:56:42,892 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Counted 137 total rows in result set: 62ms
=== 2015-07-27 10:56:42,892 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT DEALNBR, DEALDESC, STARTDTE, ENDDTE FROM IPPEDHR WHERE ('1'='1')
          Here's the comparable logging again with multiple:false on DEALNBR, showing the FilterBuilder criteria are used.
          Code:
          === 2015-07-27 11:13:21,896 [ec-9] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    },
    operationConfig:{
        dataSource:"IPPEDHR",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"IPPEDHR_fetch",
    oldValues:{
        _constructor:"AdvancedCriteria",
        operator:"and",
        criteria:[
            {
                operator:"betweenInclusive",
                fieldName:"DEALNBR",
                start:20,
                end:21,
                _constructor:"AdvancedCriteria"
            }
        ]
    }
}
=== 2015-07-27 11:13:21,896 [ec-9] DEBUG IpIDACall - Processing 1 queued RPC operations.
=== 2015-07-27 11:13:21,896 [ec-9] INFO  IpIDACall - Performing 1 operation(s)
=== 2015-07-27 11:13:21,896 [ec-9] DEBUG DataSource - Adding new default DynamicDSGenerator with hashcode '310919656'
=== 2015-07-27 11:13:21,896 [ec-9] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2015-07-27 11:13:21,896 [ec-9] DEBUG DeclarativeSecurity - DataSource IPPEDHR is not in the pre-checked list, processing...
=== 2015-07-27 11:13:21,897 [ec-9] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2015-07-27 11:13:21,897 [ec-9] DEBUG AppBase - [builtinApplication.IPPEDHR_fetch] No public zero-argument method named '_IPPEDHR_fetch' found, performing generic datasource operation
=== 2015-07-27 11:13:21,897 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-27 11:13:21,897 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Time to convert java.util.HashMap to JS Object: 0ms
=== 2015-07-27 11:13:21,897 [ec-9] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] Performing fetch operation with
	criteria: {criteria:[{operator:"betweenInclusive",fieldName:"DEALNBR",start:20,end:21,_constructor:"AdvancedCriteria"}],operator:"and",_constructor:"AdvancedCriteria"}	values: {criteria:[{operator:"betweenInclusive",fieldName:"DEALNBR",start:20,end:21,_constructor:"AdvancedCriteria"}],operator:"and",_constructor:"AdvancedCriteria"}
=== 2015-07-27 11:13:21,897 [ec-9] INFO  SQLDataSource - [builtinApplication.IPPEDHR_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-27 11:13:21,898 [ec-9] DEBUG temp - [builtinApplication.IPPEDHR_fetch] operationBinding config not found for operationType='fetch' and operationId='IPPEDHR_fetch'
=== 2015-07-27 11:13:21,898 [ec-9] DEBUG temp - [builtinApplication.IPPEDHR_fetch] Row count query will be used.
=== 2015-07-27 11:13:21,898 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE $defaultWhereClause
=== 2015-07-27 11:13:21,898 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] Eval'd row count query: SELECT COUNT(*) FROM IPPEDHR WHERE ((DEALNBR >= 20 AND DEALNBR <= 21 AND DEALNBR IS NOT NULL))
=== 2015-07-27 11:13:21,898 [ec-9] DEBUG SQLConnectionManager - [builtinApplication.IPPEDHR_fetch] Borrowed connection '406810892'
=== 2015-07-27 11:13:21,898 [ec-9] DEBUG SQLDriver - [builtinApplication.IPPEDHR_fetch] About to execute SQL query in 'iptsfiln' using connection '406810892'
=== 2015-07-27 11:13:21,898 [ec-9] INFO  SQLDriver - [builtinApplication.IPPEDHR_fetch] Executing SQL query on 'iptsfiln': SELECT COUNT(*) FROM IPPEDHR WHERE ((DEALNBR >= 20 AND DEALNBR <= 21 AND DEALNBR IS NOT NULL))
=== 2015-07-27 11:13:21,948 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] SQLTransform (1 rows): 0ms
=== 2015-07-27 11:13:21,963 [ec-9] DEBUG Timing - [builtinApplication.IPPEDHR_fetch] Counted 2 total rows in result set: 65ms
=== 2015-07-27 11:13:21,963 [ec-9] DEBUG SQLDataSource - [builtinApplication.IPPEDHR_fetch] JDBC driver windowed select rows 0->75, result size 75. Query: SELECT DEALNBR, DEALDESC, STARTDTE, ENDDTE FROM IPPEDHR WHERE ((DEALNBR >= 20 AND DEALNBR <= 21 AND DEALNBR IS NOT NULL))

          Comment

            #6
            Sorry, meant multiple (true or false) on field "couponsAssigned", not "DEALNBR".
            Code:
            <DataSource ID="IPPEDHR" dataFormat="iscServer" serverType="sql" dbName="iptsfiln" 
            sparseUpdates="true" tableName="IPPEDHR" qualifyColumnNames="false">
  <fields>
    <field name="DEALNBR" title="Deal Number" type="integer" length="10" primaryKey="true"/>
    <field name="DEALDESC" title="Deal Description" type="text" length="25" characterCasing="upper"/>    
    <field name="STARTDTE" title="Start Date" type="date" useTextField="true" textAlign="left"/>
    <field name="ENDDTE" title="End Date" type="date" useTextField="true" textAlign="left"/>       
    <field name="couponsAssigned" title="Coupons Map" type="IPPEDDC" multiple="true" hidden="true" customSQL="true"/>
  </fields>
</DataSource>

            Comment

              #7
              Ok, we see the problem - we'll take a look today and update here when it's been fixed.

              Comment

                #8
                This is fixed for builds dated July 30 and later.

                Comment

                  #9
                  Thanks so much! (Verified.)

                  Comment

                  Previous template Next
                  Working...
                  X