SQL query is incorrect for DataSourceField referencing different tableName

matus_b

Registered Developer

Join Date: Nov 2013
Posts: 98

SQL query is incorrect for DataSourceField referencing different tableName

13 Oct 2015, 03:58

Hi,
the problem is that for the field which has defined nativeName and tableName pointing to another table as defined in tableName attribute of the DataSource, WHERE clasuse part of SQL query is wrongly generated.
Reproduced on SmartClient Version: v8.3p_2015-09-19/PowerEdition Deployment (built 2015-09-19).

Modified worldDS, government field is now showing the cityName of the joined table CITY.

Code:

<DataSource
    ID="worldDS"
    serverType="sql"
    tableName="worldDS"
>
    <fields>
        <field name="pk"            type="sequence"   hidden="true"            primaryKey="true" />
        <field name="countryCode"   type="text"       title="Code"             required="true"   />
        <field name="countryName"   type="text"       title="Country"          required="true"   nativeName="COUNTRYNAME" tableName="worldDS"/>
        <field name="capital"       type="text"       title="Capital"          nativeName="CAPITAL"/>
        <field name="government"    type="text"       title="Government"  optionDataSource="citySQL"      nativeName="cityName" length="500"      tableName="c" />
        <field name="continent"     type="text"       title="Continent"        >
            <valueMap>
                <value>Europe</value>
                <value>Asia</value>
                <value>North America</value>
                <value>Australia/Oceania</value>
                <value>South America</value>
                <value>Africa</value>
            </valueMap>
        </field>
        <field name="independence"  type="date"       title="Nationhood"          />
        <field name="area"          type="float"      title="Area (km&amp;sup2;)" />
        <field name="population"    type="integer"    title="Population"          />
        <field name="gdp"           type="float"      title="GDP ($M)"            />
        <field name="member_g8"     type="boolean"    title="G8"                  />
    </fields>
    
     
  <operationBindings>
    <operationBinding operationType="fetch" sqlUsePagingHint="false" serverMethod="fetch" skipRowCount="true" >
        <tableClause>
            $defaultTableClause,
            city c
        </tableClause>
        <whereClause>
            $defaultWhereClause
            AND c.countryId = worldDS.pk;
        </whereClause>
    </operationBinding>
    
  </operationBindings>
</DataSource>

In the com.smartgwt.sample.showcase.client.dataintegration.java.sql.BasicConnectorSample the ListGrid is now fetching with criteria for government (cityName)

Code:

        final ListGrid listGrid = new ListGrid();
        listGrid.setWidth(700);
        listGrid.setHeight(224);
        listGrid.setAlternateRecordStyles(true);
        listGrid.setDataSource(DataSource.get("worldDS"));
        listGrid.setShowFilterEditor(true);
        listGrid.setCanEdit(true);
        listGrid.setEditEvent(ListGridEditEvent.CLICK);
        listGrid.setCanRemoveRecords(true);

        Criteria criteria = new Criteria("government", "Oslo");
        listGrid.fetchData(criteria);

You can that in the WHERE clause schema is used twice c.c.cityName:

Code:

     [java] === 2015-10-13 10:15:58,553 [-102] DEBUG SQLDataSource - [builtinApplication.worldDS_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT LIMIT 0 75  worldDS.area, worldDS.CAPITAL AS capital, worldDS.continent, worldDS.countryCode, worldDS.COUNTRYNAME AS countryName, worldDS.gdp, c.cityName AS government, worldDS.independence, worldDS.member_g8, worldDS.pk, worldDS.population FROM
     [java]                     worldDS,
     [java]                     city c
     [java]              WHERE
     [java]                     (c.c.cityName='Oslo')
     [java]                     AND c.countryId = worldDS.pk;

Just for convenience full server log:

Code:

     [java] === 2015-10-13 10:15:58,359 [-102] INFO  RequestContext - URL: '/showcase/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0': Moz (Gecko) with Accept-Encoding header
     [java] === 2015-10-13 10:15:58,359 [-102] DEBUG IDACall - Header Name:Value pair: Cookie:GLog=%7B%0D%20%20%20%20left%3A87%2C%20%0D%20%20%20%20top%3A-8%2C%20%0D%20%20%20%20width%3A1825%2C%20%0D%20%20%20%20height%3A990%2C%20%0D%20%20%20%20priorityDefaults%3A%7B%0D%20%20%20%20%20%20%20%20Log%3A4%0D%20%20%20%20%7D%2C%20%0D%20%20%20%20defaultPriority%3A3%2C%20%0D%20%20%20%20trackRPC%3Atrue%0D%7D; isc_cState=ready; JSESSIONID=8r53hnrslyrz15a02a2jmuol8
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Host:127.0.0.1:8888
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Content-Length:1084
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Content-Type:application/x-www-form-urlencoded; charset=UTF-8
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Accept-Language:en-US,en;q=0.5
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Pragma:no-cache
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Connection:keep-alive
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Referer:http://127.0.0.1:8888/index.html?gwt.codesvr=127.0.0.1:9997
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: User-Agent:Mozilla/5.0 (Windows NT 6.1; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0
     [java] === 2015-10-13 10:15:58,443 [-102] DEBUG IDACall - Header Name:Value pair: Cache-Control:no-cache
     [java] === 2015-10-13 10:15:58,444 [-102] DEBUG IDACall - Header Name:Value pair: Accept-Encoding:gzip, deflate
     [java] === 2015-10-13 10:15:58,445 [-102] DEBUG IDACall - session exists: 8r53hnrslyrz15a02a2jmuol8
     [java] === 2015-10-13 10:15:58,445 [-102] DEBUG IDACall - remote user: null
     [java] === 2015-10-13 10:15:58,448 [-102] DEBUG XML - Parsed XML from (in memory stream): 2ms
     [java] === 2015-10-13 10:15:58,456 [-102] DEBUG RPCManager - Processing 1 requests.
     [java] === 2015-10-13 10:15:58,458 [-102] DEBUG RPCManager - Request #1 (DSRequest) payload: {
     [java]     criteria:{
     [java]         government:"Oslo"
     [java]     },
     [java]     operationConfig:{
     [java]         dataSource:"worldDS",
     [java]         operationType:"fetch",
     [java]         textMatchStyle:"exact"
     [java]     },
     [java]     startRow:0,
     [java]     endRow:75,
     [java]     componentId:"isc_ListGrid_0",
     [java]     appID:"builtinApplication",
     [java]     operation:"worldDS_fetch",
     [java]     oldValues:{
     [java]         government:"Oslo"
     [java]     }
     [java] }
     [java] === 2015-10-13 10:15:58,547 [-102] INFO  IDACall - Performing 1 operation(s)
     [java] === 2015-10-13 10:15:58,548 [-102] DEBUG DeclarativeSecurity - Processing security checks for DataSource null, field null
     [java] === 2015-10-13 10:15:58,548 [-102] DEBUG DeclarativeSecurity - DataSource worldDS is not in the pre-checked list, processing...
     [java] === 2015-10-13 10:15:58,548 [-102] DEBUG AppBase - [builtinApplication.worldDS_fetch] No userTypes defined, allowing anyone access to all operations for this application
     [java] === 2015-10-13 10:15:58,548 [-102] DEBUG AppBase - [builtinApplication.worldDS_fetch] No public zero-argument method named '_worldDS_fetch' found, performing generic datasource operation
     [java] === 2015-10-13 10:15:58,550 [-102] INFO  SQLDataSource - [builtinApplication.worldDS_fetch] Performing fetch operation with
     [java]     criteria: {government:"Oslo"}   values: {government:"Oslo"}
     [java] === 2015-10-13 10:15:58,551 [-102] INFO  SQLDataSource - [builtinApplication.worldDS_fetch] derived query: SELECT $defaultSelectClause FROM
     [java]                     $defaultTableClause,
     [java]                     city c
     [java]              WHERE
     [java]                     $defaultWhereClause
     [java]                     AND c.countryId = worldDS.pk;
     [java]
     [java] === 2015-10-13 10:15:58,552 [-102] INFO  SQLDataSource - [builtinApplication.worldDS_fetch] Skipping row count query - Row count will be obtained by traversing the entire dataset
     [java] === 2015-10-13 10:15:58,553 [-102] DEBUG SQLDataSource - [builtinApplication.worldDS_fetch] Using SQL Limit query
     [java] === 2015-10-13 10:15:58,553 [-102] DEBUG SQLDataSource - [builtinApplication.worldDS_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT LIMIT 0 75  worldDS.area, worldDS.CAPITAL AS capital, worldDS.continent, worldDS.countryCode, worldDS.COUNTRYNAME AS countryName, worldDS.gdp, c.cityName AS government, worldDS.independence, worldDS.member_g8, worldDS.pk, worldDS.population FROM
     [java]                     worldDS,
     [java]                     city c
     [java]              WHERE
     [java]                     (c.c.cityName='Oslo')
     [java]                     AND c.countryId = worldDS.pk;
     [java]
     [java] === 2015-10-13 10:15:58,556 [-102] DEBUG PoolableSQLConnectionFactory - [builtinApplication.worldDS_fetch] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
     [java] === 2015-10-13 10:15:58,556 [-102] DEBUG PoolableSQLConnectionFactory - [builtinApplication.worldDS_fetch] Passing JDBC URL only to getConnection
     [java] === 2015-10-13 10:15:58,557 [-102] DEBUG PoolableSQLConnectionFactory - [builtinApplication.worldDS_fetch] makeObject() created an unpooled Connection '371720346'
     [java] === 2015-10-13 10:15:58,557 [-102] DEBUG SQLConnectionManager - [builtinApplication.worldDS_fetch] Returning borrowed connection '371720346'
     [java] === 2015-10-13 10:15:58,558 [-102] DEBUG SQLConnectionManager - [builtinApplication.worldDS_fetch] About to close jdbcConnection with hashcode "371720346"
     [java] === 2015-10-13 10:15:58,558 [-102] DEBUG PoolableSQLConnectionFactory - [builtinApplication.worldDS_fetch] DriverManager fetching connection for HSQLDB via jdbc url jdbc:hsqldb:hsql://localhost/isomorphic
     [java] === 2015-10-13 10:15:58,560 [-102] DEBUG PoolableSQLConnectionFactory - [builtinApplication.worldDS_fetch] Passing JDBC URL only to getConnection
     [java] === 2015-10-13 10:15:58,561 [-102] DEBUG PoolableSQLConnectionFactory - [builtinApplication.worldDS_fetch] makeObject() created an unpooled Connection '35832897'
     [java] === 2015-10-13 10:15:58,561 [-102] DEBUG SQLConnectionManager - [builtinApplication.worldDS_fetch] About to close jdbcConnection with hashcode "35832897"
     [java] === 2015-10-13 10:15:58,562 [-102] WARN  RequestContext - dsRequest.execute() failed:
     [java] java.sql.SQLException: Three part identifiers prohibited in statement [SELECT LIMIT 0 75  worldDS.area, worldDS.CAPITAL AS capital, worldDS.continent, worldDS.countryCode, worldDS.COUNTRYNAME AS countryName, worldDS.gdp, c.cityName AS government, worldDS.independence, worldDS.member_g8, worldDS.pk, worldDS.population FROM
     [java]                     worldDS,
     [java]                     city c
     [java]              WHERE
     [java]                     (c.c.]
     [java]     at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
     [java]     at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
     [java]     at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
     [java]     at com.isomorphic.sql.SQLDataSource.executeWindowedSelect(SQLDataSource.java:1953)
     [java]     at com.isomorphic.sql.SQLDataSource.SQLExecute(SQLDataSource.java:1404)
     [java]     at com.isomorphic.sql.SQLDataSource.processRequest(SQLDataSource.java:320)
     [java]     at com.isomorphic.sql.SQLDataSource.executeFetch(SQLDataSource.java:264)
     [java]     at com.isomorphic.datasource.DataSource.execute(DataSource.java:1367)
     [java]     at com.isomorphic.application.AppBase.executeDefaultDSOperation(AppBase.java:723)
     [java]     at com.isomorphic.application.AppBase.executeAppOperation(AppBase.java:658)
     [java]     at com.isomorphic.application.AppBase.execute(AppBase.java:491)
     [java]     at com.isomorphic.datasource.DSRequest.execute(DSRequest.java:2044)
     [java]     at com.isomorphic.servlet.IDACall.handleDSRequest(IDACall.java:216)
     [java]     at com.isomorphic.servlet.IDACall.processRPCTransaction(IDACall.java:173)
     [java]     at com.isomorphic.servlet.IDACall.processRequest(IDACall.java:138)
     [java]     at com.isomorphic.servlet.IDACall.doPost(IDACall.java:74)
     [java]     at javax.servlet.http.HttpServlet.service(HttpServlet.java:755)
     [java]     at com.isomorphic.servlet.BaseServlet.service(BaseServlet.java:152)
     [java]     at javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
     [java]     at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:686)
     [java]     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1494)
     [java]     at com.isomorphic.servlet.CompressionFilter.doFilter(CompressionFilter.java:260)
     [java]     at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1474)
     [java]     at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:499)
     [java]     at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:137)
     [java]     at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:557)
     [java]     at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:231)
     [java]     at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1086)
     [java]     at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:428)
     [java]     at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:193)
     [java]     at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1020)
     [java]     at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:135)
     [java]     at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
     [java]     at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:68)
     [java]     at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:116)
     [java]     at org.eclipse.jetty.server.Server.handle(Server.java:370)
     [java]     at org.eclipse.jetty.server.AbstractHttpConnection.handleRequest(AbstractHttpConnection.java:489)
     [java]     at org.eclipse.jetty.server.AbstractHttpConnection.content(AbstractHttpConnection.java:960)
     [java]     at org.eclipse.jetty.server.AbstractHttpConnection$RequestHandler.content(AbstractHttpConnection.java:1021)
     [java]     at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:865)
     [java]     at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:240)
     [java]     at org.eclipse.jetty.server.AsyncHttpConnection.handle(AsyncHttpConnection.java:82)
     [java]     at org.eclipse.jetty.io.nio.SelectChannelEndPoint.handle(SelectChannelEndPoint.java:668)
     [java]     at org.eclipse.jetty.io.nio.SelectChannelEndPoint$1.run(SelectChannelEndPoint.java:52)
     [java]     at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:608)
     [java]     at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:543)
     [java]     at java.lang.Thread.run(Thread.java:662)
     [java] === 2015-10-13 10:15:58,564 [-102] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
     [java] === 2015-10-13 10:15:58,565 [-102] DEBUG RPCManager - non-DMI response, dropExtraFields: false
     [java] === 2015-10-13 10:15:58,566 [-102] INFO  Compression - /showcase/sc/IDACall: 487 -> 311 bytes

Tags: None

matus_b

Registered Developer

Join Date: Nov 2013
Posts: 98

13 Oct 2015, 04:51

In SmartClient Version: v10.0p_2015-10-13/PowerEdition Deployment (built 2015-10-13) it looks good:

Code:

     [java] === 2015-10-13 12:49:38,587 [9-35] DEBUG SQLDataSource - [builtinApplication.worldDS_fetch] SQL windowed select rows 0->75, result size 75. Query: SELECT LIMIT 0 75  worldDS.pk, worldDS.countryCode, worldDS.COUNTRYNAME AS countryName, worldDS.CAPITAL AS capital, c.cityName AS government, worldDS.continent, worldDS.independence, worldDS.area, worldDS.population, worldDS.gdp, worldDS.member_g8 FROM
     [java]                     worldDS,
     [java]                     city c
     [java]              WHERE
     [java]                     (LOWER(c.cityName)='oslo')
     [java]                     AND c.countryId = worldDS.pk;

Comment

Isomorphic

Administrator

Join Date: May 2006

Posts: 54650
#3

15 Oct 2015, 00:52

This is fixed and available for download in 2015-10-15 nightly build.
Comment

Previous template Next

Announcement

SQL query is incorrect for DataSourceField referencing different tableName

Comment

Comment