"Will DeWitt Jr." wrote:

There is no "counter" per se -- the database just checks for the current
largest value, then adds one to it.

Correct -- due to backward compatibility constraints, all versions work
the same way.

The definition of the AUTOINC data type is that it is stored and collated
as an absolute value. This allows a developer to use the negative value
as a flag value. Unfortunately, I don't know what was going through the
original developers' minds when they decided this. It is a very cool
option, but needless to say, this does cause some havoc from time to time,
especially with the SQL engine when it tries to create temporary sort
files and the numbers suddenly sort differently. (I think all these
issues may have been corrected, but there may be another one yet.)

The only way to change this behavior is to do it in code -- search for an
available value, then force the field to that value, which will insert
correctly. You can make this a bit quicker by using the following loop:
for(i=0;i<32768;i++)
if(Btrv(Operation 5 + 50,i)==4)
break;
Obviously pseudocode, but you get the idea. The GetKey bias (+50) does
not retrieve the entire record, so it will run faster than a real GetEqual
operation. For even faster operation, store your last position in a file
somewhere, then loop to 32768 first, then back to the initial position in
a second loop.

Temporary? Even a 4-byte AutoInc field gives you 2 billion records. If
you insert 1 record every second, it'll still take 63 years to fill up the
data type. Of course, with the latest engines, you can use the 8-byte
AutoInc. I won't do the math, but I'll bet that the data file will be
purged (and possibly rebuilt) long before that happens.

Another solution is to write a rebuild application -- one that reads
records out of the old file and inserts them into a new file. Of course,
if these are key values, then you'd also need to start renumbering foreign
keys in other tables from the old value to the new value as you go along.
It can be difficult and time-consuming, but not impossible. (I once wrote
a purge/renumber function that hit 50+ files, which was loads of fun.)
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Pervasive.SQL Service & Support Classes ***
Chicago: September 16-18: See our web site for details!

Bill Bach <bbach (AT) cncdsl (DOT) com> wrote


Ahh.. yeah, I realized this behavior when I started removing entries
from the end of the database (at the largest value).

Yeah, I was beginning to think that was the best solution. I'd
forgotten about the GetKey bias-- as you say, that should speed it up
significantly I'd think. =)

Well, I'm potentially inserting dozens of records a second, but it
would certainly take *much* longer for the issue to crop up (where
with the current 2 byte AutoInc field it takes 6-8 hours to hit the
issue). As for the 8-byte AutoInc, unfortunately the engines I would
need to conform to are versions 5.1x (DOS environment) and 6.15 (Win32
environment).

Yeah, the AUTOINCREMENT field is referenced in other files and
renumbering them would mean having to update the other files as well
(which, depending on how fast/slow it was, would possibly be too much
of a performance hit to be feasible). I'm going to be doing some more
testing to see if I can get decent performance out of the idea you
mentioned ('get equal' with the 'key only' bias), perhaps coupled with
an expansion of the field to 4 bytes (which should alleviate the
problem for a much longer period, but potentially make the 'get equal'
with 'key only' bias solution slower once the limit is finally
reached).

Thank you again for your comments, they've been most helpful. =)