How to get the External Table Primary Key which is auto generated when inserting a new record in DB with RDB-Save method in Pega
Dears,
I am trying to use below query to insert data to external table. (Pega to MS SQL database)
|
INSERT INTO BKRP.STBL ( MainRequestId, StatusId, AssignmentId, CreationDate, TypeId) VALUES( {.MainRequestId}, {.StatusID}, {.AssignmentId}, {.CreationDate}, {.TypeId} ); COMMIT; |
Above table is configured to generate the primary key of the table (Id) as auto generated.
Using RDB-Save I am able to save the data to the external DB successfully.
But I need to get the auto generated ID (Primary Key) of the particular record real time after insertion.
For that with the same above query I tried adding below statement before and after COMMIT; statement in above query.
select scope_identity();
But still I am not able to get the ID (Primary key of the table which is auto generated in DB side).
Dears,
I am trying to use below query to insert data to external table. (Pega to MS SQL database)
|
INSERT INTO BKRP.STBL ( MainRequestId, StatusId, AssignmentId, CreationDate, TypeId) VALUES( {.MainRequestId}, {.StatusID}, {.AssignmentId}, {.CreationDate}, {.TypeId} ); COMMIT; |
Above table is configured to generate the primary key of the table (Id) as auto generated.
Using RDB-Save I am able to save the data to the external DB successfully.
But I need to get the auto generated ID (Primary Key) of the particular record real time after insertion.
For that with the same above query I tried adding below statement before and after COMMIT; statement in above query.
select scope_identity();
But still I am not able to get the ID (Primary key of the table which is auto generated in DB side).
Is there any way to get the autogenerated ID (Primary Key) from the external table just after executing the insert statement in Pega?
Thanks & Regards,
Surath
Pegasystems Inc.
GB
@SurathM4 please can you take a look at a similar question that was posted on the forum here:
Data integrity during multiple RDB-Save
Pegasystems Inc.
AU
This will depend on what the appropriate instruction is on MS SQL to gather the auto-generated key value.
To be clear: using Pega to directly manipulate data in another database using RDB-* operations has been against Pega's best practice for a long time. As you have found, you have to:
- Code SQL
- Code SQL-specific transaction management
- Use database-vendor-specific commands to solve problems like retrieving auto-generated key values
Mapping the external database table to a Pega class is preferable to the above, but even that does not solve all of these problems.
The recommended pattern to use for solving this sort of problem is have the owner of this database provide a REST API for Pega to interface with for entity manipulation. A REST POST would then more appropriately delegate the responsibility of providing the key of the newly created record on the service provider by requiring it be supplied in the response of the POST request.