Hi,

I have a stored-procedure in Firebird, to generate a primary key : CREATE PROCEDURE GEN_STA_NR
RETURNS (
STA_NR Integer )
AS
begin
STA_NR=gen_id(StaffelNr_GEN, 1);
end^

How do I get the result ?
I made a query with sqldb :
execute procedure GEN_STA_NR returning_values :STA_NR Then I executed that, but that gave an error. After googleing, I modified the query to :
execute procedure GEN_STA_NR returning_values (:STA_NR) Still gave an error when executing the query.
More googleing found a thread (26-3-2009) where someone said to make the stored procedure selectable. How could I make my stored-procedure selectable ?

Thanks for any pointers,

Koenraad Lelong.

Just do a

   SELECT STA_NR FROM GEN_STA_NR;

But for getting the next value of a generator you don't need a stored procedure. Just do a

   SELECT gen_id(StaffelNr_GEN, 1) AS THEID FROM RDB$DATABASE;

Since RDB$DATABASE is guaranteed to have 1 record, this always returns 1 record with the next value of your generator. I've been doing this since years.

Michael.

On 7 March 2011 18:33, <

As an alternative:
Or even easier, generate a GUID in your application, and simply assign it as the primary key to whatever record. ;-) One less query to the database, you know the primary key before the data is saved (great for master-detail records), and it is guaranteed to be unique, even across tables and across databases.

--- El lun 7-mar-11, Koenraad Lelong escribi?:

beside to the answers you already obtained, it seems in order a stored procedure return something you must call SUSPEND for each record.

Jesus Reyes A.

Correct. (well, not 100%, because GUIDs are never guaranteed to be unique, however, the possibility that 2 of them are the same is very small indeed).

A noticeable downside is that indexes are WAY bigger, and the queries joining different tables are slower, and you loose the order of creation of records.

Michael.

Only if you want to return multiple records. For 1 record, the exit puts the data on the return data stream.

Michael.

2011/3/7 :

Correct. (well, not 100%, because GUIDs are never guaranteed to be unique, however, the possibility that 2 of them are the same is very small indeed).

Very small indeed. :)

A noticeable downside is that indexes are WAY bigger, and the queries joining different tables are slower, and you loose the order of creation of records.

* creation order can easily be worked around by saving data with a timestamp or something - that's if creation order is actually important. In our applications a timestamp is suitable enough.

* As for joining tables being slower. This is so, but the margin is so small for day-to-day usage than nobody notices any difference really. We tested our system with between 30-60 concurrent users working on our system for 8 hours a day, on a low spec workstation acting as a database server.

NOTE: there are other pros to using GUID's. So too are there negatives to using DB generate/managed sequential numbers.

The bottom line for us was that the pros far outweighed the cons. Your mileage my obviously vary. I would recommend each company evaluate there own needs. I just thought it might be beneficial to the original poster, to mention an alternative option for DB generated/managed primary keys.

True, but this means an additional field, and an additional index, on top of the 'already-heavy' GUID field and index...

You'll find that as the system grows, the difference becomes significant. One of databases is currently between 4 and 5 Gb. It no longer fits in memory, so disk access becomes an important factor.

NOTE: there are other pros to using GUID's. So too are there negatives to using DB generate/managed sequential numbers.

Definitely.
Replicating for instance is next to impossible with generators. It's much easier with GUIDs.

Absolutely.
Better to make an educated decision than one made in the dark...

Michael.

There are good solutions in the middle.

Generate a guid in your own limited size format. Either on the DB, or on the client, both will work.

The IDs can be unique 32 or 64 bit integers depending on the amount of IDs needed.
In order to be able to generatethem unique and generate them on more than one computer (e.g each client pc can generate them) you need to configure each of the generating computers.
Each of them is given a on byte (or one word) ID of it's own.

Then Each PC just generates numbers in sequential order, to be used as ID. Shift it 8 or 16 bit, so you can put the unique-computer ID in the low byte(s) => unique ID. And at moderate size and cost.

Martin

Op 2011-03-08 01:33, Martin het geskryf:

There are good solutions in the middle.

Indeed, there are many alternative solutions. We simply use GUID's via CreateGUID() and GUIDToString(), then strip off the outer braces and remove the hyphens. This reduces the GUID from 38 characters down to 32 characters. Others only strip off the outer braces. Our ID generator does all this for us, we simply need to request a new object instance.

tiOPF also implements the Ambler high/low method, plus a few others. The Ambler high/low method greatly reduces the amount of server round-trips. eg: a client requests a new ID from the server. A table gets updated with the last used number. The return value is the previous last used number plus one. The result is actually a range of ID's (configurable in size), say the next 30 sequential numbers. So while the client needs to generate ID's, it doesn't need to query the server for at least the next 30 id's.

Depending on how you look at it, this might have some negative points though. This will result in gaps of unused numbers. Data will also not be in a 100% correct creation order etc.

So again, it's up to a company to evaluate what ID generating process works best for them, how portable those methods are between RDBMS etc.

Regards,
- Graeme -