Question
Canadian Imperial Bank of Commerce
CA
Last activity: 21 Apr 2022 8:57 EDT
Getting NullPointer exception when trying to get nextval('sequence') using executeRDB
Hi
We have a requirement to get sequence numbers for some datatypes, the numbers should be strictly sequential hence we cannot use GenerateID since we need caching for case types.
We created a database sequence and trying to get the nextval using
tools.getDataBase().executeRDB("Select nextval('Sequence')",Clipboard Page);
However this is always returning null.
The same query works fine with RDB-List written in an activity (we want to avoid activities) and use function that takes the sequence name as a parameter.
***Edited by Moderator Marije to add Capability tags***
Pegasystems Inc.
IN
@AnkujJ44 I remember coming across this issue when this is the first sequence number to be inserted. (i.e. 1). Is this the same in your case, or do you already have an ongoing sequencing and all you want is the next value?
Canadian Imperial Bank of Commerce
CA
@AneeshaG We need a sequence that starts from 1 but does not have any gaps.
The OOTB GenerateID uses caching and everytime server restarts the sequence numbers that were cached are lost leaving gaps. Now, this could be addressed by define the batch size for cache as 1 instead of the default of 1000. However we still need the OOTB GenerateID for casetypes since it performs well.
Thus we were looking to create database sequences to address the strict sequential nature of IDs required for some data instances. So that this design is reusable, we wanted to create a function to which the sequence could be passed. However the executeRDB statement is returning null and we are not able to understand the reason for it.
Pegasystems Inc.
IN
@AnkujJ44 We had a similar requirement to generate the sequence, that can't be accommodated with generateID.
Our approach was to create a new Data-UniqueID Prefix and run a DataPage to GenerateID for this prefix. Since LastReservedID will be stored, we always got the next sequence value.
Updated: 21 Mar 2022 12:45 EDT
Canadian Imperial Bank of Commerce
CA
@AneeshaG thanks, I will definitely try this and update this post with the results, If this works the its even better since we don't need to maintain database sequences
Are you using just lookup in the datapage to fetch lastreservedid. In that case how is lastreservedid incremented
