|Subject||Re: SQL Question|
> 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
> to find the highest numeric value in this field (so I can add a newRob,
> record with a unique numeric value i.e. max + 1).
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
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
DECLARE VARIABLE CLIENTCODE VARCHAR(20);
DECLARE VARIABLE MAXCODE BIGINT;
DECLARE VARIABLE CURRENTCODE BIGINT;
DECLARE VARIABLE STMT VARCHAR(100);
MAXCODE = 0;
WHERE ClientCode < 'A'
CURRENTCODE = CAST(:CLIENTCODE AS BIGINT);
IF (:CURRENTCODE > :MAXCODE) THEN
MAXCODE = :CURRENTCODE;
WHEN ANY DO
-- Could not cast as bigint, so who cares, not a bigint
-- should probably catch the actual exception rather than any
STMT = 'SET GENERATOR GEN_CLIENTCODE TO ' || :MAXCODE;
EXECUTE STATEMENT STMT;
SET TERM ;
EXECUTE PROCEDURE TMP_INITIALISECLIENTCODE;
DROP PROCEDURE TMP_INITIALISECLIENTCODE;
To allocate a new CLIENTCODE, you can now run the following query
SELECT CAST(GEN_ID(GEN_CLIENTCODE,1) AS VARCHAR(20))
or if you change the field to a bigint