Insert record to SQL database without specifying ID.

My issue is that I have an SQL database with a table "Commission". The Commission table has a CommissionID column that is the primary key and generated by the SQL database, and I want to insert a record using a RESTful service call.

The Commission entity is as such, mirroring the Commission table:
When I try to insert a record, the CreateCommission entity action asks for a CommissionID. If I set an explicity value (get the max CommissionID and increment) it does not work. When I enter "NullIdentifier()" for the CommissionID, it still does not work. In both cases, the error returned is states that I am not allowed to insert an explicit value into an Identity column, which I know.

The CommissionID "Is Autogenerated" is set to No.

So, I want to know how to insert into this table, without passing a CommissionID, so that it may be handled by the SQL database. Do I need to use advanced SQL?

I previously posted this thread, but I'm worried I didn't adequately describe my problem.
https://www.outsystems.com/forums/discussion/51301/create-rest-api-for-create-external-database-generates-id/ If a moderator could please delete that thread, I would appreciate it. Thank you. I apoligize for double posting - I'm just certain there MUST be a solution to this as it couldn't possibly be an uncommon issue.

Thanks very much.


Hi,

Create a comission variable assign it the values you want and don't assign anything to the ID. Use that variable has the createComission action.

Regards,

Marcelo

Marcelo Ferreira wrote:

Hi,

Create a comission variable assign it the values you want and don't assign anything to the ID. Use that variable has the createComission action.

Regards,

Marcelo

Thank you very much for your reply!

I gave it a try:


I still receive the error: "Cannot insert explicit value for identity column in table 'Commission' when IDENTITY_INSERT is set to OFF."


Solution

Hi Landon,


I'm not sure I'm reading this right, but it sounds like the commission table in the database pre-existed your OS entity, or at least did not get created by the Outsystems platform, and your Comission entity is an integration with this external table ?  

If not, can you explain how you managed to create this table, since the only 2 standard flavors I've seen so far for OS platform generated tables is either the id gets an autonumber by the OS platform or the id gets set either by user or logic before calling the Create action.  I haven't come across this third flavor where the Id gets set by the DBMS so far (but I'm new to OS, so still learning...)

If we are indeed talking about an integration, then perhaps trying to use the OS generated DB Actions is not the way to go, and maybe you could try by using the SQL widget to create a record ??


Just a tought, not sure if this is best practice or applicable in your case

Dorine

Solution

Hi Landon,

First you say that the CommissionID is generated by SQL database (for me this means is auto number) but than you also say that auto generated is set to No. What is auto generated?

the CommissionID should be Is AutoNumber.

Regards,

Marcelo

Dorine Boudry wrote:

Hi Landon,


I'm not sure I'm reading this right, but it sounds like the commission table in the database pre-existed your OS entity, or at least did not get created by the Outsystems platform, and your Comission entity is an integration with this external table ?  

If not, can you explain how you managed to create this table, since the only 2 standard flavors I've seen so far for OS platform generated tables is either the id gets an autonumber by the OS platform or the id gets set either by user or logic before calling the Create action.  I haven't come across this third flavor where the Id gets set by the DBMS so far (but I'm new to OS, so still learning...)

If we are indeed talking about an integration, then perhaps trying to use the OS generated DB Actions is not the way to go, and maybe you could try by using the SQL widget to create a record ??


Just a tought, not sure if this is best practice or applicable in your case

Dorine

Thank you Dorine, I actually decided to try using the SQL widget before reading your response and it's working like a charm.

For anyone else who has this issue, working with an external database table that has an identity column, you will need to use the SQL tool to toggle "IDENTITY_INSERT".

My query looks something like this:

SET IDENTITY_INSERT {Commission} ON
INSERT INTO {Commission} ({Commission}.[FKBrokerID], {Commission}.[FKPolicyProd], {Commission}.[CommissionID], {Commission}.[Commission], {Commission}.[Sale], {Commission}.[EntryDate], {Commission}.[StatusDate], {Commission}.[CashDate], {Commission}.[BatchNo], {Commission}.[FKReferenceID], {Commission}.[SaleType])
VALUES (x, x, x, x, x, x, x, x, x, x, x)
SET IDENTITY_INSERT {Commission} OFF;