SQLDataSource tries to update localeFloat field with a string value

[claudiobosticco]

SmartClient Version: v10.0p_2014-12-17/EVAL Deployment (expires 2015.02.15_07.40.12) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Hello, I've got a DataSourceField defined like this:

<field sqlType="decimal" name="BUDGET_FATTURATO" type="localeFloat" format=",##0.00 €"></field>

When I edit this field on a grid, in the sql update operation the value is quoted, and oracle raises an error:

2014-12-23 16:35:42,874 DEBUG RPCManager Request #1 (DSRequest) payload: {
    criteria:{
        ID_REC:847087
    },
    values:{
        ID_REC:847087,
        BUDGET_FATTURATO:123654.36
    },
    operationConfig:{
        dataSource:"JPC_REPORT_BUDGET",
        operationType:"update",
        textMatchStyle:"exact"
    },
    componentId:"gestioneReportBudgetGrid",
    appID:"builtinApplication",
    operation:"JPC_REPORT_BUDGET_update",
    oldValues:{
        SETTORE_2:"126",
        SETTORE:"102",
        DESCRIZIONE_SETTORE:"Tribuna G.Boniperti",
        ID_REC:847087,
        CREATOR:"cbosticco",
        STAGIONE:"2014/2015",
        ID_LISTINO_FK:579087,
        TIPO_LISTINO:"JPC",
        BUDGET_POSTI:0,
        DESCRIZIONE_SETTORE_2:"Tribuna G.Boniperti",
        ID_SETTORE_2_FK:579130,
        BUDGET_FATTURATO:0,
        CREATOR_TIMESTAMP:new Date(1419348314000),
        ID_STAGIONE_FK:579086,
        LISTINO:"Listino JPC 2014/2015",
        MODIFIER_TIMESTAMP:new Date(1419348314000),
        MODIFIER:"cbosticco",
        ID_SETTORE_FK:579113
    }
} 
2014-12-23 16:35:42,874 INFO  IDACall Performing 1 operation(s) 
2014-12-23 16:35:42,874 DEBUG DeclarativeSecurity Processing security checks for DataSource null, field null 
2014-12-23 16:35:42,874 DEBUG DeclarativeSecurity DataSource JPC_REPORT_BUDGET is not in the pre-checked list, processing... 
2014-12-23 16:35:42,874 DEBUG AppBase [builtinApplication.JPC_REPORT_BUDGET_update] No userTypes defined, allowing anyone access to all operations for this application 
2014-12-23 16:35:42,874 DEBUG AppBase [builtinApplication.JPC_REPORT_BUDGET_update] No public zero-argument method named '_JPC_REPORT_BUDGET_update' found, performing generic datasource operation 
2014-12-23 16:35:42,874 DEBUG BasicDataSource [builtinApplication.JPC_REPORT_BUDGET_update] Validating 1 'JPC_REPORT_BUDGET's at path '' 
2014-12-23 16:35:42,874 DEBUG BasicDataSource [builtinApplication.JPC_REPORT_BUDGET_update] Validating a 'JPC_REPORT_BUDGET' at path '' 
2014-12-23 16:35:42,875 DEBUG BasicDataSource [builtinApplication.JPC_REPORT_BUDGET_update] for field: MODIFIERadding automatically generated lengthRange validator for length:200 
2014-12-23 16:35:42,875 DEBUG BasicDataSource [builtinApplication.JPC_REPORT_BUDGET_update] Done validating 1 'JPC_REPORT_BUDGET's at path '': 1ms (avg 1) 
2014-12-23 16:35:42,875 DEBUG DataSource [builtinApplication.JPC_REPORT_BUDGET_update] post-validation valueSet: [
    {
        ID_REC:847087,
        BUDGET_FATTURATO:123654.36,
        MODIFIER:"cbosticco",
        MODIFIER_TIMESTAMP:new Date(1419348942874)
    }
] 
2014-12-23 16:35:42,875 INFO  SQLDataSource [builtinApplication.JPC_REPORT_BUDGET_update] Performing update operation with
	criteria: {ID_REC:847087}	values: {ID_REC:847087,BUDGET_FATTURATO:123654.36,MODIFIER:"cbosticco",MODIFIER_TIMESTAMP:new Date(1419348942874)} 
2014-12-23 16:35:42,880 DEBUG PoolableSQLConnectionFactory [builtinApplication.JPC_REPORT_BUDGET_update] makeObject() created an unpooled Connection '1038709505' 
2014-12-23 16:35:42,880 DEBUG SQLConnectionManager [builtinApplication.JPC_REPORT_BUDGET_update] Borrowed connection '1038709505' 
2014-12-23 16:35:42,880 DEBUG SQLTransaction [builtinApplication.JPC_REPORT_BUDGET_update] Started new dbJpcEP transaction "1038709505" 
2014-12-23 16:35:42,880 DEBUG SQLDriver [builtinApplication.JPC_REPORT_BUDGET_update] About to execute SQL update in 'dbJpcEP' using connection'1038709505' 
2014-12-23 16:35:42,880 INFO  SQLDriver [builtinApplication.JPC_REPORT_BUDGET_update] Executing SQL update on 'dbJpcEP': UPDATE DBSALES.JPC_REPORT_BUDGET SET BUDGET_FATTURATO='123654.36', MODIFIER='cbosticco', MODIFIER_TIMESTAMP=TO_DATE('2014-12-23 16:35:42','YYYY-MM-DD HH24:MI:SS') WHERE (JPC_REPORT_BUDGET.ID_REC=847087) 
2014-12-23 16:35:42,888 DEBUG SQLDriver [builtinApplication.JPC_REPORT_BUDGET_update] FAILED to execute SQL update in 'dbJpcEP' using connection'1038709505' 
2014-12-23 16:35:42,888 DEBUG DSRequest freeOnExecute is false for request of type update on DataSource JPC_REPORT_BUDGET - not freeing resources! 
2014-12-23 16:35:42,888 WARN  RequestContext dsRequest.execute() failed:  
java.sql.SQLSyntaxErrorException: ORA-01722: numero non valido

the table column is defined as:

BUDGET_FATTURATO  NUMBER  DEFAULT 0 NOT NULL

[Isomorphic]

This is fixed now, you can download latest nightly build and try it out.

[claudiobosticco]

SmartClient Version: v10.0p_2015-01-07/EVAL Development Only (expires 2015.03.08_05.49.21) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

Thanks, it's fixed.
But I'm not sure if there is a usability problem.
When, in my grid, I have a field which has thousands separators, if I edit this field, the separators remains, so I have either to delete them or to have them valid, otherwise I get a (client) validation error.
IE if I edit "4,018,000.56" in "4,018,00.56" it's an error.
I wonder if this is the best UI for users...what do you think? Wouldn't it be better to remove thousands separators when editing?
Or (more complex) to arrange them automatically when typing?

[claudiobosticco]

I've just found another bug. Please modify the #editByRow sample to show the 'gdp' field, modified in the dataSource like this:

        <field name="gdp" type="localeFloat" title="GDP"/>

and use locale=it (decimal symbol = ',')

If I edit a 'gdp' field and type
15.9
it gives me a validation error.
But if I change the value and type
15,9
when I exit editing the value changes back to 15.9 and continue to give the validation error.

with locale=en it seems to work correctly.

[Isomorphic]

Both issues are fixed for builds dated January 14 and later.

When you enter edit-mode, the edit-value will show the localized decimalSymbol as appropriate, but it will not show any groupingSymbols.

When entering your value, if you choose to enter groupingSymbols then those will be correctly interpreted. If not, they will be added on blur().

If you were to enter "5,5" with the Italian locale, that's correctly interpreted as 5 and a half. If you enter "5.5" with the Italian locale, that will fail to be interpreted, because "." is a thousands-separator (groupingSymbol).

[claudiobosticco]

SmartClient Version: v10.0p_2015-01-14/EVAL Development Only (expires 2015.03.15_10.24.32) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

I'm impressed by the new behavior, very good!

but the bug I've described is still there. To make a test case, I've modified the countrySQLDS.ds.xml file under SmartClient_v100p_2015-01-14_Evaluation/smartclientSDK/isomorphic/system/reference/inlineExamples/grids/ds
like this:

<DataSource
    ID="countryDS"
    serverType="sql"
    recordName="country"
    testFileName="/examples/shared/ds/test_data/country.data.xml"
>
    <fields>
        <field name="pk"            type="integer"    hidden="true"            primaryKey="true" />
        <field name="countryCode"   type="text"       title="Code"             required="true"   />
        <field name="countryName"   type="text"       title="Country"          required="true"   />
        <field name="capital"       type="text"       title="Capital"          />
        <field name="government"    type="text"       title="Government"       length="500"      />
        <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="member_g8"     type="boolean"    title="G8"               />
        <field name="independence"  type="date"       title="Nationhood"          />
        <field name="area"          type="float"      title="Area (km&amp;sup2;)" format=",0" />
        <field name="population"    type="integer"    title="Population"          format=",0" />
        <field name="gdp"           type="localeFloat"      title="GDP"               />
        <field name="article"       type="link"       title="Info"                detail="true"    />
    </fields>
</DataSource>

then I'm using the editByRow sample with locale=it
and the js modified like this:

isc.ListGrid.create({
    ID: "countryList",
    width:550, height:224, alternateRecordStyles:true, cellHeight:22,
    // use server-side dataSource so edits are retained across page transitions
    dataSource: countryDS,
    // display a subset of fields from the datasource
    fields:[
        {name:"countryCode", title:"Flag", width:40, type:"image", imageURLPrefix:"flags/16/", imageURLSuffix:".png", canEdit:false},
        {name:"countryName"},
        {name:"continent"},
        {name:"member_g8"},
        {name:"population"},
        {name:"gdp"}
    ],
    autoFetchData: true,
    canEdit: true,
    editEvent: "click"
})

then I double-click the first row and enter '15.9' in the GDP, press enter, and it gives the (right) validation error.
But if I double-click again, correct the dot in comma, and press enter, the value changes back to 15.9 and continue to give the validation error.

[Isomorphic]

Ok, we do see this issue - the problem is that, whilst the first value you enter, 15.9, is invalid for your locale, it is a valid actual float value, and it's equal to the second value, once we correctly convert it into a real float value for comparison - invalid 15.9 compared with valid (but converted) 15.9.

We're looking at how best to deal with this and will update here when we have more information.

[Isomorphic]

This is now fixed - you can retest with a build dated January 17 or later.

[claudiobosticco]

SmartClient Version: v10.0p_2015-01-19/EVAL Deployment (expires 2015.03.20_05.50.13) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

verified, thank you very much.

[Isomorphic]

We're not sure what year your 6/20 build is from, but you can see the dates of the various fixes above.

As far as how to correct it if it is *not* fixed by updating to the latest patched build, we don't have enough information from you to be able to figure out if what you are experiencing is the same problem, or a different one.