Hi.

Yeah I knew that a generator is the way to go. It is for an import
program and I thought I could get away with it as it should be using a
unique prefix. However I think you are right, my original design is a
bit 'dodgey'. I will look at a more elegant way of handling it. Thanks
to all for there feedback.

Rob Martin
Software Engineer

phone +64 03 377 0495
fax +64 03 377 0496
web www.chreos.com

Wild Software Ltd



Adam wrote:

>>Hi
>>
>>I have a table with a VarChar(20) field. It is commonly used for
>>storing numbers but (obviously) can also be used to store Chars. I
>>
>>
>want
>
>
>>to find the highest numeric value in this field (so I can add a new
>>record with a unique numeric value i.e. max + 1).
>>
>>
>
>Rob,
>
>Selecting a client code by looking for max + 1 is really not a good
>idea for a number of reasons.
>
>1. It is a slow operation, especially if there is no descending index
>available.
>2. It is not multi-transaction safe. If another user has already
>inserted something with this client code, but has not yet commit then
>you will not know this.
>
>Firebird provides generators to create unique bigints. Personally I
>would use a bigint field rather than a varchar to store it, but if
>that is too much hassle, the varchar is large enough that you
>probably will never roll over.
>
>Note that the following code is just a suggestion, I haven't tested
>it, but it will fix the way it is allocated and give a performance
>increase to boot.
>
>CREATE GENERATOR GEN_CLIENTCODE;
>
>SET TERM ^ ;
>
>CREATE PROCEDURE TMP_INITIALISECLIENTCODE
>AS
>DECLARE VARIABLE CLIENTCODE VARCHAR(20);
>DECLARE VARIABLE MAXCODE BIGINT;
>DECLARE VARIABLE CURRENTCODE BIGINT;
>DECLARE VARIABLE STMT VARCHAR(100);
>BEGIN
> MAXCODE = 0;
> FOR
> SELECT ClientCode
> FROM Client
> WHERE ClientCode < 'A'
> INTO :CLIENTCODE
> DO
> BEGIN
> BEGIN
> CURRENTCODE = CAST(:CLIENTCODE AS BIGINT);
> IF (:CURRENTCODE > :MAXCODE) THEN
> BEGIN
> MAXCODE = :CURRENTCODE;
> END
>
> WHEN ANY DO
> BEGIN
> -- Could not cast as bigint, so who cares, not a bigint
> -- should probably catch the actual exception rather than any
> END
> END
>
> STMT = 'SET GENERATOR GEN_CLIENTCODE TO ' || :MAXCODE;
> EXECUTE STATEMENT STMT;
>END
>^
>
>SET TERM ;
>^
>
>COMMIT WORK;
>
>EXECUTE PROCEDURE TMP_INITIALISECLIENTCODE;
>
>COMMIT WORK;
>
>DROP PROCEDURE TMP_INITIALISECLIENTCODE;
>
>COMMIT WORK;
>
>To allocate a new CLIENTCODE, you can now run the following query
>
>SELECT CAST(GEN_ID(GEN_CLIENTCODE,1) AS VARCHAR(20))
>FROM RDB$DATABASE
>
>or if you change the field to a bigint
>
>SELECT GEN_ID(GEN_CLIENTCODE,1)
>FROM RDB$DATABASE
>
>Adam
>
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>