Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

  • SmartGWT SQL engine generates query with "AS <table-alias>" against Oracle

    Hello,
    I am using following Power version with Oracle as backend

    Code:
    <version.smartgwt-power>[6.0-p20170620]</version.smartgwt-power>
    <version.smartclient-power>v11.0p_2017-06-20</version.smartclient-power>
    <version.gwt>[2.8.1]</version.gwt>
    <version.gwt.plugin>2.8.1</version.gwt.plugin>
    <version.gwt-highcharts>[1.7.0]</version.gwt-highcharts>
    My datasource for a TreeGrid has serverType="sql", a primary key, and foreign key points to pk.
    The TreeGrid shows initially the top level node without any issue. Whenever i want to filter the records, it generates the query which is rejected by Oracle (my server.properties is correctly configured for Oracle):
    Code:
    SELECT COUNT(*) FROM SLM_LOCATION_V WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT SLM_LOCATION_V.LOCATIONID, SLM_LOCATION_V.SUPERLOCATIONID, SLM_LOCATION_V.NAME, SLM_LOCATION_V.COORD, SLM_LOCATION_V.MFCCOORD, SLM_LOCATION_V.PLCCOORD, SLM_LOCATION_V.LOCATIONTYPEID, SLM_LOCATION_V.VALUESETID, SLM_LOCATION_V.WEIGHT, SLM_LOCATION_V.MAXWEIGHT, SLM_LOCATION_V.ISBLOCKED, SLM_LOCATION_V.ISROOT, SLM_LOCATION_V.LOCKED, SLM_LOCATION_V.LOCATIONTYPETEXT, SLM_LOCATION_V.BOOLLOCKED, SLM_LOCATION_V.OCCUPIEDWEIGHTPERCENT, SLM_LOCATION_V.OCCUPIEDHUCNTPERCENT, SLM_LOCATION_V.SUPERCOORD, SLM_LOCATION_V.ROWCOLOR FROM SLM_LOCATION_V WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL)))), Error Msg = ORA-00907: Rechte Klammer fehlt
    I think the problem is the keyword AS before subquery alias "temp". Oracle does not support this.

    Is this a bug? Is there some way i could get around this behavior? Many thanks in advanced and sorry for the formatting!

    here is what i found in log
    Code:
    === 2017-11-12 11:19:23,199 DEBUG [isomorphic.datasource.DSRequest] (default task-40) - Caching instance 6504 of DS sff_hbw_level
    === 2017-11-12 11:19:23,199 DEBUG [isomorphic.timing.Timing] (default task-40) - Time to convert org.apache.commons.collections.map.LinkedMap to JS Object: 0ms
    === 2017-11-12 11:19:23,200 DEBUG [isomorphic.rpc.RPCManager] (default task-40) - Request #1 (DSRequest) payload: {
    criteria:{
    operator:"and",
    _constructor:"AdvancedCriteria",
    criteria:[
    {
    operator:"and",
    criteria:[
    {
    fieldName:"LOCATIONTYPETEXT",
    operator:"iContainsPattern",
    value:"bin",
    _constructor:"AdvancedCriteria"
    }
    ],
    _constructor:"AdvancedCriteria"
    },
    {
    _constructor:"AdvancedCriteria",
    fieldName:"SUPERLOCATIONID",
    value:null,
    operator:"equals"
    }
    ]
    },
    operationConfig:{
    dataSource:"sff_hbw_level",
    repo:null,
    operationType:"fetch",
    textMatchStyle:"substring"
    },
    startRow:0,
    endRow:75,
    componentId:"SFF_HBW_LEVEL_ListGrid",
    appID:"builtinApplication",
    operation:"sff_hbw_level_fetch",
    oldValues:{
    operator:"and",
    _constructor:"AdvancedCriteria",
    criteria:[
    {
    operator:"and",
    criteria:[
    {
    fieldName:"LOCATIONTYPETEXT",
    operator:"iContainsPattern",
    value:"bin",
    _constructor:"AdvancedCriteria"
    }
    ],
    _constructor:"AdvancedCriteria"
    },
    {
    _constructor:"AdvancedCriteria",
    fieldName:"SUPERLOCATIONID",
    value:null,
    operator:"equals"
    }
    ]
    },
    resultTreeIdField:"LOCATIONID",
    resultTreeParentIdField:"SUPERLOCATIONID",
    keepParentsOnFilter:true
    }
    === 2017-11-12 11:19:23,200 INFO [isomorphic.servlet.IDACall] (default task-40) - Performing 1 operation(s)
    === 2017-11-12 11:19:23,200 DEBUG [isomorphic.datasource.DeclarativeSecurity] (default task-40) - Processing security checks for DataSource null, field null
    === 2017-11-12 11:19:23,200 DEBUG [isomorphic.datasource.DeclarativeSecurity] (default task-40) - DataSource sff_hbw_level is not in the pre-checked list, processing...
    === 2017-11-12 11:19:23,200 DEBUG [isomorphic.application.AppBase] (default task-40) - [builtinApplication.sff_hbw_level_fetch] No userTypes defined, allowing anyone access to all operations for this application
    === 2017-11-12 11:19:23,200 DEBUG [isomorphic.application.AppBase] (default task-40) - [builtinApplication.sff_hbw_level_fetch] No public zero-argument method named '_sff_hbw_level_fetch' found, performing generic datasource operation
    === 2017-11-12 11:19:23,200 DEBUG [isomorphic.timing.Timing] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Time to convert java.util.HashMap to JS Object: 0ms
    === 2017-11-12 11:19:23,201 DEBUG [isomorphic.timing.Timing] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Time to convert java.util.HashMap to JS Object: 0ms
    === 2017-11-12 11:19:23,201 INFO [isomorphic.sql.SQLDataSource] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Performing fetch operation with
    criteria: {_constructor:"AdvancedCriteria",criteria:[{criteria:[{fieldName:"LOCATIONTYPETEXT",operator:"iContainsPattern",value:"bin",_constructor:"AdvancedCriteria"}],operator:"and"},{_constructor:"AdvancedCriteria",fieldName:"SUPERLOCATIONID",value:null,operator:"equals"}],operator:"and"} values: {_constructor:"AdvancedCriteria",criteria:[{criteria:[{fieldName:"LOCATIONTYPETEXT",operator:"iContainsPattern",value:"bin",_constructor:"AdvancedCriteria"}],operator:"and"},{_constructor:"AdvancedCriteria",fieldName:"SUPERLOCATIONID",value:null,operator:"equals"}],operator:"and"}
    === 2017-11-12 11:19:23,202 INFO [isomorphic.sql.SQLDataSource] (default task-40) - [builtinApplication.sff_hbw_level_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT $defaultSelectClause FROM $defaultTableClause WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL))))
    === 2017-11-12 11:19:23,204 DEBUG [isomorphic.sql.SQLDataSource] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Executing row count query: SELECT COUNT(*) FROM $defaultTableClause WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT $defaultSelectClause FROM $defaultTableClause WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL))))
    === 2017-11-12 11:19:23,205 DEBUG [isomorphic.sql.SQLDataSource] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Eval'd row count query: SELECT COUNT(*) FROM SLM_LOCATION_V WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT SLM_LOCATION_V.LOCATIONID, SLM_LOCATION_V.SUPERLOCATIONID, SLM_LOCATION_V.NAME, SLM_LOCATION_V.COORD, SLM_LOCATION_V.MFCCOORD, SLM_LOCATION_V.PLCCOORD, SLM_LOCATION_V.LOCATIONTYPEID, SLM_LOCATION_V.VALUESETID, SLM_LOCATION_V.WEIGHT, SLM_LOCATION_V.MAXWEIGHT, SLM_LOCATION_V.ISBLOCKED, SLM_LOCATION_V.ISROOT, SLM_LOCATION_V.LOCKED, SLM_LOCATION_V.LOCATIONTYPETEXT, SLM_LOCATION_V.BOOLLOCKED, SLM_LOCATION_V.OCCUPIEDWEIGHTPERCENT, SLM_LOCATION_V.OCCUPIEDHUCNTPERCENT, SLM_LOCATION_V.SUPERCOORD, SLM_LOCATION_V.ROWCOLOR FROM SLM_LOCATION_V WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL))))
    === 2017-11-12 11:19:23,205 DEBUG [isomorphic.sql.PoolableSQLConnectionFactory] (default task-40) - [builtinApplication.sff_hbw_level_fetch] makeObject() created an unpooled Connection '375591394'
    === 2017-11-12 11:19:23,205 DEBUG [isomorphic.sql.SQLConnectionManager] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Borrowed connection '375591394'
    === 2017-11-12 11:19:23,205 DEBUG [isomorphic.sql.SQLTransaction] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Started new EMESDB transaction "375591394"
    === 2017-11-12 11:19:23,205 DEBUG [isomorphic.sql.SQLDataSource] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Setting DSRequest as being part of a transaction
    === 2017-11-12 11:19:23,205 INFO [isomorphic.sql.SQLDriver] (default task-40) - [builtinApplication.sff_hbw_level_fetch] Executing SQL query on 'EMESDB' using connection '375591394': SELECT COUNT(*) FROM SLM_LOCATION_V WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT SLM_LOCATION_V.LOCATIONID, SLM_LOCATION_V.SUPERLOCATIONID, SLM_LOCATION_V.NAME, SLM_LOCATION_V.COORD, SLM_LOCATION_V.MFCCOORD, SLM_LOCATION_V.PLCCOORD, SLM_LOCATION_V.LOCATIONTYPEID, SLM_LOCATION_V.VALUESETID, SLM_LOCATION_V.WEIGHT, SLM_LOCATION_V.MAXWEIGHT, SLM_LOCATION_V.ISBLOCKED, SLM_LOCATION_V.ISROOT, SLM_LOCATION_V.LOCKED, SLM_LOCATION_V.LOCATIONTYPETEXT, SLM_LOCATION_V.BOOLLOCKED, SLM_LOCATION_V.OCCUPIEDWEIGHTPERCENT, SLM_LOCATION_V.OCCUPIEDHUCNTPERCENT, SLM_LOCATION_V.SUPERCOORD, SLM_LOCATION_V.ROWCOLOR FROM SLM_LOCATION_V WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL))))
    === 2017-11-12 11:19:23,208 DEBUG [isomorphic.datasource.DSRequest] (default task-40) - freeOnExecute is false for request of type fetch on DataSource sff_hbw_level - not freeing resources!
    === 2017-11-12 11:19:23,209 WARN [isomorphic.servlet.RequestContext] (default task-40) - dsRequest.execute() failed:
    java.sql.SQLSyntaxErrorException: ORA-00907: Rechte Klammer fehlt
    
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:213)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:37)
    at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:733)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:904)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1082)
    at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1276)
    at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:366)
    at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:381)
    at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:653)
    at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:577)
    at com.isomorphic.sql.SQLDriver.getTransformedResults(SQLDriver.java:570)
    at com.isomorphic.sql.SQLDriver.getScalarResult(SQLDriver.java:763)
    at com.isomorphic.sql.SQLDriver.executeScalar(SQLDriver.java:1058)
    at com.isomorphic.sql.SQLDataSource.runRowCountQuery(SQLDataSource.java:3219)
    at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2598)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:2024)
    at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:449)
    at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:394)
    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:2723)
    at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:230)
    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:707)
    at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:162)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
    at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:86)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:130)
    at enisco.emes.platform.api.gwt.server.filter.CacheFilter.doFilter(CacheFilter.java:109)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:132)
    at com.isomorphic.servlet.CompressionFilter._doFilter(CompressionFilter.java:260)
    at com.isomorphic.servlet.BaseFilter.doFilter(BaseFilter.java:88)
    at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60)
    at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:132)
    at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:85)
    at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62)
    at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36)
    at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131)
    at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57)
    at io.undertow.server.handlers.DisableCacheHandler.handleRequest(DisableCacheHandler.java:33)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.security.handlers.AuthenticationConstraintHandler.handleRequest(AuthenticationConstraintHandler.java:51)
    at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46)
    at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64)
    at io.undertow.servlet.handlers.security.ServletSecurityConstraintHandler.handleRequest(ServletSecurityConstraintHandler.java:56)
    at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:58)
    at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:72)
    at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest(NotificationReceiverHandler.java:50)
    at io.undertow.security.handlers.SecurityInitialHandler.handleRequest(SecurityInitialHandler.java:76)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43)
    at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:282)
    at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:261)
    at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:80)
    at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:172)
    at io.undertow.server.Connectors.executeRootHandler(Connectors.java:199)
    at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:774)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
    Caused by: Error : 907, Position : 75, Sql = SELECT COUNT(*) FROM SLM_LOCATION_V WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT SLM_LOCATION_V.LOCATIONID, SLM_LOCATION_V.SUPERLOCATIONID, SLM_LOCATION_V.NAME, SLM_LOCATION_V.COORD, SLM_LOCATION_V.MFCCOORD, SLM_LOCATION_V.PLCCOORD, SLM_LOCATION_V.LOCATIONTYPEID, SLM_LOCATION_V.VALUESETID, SLM_LOCATION_V.WEIGHT, SLM_LOCATION_V.MAXWEIGHT, SLM_LOCATION_V.ISBLOCKED, SLM_LOCATION_V.ISROOT, SLM_LOCATION_V.LOCKED, SLM_LOCATION_V.LOCATIONTYPETEXT, SLM_LOCATION_V.BOOLLOCKED, SLM_LOCATION_V.OCCUPIEDWEIGHTPERCENT, SLM_LOCATION_V.OCCUPIEDHUCNTPERCENT, SLM_LOCATION_V.SUPERCOORD, SLM_LOCATION_V.ROWCOLOR FROM SLM_LOCATION_V WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL)))), OriginalSql = SELECT COUNT(*) FROM SLM_LOCATION_V WHERE ((SLM_LOCATION_V.SUPERLOCATIONID IS NULL) AND (((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL)) OR EXISTS(SELECT * FROM ( SELECT SLM_LOCATION_V.LOCATIONID, SLM_LOCATION_V.SUPERLOCATIONID, SLM_LOCATION_V.NAME, SLM_LOCATION_V.COORD, SLM_LOCATION_V.MFCCOORD, SLM_LOCATION_V.PLCCOORD, SLM_LOCATION_V.LOCATIONTYPEID, SLM_LOCATION_V.VALUESETID, SLM_LOCATION_V.WEIGHT, SLM_LOCATION_V.MAXWEIGHT, SLM_LOCATION_V.ISBLOCKED, SLM_LOCATION_V.ISROOT, SLM_LOCATION_V.LOCKED, SLM_LOCATION_V.LOCATIONTYPETEXT, SLM_LOCATION_V.BOOLLOCKED, SLM_LOCATION_V.OCCUPIEDWEIGHTPERCENT, SLM_LOCATION_V.OCCUPIEDHUCNTPERCENT, SLM_LOCATION_V.SUPERCOORD, SLM_LOCATION_V.ROWCOLOR FROM SLM_LOCATION_V WHERE ((LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL) AND (LOWER(SLM_LOCATION_V.LOCATIONTYPETEXT) LIKE LOWER('%bin%') ESCAPE'\' AND SLM_LOCATION_V.LOCATIONTYPETEXT IS NOT NULL))) AS temp WHERE temp.SUPERLOCATIONID = SLM_LOCATION_V.LOCATIONID OR (temp.SUPERLOCATIONID IS NULL AND SLM_LOCATION_V.LOCATIONID IS NULL)))), Error Msg = ORA-00907: Rechte Klammer fehlt
    
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:498)
    ... 75 more
    === 2017-11-12 11:19:23,210 DEBUG [isomorphic.servlet.RequestContext] (default task-40) - Setting headers to disable caching
    === 2017-11-12 11:19:23,210 DEBUG [isomorphic.rpc.RPCManager] (default task-40) - Content type for RPC transaction: text/plain; charset=UTF-8
    Last edited by mail; 14th Nov 2017, 00:51.

  • #2
    We need to see the DataSource definition, especially the operationBindings, where customized SQL may be the explanation for this bad query.

    Comment


    • #3
      Hello Isomorphic,

      here is my datasource:

      Code:
      <DataSource 
          ID="sff_hbw_level" 
          serverType="sql" 
          tableName="SLM_LOCATION_V"
          showPrompt="false"
      > 
          <!-- requiresAuthentication="true" -->
          <!-- editRequiresRole parameter doesn't work. We have this definition to prevent that the DS -->
          <!-- fetch data from database without a valid user authentication. -->
      
          <!-- Parameter "tableName": Only applicable to "sql" dataSources, setting tableName  -->
          <!-- indicates that this field will be coming from another table by way of a customized SQL query. -->
          <fields>    
              <field  name="LOCATIONID"               type="integer" primaryKey="true"        />
              <field  name="SUPERLOCATIONID"          type="integer" foreignKey="LOCATIONID"  />
              <field  name="NAME"                     type="text"                             />
              <field  name="COORD"                    type="text"                             />   
              <field  name="MFCCOORD"                 type="text"                             />
              <field  name="PLCCOORD"                 type="text"                             />
              <field  name="LOCATIONTYPEID"           type="integer"                          />
              <field  name="VALUESETID"               type="integer"                          />
              <field  name="WEIGHT"                   type="integer"                          />
              <field  name="MAXWEIGHT"                type="integer"                          />
              <field  name="ISBLOCKED"                type="text"                             />
              <field  name="ISROOT"                   type="text"                             />
              <field  name="LOCKED"                   type="integer"                          />
              <field  name="LOCATIONTYPETEXT"         type="text"                             />
              <field  name="BOOLLOCKED"               type="integer"                          />
              <field  name="OCCUPIEDWEIGHTPERCENT"    type="text"                             />
              <field  name="OCCUPIEDHUCNTPERCENT"     type="integer"                          />
              <field  name="SUPERCOORD"               type="text"                             />
              <field  name="ROWCOLOR"                 type="integer"                          />
          </fields>
      </DataSource>
      Thank you!

      Comment


      • #4
        Thanks - sorry we did not mention this before, but you are not up to date on patches. Please download the latest patched version and let us know if the problem persists.

        Comment


        • #5
          Hello Isomorphic,
          We have just tested with version 20171114, unfortunately it has the same problem with Oracle.

          Could you please investigate this?
          Thank you!

          Comment


          • #6
            This is fixed and will be available for download in nightly builds since Nov 25 (tomorrow). Please let us know how it worked for you.

            Comment

            Working...
            X