Search This Blog

Friday, January 20, 2012

Auto Increment: Best practice

I came across this function recently and found it quite useful and a best coding practice.

If you have a counter setup and wanted to increment the counter manually in the code, rather than writing a sqlexec increment step you can use this delivered functions: GetNextNumber,GetNextNumberWithGaps and GetNextNumberWithGapsCommit.

Use the GetNextNumber function to increment the value in a record for the field you specify by one and returns that value. You might use this function to increment an employee ID field by one when you are adding a new employee. If the new value generated exceeds max_number, a negative value is returned and the field value isn't incremented.

Use the GetNextNumberWithGaps function to determine the highest value in a table for the field you specify, and return that value plus increment

Use the GetNextNumberWithGapsCommit function to return the sequence number value plus increment for the given field residing in the given record. This function also enables you to specify a SQL Where clause as part of the function for maintaining multiple sequence numbers in a single record.

This function is typically used for obtaining a new sequence number for the application, for example, getting a new Purchase Order number to be used in the application transaction.

Use this function instead of the GetNextNumberWithGaps function. The GetNextNumberWithGaps function is very restrictive in its usage. The GetNextNumberWithGapsCommit function can be used in any event. The sequence number (record.field ) is incremented right away and it doesn't hold any database internal row lock beyond the execution of this function.

No comments:

Post a Comment