SmartGWT 13.1 Child Record SQL Issue

[aderosso]

Version: Isomorphic SmartClient/SmartGWT Framework (v13.1p_2024-12-04/Enterprise Deployment 2024-12-04) - Initialization Complete

We're in the process of updating our application to the new 13.1 version that just came out. We have come across an issue with SQL INSERT statements from a SQL data source adding extra fields that do not exist in the table.

We have 2 tables, "hardware" and "hardware_attribute". The relationship is 1 to many through a foreign key in the hardware_attribute table. The data sources look like this:

<DataSource ID="HardwareSQL"
            serverType="sql"
            tableName="hardware"
            dropExtraFields="false">
    <fields>
        <field name="hardwareId" nativeName="hardware_id" type="sequence" hidden="true" primaryKey="true"/>

        <field name="attributes" type="HardwareAttributeSQL" multiple="true" customSQL="true" hidden="true"/>
    </fields>
</DataSource>

<DataSource ID="HardwareAttributeSQL"
            serverType="sql"
            tableName="hardware_attribute"
            dropExtraFields="false">
    <fields>
        <field name="hardwareAttributeId" nativeName="hardware_attribute_id" type="sequence" hidden="true" primaryKey="true"/>

        <field name="hardwareId" nativeName="hardware_id" type="integer" hidden="true" foreignKey="HardwareSQL.hardwareId"/>
    </fields>
</DataSource>

We use the "attributes" field to send child records to the server, but handle those updates separately in DMI. Inserts with that field present in the data source work without issue in 13.0.

Now in 13.1, we get a "hardware_attribute_id" field in the SQL insert statement for HardwareSQL. This is the primary key to the child table and obviously not present in the parent. We've found that simply removing the "type" from the field definition makes this work without issue, like this:

<field name="attributes" multiple="true" customSQL="true" hidden="true"/>

I'm assuming this is a bug? Or did something change in 13.1 that we would need to be doing differently?

[Isomorphic]

There is new support for "relation fields", where you can declare a field like you have, and the framework will now have it be a read-write field, which consists of either a list of foreignKey values, or of related records:

https://smartclient.com/smartclient-...ourceRelations

Basically what you've implemented by hand here is now automated. There's also a complete built-in UI for it, so you just declare the field and you've got a fully automated 1-Many or Many-Many editing setup.

Removing the "type" attribute meant that the framework no longer considered it a relation field, so that went back to normal behavior for the field. For now, that workaround is fine.

We haven't actually considered what customSQL="true" should mean on a relation field. Can you show the inbound data you're supplying and what SQL you're seeing? Then we can take a look.

[aderosso]

Here's an example:

2024-12-06 11:58:32.435 INFO com.isomorphic.datasource.DSRequest:207 - - Executing HardwareSQL.add with
criteria: {}
values: {projectId:3148, hdwStdId:-464726941, hdwXrefId:-222807854, hdwLineId:-211937452,
 vendorMaterialId:-4532619, priceBookId:-211558072, hdwTypeId:"HI",
 printDescription:"019OK 8\" x 4-1/2\" S5", printDescriptionWithoutFinish:"019OK 8\" x 4-1/2\" S5",
 orderDescription:"019OK 8\" x 4-1/2\" S5", hdwStdDescription:"Hinge, Olive Knuckle", uom:-388,
 listCost:610.00, cylinders:0, deadlock:false, handed:true, showOpeningSize:false,
 subTypeDescription:"Hinge, Olive Knuckle", framePrep1Id:-13202472, doorPrep1Id:-13202472,
 electric:false, createdBy:"1", createdTime:new Date(1733511511893), modifiedBy:"1",
 modifiedTime:new Date(1733511511893), adjustment1:null, adjustment2:null, sequence:0, hdwLength:null,
 hdwWidth:null, hdwHeight:null, sizeString:"", originalSpecialNetCost:0.00, inactivePrepId:null,
 doorPrep2Id:null, originalSpecialNet:false, framePrep2Id:null}
2024-12-06 11:58:32.574 INFO com.isomorphic.SQL:207 - - Executing SQL query on 'Mysql': INSERT INTO hardware (adjustment1, adjustment2, created_by, created_time, cylinders, deadlock, door_prep1_id, door_prep2_id, electric, frame_prep1_id, frame_prep2_id, handed, hdw_height, hdw_length, hdw_line_id, hdw_std_description, hdw_std_id, hdw_type_id, hdw_width, hdw_xref_id, inactive_prep_id, list_cost, modified_by, modified_time, order_description, original_special_net, original_special_net_cost, price_book_id, print_description, print_description_without_finish, project_id, sequence, show_opening_size, size_string, sub_type_description, uom, vendor_material_id, hardware_id, hardware_attribute_id) VALUES (NULL, NULL, '1', '2024-12-06 11:58:31', 0, 0, -13202472, NULL, 0, -13202472, NULL, 1, NULL, NULL, -211937452, 'Hinge, Olive Knuckle', -464726941, 'HI', NULL, -222807854, NULL, 610.00, '1', '2024-12-06 11:58:31', '019OK 8" x 4-1/2" S5', 0, 0.00, -211558072, '019OK 8" x 4-1/2" S5', '019OK 8" x 4-1/2" S5', 3148, 0, 0, '', 'Hinge, Olive Knuckle', -388, -4532619, NULL, NULL)
2024-12-06 11:58:33.163 DEBUG com.isomorphic.SQL_ERROR:215 - - Failed to execute update: INSERT INTO hardware (adjustment1, adjustment2, created_by, created_time, cylinders, deadlock, door_prep1_id, door_prep2_id, electric, frame_prep1_id, frame_prep2_id, handed, hdw_height, hdw_length, hdw_line_id, hdw_std_description, hdw_std_id, hdw_type_id, hdw_width, hdw_xref_id, inactive_prep_id, list_cost, modified_by, modified_time, order_description, original_special_net, original_special_net_cost, price_book_id, print_description, print_description_without_finish, project_id, sequence, show_opening_size, size_string, sub_type_description, uom, vendor_material_id, hardware_id, hardware_attribute_id) VALUES (NULL, NULL, '1', '2024-12-06 11:58:31', 0, 0, -13202472, NULL, 0, -13202472, NULL, 1, NULL, NULL, -211937452, 'Hinge, Olive Knuckle', -464726941, 'HI', NULL, -222807854, NULL, 610.00, '1', '2024-12-06 11:58:31', '019OK 8" x 4-1/2" S5', 0, 0.00, -211558072, '019OK 8" x 4-1/2" S5', '019OK 8" x 4-1/2" S5', 3148, 0, 0, '', 'Hinge, Olive Knuckle', -388, -4532619, NULL, NULL), SQL error: Unknown column 'hardware_attribute_id' in 'field list'

[Isomorphic]

OK. We plan to do two things here, let us know if this isn't right:

1) make it so that customSQL on a relation field turns off the SQL generation

2) give you a way to turn off the other "relation" behaviors by setting relation="false" on the field. This is needed because otherwise you might see the framework automatically do the related inserts that your custom code is meant to do, so you'd get duplicates!

[aderosso]

Perfect! Both sound good.

[Isomorphic]

Based on the information provided, we are unable to reproduce the SQL issue locally. To address the problem effectively as discussed, or to explore an alternative solution if the issue arises from a different cause, we need to observe it in action. Could you please share complete details about all the components involved? Ideally, a standalone use case with the minimal setup required to reproduce the issue would be very helpful. Thank you.

[aderosso]

I'm unable to reproduce this in a smaller example. I've been trying to step through this in a debugger. It looks like the datasource is picking up the related field as a "sequence" field in 13.1.

Here's some screenshots from 13.1:





The same code and datasources running on 13.0:





Is there something in the SQLDataSource.getField2ColumnMap() that could ignore looking up the field in the related data source if customSQL=true?