Announcement

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

    Error in Custom SQL Generation with Velocity Template variable $rawValue

    I'm having an issue with SmartGWT 4.1p
    SmartClient Version: v9.1p_2014-03-08/PowerEdition Deployment (built 2014-03-08)

    with the SQL generated when using a custom SQL.

    In the older SmartGWT 3.2
    SmartClient Version: v8.3p_2014-02-24/PowerEdition Deployment (built 2014-02-24)

    I’m passing the values of $rawValue.advancedCriteria.groupdimfield as "delivery point, rate class" and everything works great. The only difference is using the SmartGWT 9.1.

    (see SQL generated by ServerSide SmartGWT)
    SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY deliverypoint, rateclass) AS rowID FROM (SELECT TOP 100 PERCENT measurementyear,
    sum(Jan) jan, sum(Feb) feb, sum(Mar) mar, sum(Apr) apr,
    sum(May) may, sum(Jun) jun, sum(Jul) jul, sum(Aug) aug,
    sum(Sep) sep, sum(Oct) oct, sum(Nov) nov, sum(Dec) dec,
    sum(measurementvalue) total, deliverypoint,rateclass
    FROM
    attributes, measurementheader, reportmeasurement
    WHERE
    attributes.uidmeter = measurementheader.uidmeter and measurementheader.uidmeasurementheader = reportmeasurement.uidmeasurementheader
    and ((versionname = 'G1203131519' AND versionname IS NOT NULL) AND (utility IS NULL) AND (tos IS NULL) AND (weatherzone IS NULL) AND (loadprofile IS NULL) AND (tripcycle IS NULL) AND (governmenttype IS NULL) AND (billingmethod IS NULL) AND (resourcename IS NULL) AND (ICAP IS NULL) AND (businesspartnername IS NULL) AND (powerprogram IS NULL) AND (minbill = 'N' AND minbill IS NOT NULL) AND (powertype IS NULL) AND (accountstatuscode IS NULL) AND (contractvalue IS NULL) AND (measurementtype = 'TE' AND measurementtype IS NOT NULL) AND (customertype = 'G' AND customertype IS NOT NULL) AND (measurementyear = '2013' AND measurementyear IS NOT NULL) AND '1'='1' AND '1'='1')
    GROUP BY
    measurementyear, deliverypoint,rateclass
    ORDER BY
    measurementyear, deliverypoint,rateclass
    ) x) y WHERE y.rowID BETWEEN 1 AND 1001

    On the SmartGWT 4.1, I get this error and I can see that the ORDER BY clause doesn't decode the $rawValue.advancedCrriteria.groupdimfield which was set as "deliverypoint, rateclass" although the main query works. I'm attaching the datasource definition.

    === 2014-04-09 03:33:01,903 [ec-9] DEBUG SQLDataSource - [builtinApplication.DPxRC] SQL windowed select rows 0->1001, result size 1001. Query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY
    measurementyear, $rawValue.advancedCriteria.groupdimfield
    ) AS rowID FROM (SELECT TOP 100 PERCENT measurementyear,
    sum(Jan) jan, sum(Feb) feb, sum(Mar) mar, sum(Apr) apr,
    sum(May) may, sum(Jun) jun, sum(Jul) jul, sum(Aug) aug,
    sum(Sep) sep, sum(Oct) oct, sum(Nov) nov, sum(Dec) dec,
    sum(measurementvalue) total, deliverypoint,rateclass
    FROM
    attributes, measurementheader, reportmeasurement
    WHERE
    attributes.uidmeter = measurementheader.uidmeter and measurementheader.uidmeasurementheader = reportmeasurement.uidmeasurementheader
    and ((versionname = 'G1203131519' AND versionname IS NOT NULL) AND (utility IS NULL) AND (tos IS NULL) AND (weatherzone IS NULL) AND (loadprofile IS NULL) AND (tripcycle IS NULL) AND (governmenttype IS NULL) AND (billingmethod IS NULL) AND (resourcename IS NULL) AND (ICAP IS NULL) AND (businesspartnername IS NULL) AND (powerprogram IS NULL) AND (minbill = 'N' AND minbill IS NOT NULL) AND (powertype IS NULL) AND (accountstatuscode IS NULL) AND (contractvalue IS NULL) AND (measurementtype = 'TE' AND measurementtype IS NOT NULL) AND (customertype = 'G' AND customertype IS NOT NULL) AND (measurementyear = '2013' AND measurementyear IS NOT NULL) AND '1'='1' AND '1'='1')
    GROUP BY
    measurementyear, deliverypoint,rateclass
    ORDER BY
    measurementyear, deliverypoint,rateclass
    ) x) y WHERE y.rowID BETWEEN 1 AND 1001
    === 2014-04-09 03:33:01,904 [ec-9] DEBUG SQLDataSource - [builtinApplication.DPxRC] SQL windowed select rows 0->1001, result size 1001. Query: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY
    measurementyear, $rawValue.advancedCriteria.groupdimfield
    ) AS rowID FROM (SELECT TOP 100 PERCENT measurementyear,
    sum(Jan) jan, sum(Feb) feb, sum(Mar) mar, sum(Apr) apr,
    sum(May) may, sum(Jun) jun, sum(Jul) jul, sum(Aug) aug,
    sum(Sep) sep, sum(Oct) oct, sum(Nov) nov, sum(Dec) dec,
    sum(measurementvalue) total, deliverypoint,rateclass
    FROM
    attributes, measurementheader, reportmeasurement
    WHERE
    attributes.uidmeter = measurementheader.uidmeter and measurementheader.uidmeasurementheader = reportmeasurement.uidmeasurementheader
    and ((versionname = 'G1203131519' AND versionname IS NOT NULL) AND (utility IS NULL) AND (tos IS NULL) AND (weatherzone IS NULL) AND (loadprofile IS NULL) AND (tripcycle IS NULL) AND (governmenttype IS NULL) AND (billingmethod IS NULL) AND (resourcename IS NULL) AND (ICAP IS NULL) AND (businesspartnername IS NULL) AND (powerprogram IS NULL) AND (minbill = 'N' AND minbill IS NOT NULL) AND (powertype IS NULL) AND (accountstatuscode IS NULL) AND (contractvalue IS NULL) AND (measurementtype = 'TE' AND measurementtype IS NOT NULL) AND (customertype = 'G' AND customertype IS NOT NULL) AND (measurementyear = '2013' AND measurementyear IS NOT NULL) AND '1'='1' AND '1'='1')
    GROUP BY
    measurementyear, deliverypoint,rateclass
    ORDER BY
    measurementyear, deliverypoint,rateclass
    ) x) y WHERE y.rowID BETWEEN 1 AND 1001
    === 2014-04-09 03:33:01,913 [ec-9] DEBUG SQLConnectionManager - [builtinApplication.DPxRC] About to close PoolGuardConnectionWrapper with hashcode "1286234893"
    === 2014-04-09 03:33:01,916 [ec-9] DEBUG PoolableSQLConnectionFactory - [builtinApplication.DPxRC] makeObject() created an unpooled Connection '1286234893'
    === 2014-04-09 03:33:01,926 [ec-9] WARN RequestContext - dsRequest.execute() failed:
    com.microsoft.sqlserver.jdbc.SQLServerException: Invalid pseudocolumn "$rawValue".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:775)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:676)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:611)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
    at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:2437)
    at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1859)
    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:1488)
    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:2564)
    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:641)
    at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:260)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:999)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:565)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:309)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:744)
    Attached Files
    Tags: None
    #2
    We've tried various things, but we are unable to reproduce any situation in which $rawValue is not being substituted as expected.

    Please post the complete server log for the request (remember to always do this), and we might be able to spot something more. For example, perhaps the "groupdimfield" criteria is not being passed from the client, which would mean you actually have some kind of client-side problem.

    Comment

      #3
      I'll send you an example application

      I'll post a simplified version of the application. The fact that it works with the 3.2 version of the SmartGWT and when I just recompile it and run it on the 4.1 and it fails tells me that something in the 4.1 breaks the code as nothing was changed other a change out of all the jar files and a GWT recompile of the code.

      Comment

        #4
        Aside from there being no such thing as SmartGWT 3.2 :) ...

        Upgrading can sometimes reveal longstanding usage issues or reliance on undocumented behavior. A large proportion of "regression" reports turn out to be this.

        We've made an attempt to find a general issue with $rawValue and can't find anything, so yes, we'll need a way to reproduce the issue.

        Comment

          #5
          By the way, whatever the issue here is, it's clear that it will only happen with sqlPaging:"sqlLimit" on MS SQL Server. So you could set operationBinding.sqlPaging to "jdbcScroll" as a workaround.

          Comment

            #6
            This is fixed now, you may download next (2014-05-02) nightly build and try it out.

            Comment

              #7
              Looks like it works

              Thanks - it looks like it works now - I meant SmartGWT 3.1p.

              Comment

              Previous template Next
              Working...
              X