How to perform CRUD actions on external table with composite keys

How to perform CRUD actions on external table with composite keys

  

Hello

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.)

Thanks. 

Regards, Ellen Visser

Hi Ellen,

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

Martijn Habraken wrote:

Hi Ellen,

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:

Regards, Ellen


Hi Ellen,

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:

Kind regards,
Martijn Habraken

Hi Martijn

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:

 

Thanks.

Regards, Ellen

Hi Ellen,

Could you do something like this and tell me if you still get an error:

  1. Create a Button/Link linked to a Screen Action
  2. Inside this Screen Action use an Aggregate that fetches a record from the blIECM10_Beheer Etity
  3. Add an assign after the Aggregates that changes a simple attribute (Like the "omschrijving") of the current record of the Aggregate
  4. After the assign use the UpdatebIECM10_Beheer Entity Action that tries to update the current record of the Aggregate you just changes.

Kind regards,
Martijn Habraken

Hi Martijn

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.

Regards, Ellen

Solution

Hi Ellen,

1) Can you verify if the table "ECM10_Beheer" have composite key in outsystems with indexes set with par of those two fileds (ECM10_BriefSleutel, ECM10_Label), and Unique is Yes.

2) The error causing because the data you try to insert is already exists with par,

3) For Updating Entity with composite key, you have to use the advancequery with clear SQL statements, with where conditions of both keys.

4) For getdata you need to use Aggregates/AdvanceQuery not Get<Entity>action, because for getting unique record you need clear where condition with both keys specified.


Thanks,

Balu

Solution