Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 14 total
Thread BDE Database Transfer Utility Not "Seeding" An AutoIncrement Field Properly
Fri, May 29 2015 8:20 AMPermanent Link

Rick Hillier

Greetings,

I have an application in which I have a simple table containing a single autoincrement field that my application uses to obtain new job numbers as they are added (the reason for this is too lengthy to explain here)... basically, a new record is created, saved, the new job number noted and then, the record is deleted).  So typically, there are no records in this file unless something happens that prevents the application from deleting them.  

When I last looked in the file, there were a couple of stragglers in there - around 51000 or so - the current new value is in the 61000 range or so.

When I use the BDE conversion tool, it is seeding the autoincrement value to one past the value in the last record in the table.  If I manually delete those stragglers from the table, the conversion tool seeds the autoincrement value at 1 as if it were a brand new, empty table.  This is, of course, causing key violations in other areas of my software because I am creating detail records in other tables based in the "new" job number.

Does anyone have any idea as to what could be causing this problem and how it can be corrected?

Thank you in advance,
Fri, May 29 2015 8:47 AMPermanent Link

Matthew Jones

Rick Hillier wrote:

> Does anyone have any idea as to what could be causing this problem
> and how it can be corrected?

I know this isn't answering your question, but hopefully it might help.
Basically, I suggest that you move away from the auto-inc value, and
just code it yourself. Since you are using DBISAM, and doing a
migration, I have put my code for this below. I have a table that is
just for this purpose, only it can have multiple counters in the same
table. I don't appear to have code to create the table, but you can see
the fields easy enough.


--

Matthew Jones


unit uNextRef;

interface

uses db, dbisamtb;

function GetNextReference(xSession : TDBISAMSession; szRefName :
String; bAllowCreate : Boolean; nLoopAt : Integer) : Integer;


implementation

uses uServerConfig, Windows, uLogSystem, Sysutils;

function GetNextReference(xSession : TDBISAMSession; szRefName :
String; bAllowCreate : Boolean; nLoopAt : Integer) : Integer;
const
   MAX_RETRIES = 100;
var
   bTableLocked : boolean;
   nLoop : Integer;
   nNextValue : Integer;
   tableNextRef : TDbisamTable;
begin
   bTableLocked := False;
   result := 0;
   tableNextRef := nil;

   try
      tableNextRef := TDBISAMTable.Create(nil);
      tableNextRef.SessionName := xSession.SessionName;
      tableNextRef.DatabaseName := g_xStartupConfig.DatabasePath;
      tableNextRef.TableName := 'NextRefMulti';

      for nLoop := 1 to MAX_RETRIES do
      begin
         try
            tableNextRef.Active := true;
            tableNextRef.LockTable;
            bTableLocked := True;
            break;    // skip past retry
         except
            if nLoop < MAX_RETRIES then
            begin
               Sleep(5);
            end
            else
            begin
               LogReport(leWarning, 'Unable to obtain lock on reference file.');
               exit;
            end;
         end;
      end;

      try
         if tableNextRef.Locate('nrRefName', szRefName, []) then
         begin
            tableNextRef.Edit;
         end
         else
         begin
            tableNextRef.Insert;
            tableNextRef.FieldByName('nrRefName').AsString := szRefName;
         end;
         nNextValue := tableNextRef.FieldByName('nrNextValue').AsInteger;
         if (nLoopAt <> 0) and ((nNextValue + 1) >= nLoopAt) then
         begin
            tableNextRef.FieldByName('nrNextValue').AsInteger := 1;
         end
         else
         begin
            tableNextRef.FieldByName('nrNextValue').AsInteger := nNextValue + 1;
         end;
         tableNextRef.Post;
      except
         nNextValue := 1;    // if error, then we just use one item
      end;
      result := nNextValue;
   finally
      if bTableLocked then
         tableNextRef.UnlockTable;
      tableNextRef.Active := false;
      FreeAndNil(tableNextRef);
   end;
end;


end.
Fri, May 29 2015 10:59 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Rick


Like Matthew I don't know what's causing it but I have two otehr suggestions:

1. If you have purchased support contact ElevateSoft support (aka Tim) directly - he will know the answer
2. You can do a bit of post processing after the conversion to get the right number in there - look up AlterTable in the pdf help that tells you what you need to make the changes.

Roy Lambert
Fri, May 29 2015 12:25 PMPermanent Link

Rick Hillier

Hi Roy,

I have a maintenance contract in place, but I shouldn't have to purchase support for something that, in my opinion, is a bug.

There is some post processing carried out after conversion, as I have made a plethora of upgrades to the software as well.  The post processing is carried out by SQL statements that are provided as a sort of "script" that my update module reads and passes along to the DBISAMQuery component handling those queries.  It needs to be automated, and I am not sure if this can be done by straight queries.  

I don't know why it is considered problematic to use something that is built into the database to get the "next" value for a job number (as I had mentioned, there are reasons for doing things this way in my application that would take a long time to explain).  I may have to try it that way, though if all else fails.

Roy Lambert wrote:

Rick


Like Matthew I don't know what's causing it but I have two otehr suggestions:

1. If you have purchased support contact ElevateSoft support (aka Tim) directly - he will know the answer
2. You can do a bit of post processing after the conversion to get the right number in there - look up AlterTable in the pdf help that tells you what you need to make the changes.

Roy Lambert
Fri, May 29 2015 12:36 PMPermanent Link

Raul

Team Elevate Team Elevate

On 5/29/2015 12:25 PM, Rick Hillier wrote:
> I have a maintenance contract in place, but I shouldn't have to purchase support for something that, in my opinion, is a bug.

That's not the point.

You need to contact support for "official" support - this newsgroup is
peer supported and while Tim might eventually read it it's likely not
going to be anytime soon.

Anything that's a defect or bug does not consume any of your support
sessions.


Raul
Fri, May 29 2015 1:10 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Rick


>I have a maintenance contract in place, but I shouldn't have to purchase support for something that, in my opinion, is a bug.

Essentially as Raul said.. Also just to make it clear I wrote support but maintenance is as good. The way Tim has it set up you buy the product and that also buys you a year of support/maintenance/minor releases. You renew that annually - that's what I was referring to.

Whilst I have some suspicions as to what may be happening its a long long time since I converted from BDE to DBISAM and I'm now on ElevateDB and even if right it wouldn't help you.

>There is some post processing carried out after conversion, as I have made a plethora of upgrades to the software as well. The post processing is carried out by SQL statements that are provided as a sort of "script" that my update module reads and passes along to the DBISAMQuery component handling those queries. It needs to be automated, and I am not sure if this can be done by straight queries.

Not quite, at least not the way I'd do it, I'd use a query to find the current max value used and feed that into a query which would ALTER TABLE ... LAST AUTOINC

>I don't know why it is considered problematic to use something that is built into the database to get the "next" value for a job number (as I had mentioned, there are reasons for doing things this way in my application that would take a long time to explain). I may have to try it that way, though if all else fails.

It isn't. Its (in my personal view) a bit of a weird way to do it but as you say you had reasons. Neither Matthew nor I have said that its problematic Smiley

Roy Lambert
Fri, May 29 2015 1:51 PMPermanent Link

Raul

Team Elevate Team Elevate

On 5/29/2015 8:20 AM, Rick Hillier wrote:
> When I use the BDE conversion tool, it is seeding the autoincrement value to one past the value in the last record in the table.  If I manually delete those stragglers from the table, the conversion tool seeds the autoincrement value at 1 as if it were a brand new, empty table.  This is, of course, causing key violations in other areas of my software because I am creating detail records in other tables based in the "new" job number.
> Does anyone have any idea as to what could be causing this problem and how it can be corrected?

If i'm understanding this right then basically what you're asking for
for is the BDE conversion tool to use the last autoinc value of the
table (i.e. the one stored by the table internally) and populate it into
the dbisam.

Currently it simply uses the current data and then resets the last
autoinc in dbisam side to next logical number (either +1 of the existing
max or to 1 if table is blank).

I'd say current behavior is by design (no RI is being broken) so you
definitely need Tim to look at it as it would be a feature enhancement
(somebody else might not want same behavior - one might also want to
reset the values to match whatever data is present).

Raul
Fri, May 29 2015 2:03 PMPermanent Link

Rick Hillier

If I am creating a new table, then it would make sense to set the autoincrement number to 1, but if I am bringing an existing table over, then would it not make sense to transfer the autoincrement information that is currently in the BDE table into the DBISAM table?

I must be one of the last going over from the BDE.  This project is a large one (over 500,000 lines of code, not counting the visual stuff) and is taking some time to get done, as it is being significantly enhanced at the same time.
Fri, May 29 2015 2:04 PMPermanent Link

Raul

Team Elevate Team Elevate

On 5/29/2015 12:25 PM, Rick Hillier wrote:
> I don't know why it is considered problematic to use something that is built into the database to get the "next" value for a job number (as I had mentioned, there are reasons for doing things this way in my application that would take a long time to explain).  I may have to try it that way, though if all else fails.

Using autoinc itself is not considered problematic but your usage makes
certain assumptions on how db engine maintains table level autoinc
values : for example you're assuming that autoinc value does not get
reset during lifetime of the table (which is generally true in DBISAM
for things like repair and optimize but it will roll over if max value
is reached).

You're also assuming conversion tools will honour this internal setting
which they might or might not since most of them address data in
integrity and not internal table/engine state.


Raul
Fri, May 29 2015 2:13 PMPermanent Link

Raul

Team Elevate Team Elevate

On 5/29/2015 2:03 PM, Rick Hillier wrote:
> If I am creating a new table, then it would make sense to set the autoincrement number to 1, but if I am bringing an existing table over, then would it not make sense to transfer the autoincrement information that is currently in the BDE table into the DBISAM table?

It's an interesting question since you have no data in the table - just
my opinion but main goal of the db conversions is to retain data
integrity and that did take place here (i.e. your other data has the
right values).

Having both options would be useful - bring over current value or reset
to next value based on data (and then there are situations where it's
not possible to easily retrieve the next autoinc value from engine or
migration has to rely on CSV files or such).


> I must be one of the last going over from the BDE.  This project is a large one (over 500,000 lines of code, not counting the visual stuff) and is taking some time to get done, as it is being significantly enhanced at the same time.

I have no doubt Tim can can help you - every time we have contacted him
support has always been outstanding.

We've been on DBISAM for 10+ years for our main product and it's has
very good.

Raul
Page 1 of 2Next Page »
Jump to Page:  1 2
Image