Announcement

**Isomorphic** · 30 Jun 2014, 06:46

We'll take a look at this error anyway, but I don't see why you would need to configure your data sources that particular way. If there *is* a reason, could you provide complete ds.xml files? I assume that will speak for itself, but if you think some parts may require explanations, it would be great if you could share those too.

Despite all said above..

What if you just declare FK field in IMA_MATCH data source like shown below?

Code:

<field name="ID_STAGIONE_FK" type="integer" foreignKey="JPC_STAGIONI.ID_REC"/>

**claudiobosticco** · 30 Jun 2014, 07:13

thanks for your reply.
Actually I've removed all the custom sql to obtain a simpler test case.

I can't declare the field ID_STAGIONE_FK as you wrote because it's actually a field from the other table.

My real IMA_MATCH datasource is:

Code:

<!--
 <%@page pageEncoding="UTF-8" contentType="text/xml; charset=UTF-8"%>
 <%@ taglib prefix="fmt" uri="/WEB-INF/fmt.tld" %>
 <fmt:setBundle basename="i18nMessages_${locale}" var="i18n"/>
 -->
<DataSource xmlns="http://www.smartclient.com/schema"
            ID="IMA_MATCH"
            tableName="IMA_MATCH"
            schema="DBSALES"
            dbName="dbJpcEP"
            serverType="sql"
        >
	<fields>
        <field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true">
            <sequenceName>SEQUENCE_ID_REC</sequenceName>
        </field>
        <field name="STATO" type="text" length="1" sqlLength="1" sqlType="varchar">
            <valueMap>
                <value ID="C">Ceduta</value>
                <value ID="V">Messa in vendita</value>
                <value ID="N">Non ceduta</value>
            </valueMap>
        </field>
        <field name="IS_VENDUTO" type="boolean" length="1" sqlLength="1" sqlType="varchar"
               sqlStorageStrategy="singleCharTF"/>
        <field name="ID_RISERVA_FK" type="integer" foreignKey="IMA_RISERVE.ID_REC"/>
        <field name="ID_EVENTO_FK" type="integer" foreignKey="JPC_LISTINI.ID_REC" tableName="JPC_LISTINI"
               nativeName="ID_REC"/>
        <field name="ID_ABBONAMENTO_FK" type="integer" foreignKey="IMA_ABBONAMENTI.ID_REC" tableName="IMA_ABBONAMENTI"
               nativeName="ID_REC"/>
        <field name="CREDITO" type="float"/>
        <field name="CREATOR" type="creator" length="100" sqlLength="100" sqlType="varchar"/>
        <field name="CREATOR_TIMESTAMP" type="creatorTimestamp"/>
        <field name="MODIFIER" type="modifier" length="100" sqlLength="100" sqlType="varchar"/>
        <field name="MODIFIER_TIMESTAMP" type="modifierTimestamp"/>
        <field name="DATA_AGGIORNAMENTO_LISTICKET" type="datetime"/>


        <field name="TIPO_LISTINO" type="text" tableName="JPC_LISTINI"/>
        <field name="DATA_INIZIO" type="datetime" tableName="JPC_LISTINI"/>
        <field name="DATA_FINE" type="datetime" tableName="JPC_LISTINI"/>
        <field name="ID_STAGIONE_FK" nativeName="ID_REC" type="integer" tableName="JPC_STAGIONI"/>
        <field name="STAGIONE" nativeName="DESCRIZIONE" type="text" tableName="JPC_STAGIONI"/>
        <field name="DATA_INIZIO_STAGIONE" nativeName="DATA_INIZIO" type="date" tableName="JPC_STAGIONI"/>
        <field name="DATA_FINE_STAGIONE" nativeName="DATA_FINE" type="date" tableName="JPC_STAGIONI"/>
        <field name="HOME_TEAM" nativeName="DESCRIZIONE" type="text" tableName="SQUADRE_HOME"/>
        <field name="AWAY_TEAM" nativeName="DESCRIZIONE" type="text" tableName="SQUADRE_AWAY"/>
        <field name="MATCH" type="text"
               customSelectExpression="SQUADRE_HOME.DESCRIZIONE || ' - ' || SQUADRE_AWAY.DESCRIZIONE"/>

        <field name="TIPO_EVENTO" nativeName="NOME" type="text" tableName="LEG_TIPI_EVENTO"/>
        <field name="CODICE_TIPO_EVENTO" nativeName="CODICE" type="text" tableName="LEG_TIPI_EVENTO"/>

        <field name="NOME_RISERVA" nativeName="NOME" type="text" tableName="IMA_RISERVE"/>
        <field name="COGNOME_RISERVA" nativeName="COGNOME" type="text" tableName="IMA_RISERVE"/>
        <field name="NOMINATIVO_RISERVA" type="text"
               customSelectExpression="IMA_RISERVE.COGNOME || ' ' || IMA_RISERVE.NOME"/>
        <field name="NICKNAME" type="text" tableName="IMA_TITOLARI"/>
        <field name="NOME" type="text" tableName="IMA_TITOLARI"/>
        <field name="COGNOME" type="text" tableName="IMA_TITOLARI"/>
        <field name="EMAIL" type="text" tableName="IMA_TITOLARI"/>

        <field name="SETTORE" type="text" tableName="IMA_ABBONAMENTI"/>
        <field name="FILA" type="text" tableName="IMA_ABBONAMENTI"/>
        <field name="POSTO" type="text" tableName="IMA_ABBONAMENTI"/>

        <field name="CODICE_MACRO_AREA" nativeName="CODICE" type="text" tableName="IMA_MACRO_AREE" customSQL="true"/>
        <field name="DESCRIZIONE_MACRO_AREA" nativeName="DESCRIZIONE" type="text" tableName="IMA_MACRO_AREE"
               customSQL="true"/>

    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" customFields="CODICE_MACRO_AREA, DESCRIZIONE_MACRO_AREA">
            <tableClause>
                $defaultTableClause, IMA_RISERVE, IMA_TITOLARI, IMA_ABBONAMENTI, IMA_SETTORI, IMA_MACRO_AREE,
                JPC_LISTINI, JPC_STAGIONI,
                LEG_TIPI_EVENTO, LEG_SQUADRE SQUADRE_HOME, LEG_SQUADRE SQUADRE_AWAY
            </tableClause>
            <whereClause>
                $defaultWhereClause
                and IMA_MATCH.ID_RISERVA_FK = IMA_RISERVE.ID_REC(+)
                and IMA_MATCH.ID_EVENTO_FK = JPC_LISTINI.ID_REC
                and JPC_LISTINI.ID_TIPO_EVENTO_FK = LEG_TIPI_EVENTO.ID_REC
                and JPC_LISTINI.ID_HOME_TEAM_FK = SQUADRE_HOME.ID_REC
                and JPC_LISTINI.ID_AWAY_TEAM_FK = SQUADRE_AWAY.ID_REC
                and IMA_MATCH.ID_ABBONAMENTO_FK = IMA_ABBONAMENTI.ID_REC
                and JPC_LISTINI.ID_STAGIONI_FK = JPC_STAGIONI.ID_REC
                and IMA_ABBONAMENTI.ID_TITOLARE_FK = IMA_TITOLARI.ID_REC
                and IMA_ABBONAMENTI.SETTORE = IMA_SETTORI.CODICE(+)
                and IMA_SETTORI.ID_MACRO_AREA_FK = IMA_MACRO_AREE.ID_REC(+)
            </whereClause>
            <criteria fieldName="TIPO_LISTINO" value="LEG" operator="equals"/>
        </operationBinding>
        <operationBinding operationType="fetch" operationId="fetchMatchPerAbbonamento">
            <selectClause>$defaultSelectClause</selectClause>
            <tableClause>
                $defaultTableClause
                right join IMA_RISERVE on IMA_RISERVE.ID_REC = IMA_MATCH.ID_RISERVA_FK
                right join JPC_LISTINI on JPC_LISTINI.ID_REC = IMA_MATCH.ID_EVENTO_FK
                left join JPC_STAGIONI on JPC_STAGIONI.ID_REC = JPC_LISTINI.ID_STAGIONI_FK
                left join LEG_SQUADRE SQUADRE_HOME on SQUADRE_HOME.ID_REC = JPC_LISTINI.ID_HOME_TEAM_FK
                left join LEG_SQUADRE SQUADRE_AWAY on SQUADRE_AWAY.ID_REC = JPC_LISTINI.ID_AWAY_TEAM_FK
                left join LEG_TIPI_EVENTO on LEG_TIPI_EVENTO.ID_REC = JPC_LISTINI.ID_TIPO_EVENTO_FK
                left join IMA_ABBONAMENTI on IMA_ABBONAMENTI.ID_REC = IMA_MATCH.ID_ABBONAMENTO_FK
                left join IMA_TITOLARI on IMA_TITOLARI.ID_REC = IMA_ABBONAMENTI.ID_TITOLARE_FK
            </tableClause>
            <criteria fieldName="TIPO_LISTINO" value="LEG" operator="equals"/>
        </operationBinding>
        <operationBinding operationType="fetch" operationId="matchesAlreadyPlayed" serverMethod="matchesAlreadyPlayed">
            <serverObject lookupStyle="spring" bean="matchDMI"/>
            <selectClause>$defaultSelectClause</selectClause>
            <tableClause>
                $defaultTableClause
                right join IMA_RISERVE on IMA_RISERVE.ID_REC = IMA_MATCH.ID_RISERVA_FK
                right join JPC_LISTINI on JPC_LISTINI.ID_REC = IMA_MATCH.ID_EVENTO_FK
                left join JPC_STAGIONI on JPC_STAGIONI.ID_REC = JPC_LISTINI.ID_STAGIONI_FK
                left join LEG_SQUADRE SQUADRE_HOME on SQUADRE_HOME.ID_REC = JPC_LISTINI.ID_HOME_TEAM_FK
                left join LEG_SQUADRE SQUADRE_AWAY on SQUADRE_AWAY.ID_REC = JPC_LISTINI.ID_AWAY_TEAM_FK
                left join LEG_TIPI_EVENTO on LEG_TIPI_EVENTO.ID_REC = JPC_LISTINI.ID_TIPO_EVENTO_FK
                left join IMA_ABBONAMENTI on IMA_ABBONAMENTI.ID_REC = IMA_MATCH.ID_ABBONAMENTO_FK
                left join IMA_TITOLARI on IMA_TITOLARI.ID_REC = IMA_ABBONAMENTI.ID_TITOLARE_FK
            </tableClause>
            <whereClause>
                $defaultWhereClause
                and JPC_LISTINI.TIPO_LISTINO = 'LEG'
                and LEG_TIPI_EVENTO.CODICE = 'SA'
                and sysdate <![CDATA[ >= ]]> JPC_LISTINI.DATA_FINE
            </whereClause>
            <orderClause>DATA_FINE_STAGIONE ASC, DATA_INIZIO ASC</orderClause>
            <criteria fieldName="DATA_INIZIO_STAGIONE" operator="lessOrEqual" value="$currentDate"/>
            <criteria fieldName="DATA_FINE_STAGIONE" operator="greaterOrEqual" value="$currentDate"/>
        </operationBinding>
        <operationBinding operationType="fetch" operationId="matchesNotPlayedYet" serverMethod="matchesNotPlayedYet">
            <serverObject lookupStyle="spring" bean="matchDMI"/>
            <selectClause>$defaultSelectClause</selectClause>
            <tableClause>
                $defaultTableClause
                right join IMA_RISERVE on IMA_RISERVE.ID_REC = IMA_MATCH.ID_RISERVA_FK
                right join JPC_LISTINI on JPC_LISTINI.ID_REC = IMA_MATCH.ID_EVENTO_FK
                left join JPC_STAGIONI on JPC_STAGIONI.ID_REC = JPC_LISTINI.ID_STAGIONI_FK
                left join LEG_SQUADRE SQUADRE_HOME on SQUADRE_HOME.ID_REC = JPC_LISTINI.ID_HOME_TEAM_FK
                left join LEG_SQUADRE SQUADRE_AWAY on SQUADRE_AWAY.ID_REC = JPC_LISTINI.ID_AWAY_TEAM_FK
                left join LEG_TIPI_EVENTO on LEG_TIPI_EVENTO.ID_REC = JPC_LISTINI.ID_TIPO_EVENTO_FK
                left join IMA_ABBONAMENTI on IMA_ABBONAMENTI.ID_REC = IMA_MATCH.ID_ABBONAMENTO_FK
                left join IMA_TITOLARI on IMA_TITOLARI.ID_REC = IMA_ABBONAMENTI.ID_TITOLARE_FK
            </tableClause>
            <whereClause>
                $defaultWhereClause
                and JPC_LISTINI.TIPO_LISTINO = 'LEG'
                and LEG_TIPI_EVENTO.CODICE = 'SA'
                and sysdate <![CDATA[ < ]]> JPC_LISTINI.DATA_FINE
            </whereClause>
            <orderClause>DATA_FINE_STAGIONE ASC, DATA_INIZIO ASC</orderClause>
            <criteria fieldName="DATA_INIZIO_STAGIONE" operator="lessOrEqual" value="$currentDate"/>
            <criteria fieldName="DATA_FINE_STAGIONE" operator="greaterOrEqual" value="$currentDate"/>
        </operationBinding>
    </operationBindings>
</DataSource>

**Isomorphic** · 1 Jul 2014, 07:56

Indeed you can't, now it's clear. We'll look into this issue, thank you for sharing it.

In the meantime, here's workaround for you..

Declare ID_STAGIONE_FK field like shown below, this will avoid the conflict with same nativeNames.

Code:

<field name="ID_STAGIONE_FK" type="integer" customSQL="true"/>

Add custom SQL for ID_STAGIONE_FK field via SQL templating, something like this:

Code:

<selectClause>$defaultSelectClause, JPC_STAGIONI.ID_REC as ID_STAGIONE_FK</selectClause>

**claudiobosticco** · 16 Jul 2014, 00:57

Hi, any news about this issue?

**claudiobosticco** · 24 Jul 2014, 09:42

Hi, I've just discovered a regression on an older application, for this same bug. It happens in a function not frequently used, but fundamental.

I've recently migrated that application to 9.1 from 9.0.

Switching back to SmartClient Version: v9.0p_2014-05-20/PowerEdition Deployment (built 2014-05-20)
fixes the issue.

Could you please fix it for 9.1?
Other than that I hope I won't find regressions in other applications, that application had required some fix in our code to migrate to 9.1, and so I'm not much confident in migrating it to 10.0 for now.

**claudiobosticco** · 6 Aug 2014, 05:31

Hi, just discovered this regression on another application....any ETA ?

**claudiobosticco** · 9 Oct 2014, 05:01

Hello, please let me know if you'll fix that. If yes, any ETA?

**Isomorphic** · 23 Oct 2014, 01:06

This is fixed for 10.1d version only and won't be backported as a patch because it introduces too much instability risk.

So, you may use work around suggested earlier in this thread or consider trying 10.1d version.

Announcement

[bug] strange SQLDataSource error on add operation with DataSourceField.tableName

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment