How to perform CRUD actions on external table with composite keys

In my project I am using an integration to an external database (SQL server Azure).The tables in this external database have composite keys.

How can I accomplish using Get, Update and Delete actions on these tables (in the actual external database)?

I have already assigned the first attribute of the composite key as identifier in Integration Studio. This provides the get, update and delete actions in the extension within Server Studio, but the CRUD actions don't work as they should, because they are using only part of the primary key.

For instance in this table, the composite key consists of the two highlighted fields.The Get action only ever returns the first of the three records, since only the first part of the primary key is used.

Does anyone have a solution to this? (I am not storing the data in Outsystems itself.)

You will need to assign one of the columns as the identifier as you already found. What do you mean by: "The CRUD actions don't work as they should"? Are you able to insert new or update records to this ECM10 table?

You will need to assign one of the columns as the identifier as you already found. What do you mean by: "The CRUD actions don't work as they should"? Are you able to insert new or update records to this ECM10 table?

Kind regards,Martijn Habraken

Hi Martijn

In my example I can never get the second or third row returned by the Get action, because the Get is performed with only the ECM10_BriefSleutel as identifier. So even if I fill in ECM10_Label 004, the record with ECM10_Label 010 is returned, because the Get is performed with only the identifier (ECM10_BriefSleutel) in the filter, which always returns the first row that corresponds with it.

I have tried the Update action. I tried to update the second attribute (ECM10_Omschrijving) in the first row only ECM10_Label 001. I expected it to also update the second and third row, because only ECM10_BriefSleutel is in the identifier and not also ECM10_Label.Instead the update action returned this error, which I don't understand:

Is there a reason why you use the Get action instead of using aggregates? Personally I try to avoid the Get Entity Action because of the errors it could throw. For several customers we solved a lot of these problems by creating a Database View based on the ECM10 table with a single technical column that we use as Identifier. With Instead of Triggers we could catch insert attempts into this view and correctly insert them to the correct database table.

The error you get from the Update statement is a database error. OutSystems wil always offer all attributes to the database for Update because of the Update Behaviour:

Thank you for your answer. Using an aggregate instead of the Get action is indeed the solution.

But I still don't understand why I get a duplicate key error on an update action. Update actions normally only update one (or more) record(s) as far as I know. Why is it trying to insert the record? (resulting in a duplicate key error.)I am probably missing something here because I cannot see how Integration Studio will be doing the actual update.

I am using the blue highlighted action of the table of the xif in Server studio:

I have tried exactly what you suggested, but got the exact same error.

I have figured out how I can do CRUD actions on the external database after all:Not define any identifiers in the extension in Integration Studio. And just use SQL statements for the CRUD actions instead of table actions of the tables of the extension.