Key Fields not automatically recognized when query is not AutoSql

Data Abstract
1

MSSQL 2012
DELPHI Berlin

image
image953×239 42.2 KB

image
image827×105 11.6 KB

image
image1953×738 148 KB

The Key Fields are correctly identified using AutoSQL.
Change the statement type to SQL and use the auto-generated SQL.
Recreate the fields and mappings.
The fields no longer have the Key fields an other attributes correctly identified.

image
image2112×724 129 KB

When the Key Fields are not identified, updates to not work correctly. Now that I know what is going on, I can work around the problem by manually flagging the key fields every time I tweak a query, but I don’t think that should be necessary.

2

Hi,

this is as expected.
when you have AutoSQL mode, we can grab the table metadata from TargetTable directly in DB and detect PK fields.
in case of SQL, we can’t do this and receive metadata that was provided by direct access component library (DAC in short).
in your case, this is ADO

3

Well, the target table property still exists with SQL. The target table property could be used get table metadata just like with autosql and that metadata could be used when the field names in the query match.

This has caused me quite a bit of grief because something is different with the advent of schema modeler 10 and how it generates updates. I have had to fix many broken queries since upgrading to 10, and after beating my head against the wall for an extended period I finally realized what was going on. Something in the way update sql is generated is different, making the marking of Key Fields in the Schema Modeler more critical. I am not saying that the way updates generate now is wrong, but is different, and having the Schema Modeler mark the correct keys by default would be nice.

If the time to implement such a feature makes implementation not feasible, then there should be a strong instruction in the schema modeler notifying the user that updates will not function correctly unless key fields are manually marked when using SQL. This should flash every time fields in the schema modeler are created or recreated. That’s my opinion anyway…

4

Hi,

you can perform select from view but have TargetTable for Insert/Update/Delete commands.

also you can use field aliases in custom SQL, e.g.

select 
   field1 as "field2",  /* this one is PK */
   field2 as "name",
   field3 as "field1"
from table

so we can’t guarantee that 1st field are PK because according to metadata, it will be 3rd field.

5

That’s correct, you could only use the names provided by the query to match. I think that it would be a better starting point. Again, that’s my opinion. Either way, warning the user that they need to verify the Key Fields for updates to function properly would be nice.