Loading ...
Sorry, an error occurred while loading the content.

Get duplicate generator value? Am I doing wrong?

Expand Messages
  • bigredinf
    Hi,Groups! I use Ibobject and firebird 1.03.972 on win2k server. I have a application write by delphi, when a record before post,I need a lotno,so I write :
    Message 1 of 7 , Apr 1, 2005
    • 0 Attachment
      Hi,Groups!

      I use Ibobject and firebird 1.03.972 on win2k server.
      I have a application write by delphi,
      when a record before post,I need a lotno,so I write :
      procedure QDtlBeforePost(DataSet: TDataSet);
      begin
      if (qdtllotno.AsString = '') then
      qdtlLOTNO.Value :=
      afgetNewNo_str(qMainINDT.Value, 4);
      end;

      function afgetNewNo_str(pddate: tdate):
      string;
      var
      lignid: integer;
      begin
      QGnid.sql.Clear; //QGnid is a Tiboquery
      QGnid.sql.add('select gen_id(gn_lotno,1) from
      rdb$database');
      QGnid.ExecSQL;
      lignid := QGnid.Fields[0].AsInteger;
      Result := FormatDateTime('yymm', PDDATE)
      + inttostr((lignid + 10000000) mod 10000);
      //so if the Gnid get the value 25418
      // then the lotno will become "05035418"
      end;

      I though this way the lotno won't be duplicate.
      and this application run just fine about 6 month.
      but last week, my client report they found 2 diffent records with same
      lotno.
      The time between 2 record insert is about 2hours,
      and I am sure they won't insert 10000 record within 2hours.
      Do I do something Wrong?
      Please forget my poor English.
    • Piotr Gawlicki
      ... I had similar problem. When I upgrade Firebird to version 1.5 this problem never happend again. Best regards. Piotr Gawlicki
      Message 2 of 7 , Apr 1, 2005
      • 0 Attachment
        "bigredinf" <bigredinf@...> napisaƂ(a):
        >
        > Hi,Groups!
        >
        > I use Ibobject and firebird 1.03.972 on win2k server.
        > I have a application write by delphi,
        > when a record before post,I need a lotno,so I write :
        > procedure QDtlBeforePost(DataSet: TDataSet);
        > begin
        > if (qdtllotno.AsString = '') then
        > qdtlLOTNO.Value :=
        > afgetNewNo_str(qMainINDT.Value, 4);
        > end;
        >
        > function afgetNewNo_str(pddate: tdate):
        > string;
        > var
        > lignid: integer;
        > begin
        > QGnid.sql.Clear; //QGnid is a Tiboquery
        > QGnid.sql.add('select gen_id(gn_lotno,1) from
        > rdb$database');
        > QGnid.ExecSQL;
        > lignid := QGnid.Fields[0].AsInteger;
        > Result := FormatDateTime('yymm', PDDATE)
        > + inttostr((lignid + 10000000) mod 10000);
        > //so if the Gnid get the value 25418
        > // then the lotno will become "05035418"
        > end;
        >
        > I though this way the lotno won't be duplicate.
        > and this application run just fine about 6 month.
        > but last week, my client report they found 2 diffent records with same
        > lotno.
        > The time between 2 record insert is about 2hours,
        > and I am sure they won't insert 10000 record within 2hours.
        > Do I do something Wrong?
        > Please forget my poor English.
        >
        > Yahoo! Groups Links
        >
        > To visit your group on the web, go to:
        > http://groups.yahoo.com/group/firebird-support/
        >
        > To unsubscribe from this group, send an email to:
        > firebird-support-unsubscribe@yahoogroups.com
        >
        > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

        I had similar problem. When I upgrade Firebird to version 1.5 this problem
        never happend again.

        Best regards. Piotr Gawlicki
      • Helen Borrie
        ... Given that your calculation causes the generated number, at its current order, to shed the first digit, you would start getting duplicates if you spun more
        Message 3 of 7 , Apr 1, 2005
        • 0 Attachment
          At 08:31 AM 1/04/2005 +0000, you wrote:



          >Hi,Groups!
          >
          >I use Ibobject and firebird 1.03.972 on win2k server.
          >I have a application write by delphi,
          >when a record before post,I need a lotno,so I write :
          >procedure QDtlBeforePost(DataSet: TDataSet);
          >begin
          >if (qdtllotno.AsString = '') then
          > qdtlLOTNO.Value :=
          > afgetNewNo_str(qMainINDT.Value, 4);
          >end;
          >
          >function afgetNewNo_str(pddate: tdate):
          > string;
          >var
          > lignid: integer;
          >begin
          > QGnid.sql.Clear; //QGnid is a Tiboquery
          > QGnid.sql.add('select gen_id(gn_lotno,1) from
          > rdb$database');
          > QGnid.ExecSQL;
          > lignid := QGnid.Fields[0].AsInteger;
          > Result := FormatDateTime('yymm', PDDATE)
          > + inttostr((lignid + 10000000) mod 10000);
          > //so if the Gnid get the value 25418
          > // then the lotno will become "05035418"
          >end;
          >
          >I though this way the lotno won't be duplicate.

          Given that your calculation causes the generated number, at its current
          order, to shed the first digit, you would start getting duplicates if you
          spun more than 9999 numbers of this generator in a single month. If you
          lost generator numbers for some reason during that month, the maximum
          number of potential lot numbers might be considerably less than the
          supposed 9999.

          >and this application run just fine about 6 month.

          Let's see about those 6 months:

          October, 31 days, safe. (Not a full month's use?)
          November, 30 days, safe. (Short month)
          December, 31 days, safe. (Christmas holidays?)
          January, 31 days, safe. (New Year holidays?)
          February, 28 days, safe. (Short month)
          March, 31 days, not safe. (Full month, no holidays).

          >but last week, my client report they found 2 diffent records with same
          >lotno.
          >The time between 2 record insert is about 2hours,
          >and I am sure they won't insert 10000 record within 2hours.

          I don't see where "2 hours" fits in. You will begin producing duplicates
          if you use up more than 9999 generated numbers in one calendar month.

          Since you are using IBO, you have no need for your complicated method of
          getting the generator value: use the Gen_ID() function:

          function afgetNewNo_str(pddate: tdate):
          string;
          begin
          Result := IntToStr(IB_Connection1.Gen_ID(gn_lotno,1)); // returns '25418'
          Result := FormatDateTime('yymm', PDDATE)
          + copy(Result, (Length(Result) - 3), 4);
          // produces '05035418'
          end;

          But I think you need to add two characters to the length of LotNo and
          include the day part of the date in your string.

          ./hb
        • Kevin Stanton
          Hi Helen, I thought there was also a transaction issue as well. I was getting duplicates as well at one point using a TIB_Cursor and you recommended the
          Message 4 of 7 , Apr 1, 2005
          • 0 Attachment
            Hi Helen,

            I thought there was also a transaction issue as well. I was getting
            duplicates as well at one point using a TIB_Cursor and you recommended the
            following:





            qryGetOrderNos is TIB_DSQL





            with qryGetOrderNos do

            begin

            try

            trNextID.StartTransaction;

            SQL.Strings[0] := 'select gen_id ( gen_orderno, ' + IntToStr(OrderCnt)
            + ' ) as EOrderNo from rdb$database';

            If NOT Prepared then Prepare;

            Execute;

            eo := FieldByName('EOrderNo').AsInteger;

            trNextID.Commit;

            except

            trNextID.Rollback;

            raise;

            end;

            end;



            This has always confused me because in the docs that I've read, the
            generator function works independent/outside of transactions thus always
            guaranteeing a unique value.

            (I'm using FB 1.03, soon to be 1.5.x)



            Is using the IB_Connection a better, more bullet-proof way of retrieving
            generator values? Someone else posted a reply stating that once he moved to
            FB 1.5, the problem went away. Is this a possible bug w/ <= 1.3?



            As always, thanks for the help.

            Kevin







            _____

            From: Helen Borrie [mailto:helebor@...]
            Sent: Friday, April 01, 2005 5:25 AM
            To: firebird-support@yahoogroups.com
            Subject: Re: [firebird-support] Get duplicate generator value? Am I doing
            wrong?



            At 08:31 AM 1/04/2005 +0000, you wrote:


            Since you are using IBO, you have no need for your complicated method of
            getting the generator value: use the Gen_ID() function:

            function afgetNewNo_str(pddate: tdate):
            string;
            begin
            Result := IntToStr(IB_Connection1.Gen_ID(gn_lotno,1)); // returns '25418'
            Result := FormatDateTime('yymm', PDDATE)
            + copy(Result, (Length(Result) - 3), 4);
            // produces '05035418'
            end;





            _____

            Yahoo! Groups Links

            * To visit your group on the web, go to:
            http://groups.yahoo.com/group/firebird-support/

            * To unsubscribe from this group, send an email to:
            firebird-support-unsubscribe@yahoogroups.com
            <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>

            * Your use of Yahoo! Groups is subject to the Yahoo!
            <http://docs.yahoo.com/info/terms/> Terms of Service.



            [Non-text portions of this message have been removed]
          • Helen Borrie
            ... This doesn t look like my code! maybe a mod of someone else s, though. This routine could certainly return duplicates - it would only take the increment
            Message 5 of 7 , Apr 1, 2005
            • 0 Attachment
              At 07:47 AM 1/04/2005 -0800, Kevin Stanton wrote:

              >Hi Helen,
              >
              >I thought there was also a transaction issue as well. I was getting
              >duplicates as well at one point using a TIB_Cursor and you recommended the
              >following:
              >
              >qryGetOrderNos is TIB_DSQL
              >
              > with qryGetOrderNos do
              > begin
              > try
              > trNextID.StartTransaction;
              >
              > SQL.Strings[0] := 'select gen_id ( gen_orderno, ' + IntToStr(OrderCnt)
              >+ ' ) as EOrderNo from rdb$database';
              > If NOT Prepared then Prepare;
              > Execute;
              > eo := FieldByName('EOrderNo').AsInteger;
              > trNextID.Commit;
              > except
              > trNextID.Rollback;
              > raise;
              > end;
              > end;

              This doesn't look like my code! maybe a mod of someone else's,
              though. This routine could certainly return duplicates - it would only
              take the increment expression evaluating to '0' to cause that.

              >This has always confused me because in the docs that I've read, the
              >generator function works independent/outside of transactions thus always
              >guaranteeing a unique value.

              It does. But gen_id(MyGenerator, 0) is always going to return a duplicate
              number. Using client code to mess around with generator values needs a
              very even head. Checking that the increment value is > 0 would be essential.

              >(I'm using FB 1.03, soon to be 1.5.x)
              >
              >Is using the IB_Connection a better, more bullet-proof way of retrieving
              >generator values?

              Not better or more bullet-proof, just more elegant and always consistent.
              One line of code as compared with about 8. Code re-use is the key here.
              Why keep a dog and do the barking yourself?

              >Someone else posted a reply stating that once he moved to
              >FB 1.5, the problem went away. Is this a possible bug w/ <= 1.3?

              If so, it's the first I've heard of it. v.1.0.3 has been around a long
              time. Many gazillions of generators have rolled out since then. I would bet
              that something else they had to fix in the transition to v.1.5 also forced
              them to fix whatever was causing that problem (revisiting their return
              structure without a fresh call to gen_id() ?)

              ./heLen
            • bigredinf
              ... if you ... I understand,But I can sure my client won t key over 2000 records within 1 month. so I used this structure lotno. If you ... duplicates ... I
              Message 6 of 7 , Apr 1, 2005
              • 0 Attachment
                > Given that your calculation causes the generated number, at its current
                > order, to shed the first digit, you would start getting duplicates
                if you
                > spun more than 9999 numbers of this generator in a single month.

                I understand,But I can sure my client won't key over 2000 records
                within 1 month. so I used this structure lotno.

                If you
                > lost generator numbers for some reason during that month, the maximum
                > number of potential lot numbers might be considerably less than the
                > supposed 9999.


                > I don't see where "2 hours" fits in. You will begin producing
                duplicates
                > if you use up more than 9999 generated numbers in one calendar month.

                I check the data on firebird, the later one record with same
                lotno value was inserted after first one by 120 minuts,
                and there is not other record inserted into the table within
                that period.
                So I donot think there will happen
                10000 genertor call and not insert one record into table
                in thar "120 minutes" period.


                >
                > Since you are using IBO, you have no need for your complicated
                method of
                > getting the generator value: use the Gen_ID() function:
                >
                > function afgetNewNo_str(pddate: tdate):
                > string;
                > begin
                > Result := IntToStr(IB_Connection1.Gen_ID(gn_lotno,1)); // returns
                '25418'
                > Result := FormatDateTime('yymm', PDDATE)
                > + copy(Result, (Length(Result) - 3), 4);
                > // produces '05035418'
                > end;
                >
                > But I think you need to add two characters to the length of LotNo and
                > include the day part of the date in your string.

                If my client have such loading
                (over 1000 records per day) ,I will take this advice.


                Thank you for your quick reply.

                Kadee/Bigredinf
              • Kevin Stanton
                As always - Thanks Helen! Kevin _____ From: Helen Borrie [mailto:helebor@tpg.com.au] Sent: Friday, April 01, 2005 2:35 PM To: firebird-support@yahoogroups.com
                Message 7 of 7 , Apr 4, 2005
                • 0 Attachment
                  As always - Thanks Helen!

                  Kevin



                  _____

                  From: Helen Borrie [mailto:helebor@...]
                  Sent: Friday, April 01, 2005 2:35 PM
                  To: firebird-support@yahoogroups.com
                  Subject: RE: [firebird-support] Get duplicate generator value? Am I doing
                  wrong?



                  At 07:47 AM 1/04/2005 -0800, Kevin Stanton wrote:

                  >Hi Helen,
                  >
                  >I thought there was also a transaction issue as well. I was getting
                  >duplicates as well at one point using a TIB_Cursor and you recommended the
                  >following:
                  >
                  >qryGetOrderNos is TIB_DSQL
                  >
                  > with qryGetOrderNos do
                  > begin
                  > try
                  > trNextID.StartTransaction;
                  >
                  > SQL.Strings[0] := 'select gen_id ( gen_orderno, ' +
                  IntToStr(OrderCnt)
                  >+ ' ) as EOrderNo from rdb$database';
                  > If NOT Prepared then Prepare;
                  > Execute;
                  > eo := FieldByName('EOrderNo').AsInteger;
                  > trNextID.Commit;
                  > except
                  > trNextID.Rollback;
                  > raise;
                  > end;
                  > end;

                  This doesn't look like my code! maybe a mod of someone else's,
                  though. This routine could certainly return duplicates - it would only
                  take the increment expression evaluating to '0' to cause that.





                  [Non-text portions of this message have been removed]
                Your message has been successfully submitted and would be delivered to recipients shortly.