I take STRONG exception.

I have tables with membership ID numbers and when I used autoincrment
field, the organization was always complaining
about not having sequentail and realistic membership numbers. Why is Id=
8059 when we only have 537 active members. Then I looked at having a
unique autoincrment and generating sequential Id's. Then after coding
that I realized that the autoincrement was silly and use the Id.
When I need a next member Id I lock the table for an instant while
finding that next number and inserting a record with that as the key
then posting. . Passing the number back allows for the new record to be
located and the update to contnue.
I tested this with 7 instances all calling the SP one after another to
get them all qued up whil the first call was looping then when it released,
all the other just pulled the numbers in sequence. Bee using for years
just fine.

This is a very good good way to generate the record Id's.


Dan Wilson wrote:

>This is a very bad way to generate record ID numbers. The first client to execute this SP will lock the row until it commits, which will block any other clients from getting their ID number. I assume in the 'forever' case, that you have a client just sitting there without committing?
>
>It would be a much better idea to replace this with an sp that uses a generator.
>
>Regards,
>Dan.
>
>
>*********** REPLY SEPARATOR ***********
>
>On 1/14/2004 at 1:19 PM Pablo Lerner wrote:
>
>Hello everyone...
>
> I'm having some troubles with a store procedure. It wasn't written by me so
>I don't realy know what may be the problem (I don't really know how to write
>one but it is MY problem if application doesn't work properly).
>
> Problem is that sometimes when one application client executes the
>procedure, it takes too long to finish its work. Sometimes up to 3 min,
>sometimes forever (you have to reset PC to contonue working). I think it
>happends when two or more clients try to execute it while anotherone's
>execution hasn't finished.
>
> My guess is that there's a transaction problem. But ODBC driver is supposed
>to handle transaction automatically. Is there a problem with the procedure
>I'm missing ?? Is there any sentence missing ?? should I ask for ODBC driver
>support instead ??
>
>Technical Info. ahead....
>
>Server : Firebird 1.02 on Win2000
>Clients :Win98, Visual Fox Pro 7.0 application, connecting via Gemini ODBC
>2.1 driver.
>
>The Store Procedure :
>
>CREATE PROCEDURE INCAN (
> TABLENAME_ CHAR(20) CHARACTER SET NONE,
> INCREMENT INTEGER)
>RETURNS (
> NUMBER_ INTEGER)
>AS
>DECLARE VARIABLE CN INTEGER;
>BEGIN
> SELECT COUNT(*) FROM AUTONUM WHERE TABLENAME = :tablename_ INTO :cn ;
> IF (:cn = 0) THEN
> INSERT INTO AUTONUM (TABLENAME,NUMBER) VALUES(:tablename_,1);
> SELECT NUMBER FROM AUTONUM WHERE TABLENAME = :tablename_ INTO :number_;
> UPDATE AUTONUM SET NUMBER = NUMBER + :INCREMENT WHERE TABLENAME =
>:tablename_;
> SUSPEND;
>END
>
>Table Autonum:
>
>CREATE TABLE AUTONUM (
> TABLENAME VARCHAR(20) CHARACTER SET NONE NOT NULL,
> NUMBER INTEGER,
> AUTO_STAMP TIMESTAMP
>);
>
>
>On client application execution is like:
>
>cTName = "PRV"
>cStatement = "SELECT * FROM INCAN (?cTName,1)"
>nOk = SQLEXEC( nCon, cStatement, '_INCAN' )
>
>
>
>
>
>Yahoo! Groups Links
>
>To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
>To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>
>
>
>
>
>
>Yahoo! Groups Links
>
>To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
>To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>Your use of Yahoo! Groups is subject to:
> http://docs.yahoo.com/info/terms/
>
>
>
>
>