External DB Extension - actions missing for table

I just created a db connection to an external db and an extension that uses that connection. I added the extension to my application, and I can see the db just fine. However, only the 'Create' action is available for the table. How do I make the 'Update', 'CreateOrUpdate', and 'Delete' actions available for this table as well? I suspect I missed something when setting up my extension, but haven't been able to figure it out.

Hi Ryan,

Could be that the credentials you use do not have all the DB permissions you need.

Regards,

Daniel

The credentials I am using have the SELECT and UPDATE permissions for the table. 

I would expect the Get and Update actions to be available, but not the Create, CreateOrUpdate, or Delete actions.

Instead, I only have the Create action available to me. Can you comment on this? 

Hi Ryan,

Strange indeed, Does your external table have a unique primary key?

And did you set the correct column as the Identifier in Integration Studio?

Regards,

Daniel

The table has 3 columns (application, paramname, and paramvalue) and a 2-part primary key (application and paramname). The table doesn't have a single column that can be used as an identifier, so I didn't set a column as an identifier. 

UPDATE:

In the extension I tried setting the application column as the identifier, and that exposed 6 actions for the table (Create, CreateOrUpdate, Update, Get, GetForUpdate, and Delete). I tried using the Update action in my application and it compiles fine, but at runtime I get an error that claims a violation of the primary key constraint, saying that I am trying to insert a duplicate key. This seems really weird to me because the Update action shouldn't be creating a new record in the database, it should only be modifying an existing record. Any idea how I can avoid this error?

Solution

OutSystems only supports primary key of one column.

So you need to have a unique primary key of one column in your table to fix the problems you have.

Solution

Ryan Rigby wrote:

The table has 3 columns (application, paramname, and paramvalue) and a 2-part primary key (application and paramname). The table doesn't have a single column that can be used as an identifier, so I didn't set a column as an identifier. 


Indeed, as Daniel mentioned, the composite primary key is not supported. You have to use the one column as PK/identifier.

Ryan, 

As was told you, without a PK available to the OutSystems side, the platform will not provide you with Entity Actions that REQUIRE the PK.

OutSystems do not work with composite PK. So, you have 2 options:

1. In the Integration Studio, you set the in the imported entity structure one of the attributes as the PK. 

2. If you can't do it as no single attribute is a candidate key, then you will have to create your own Update/CreateOrUpdate/Delete server actions using SQL Tool, as you will be able to use UPDATE and DELETE SQL statements directly, defining your own WHERE statements using your entity fields instead of the PK required by the default Entity Actions.

Hope this helps.

Cheers.