Announcement

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

    SQLDataSource - strange behaviour where a field is ignored?

    SmartClient Version: v11.1p_2019-12-02/Enterprise Deployment (built 2019-12-02)


    Hello, I just noticed a strange behaviour in a SQLDataSource, where In my actual use case it seems that I forgot to add a join for the default fetch, which seems required for a field, but it does work indeed, as the field seems to be ignored.
    It is like that in my application since a long time.

    I've simplified it for a test case:

    Code:
    <DataSource xmlns="http://www.smartclient.com/schema"
                ID="IMA_ABBONAMENTI"
                tableName="IMA_ABBONAMENTI"
                schema="DBSALES"
                dbName="dbJpcEP"
                serverType="sql"
                dropExtraFields="false"
            >
        <fields>
            <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true"
                   sequenceName="SEQUENCE_ID_REC"/>
            <field name="ID_STAGIONE_FK" type="integer" foreignKey="JPC_STAGIONI.ID_REC"/>
            <field name="DATA_INIZIO_STAGIONE" nativeName="DATA_INIZIO" type="date" tableName="JPC_STAGIONI"/>
        </fields>
    ....
    When I execute IMA_ABBONAMENTI.fetchData() the generated query doesn't contain JPC_STAGIONI.DATA_INIZIO:

    Code:
    SELECT IMA_ABBONAMENTI.ID_REC, 
           IMA_ABBONAMENTI.ID_STAGIONE_FK
    FROM DBSALES.IMA_ABBONAMENTI
    WHERE (IMA_ABBONAMENTI.ID_STAGIONE_FK = 4485361)
    so DATA_INIZIO_STAGIONE seems to be ignored, which I find strange, but as it's working I really haven't noticed it...until I've added an operationBinding:

    Code:
            <operationBinding operationType="fetch">
                <whereClause>$defaultWhereClause</whereClause>
            </operationBinding>
    that seems to be sufficient to trigger an error, as now the generated query is:

    Code:
    SELECT IMA_ABBONAMENTI.ID_REC, 
           IMA_ABBONAMENTI.ID_STAGIONE_FK, 
           JPC_STAGIONI.DATA_INIZIO AS DATA_INIZIO_STAGIONE
    FROM DBSALES.IMA_ABBONAMENTI
    WHERE (IMA_ABBONAMENTI.ID_STAGIONE_FK = 4485361)
    which leads to:
    java.sql.SQLSyntaxErrorException: ORA-00904: "JPC_STAGIONI"."DATA_INIZIO": identificativo non valido
    Why is there a different behaviour? Which is correct?

    #2
    In order to have a field with tableName defined to be included in fetch operation, you must join that table via SQL templating, like it is documented. Behaviour is expected in both cases described:
    - if no SQL templating is involved, including field with tableName would lead to wrong SQL, so we ignore it.
    - if there is SQL templating involved, we assume that table is joined manually and include it in SQL query. In your case it results in wrong SQL, cause JPC_STAGIONI table is not properly joined.

    Comment


      #3
      I didn't recall that fields with tableName are joined only in customized fetches, thanks for the heads up!

      Comment

      Working...
      X