How to perform auto increment for the external db table primary key
Hi,
I'm using external database table and I need to get the last inserted row's primary key value and when I insert a new row, I want to perform auto increment for the primary key. How can I achieve this task? Do I have to write custom java function?
Thank you!
Accenture Solutions
IN
Hi Ruwan,
U can achieve it, By writing a logic to increment the primary key in our application before inserting into external DB.
Virtusa
LK
Yeah. To do that I need to access the primary key of the table. How can I access a column of a external db table using a java code? Can you please share a sample java code?
Pegasystems Inc.
IN
Hi Ruwan,
The simplest way in SQL to use sequences to define a column as AUTO_INCREMENT and leave the rest to SQL to take care.
Hope this helps you.
Thanks
Sai Kishore Boyana
Virtusa
LK
Sure. I can make the primary key as auto-increment when creating the table. But in my case primary key is consisted with a letter. As an example there is one record in the table with primary key 'E001'. And when I insert a new record, the new record's primary key should be 'E002'. So how can I do that using a java code in an activity step? Can you please share a sample java code?
Pegasystems Inc.
AU
Hey Ruwan,
Are you expecting updates/inserts in the table outside of Pega?
If you are, you'll need to obtain the highest value prior to persisting as you can't guarantee the consistency of the table within pega without retrieving; even then it's 'best effort' as a transaction may occur between retrieval and persistence, often external DB's will wrap the save code in a stored procecure (or define a database trigger) to do this for exactly this reason.
If a stored procedure is not viable, but you are not the sole updater, I'd recommend as previous commenters that you use an auto-increment column in conjunction with a trigger to populate a Surrogate key column containing the conjunction of your nominated prefix, and the auto-increment value.
If however, Pega is the sole updater of the table, you can utilise the Pega Public API to manage the keys for you; using the Database class in the pega API you can generate a unique id using the getUniqueID method, this method will generate an ID based on prefix and suffix keeping track of the last reserved id in the pc_data_unqueid table.
A function on our current client site wraps this, returning a unique string for a record with the following Java code (prefix being mandatory and suffix optional):
return getTools().getDatabase().getDataUniqueID("", prefix, (suffix == null) ? "" : suffix);
Hey Ruwan,
Are you expecting updates/inserts in the table outside of Pega?
If you are, you'll need to obtain the highest value prior to persisting as you can't guarantee the consistency of the table within pega without retrieving; even then it's 'best effort' as a transaction may occur between retrieval and persistence, often external DB's will wrap the save code in a stored procecure (or define a database trigger) to do this for exactly this reason.
If a stored procedure is not viable, but you are not the sole updater, I'd recommend as previous commenters that you use an auto-increment column in conjunction with a trigger to populate a Surrogate key column containing the conjunction of your nominated prefix, and the auto-increment value.
If however, Pega is the sole updater of the table, you can utilise the Pega Public API to manage the keys for you; using the Database class in the pega API you can generate a unique id using the getUniqueID method, this method will generate an ID based on prefix and suffix keeping track of the last reserved id in the pc_data_unqueid table.
A function on our current client site wraps this, returning a unique string for a record with the following Java code (prefix being mandatory and suffix optional):
return getTools().getDatabase().getDataUniqueID("", prefix, (suffix == null) ? "" : suffix);
I'd encourage wrapping the code in a function as above rather than a direct java step due to both re-use and maintenance considerations.