Posted: 28 Jul 2017 1:53 EDT Last activity: 7 Aug 2017 10:49 EDT
How to perform auto increment for the external db table primary key
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?
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?
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):