I have a C/S application that appends delivery orders from approx. 50 users to a master table with an auto increment field. The application does an EDI import from a spreadsheet on a daily basis of about 200 orders from a customer, which yesterday something went wrong. This has worked awesome for ten years. For some reason my AutoInc numbers have gone corrupt or may have reached a value limit of 2147483647 that is now creating erroneous values in the AutoInc field of the master table on new delivery orders. Luckily, in this case (maybe good design) all I need to do reset set my AutoInc.value to a lower starting number. eg. 200001 which I have done, and reassign anything below that 200,000 value.  My master table has up to 100,000 records at any given time. The question is how I can safely re-assign those high record numbers to a value below 200,000 while keeping my records. I would think a simple SQL UPDATE should do it. I do want to double check on how I can do an incremental update or fix this.

John P.

John Postnikoff wrote:

> records. I would think a simple SQL UPDATE should do it. I do want to
> double check on how I can do an incremental update or fix this.

Assuming you have support for your DBISAM (and if not, why not on a
mission critical tool), then I'd ask this direct to Tim via the support
mechanisms. The peer support here might not get you what you need in
time.

--

Matthew Jones

John


I'd go for Delphi rather than SQL simply loop through the records where the id is > 200000, edit .Clear the id field and post - it should pick up a new id automatically. Doing an UPDATE to NULL may have the same effect in SQL but I don't know.

Roy Lambert

Thanks again.  I took your advise with simple old code to get a base set of values and rid of those high erroneous numbers  I can run another loop that needs to be within a range or change with simple SQL commands in the DBSYS utility. I also applied a filter to my table to skip certain records and went through the process again. . Now I just have to figure out my corruption on that import and hopefully figure a way to prevent that again.

I do subscribe to DBISAM support for quite a few years now.  I program during the winter season now here in Canada for most parts.  So sometimes I tend to forget the easiest solution.  I believe I am almost due to renew.


   DBISAMtable1.First;
     while not DBISAMTable1.EOF do
     begin
       if  DBISAMTABLE1Probill_ID.Value  > 200000 then

       else
       DBISAMtable1.Edit;
   DBISAMTABLE1Probill_ID.Value := DBISAMTABLE1.recno;
   DBISAMTABLE1.Next;



Roy Lambert wrote:

John


I'd go for Delphi rather than SQL simply loop through the records where the id is > 200000, edit .Clear the id field and post - it should pick up a new id automatically. Doing an UPDATE to NULL may have the same effect in SQL but I don't know.

Roy Lambert

On 12/9/2014 2:48 PM, John Postnikoff wrote:
> Now I just have to figure out my corruption on that import and hopefully figure a way to prevent that again.

What was the actual issue ?

Was it maybe issue 3755 - rollover not working  ?
(http://www.elevatesoft.com/incident?action=viewrep&category=dbisam&release=4.35&incident=3755)

Raul

It looks like my problem is back when a new batch of records was appended today. That rollover that you are referring to seems to be the problem.  My last number on the increment field when the issue started was 2147483647. It seemed to restart start a new number at some random spot 3777152 .   I am using DBISAM 4.40 build 1 and in this case with D7.  

I may have to change how that auto-inc number is used on my end and come up with something else. Perhaps I can work with a a record number by joining a prefix to that for what I need as I do on a client program. Anyone know how I can create an integer from a date-time stamp? I would like to look at all options.

I also use SMI/SME tools from Scalabium Software to import all kinds of data from spreadsheets and other tables.  Always worked great for many years until today.  Now I cannot get my import components to work under DBISAM 4.40.  It does work on an earlier build DBISAM but again my auto inc fields are out of wack. Not sure if or how this is related. I do see a max row property is preset at 2147483647 in my SMI component. I cannot seem to append using another key. I will have to try some conventional SQL methods to append.  Hmmm.

I will have to try to reach Tim on this one, or hopefully can hear back.

John



Raul wrote:

On 12/9/2014 2:48 PM, John Postnikoff wrote:
> Now I just have to figure out my corruption on that import and hopefully figure a way to prevent that again.

What was the actual issue ?

Was it maybe issue 3755 - rollover not working  ?
(http://www.elevatesoft.com/incident?action=viewrep&category=dbisam&release=4.35&incident=3755)

Raul

John


1. I assume you reset the LastAutoInc for the table

2. I've always found Mike's components to be solid so I'd suspect something else, usually the data. I think you should be able to add a check for what's going into the autoinc field as the import is progressing. My SMI is an old one as is my DBISAM but if you can't sus it and want some help feel free to send me the necessary bits and pieces and I'll have a look.

Roy Lambert