Transactions and Stored Procedures

Hi.

I need to implement a unique number generator (Generators for those familiar with Firebird) in the application I am writing.

For doing so I want to create a table (HF C/S) with 1 line and several numeric columns (1 column per different generator). The idea is to have a stored procedure to serve the requests for unique numbers. It would select the column for reading the current value then would update the column with the value + 1. With this I would guarantee a unique number (for keys, for example) for each request.

Now, my doubt is regarding transactions. I need to guarantee the increment by the stored procedure regardless if the user transaction commits or not. But since HF allows only 1 transaction I just cannot have the stored procedure to run in a different one.

Are stored procedures transactionless ? Is it possible to implement the idea above ?

Thanks.

_______
Marco

Hi,

the best way to create unique numbers for these things is to use Guenters superb GETGUID() procedure.

I´m in hurry, so I can´t find the link, but if you search in the forum you will find it.

Searched but could not locate GetGuid :-( .

______
Marco

See my post about the forum search not working.

Hi,

I put it in the Windev Solution section of this forum because the link doesn´t work anymore.

Saw the code but IT IS NOT WHAT I NEED. This is not a sequential and unique number generator for HF/CS.

The way I found to do this is to first get the number in an independent transaction (select > update > commit) then start the other transaction where I will use the number as a key.

Thanks anyway.

_______
Marco

Hi Marco,

GETGUID creates a unique string. The advantage is that you can use this string as a key before you save one or many files. You don´t need any file locking etc.

It´s a combination of time+date+the mac adress of your NIC.

IMHO the best way to create a unique string, but not the only one of corse.

Hi Michael,

GUID - the name says it all: Globally Unique IDentififer

This means, that the number of GUIDs is huge, in fact so huge that - using the correct procedure - you will not generate the same GUID for a second time in your life time ! This is ideal for merging several files from anywhere with the same structure, this key will always stay unique! It's a real alternative to the 'automatic identifier' that you can find in Wx products and where, from time to time, someone crashes with a duplicate key because things don't work as they should.

Of course, there's always a possibility to use pseudo-unique keys for a file:
Generating Machine ID
+ Program instance
+ Date+Time
But beware of resetting / malsetting the date / time of the computer - it will eventually result in a duplicate key ..

Kind regards,
Guenter

Yes, got it, but it is not what I need.

Generators (database wise) works in a client/server environment. It does not matter the client which is requesting a number, it will get the next in sequence and a unique one.

What makes generators cool is the fact that it can serve many requests from different users and at the same time guarantees sequence and uniqueness, because it is generated in the database.

Thanks anyway.

______
Marco