Hi Guido,

I get alot of email, not including the mailing lists I am on. Very
often, when I am extreamly busy, I will ignore any emails that arn't
specifically formated to be picked up by certain filters that I use.

Here is some code and a brief explanation of why they are the way they
are.
For the most part, this is a simple extract from a working private
database and depending on your needs, you will alter it as needed.

hope it helps you

Dalton


===============================================

ok, for the table "MYTABLE" I have two procedures, GEN and C (count).
The GEN procedure is the main procedure that every table has and it has
no parameters and it returns the unique ID.

ALTER PROCEDURE "PRC$MYTABLE_GEN"
RETURNS
(
"RETURNVAL" CHAR(31)
)
AS
declare variable varCURR_INDEX INTEGER;
declare variable varRETURNVAL CHAR(31);
declare variable varINT1 INTEGER;
begin
varCURR_INDEX = GEN_ID("GEN$MYTABLE",1);
/** The '1E' that gets passed to the GET_INDEX procedure is this
tables unique ID # **/
execute procedure "PRC$SYS_GET_INDEX"('1E', :varCURR_INDEX)
returning_values :varRETURNVAL;
execute procedure "PRC$MYTABLE_C"(:varRETURNVAL) returning_values
:varInt1;
while (:varINT1<>0) do
begin
varCURR_INDEX = GEN_ID("GEN$MYTABLE",1);
execute procedure "PRC$SYS_GET_INDEX"('1E', :varCURR_INDEX)
returning_values :varRETURNVAL;
execute procedure "PRC$MYTABLE_C"(:varRETURNVAL) returning_values
:varInt1;
end
RETURNVAL=:varRETURNVAL;
end
^
/*********


The FillChar procedure is like a stringstr function in basic, it creates
a string of CH, LEN character long.


****************/

ALTER PROCEDURE "PRC$SYS_FILLCHAR"
(
"CH" CHAR(1),
"LEN" INTEGER
)
RETURNS
(
"RESULT" VARCHAR(255)
)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
if (NOT((:LEN is NULL) or (:LEN < 1))) then
begin
Result = '';
I = 0;
while (I < Len) do
begin
Result = Result || Ch;
I = I + 1;
end
end
else
begin
Result = '';
end
END
^

/**

GET INDEX is passed the unique table name, and the result from the
tables generator. It returns the properly formated ID

**/
ALTER PROCEDURE "PRC$SYS_GET_INDEX"
(
"INTABLE" CHAR(2),
"INNUMBER" INTEGER
)
RETURNS
(
"RESULT" CHAR(31)
)
AS
declare variable varTEMP varchar(254);
declare variable varSERVER char(2);
declare variable varGDB char(2);
declare variable varWORK varchar(40);
begin
execute procedure "PRC$SYS_GET_VAR_STRING"('SERVERNAME')
returning_values :varTEMP;
varSERVER = CAST(:varTEMP as CHAR(2));
execute procedure "PRC$SYS_GET_VAR_STRING"('GDBNAME') returning_values
:varTEMP;
varGDB = CAST(:varTEMP as CHAR(2));
varWORK = :varSERVER||:varGDB||:inTABLE;
Execute procedure "PRC$SYS_PAD_NUMBER"(:varWORK,'0',11,:inNUMBER)
returning_values :varTEMP;
varwork = CAST(:varTEMP as char(31));
result = :varwork ;
end
^


ALTER PROCEDURE "PRC$SYS_GET_VAR_DATE"
(
"INVAR" VARCHAR(254)
)
RETURNS
(
"RESULT" DATE
)
AS
begin
select distinct "VAR_DATE"
from "TBL$SYS_VARIABLES"
where "VARIABLE_NAME" = :inVar
and "DELETED" = 'F'
into :RESULT;
end
^


ALTER PROCEDURE "PRC$SYS_GET_VAR_INTEGER"
(
"INVAR" VARCHAR(254)
)
RETURNS
(
"RESULT" INTEGER
)
AS
begin
select distinct "VAR_INT"
from "TBL$SYS_VARIABLES"
where "VARIABLE_NAME" = :inVar
and "DELETED" = 'F'
into :RESULT;
end
^


ALTER PROCEDURE "PRC$SYS_GET_VAR_NUMBER"
(
"INVAR" VARCHAR(254)
)
RETURNS
(
"RESULT" DOUBLE PRECISION
)
AS
begin
select distinct "VAR_NUMBER"
from "TBL$SYS_VARIABLES"
where "VARIABLE_NAME" = :inVar
and "DELETED" = 'F'
into :RESULT;
end
^


ALTER PROCEDURE "PRC$SYS_GET_VAR_STRING"
(
"INVAR" VARCHAR(254)
)
RETURNS
(
"RESULT" VARCHAR(254)
)
AS
begin
select distinct "VAR_STRING"
from "TBL$SYS_VARIABLES"
where "VARIABLE_NAME" = :inVar
and "DELETED" = 'F'
into :RESULT;
end
^


ALTER PROCEDURE "PRC$SYS_PAD_NUMBER"
(
"APREFIX" VARCHAR(20),
"ALEADINGCHAR" CHAR(1),
"AMAXLEN" INTEGER,
"ANUMBER" INTEGER
)
RETURNS
(
"RESULT" VARCHAR(100)
)
AS
DECLARE VARIABLE I INTEGER;
DECLARE VARIABLE Dummy VARCHAR(100);
DECLARE VARIABLE WorkInvNum CHAR(100);
BEGIN
I = 100-AMaxLen;
EXECUTE PROCEDURE "PRC$SYS_FILLCHAR"('0', I) RETURNING_VALUES Dummy;
result = ANumber;
I = 1;
WHILE(I < 100) DO
BEGIN
WorkInvNum = CAST(Dummy || result AS CHAR(99));
result = ALeadingChar || result;
I = I +1;
WHEN ANY DO
BEGIN
result = APrefix || result;
EXIT;
END
END
END
^


guido.klapperich@... wrote:

>
> Hi Dalton
>
> Some time ago, I sent you a email, that I'm very interested in the code of the SP, that will create unique ID's (as you mentioned in your article),
> but till now no response. Is it possible, that you can send the to me ?
>
> Regards
>
> Guido.
>