--- In firebird-support@yahoogroups.com, Pavel Menshchikov
<developer@...> wrote:

>
> Hello,
>
> m> I found SET GENERATOR name TO int statement in firebird help!
> m> So I can select all my generators and in to for loop set all max
> m> values into generators, but how can I read only part of

generator name

> m> taht will represent table name!
>
> m> For example:
> m> Table: Article
> m> Generator: Gen_Article_Id
> m> I try with SUBSTR(Gen_Article_Id FROM 5 FOR ???), everything I

need to

> m> replace ??? with LENGTH(Gen_Article_Id) - 3 but there in no sql
> m> command like this! Do you have a idea how to solve this, it will

be a

> m> complete solution for my work?
>
> You may reverse your point of view: in a stored procedure perform
> -----
> for select g.rdb$generator_name from rdb$generators g
> join rdb$relations r
> on g.rdb$generator_name = 'GEN_' || rtrim

(r.rdb$relation_name) || '_ID'

> where r.rdb$system_flag = 0 and g.rdb$system_flag is null
> into :GName
> begin
> ExecStmt = 'set generator ' || GName || ' to ' || InitValue;
> execute statement ExecStmt;
> end
> -----
> (assuming you pass InitValue as a parameter to the SP, and you

defined

> a UDF rtrim in your DB from standard UDF library [ib_udf] in a way
> like
> DECLARE EXTERNAL FUNCTION RTRIM
> CSTRING(200) /* you may change the max. string length as you need

*/

> RETURNS CSTRING(200)
> ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf'
> ).
>
> Or, probably using some other UDFs you could implement the
> functionality you want with your original logic...
>
>
> --
> Best regards,
> Pavel Menshchikov
> http://www.ls-software.com
>

Vous pouvez modifier directement la valeur du générateur.

CREATE PROCEDURE LIREMAXSEQ (
RETURNS (
SEQMAX INTEGER)
..
DECLARE VARIABLE SEQGEN INTEGER;
..
SELECT MAX(SEQ) FROM CCAGE
INTO :SEQMAX;
IF (:SEQMAX IS NULL) THEN SEQMAX = 0;

SEQGEN = GEN_ID(GEN_SEQCCAGE,0);
IF (:SEQMAX <> :SEQGEN)
THEN SEQMAX = GEN_ID (GEN_SEQCCAGE, :SEQMAX - GEN_ID(GEN_SEQCCAGE,
0));