[10.0 bug] customFields not working with more than one field

[claudiobosticco]

SmartClient Version: v10.0p_2014-10-23/EVAL Deployment (expires 2014.12.22_09.35.28) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

verified, thank you very much

[Isomorphic]

Thank you for the information, this is now also fixed and will be available in today's (2014-10-23) nightly build.

[claudiobosticco]

SmartClient Version: v10.0p_2014-10-18/EVAL Deployment (expires 2014.12.17_14.26.18) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Hello, I've just noticed that there's the same problem with customValueFields.

So

customValueFields="field1,field2"

doesn't work

instead

<customValueFields>
  <customValueField>field1</customValueField>
  <customValueField>field2</customValueField>
</customValueFields>

is working

[claudiobosticco]

SmartClient Version: v10.0p_2014-10-06/EVAL Deployment (expires 2014.12.05_04.48.46) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

verified, thank you very much.

[Isomorphic]

Regardless syntax in 10.0 - we fixed this issue, it will be available in the next (2014-10-03) nightly build. Both syntaxes should work.

[claudiobosticco]

and actually I'm a bit worried in reading this:

we simply don't generate SQL for a field if no data value is submitted for that field.

The doc says:

customFields:
Indicates that the listed fields should be included in the default selectClause and whereClause generated for this operationBinding, even if they are marked customSQL="true".

So I understand that the fields should be included in selectClause and whereClause for a fetch operation.
While I found normal that they won't be included in the whereClause if there aren't values for them in criteria, I don't understand why they won't be included in selectClause.
I thought that customSQL=true excluded a field from the $defaultSelectClause, and that customFields could be used to re-include them.

[claudiobosticco]

Actually I think that my more common use case fits the situation described in the documentation for customSQL:

Use customSQL in situations like:

there are multiple variations of the "fetch" operation with different operationIds, and the field is only used in some of them; 
in that case, consider using OperationBinding.customFields to selectively re-introduce SQL generation for the field only in operations where it's used.

while the docs for customFields says:

Indicates that the listed fields should be included in the default selectClause and whereClause generated for this operationBinding, even if they are marked customSQL="true".

So I use customSQL="true" to exclude some fields, and then include them for some specific operation binding, something as:

<DataSource xmlns="http://www.smartclient.com/schema"
            ID="TREE_MENU"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="TREE_MENU"
            dropExtraFields="false"
        >
    <fields> 
		<field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true" sequenceName="SEQUENCE_REC"/>
        <field name="VOCE_MENU" type="text" required="true" length="40" title="Voci di Menù" customSQL="true"/>
        <field name="VOCE_MENU_EN" type="text" required="true" length="40" title="Menu Option Titles" customSQL="true"/>
        <field name="ID_FUNZIONE" type="text" length="40"/>
        <field name="ORDINAMENTO_PER_LIVELLO" type="integer" required="true" canEdit="false"/>
        <field name="ID_VOCE_PADRE" type="integer" hidden="true" foreignKey="TREE_MENU.ID_REC"/>

    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" customFields="VOCE_MENU,VOCE_MENU_EN">
            <selectClause>$defaultSelectClause</selectClause>
            <tableClause>$defaultTableClause</tableClause>
            <whereClause>$defaultWhereClause</whereClause>
        </operationBinding>
    </operationBindings>
</DataSource>

this works in 9.1, while in 10.0 works only if using the sintax:

            <customFields>
                <customField>VOCE_MENU</customField>
                <customField>VOCE_MENU_EN</customField>
            </customFields>

which, unfortunately, I've never used before.

[Isomorphic]

Hmm, well, it's strange that one syntax would work and the other not, so we'll look into that regardless.

However overall we're not clear on your use case for this behavior, can you elaborate with some real-world details (doesn't have to be runnable test code)?

[claudiobosticco]

actually the sintax:

    <operationBindings>
        <operationBinding operationType="fetch">
            <customFields>
            <customField>test_1</customField>
            <customField>test_2</customField>
            </customFields>
            <selectClause>$defaultSelectClause</selectClause>
            <tableClause>$defaultTableClause</tableClause>
            <whereClause>$defaultWhereClause</whereClause>
        </operationBinding>
    </operationBindings>

as you suggested, is working with 10.0, as was the customFields="test_1,test_2" with 9.1, even if neither criteria nor values are passed

To rephrase, there is no data to use to form the SQL query. The log shows that both criteria and values are empty.

Sorry but I don't understand why criteria or values may be necessary to form a selectClause.

As I said, in 9.1 it was working, this is the log for the original test case with SmartClient Version: v9.1p_2014-10-01/PowerEdition Deployment (built 2014-10-01):

2014-10-02 00:03:57,368 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"TREE_MENU",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    appID:"builtinApplication",
    operation:"TREE_MENU_fetch",
    oldValues:null
} 
2014-10-02 00:03:57,368 INFO  IDACall Performing 1 operation(s) 
2014-10-02 00:03:57,370 INFO  SQLDataSource [builtinApplication.TREE_MENU_fetch] Performing fetch operation with
	criteria: {}	values: {} 
2014-10-02 00:03:57,378 INFO  SQLWhereClause [builtinApplication.TREE_MENU_fetch] empty condition 
2014-10-02 00:03:57,378 INFO  SQLDataSource [builtinApplication.TREE_MENU_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause 
2014-10-02 00:03:57,379 INFO  SQLDataSource [builtinApplication.TREE_MENU_fetch] 174: Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, 'test_1' AS test_1, 'test_2' AS test_2 FROM DBJFRAME.TREE_MENU WHERE ('1'='1') 
2014-10-02 00:03:57,382 INFO  SQLDriver [builtinApplication.TREE_MENU_fetch] Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC, 'test_1' AS test_1, 'test_2' AS test_2 FROM DBJFRAME.TREE_MENU WHERE ('1'='1') 
2014-10-02 00:03:59,069 INFO  DSResponse [builtinApplication.TREE_MENU_fetch] DSResponse: List with 340 items

which returns:

[
    {
        affectedRows:0, 
        data:[
            {
                test_1:"test_1", 
                test_2:"test_2", 
                ID_REC:2
            }, 
            {
                test_1:"test_1", 
                test_2:"test_2", 
                ID_REC:4
            }, 
            {
                test_1:"test_1", 
                test_2:"test_2", 
                ID_REC:5
            }, 
........

So, 9.1 was behaving differently.

With your particular .ds.xml definition, it would actually be safe to go ahead and form a SQL query, because you have a customSelectExpression that is hardcoded so that it doesn't depend on the availability of data. But this is not something we can reliably detect, so we simply don't generate SQL for a field if no data value is submitted for that field.
If you have a use case where you want to add SQL that doesn't depend on a data value for a field being available, you should code such SQL directly into one of the clauses, and not place it on a <field> declaration, since it doesn't have anything to with the <field> in this case.

my actual use case is more complicated, actually I found the difference while trying to migrate an application from 9.1 to 10.0.
If you need it, I'll make up a more realistic test case.

If otherwise there's been a change in behavior, is it sufficient to substitute every customFields attribute with multiple fields with repeated customFields tags, or is it a deprecated feature?

[Isomorphic]

To rephrase, there is no data to use to form the SQL query. The log shows that both criteria and values are empty.

With your particular .ds.xml definition, it would actually be safe to go ahead and form a SQL query, because you have a customSelectExpression that is hardcoded so that it doesn't depend on the availability of data. But this is not something we can reliably detect, so we simply don't generate SQL for a field if no data value is submitted for that field.

If you have a use case where you want to add SQL that doesn't depend on a data value for a field being available, you should code such SQL directly into one of the clauses, and not place it on a <field> declaration, since it doesn't have anything to with the <field> in this case.

[claudiobosticco]

Why values? I expect them to be included in the selectClause.

[Isomorphic]

Actually it looks like the problem from this particular log is that no values were passed for either custom field. customFields only takes effect when values are passed.

If you see the same result even if values are actually passed, have you tried the alternative format of repeated <customFields> tags under <operationBinding>?

[claudiobosticco]

SmartClient Version: v10.0p_2014-09-30/EVAL Deployment (expires 2014.11.29_06.38.33) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

When I've got more than one customSQL="true" fields, and I try to list in customFields more than one, they aren't included.
test case:

<DataSource xmlns="http://www.smartclient.com/schema"
            ID="TREE_MENU"
            serverType="sql"
            dbName="dbJFrame"
            schema="DBJFRAME"
            tableName="TREE_MENU"
            dropExtraFields="false"
        >
    <fields> 
		<field sqlType="decimal" primaryKey="true" name="ID_REC" type="sequence" hidden="true" sequenceName="SEQUENCE_REC"/>

        <field name="test_1" type="text" customSelectExpression="'test_1'" customSQL="true"/>
        <field name="test_2" type="text" customSelectExpression="'test_2'" customSQL="true"/>
    </fields>
    <operationBindings>
        <operationBinding operationType="fetch" customFields="test_1,test_2">
            <selectClause>$defaultSelectClause</selectClause>
            <tableClause>$defaultTableClause</tableClause>
            <whereClause>$defaultWhereClause</whereClause>
        </operationBinding>
    </operationBindings>
</DataSource>

TREE_MENU.fetchData() produces this log:

2014-10-01 16:39:14,077 DEBUG RPCManager Processing 1 requests. 
2014-10-01 16:39:14,078 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
    },
    operationConfig:{
        dataSource:"TREE_MENU",
        operationType:"fetch",
        textMatchStyle:"exact"
    },
    appID:"builtinApplication",
    operation:"TREE_MENU_fetch",
    oldValues:null
} 
2014-10-01 16:39:14,078 INFO  IDACall Performing 1 operation(s) 
2014-10-01 16:39:14,079 INFO  SQLDataSource [builtinApplication.TREE_MENU_fetch] Performing fetch operation with
	criteria: {}	values: {} 
2014-10-01 16:39:14,080 INFO  SQLWhereClause [builtinApplication.TREE_MENU_fetch] empty condition 
2014-10-01 16:39:14,080 INFO  SQLDataSource [builtinApplication.TREE_MENU_fetch] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause 
2014-10-01 16:39:14,081 INFO  SQLDataSource [builtinApplication.TREE_MENU_fetch] 264: Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC FROM DBJFRAME.TREE_MENU WHERE ('1'='1') 
2014-10-01 16:39:14,085 INFO  SQLDriver [builtinApplication.TREE_MENU_fetch] Executing SQL query on 'dbJFrame': SELECT TREE_MENU.ID_REC FROM DBJFRAME.TREE_MENU WHERE ('1'='1') 
2014-10-01 16:39:14,139 INFO  DSResponse [builtinApplication.TREE_MENU_fetch] DSResponse: List with 340 items

If I list only one field, it is included.