Sort on a customSQL="true" field

[edulid]

If I have a field with customSQL="true", then I cannot sort on this field, even if the customFields attribute is set. I get an sql exception.


Entrypoint:

public void onModuleLoad() {

        VStack vStack = new VStack(20);
        vStack.setWidth100();

        final ListGrid lg = new ListGrid();
        
        ListGridField nameField = new ListGridField("f_name", "Name");
        ListGridField vornameField = new ListGridField("f_vorname", "Vorname");
        
        lg.setDataSource(DataSource.get("simpleTable"));
        lg.setFields(nameField, vornameField);
        lg.setSortField("f_vorname");
        lg.setWidth100();
        lg.setHeight100();
        
        lg.fetchData();
        lg.draw();
        
    }

simpleTable.ds.xml:

<DataSource ID="simpleTable" serverType="sql" tableName="t_schueler">

    <fields>
        <field name="f_schueler_id" type="sequence" primaryKey="true" />
        <field name="f_name" type="text" />
        <field name="f_vorname" type="text" customSQL="true" />
    </fields>
    
    <operationBindings>
        <operationBinding operationType="fetch" customFields="f_vorname">
            <tableClause><![CDATA[
                t_schueler
            ]]></tableClause>
        </operationBinding>
        
    </operationBindings>

</DataSource>

Error:

=== 2016-05-05 20:11:32,402 [ec-1] DEBUG XML - Parsed XML from (in memory stream): 1ms
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG ISCKeyedObjectPool - Borrowing object for 'transaction'
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG PoolableDataSourceFactory - Tried to create DataSource  of type 'transaction' but null was returned
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG PoolableDataSourceFactory - Created DataSource null of type 'transaction' in the pooling flow
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG ISCKeyedObjectPool - Borrowing object for 'Object'
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG PoolableDataSourceFactory - Created DataSource 659 of type 'Object' and assigned it to thread http-nio-8080-exec-1
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG PoolableDataSourceFactory - Created DataSource 659 of type 'Object' in the pooling flow
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG PoolableDataSourceFactory - Activated DataSource 659 of type 'Object'
=== 2016-05-05 20:11:32,402 [ec-1] DEBUG ISCKeyedObjectPool - Borrowing object for 'List'
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG PoolableDataSourceFactory - Created DataSource 660 of type 'List' and assigned it to thread http-nio-8080-exec-1
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG PoolableDataSourceFactory - Created DataSource 660 of type 'List' in the pooling flow
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG PoolableDataSourceFactory - Activated DataSource 660 of type 'List'
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG ISCKeyedObjectPool - Borrowing object for 'elem'
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG PoolableDataSourceFactory - Tried to create DataSource  of type 'elem' but null was returned
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG PoolableDataSourceFactory - Created DataSource null of type 'elem' in the pooling flow
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG RPCManager - Processing 1 requests.
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG ISCKeyedObjectPool - Borrowing object for 'simpleTable'
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG PoolableDataSourceFactory - Activated DataSource 463 of type 'simpleTable'
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG DSRequest - Caching instance 463 of DS 'simpleTable' from DSRequest.getDataSource()
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG DSRequest - Caching instance 463 of DS simpleTable
=== 2016-05-05 20:11:32,403 [ec-1] DEBUG RPCManager - Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"simpleTable",
        repo:null,
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    startRow:0,
    endRow:119,
    sortBy:[
        "f_vorname"
    ],
    componentId:"isc_ListGrid_0",
    appID:"builtinApplication",
    operation:"simpleTable_fetch",
    oldValues:{
    }
}
=== 2016-05-05 20:11:32,404 [ec-1] INFO  IDACall - Key[type=com.isomorphic.servlet.IDACall, annotation=[none]] - Performing 1 operation(s)
=== 2016-05-05 20:11:32,404 [ec-1] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
=== 2016-05-05 20:11:32,404 [ec-1] DEBUG DeclarativeSecurity - DataSource simpleTable is not in the pre-checked list, processing...
=== 2016-05-05 20:11:32,404 [ec-1] DEBUG AppBase - [builtinApplication.simpleTable_fetch] No userTypes defined, allowing anyone access to all operations for this application
=== 2016-05-05 20:11:32,404 [ec-1] DEBUG AppBase - [builtinApplication.simpleTable_fetch] No public zero-argument method named '_simpleTable_fetch' found, performing generic datasource operation
=== 2016-05-05 20:11:32,404 [ec-1] INFO  SQLDataSource - [builtinApplication.simpleTable_fetch] Performing fetch operation with
    criteria: {}    values: {}
=== 2016-05-05 20:11:32,404 [ec-1] INFO  SQLWhereClause - [builtinApplication.simpleTable_fetch] empty condition
=== 2016-05-05 20:11:32,404 [ec-1] INFO  SQLDataSource - [builtinApplication.simpleTable_fetch] derived query: SELECT $defaultSelectClause FROM
                t_schueler
             WHERE $defaultWhereClause ORDER BY $defaultOrderClause
=== 2016-05-05 20:11:32,405 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] Executing row count query: SELECT COUNT(*) FROM
                t_schueler
            
=== 2016-05-05 20:11:32,405 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] Eval'd row count query: SELECT COUNT(*) FROM
                t_schueler
            
=== 2016-05-05 20:11:32,405 [ec-1] DEBUG SQLConnectionManager - [builtinApplication.simpleTable_fetch] Borrowed connection '206945717'
=== 2016-05-05 20:11:32,406 [ec-1] DEBUG SQLTransaction - [builtinApplication.simpleTable_fetch] Started new SQLSERVER transaction "206945717"
=== 2016-05-05 20:11:32,406 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] Setting DSRequest as being part of a transaction
=== 2016-05-05 20:11:32,406 [ec-1] INFO  SQLDriver - [builtinApplication.simpleTable_fetch] Executing SQL query on 'SQLSERVER' using connection '206945717': SELECT COUNT(*) FROM
                t_schueler
            
=== 2016-05-05 20:11:32,408 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] Using SQL Limit query
=== 2016-05-05 20:11:32,408 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] SQL windowed select rows 0->119, result size 119. Query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY t_schueler.f_vorname) AS rowID FROM (SELECT TOP 100 PERCENT  t_schueler.f_schueler_id, t_schueler.f_name, t_schueler.f_vorname FROM
                t_schueler
             WHERE ('1'='1') ORDER BY t_schueler.f_vorname) x) y WHERE y.rowID BETWEEN 1 AND 119
=== 2016-05-05 20:11:32,408 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] SQL windowed select rows 0->119, result size 119. Query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY t_schueler.f_vorname) AS rowID FROM (SELECT TOP 100 PERCENT  t_schueler.f_schueler_id, t_schueler.f_name, t_schueler.f_vorname FROM
                t_schueler
             WHERE ('1'='1') ORDER BY t_schueler.f_vorname) x) y WHERE y.rowID BETWEEN 1 AND 119
=== 2016-05-05 20:11:32,408 [ec-1] DEBUG SQLDataSource - [builtinApplication.simpleTable_fetch] Setting DSRequest as being part of a transaction
=== 2016-05-05 20:11:32,409 [ec-1] DEBUG DSRequest - freeOnExecute is false for request of type fetch on DataSource simpleTable - not freeing resources!
=== 2016-05-05 20:11:32,409 [ec-1] WARN  RequestContext - dsRequest.execute() failed:
com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "t_schueler.f_vorname" could not be bound.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:865)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:762)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:661)
    at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2730)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:2009)
    at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:447)
    at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:392)
    at com.isomorphic.datasource.DataSource.execute(DataSource.java:2236)
    at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:735)
    at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:652)
    at com.isomorphic.application.AppBase.execute(AppBase.java:493)
    at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2689)
    at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:228)
    at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:187)
    at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:152)
    at com.isomorphic.servlet.IDACall._processRequest(IDACall.java:119)
    at com.isomorphic.servlet.IDACall.doPost(IDACall.java:79)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648)
    at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:162)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
    at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
    at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
    at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
    at de.mks_infofabrik.kids.server.filter.GWTCacheControlFilter.doFilter(GWTCacheControlFilter.java:50)
    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
    at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:260)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:88)
    at com.google.inject.servlet.FilterDefinition.doFilter(FilterDefinition.java:163)
    at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:58)
    at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
    at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:217)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1500)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1456)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
=== 2016-05-05 20:11:32,410 [ec-1] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
=== 2016-05-05 20:11:32,410 [ec-1] DEBUG SQLTransaction - Rolling back SQLSERVER transaction "206945717"
=== 2016-05-05 20:11:32,411 [ec-1] DEBUG RPCManager - non-DMI response, dropExtraFields: false
=== 2016-05-05 20:11:32,411 [ec-1] DEBUG SQLTransaction - getConnection() looked for transactional connection for SQLSERVER:  hashcode "206945717"
=== 2016-05-05 20:11:32,411 [ec-1] DEBUG SQLTransaction - Ending SQLSERVER transaction "206945717"
=== 2016-05-05 20:11:32,412 [ec-1] DEBUG SQLConnectionManager - About to close connection with hashcode "206945717"
=== 2016-05-05 20:11:32,412 [ec-1] DEBUG PoolableSQLConnectionFactory - Passivating connection '206945717
=== 2016-05-05 20:11:32,412 [ec-1] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 463
=== 2016-05-05 20:11:32,412 [ec-1] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 463
=== 2016-05-05 20:11:32,412 [ec-1] DEBUG SQLDataSource - About to clear SQLDriver state for DS instance 463
=== 2016-05-05 20:11:32,412 [ec-1] DEBUG PoolableDataSourceFactory - Cleared and passivated DataSource 463 of type 'simpleTable'

Using smartgwt power 6.0-p20160408

[Isomorphic]

Use customFields you've told the SQL engine to go ahead and put your field in the ORDER BY clause despite being marked customSQL. Since the column doesn't actually exist, this obviously can't work - you'll need to provide clause by clause SQL for whatever you want to happen (see eg dsField.customSelectExpression).

[edulid]

Using

customFields="f_vorname"

I am telling the engine that I am using this column: "f_vorname". So it does exist, or?

"Use customFields you've told the SQL engine to go ahead and put your field in the ORDER BY"
What do the customFields have to do with the ORDER BY clause? Using customFIelds I told the SQL engine to put my field in the order by? what ? I didn't understand your point sorry

[edulid]

The sql sent to the server is:

 
 SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY t_schueler.f_vorname) AS rowID FROM (SELECT TOP 100 PERCENT  t_schueler.f_schueler_id, t_schueler.f_name, t_schueler.f_vorname FROM                 t_schueler              WHERE ('1'='1') ORDER BY t_schueler.f_vorname) x) y WHERE y.rowID BETWEEN 1 AND 119

Which shows that the column f_vorname is actually being sent, or ?

[Isomorphic]

If you set dsField.customSQL, the default is not to generate SQL for the field, then when you listed the field in customFields, this overrides the dsField.customSQL setting and causes SQL to be generated for the field.

Since, according to the error thrown by your SQL DB, the field doesn't actually exist, at this point you're going to need to customize the SQL output (via dsField.customSelectExpression and related properties, or via operationBinding.orderClause) to do whatever you had in mind for sorting by that field.

[edulid]

It actually exists:
The generated sql is:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [B]t_schueler.f_vorname[/B]) AS rowID FROM (SELECT TOP 100 PERCENT  t_schueler.f_schueler_id, t_schueler.f_name, t_schueler.f_vorname FROM                 t_schueler              WHERE ('1'='1') ORDER BY t_schueler.f_vorname) x) y WHERE y.rowID BETWEEN 1 AND 119

Which throws the error shown.
If I replace it with:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [B]f_vorname[/B]) AS rowID FROM (SELECT TOP 100 PERCENT  t_schueler.f_schueler_id, t_schueler.f_name, t_schueler.f_vorname FROM                 t_schueler              WHERE ('1'='1') ORDER BY t_schueler.f_vorname) x) y WHERE y.rowID BETWEEN 1 AND 119

(the differences are shown bold), then it works.
So the sql generated seems invalid.

I will try to set the customSelectExpression="f_vorname" for this field, I think this should work.

[Isomorphic]

This is fixed now and will be available for download in nightly builds since May 7 (tomorrow). Let us know please how it worked for you.

[edulid]

Thank you, the fix works fine.