FilterBuilder using a value across fields

[valerien]

Hi,

I'm using SmartClient Version: v8.3p_2014-03-06/PowerEdition Deployment (built 2014-03-06) (SmartGWT 3.1p) with PostgreSQL 9.2.2, Firefox 26 and Tomcat 7.0.33.

I'm using includeFrom to reference some values in look up tables. Things are working pretty well, but I've encountered an odd error condition.

I am using both a filterEditor and a filterBuilder. On the DataSourceField I use setEditorType to set the optionDataSource, valueField and displayField so that the pick list in the filterBuilder for the field will show the list of available choices.

I'm having a problem with the filter builder. One of other fields in the dataSource is a date-time field. When you add a clause to the filter builder you can pick the data-time field from the list. This provides a default value of 'today' for the date-time field. Then I can, in that clause, change the selected field from the date-time field to the includeFrom field. The system attempts to populate the pick list for the includeFrom field, but it uses the value from the date-time field as criteria. So I get an postgres error that says:
ERROR: invalid input syntax for integer: "2014-05-08 00:00:00"
I get this error because it is using the date-time field's value of 'today' as criteria for the includeFrom field which is based on an integer id referencing a look up table to get a matching string. The date string representing 'today' is not a valid value for the integer id. Even if it was an integer, it shouldn't be used.

It looks like filter builder is using the previous value when the column of interest is changed to the includeFrom field. The documentation implied that it would not do this, but it does. I have also tried to explicitly tell it to not use the previous value by calling FilterBuilder.setRetainValuesAcrossFields with false, but the behavior didn't change.

In the filter builder, when I switch to the includeFrom field, it should not use the previous field's value for the includeFrom field. Is there something I need to set that I missed or is this a bug?

Thanks,
Kevin

[valerien]

Hi,

For unrelated reasons I upgraded from 3.1p to 4.1p. I retested and this problem still occurs. My current testing is with:
SmartClient Version: v9.1p_2014-05-08/PowerEdition Deployment (built 2014-05-08)

Thanks,
Kevin

[Isomorphic]

Thanks for the extra info - we *are* looking into it and will respond here in the next day or so.

[Isomorphic]

We're not seeing this in testing - can you show some code we can run? If that takes time to arrange, the DS definitions would help in the meantime.

[valerien]

Hi,

Thanks for the update.

To test this outside of my environment I started with the SQLIncludeFrom showcase example. I copied the built-in-ds sample and stuck the SQLIncludeFrom code in there to run.

For the datasources I changed them to use my postgres database. This added a sequenceName to the sequence fields. This probably isn't necessary since the example doesn't let you add rows.

I also added a datetime field to the cityIncludeFromSQL dataSource. In postgres I created tables to match the dataSources and inserted data to match what was in the example.

In SQLIncludeFrom.java I added a filterBuilder to the display. I just copy/pasted from a place I use a FilterBuilder in my code. I commented out some extra stuff, but the stuff that is left shows the problem for me.

With these pieces I run SQLIncludeFrom and see the listGrid with four columns (City, Country, Timestamp, Continent). The listGrid has the FilterEditor on top and the FilterBuilder below it.

I click the green + in the filter builder and get a filter builder clause for City. I use the comboBox to change City to Timestamp. Now I have a clause that says Timestamp equals Today. Then I use the comboBox to change Timestamp to Country. The clause changes to Country equals Loading... and I get a popup that says invalid input syntax for integer: "2014-05-12 00:00:00" Position: 163.

This error comes from the trying to get the select list with the date value as criteria for retrieval from the Country datasource. This is the part of the log in Eclipse where the query is generated:

=== 2014-05-12 11:04:22,472 [l0-4] DEBUG RPCManager - Processing 1 requests.
=== 2014-05-12 11:04:22,472 [l0-4] DEBUG ISCKeyedObjectPool - Borrowing object for 'countryIncludeFromSQL'
=== 2014-05-12 11:04:22,472 [l0-4] DEBUG DSRequest - Caching instance 25 of DS countryIncludeFromSQL from DSRequest.getDataSource()
=== 2014-05-12 11:04:22,472 [l0-4] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
        countryId:new Date(1399867200000)
    },
    operationConfig:{
        dataSource:"countryIncludeFromSQL",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    componentId:"isc_FilterBuilder_0_clause_clause",
    appID:"builtinApplication",
    operation:"countryIncludeFromSQL_fetch",
    oldValues:{
        countryId:new Date(1399867200000)
    }
}
=== 2014-05-12 11:04:22,472 [l0-4] INFO  IDACall - Performing 1 operation(s)
=== 2014-05-12 11:04:22,472 [l0-4] DEBUG ISCKeyedObjectPool - Borrowing object for 'continentIncludeFromSQL'
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG Relation - Caching instance of toDS 'continentIncludeFromSQL' in the DSRequest map
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG DeclarativeSecurity - DataSource countryIncludeFromSQL is not in the pre-checked list, processing...
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG DeclarativeSecurity - Processing security checks for DataSource continentIncludeFromSQL, field continentId
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG DeclarativeSecurity - DataSource continentIncludeFromSQL is not in the pre-checked list, processing...
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG DeclarativeSecurity - Processing security checks for DataSource continentIncludeFromSQL, field continentName
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG DeclarativeSecurity - DataSource continentIncludeFromSQL is not in the pre-checked list, processing...
=== 2014-05-12 11:04:22,482 [l0-4] DEBUG AppBase - [builtinApplication.countryIncludeFromSQL_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2014-05-12 11:04:22,483 [l0-4] DEBUG AppBase - [builtinApplication.countryIncludeFromSQL_fetch] No public zero-argument method named '_countryIncludeFromSQL_fetch' found, performing generic datasource operation
=== 2014-05-12 11:04:22,483 [l0-4] INFO  SQLDataSource - [builtinApplication.countryIncludeFromSQL_fetch] Performing fetch operation with
	criteria: {countryId:new Date(1399867200000)}	values: {countryId:new Date(1399867200000)}
=== 2014-05-12 11:04:22,483 [l0-4] INFO  SQLDataSource - [builtinApplication.countryIncludeFromSQL_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause AND $defaultJoinWhereClause
=== 2014-05-12 11:04:22,483 [l0-4] INFO  SQLDataSource - [builtinApplication.countryIncludeFromSQL_fetch] 25: Executing SQL query on 'PostgreSQL': SELECT country.continentId, country.countryCode, country.countryId, country.countryName, continent.continentName FROM country, continent WHERE (country.countryId='2014-05-12 00:00:00') AND country.continentId = continent.continentId
=== 2014-05-12 11:04:22,483 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] Connection was already closed in validateObject - returning false
=== 2014-05-12 11:04:22,483 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] Connection was already closed in validateObject - returning false
=== 2014-05-12 11:04:22,483 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] DriverManager fetching connection for PostgreSQL via jdbc url jdbc:postgresql://localhost:5432/utilimesh?user=********&password=*******
=== 2014-05-12 11:04:22,483 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] Passing JDBC URL only to getConnection
=== 2014-05-12 11:04:22,723 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] makeObject() created a pooled Connection '412891564'
=== 2014-05-12 11:04:22,723 [l0-4] DEBUG SQLConnectionManager - [builtinApplication.countryIncludeFromSQL_fetch] Borrowed connection '412891564'
=== 2014-05-12 11:04:22,723 [l0-4] DEBUG SQLDriver - [builtinApplication.countryIncludeFromSQL_fetch] About to execute SQL query in 'PostgreSQL' using connection '412891564'
=== 2014-05-12 11:04:22,723 [l0-4] INFO  SQLDriver - [builtinApplication.countryIncludeFromSQL_fetch] Executing SQL query on 'PostgreSQL': SELECT country.continentId, country.countryCode, country.countryId, country.countryName, continent.continentName FROM country, continent WHERE (country.countryId='2014-05-12 00:00:00') AND country.continentId = continent.continentId
=== 2014-05-12 11:04:22,773 [l0-4] INFO  SQLDriver - [builtinApplication.countryIncludeFromSQL_fetch] Execute of select: SELECT country.continentId, country.countryCode, country.countryId, country.countryName, continent.continentName FROM country, continent WHERE (country.countryId='2014-05-12 00:00:00') AND country.continentId = continent.continentId on db: PostgreSQL threw exception: org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "2014-05-12 00:00:00"
  Position: 163 - assuming stale connection and retrying query.
=== 2014-05-12 11:04:22,773 [l0-4] DEBUG SQLConnectionManager - [builtinApplication.countryIncludeFromSQL_fetch] About to close ISCPoolableConnection with hashcode "412891564"
=== 2014-05-12 11:04:22,773 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] DriverManager fetching connection for PostgreSQL via jdbc url jdbc:postgresql://localhost:5432/utilimesh?user=********&password=*******
=== 2014-05-12 11:04:22,773 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] Passing JDBC URL only to getConnection
=== 2014-05-12 11:04:23,043 [l0-4] DEBUG PoolableSQLConnectionFactory - [builtinApplication.countryIncludeFromSQL_fetch] makeObject() created a pooled Connection '1818601393'
=== 2014-05-12 11:04:23,043 [l0-4] WARN  RequestContext - dsRequest.execute() failed: 
org.postgresql.util.PSQLException: ERROR: invalid input syntax for integer: "2014-05-12 00:00:00"
  Position: 163
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
	at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
	at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:671)
	at com.isomorphic.sql.SQLDriver.executeQuery(SQLDriver.java:985)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:559)
	at com.isomorphic.sql.SQLDataSource.executeNativeQuery(SQLDataSource.java:553)
	at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1865)
	at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:444)
	at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:389)
	at com.isomorphic.datasource.DataSource.execute(DataSource.java:1517)
	at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:723)
	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:2553)
	at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:215)
	at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:172)
	at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:137)
	at com.isomorphic.servlet.IDACall.doPost(IDACall.java:73)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1097)
	at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:260)
	at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
	at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
	at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
	at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
	at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
	at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
	at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
	at org.mortbay.jetty.Server.handle(Server.java:324)
	at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
	at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
	at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
	at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
	at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
	at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
	at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
=== 2014-05-12 11:04:23,043 [l0-4] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2014-05-12 11:04:23,043 [l0-4] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2014-05-12 11:04:23,043 [l0-4] DEBUG SQLDriver - Freeing SQLDriver dbConnection 1818601393
=== 2014-05-12 11:04:23,043 [l0-4] DEBUG SQLConnectionManager - About to close ISCPoolableConnection with hashcode "1818601393"
=== 2014-05-12 11:04:23,043 [l0-4] INFO  Compression - /builtinds/sc/IDACall: 217 -> 184 bytes

The log shows a call to the countryIncludeFromSQL datasource with a date as the value for the integer countryId. No good comes from that and a postgres exception is raised.

I have attached the dataSource and java files which I used. Please let me know if I've missed something you need.

Thanks,
Kevin

[Isomorphic]

Ok, this is fixed for builds dated May 13 and later