Solved

Best way to get a unique key field value using PowerBuilder and SQL Server?

Posted on 2015-02-12
7
210 Views
Last Modified: 2015-02-23
The program that I have created using PowerBuilder has a database table with a unique key field.  It is not an identity field, but just an integer field.  I have always used a system to get a new unique value of having a separate table that holds the next number to be used, and then updates that table when the next value is needed from it.  This has worked well up until now.  There are several users of the program and they are entering data quickly.  More than one user is trying to grab a unique value at the same time and in some cases they get the same value, causing a database error when trying to save the data (problem with the primary key).  
At a job a long time ago I seem to recall using a SQL Server stored procedure maybe to grab that next value, because maybe it locked the table, or somehow provided a more reliable way of insuring the value is unique.  Anyhow, I can't find that code and am not sure how to approach it.  Does that process sound familiar to anyone?
Or, what method do you recommend to solve this issue?
0
Comment
Question by:lwillford
7 Comments
 
LVL 3

Accepted Solution

by:
kotukunui earned 350 total points
ID: 40606589
The best solution is to use SQL Server's "identity" feature.

If that is completely out of the question then maintaining your own sequence table can be made to work. Just remember to enclose the whole process in a database transaction to make sure the table is locked to other users until you get your value. Maybe encapsulate it into a stored procedure:

CREATE PROCEDURE [get_next_value] 
	@NewKeyValue INT OUTPUT 
AS
BEGIN

    BEGIN TRANSACTION

		SELECT @NewKeyValue = MAX(key_value) + 1 
		FROM sequence_table

		UPDATE sequence_table
		SET key_value = @NewKeyValue

	COMMIT TRANSACTION

	RETURN 
END

Open in new window


Put in a bit of error handling code just to make sure it all goes smoothly and the job is done.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40613414
If you are using at least SQL Server 2012 ou may want to look into using the Sequence object.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40613710
Yes, Sequence is a very good idea.
How to create it:
CREATE SEQUENCE dbo.MySeqName
    START WITH 1
    INCREMENT BY 1

Open in new window


And how to increment:
SELECT NEXT VALUE FOR dbo.MySeqName

Open in new window


And how to verify the properties of a sequence:
SELECT * FROM sys.sequences WHERE name = 'MySeqName'

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:lwillford
ID: 40614904
All - we are still rocking it old school here with SQL Server 2005, so Sequence would not work.  Good to know about for future work though.

kotukunui - thanks for the info.  I believe that is exactly what I used to use.  My original code is escaping me now - probably lost during job changes!  I did create a stored procedure and I can execute it successfully.  Refresh my memory on one more point... does encapsulating the process in a stored procedure put a lock on the table?    
Just wondering if there is anything else I need to do to ensure a unique value other than making the stored procedure call.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40616017
does encapsulating the process in a stored procedure put a lock on the table?    
No.

I would use a solution with a simple UPDATE so it will lock the row:
UPDATE sequence_table 	SET key_value = key_value+1

Open in new window

0
 

Author Comment

by:lwillford
ID: 40625889
Vitor - So if my stored procedure in SQL Server contains the update, I will not need to do any additional locking on the table or worry about two people getting the same value from it?
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
ID: 40625921
UPDATE command already locks the records that are being changed so no one can update the same record at same time.
Also, during an update no one are able to read the records being changed unless they use locking hints but then they will read the old values and not the new ones.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now