Board index » delphi » URGENT HELP REQUIRE - RESETTING A GENERATOR

Anthony

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

URGENT HELP REQUIRE - RESETTING A GENERATOR

Hi all,
I have a problem with a generator which I am trying to reset.  Usually, I
just drop it and recreate it with the correct value.
I have done this through both SQL-Explorer and WISQL and I get the corrct
value when I check it.
However, I have written a liitle app that uses a stored Procedure to
increment the generator and return th value.  This is still showing the
original value (and still going up each time i test) as it was before I
reset it.
I have even dropped the trigger and the Delphi app still returns the
original value!

Help, they've got to do an invoice run later today!

TIA.

Stored Procedure is :-
/*  Procedure INVOICE_NO  */
CREATE PROCEDURE INVOICE_NO  RETURNS (
  INVOICE_NO INTEGER
) AS
    BEGIN
INVOICE_NO = GEN_ID(INVOICE_NO_GEN,1);
END

 
Anthon

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


Aaaaaaaah, I had to drop both the Generator and the Stored Procedure, then
recreate with the correct value.

One to remember I guess!

Quote
Anthony wrote in message <8dmlit$s...@bornews.borland.com>...
>Hi all,
>I have a problem with a generator which I am trying to reset.  Usually, I
>just drop it and recreate it with the correct value.
>I have done this through both SQL-Explorer and WISQL and I get the corrct
>value when I check it.
>However, I have written a liitle app that uses a stored Procedure to
>increment the generator and return th value.  This is still showing the
>original value (and still going up each time i test) as it was before I
>reset it.
>I have even dropped the trigger and the Delphi app still returns the
>original value!

>Help, they've got to do an invoice run later today!

>TIA.

>Stored Procedure is :-
>/*  Procedure INVOICE_NO  */
>CREATE PROCEDURE INVOICE_NO  RETURNS (
>  INVOICE_NO INTEGER
>) AS
>    BEGIN
>INVOICE_NO = GEN_ID(INVOICE_NO_GEN,1);
>END

Craig Stunt

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


Quote
Anthony wrote:

> I have a problem with a generator which I am trying to reset.  Usually, I
> just drop it and recreate it with the correct value.

        There's no need to drop and recreate the generator:

SET GENERATOR WHATEVER_G TO 1000;

        HTH,

        -Craig

--
Craig Stuntz            Vertex Systems Corporation
Senior Developer        http://www.vertexsoftware.com

Henk Nopp

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


Craig Stuntz <cstuntz@no_spam.vertexsoftware.com> wrote in
38FF12E6.54477E9D@no_spam.vertexsoftware.com...

Quote
> Anthony wrote:

> > I have a problem with a generator which I am trying to reset.  Usually,
I
> > just drop it and recreate it with the correct value.

> There's no need to drop and recreate the generator:

> SET GENERATOR WHATEVER_G TO 1000;

can you use the statement above in a stored procedure?

I've tried it but I can't get it right. Below is the source I wrote. the
error I got is:
Token unkown -line 11 char 3 SET

CREATE PROCEDURE New$Relation$ID (RelNrID INTEGER)  RETURNS (
  NewRelationID INTEGER
) AS
  DECLARE VARIABLE MaxRelNr INTEGER;

BEGIN
  MaxRelNr = Gen_Id(TB4$FLd1$GEN,0);

  IF (MaxRelNr < RelNrID) THEN
  BEGIN
    SET GENERATOR TB4$FLd1$GEN to RelNrID;
  END;

  NewRelationID = Gen_Id(TB4$FLd1$GEN,0)

  INSERT INTO TB4 (FLD2) VALUES ("Nieuwe Relatie");

/*   SELECT MAX(FLD1) FROM TB4
  INTO :NewRelationID; */

  SUSPEND;
END;

Henk

Eric

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


Hi,

To reset a generator in a strored procedure use the genid function like that
:

declare variable i integer;

 i = gen_id(G_GENERATOR, gen_id(G_GENERATOR,0)*-1);

Eric

Henk Noppe <h...@onlinebible.org> a crit dans le message :
391a85de@dnews...

Quote

> Craig Stuntz <cstuntz@no_spam.vertexsoftware.com> wrote in
> 38FF12E6.54477E9D@no_spam.vertexsoftware.com...

> > Anthony wrote:

> > > I have a problem with a generator which I am trying to reset.
Usually,
> I
> > > just drop it and recreate it with the correct value.

> > There's no need to drop and recreate the generator:

> > SET GENERATOR WHATEVER_G TO 1000;

> can you use the statement above in a stored procedure?

> I've tried it but I can't get it right. Below is the source I wrote. the
> error I got is:
> Token unkown -line 11 char 3 SET

> CREATE PROCEDURE New$Relation$ID (RelNrID INTEGER)  RETURNS (
>   NewRelationID INTEGER
> ) AS
>   DECLARE VARIABLE MaxRelNr INTEGER;

> BEGIN
>   MaxRelNr = Gen_Id(TB4$FLd1$GEN,0);

>   IF (MaxRelNr < RelNrID) THEN
>   BEGIN
>     SET GENERATOR TB4$FLd1$GEN to RelNrID;
>   END;

>   NewRelationID = Gen_Id(TB4$FLd1$GEN,0)

>   INSERT INTO TB4 (FLD2) VALUES ("Nieuwe Relatie");

> /*   SELECT MAX(FLD1) FROM TB4
>   INTO :NewRelationID; */

>   SUSPEND;
> END;

> Henk

Henk Nopp

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


What I'm trying to do is to set the generator to a value which is not 0 but
eg. 2000001 and when it becomes the year 2001 the generator must be set to
2001001 and at 1-1-2002 at 2002001.

Is there a solution for this?

Henk

Eric <e...@menlog.com> schreef in berichtnieuws
8fe773$n...@bornews.borland.com...

Quote
> Hi,

> To reset a generator in a strored procedure use the genid function like
that
> :

> declare variable i integer;

>  i = gen_id(G_GENERATOR, gen_id(G_GENERATOR,0)*-1);

> Eric

> Henk Noppe <h...@onlinebible.org> a crit dans le message :
> 391a85de@dnews...

> > Craig Stuntz <cstuntz@no_spam.vertexsoftware.com> wrote in
> > 38FF12E6.54477E9D@no_spam.vertexsoftware.com...

> > > Anthony wrote:

> > > > I have a problem with a generator which I am trying to reset.
> Usually,
> > I
> > > > just drop it and recreate it with the correct value.

> > > There's no need to drop and recreate the generator:

> > > SET GENERATOR WHATEVER_G TO 1000;

> > can you use the statement above in a stored procedure?

> > I've tried it but I can't get it right. Below is the source I wrote. the
> > error I got is:
> > Token unkown -line 11 char 3 SET

> > CREATE PROCEDURE New$Relation$ID (RelNrID INTEGER)  RETURNS (
> >   NewRelationID INTEGER
> > ) AS
> >   DECLARE VARIABLE MaxRelNr INTEGER;

> > BEGIN
> >   MaxRelNr = Gen_Id(TB4$FLd1$GEN,0);

> >   IF (MaxRelNr < RelNrID) THEN
> >   BEGIN
> >     SET GENERATOR TB4$FLd1$GEN to RelNrID;
> >   END;

> >   NewRelationID = Gen_Id(TB4$FLd1$GEN,0)

> >   INSERT INTO TB4 (FLD2) VALUES ("Nieuwe Relatie");

> > /*   SELECT MAX(FLD1) FROM TB4
> >   INTO :NewRelationID; */

> >   SUSPEND;
> > END;

> > Henk

Henk Nopp

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


I've played a round a bit with the line of code you have gave me and I got
the solution. Thanks!

Henk
Eric <e...@menlog.com> schreef in berichtnieuws
8fe773$n...@bornews.borland.com...

Quote
> Hi,

> To reset a generator in a strored procedure use the genid function like
that
> :

> declare variable i integer;

>  i = gen_id(G_GENERATOR, gen_id(G_GENERATOR,0)*-1);

> Eric

> Henk Noppe <h...@onlinebible.org> a crit dans le message :
> 391a85de@dnews...

> > Craig Stuntz <cstuntz@no_spam.vertexsoftware.com> wrote in
> > 38FF12E6.54477E9D@no_spam.vertexsoftware.com...

> > > Anthony wrote:

> > > > I have a problem with a generator which I am trying to reset.
> Usually,
> > I
> > > > just drop it and recreate it with the correct value.

> > > There's no need to drop and recreate the generator:

> > > SET GENERATOR WHATEVER_G TO 1000;

> > can you use the statement above in a stored procedure?

> > I've tried it but I can't get it right. Below is the source I wrote. the
> > error I got is:
> > Token unkown -line 11 char 3 SET

> > CREATE PROCEDURE New$Relation$ID (RelNrID INTEGER)  RETURNS (
> >   NewRelationID INTEGER
> > ) AS
> >   DECLARE VARIABLE MaxRelNr INTEGER;

> > BEGIN
> >   MaxRelNr = Gen_Id(TB4$FLd1$GEN,0);

> >   IF (MaxRelNr < RelNrID) THEN
> >   BEGIN
> >     SET GENERATOR TB4$FLd1$GEN to RelNrID;
> >   END;

> >   NewRelationID = Gen_Id(TB4$FLd1$GEN,0)

> >   INSERT INTO TB4 (FLD2) VALUES ("Nieuwe Relatie");

> > /*   SELECT MAX(FLD1) FROM TB4
> >   INTO :NewRelationID; */

> >   SUSPEND;
> > END;

> > Henk

Martijn Tonie

Delphi Developer
Wed, 18 Jun 1902 08:00:00 GMT

Re:URGENT HELP REQUIRE - RESETTING A GENERATOR


No - IB doesn't support DDL in stored code.

--

Martijn Tonies
Upscene Productions

For InterBase Workbench,
the developer tool for InterBase
http://www.interbaseworkbench.com

For Delphi Components and InterBase tools:
http://www.upscene.com

Quote
"Henk Noppe" <h...@onlinebible.org> wrote in message news:391a85de@dnews...

> Craig Stuntz <cstuntz@no_spam.vertexsoftware.com> wrote in
> 38FF12E6.54477E9D@no_spam.vertexsoftware.com...

> > Anthony wrote:

> > > I have a problem with a generator which I am trying to reset.
Usually,
> I
> > > just drop it and recreate it with the correct value.

> > There's no need to drop and recreate the generator:

> > SET GENERATOR WHATEVER_G TO 1000;

> can you use the statement above in a stored procedure?

> I've tried it but I can't get it right. Below is the source I wrote. the
> error I got is:
> Token unkown -line 11 char 3 SET

> CREATE PROCEDURE New$Relation$ID (RelNrID INTEGER)  RETURNS (
>   NewRelationID INTEGER
> ) AS
>   DECLARE VARIABLE MaxRelNr INTEGER;

> BEGIN
>   MaxRelNr = Gen_Id(TB4$FLd1$GEN,0);

>   IF (MaxRelNr < RelNrID) THEN
>   BEGIN
>     SET GENERATOR TB4$FLd1$GEN to RelNrID;
>   END;

>   NewRelationID = Gen_Id(TB4$FLd1$GEN,0)

>   INSERT INTO TB4 (FLD2) VALUES ("Nieuwe Relatie");

> /*   SELECT MAX(FLD1) FROM TB4
>   INTO :NewRelationID; */

>   SUSPEND;
> END;

> Henk

Other Threads

1. Urgent HELP required

2. URGENT HELP REQUIRED!!!

3. URGENT HELP REQUIRED!

4. URGENT HELP REQUIRED!!

5. URGENT HELP REQUIRED

6. Urgent help required - Pascal book refernce

7. Urgent help required - PASCAL

8. Urgent Help Required -

9. URGENT help required with Delphi re-install!

10. URGENT HELP WITH STRING MANIPULATION REQUIRED!!!!