Auto increment query

[tarlachmoorhouse]

I'm working on a method to provide remote upgrades to databases, i.e I send them a new version and a 'utility' does the upgrading for them. I am using a methodology that has worked successfully in the past, where by all data is uploaded from the previous version to the new one and then the old version is over written by the new one, this means that we can test that the upgrade was successful before the old version is replaced.

For each table in the database I temporally attach them to the utility database using the FILE_ADD_TO_DB() and FILE_DROP_FROM_DB() commands, I am then using

tbl_source = table.open(file_path)
query.filter = ".T."
indx = tbl_source.query_create()
result = tbl_source.copy_records_to(file_path_up)

to transfer all the records from the old version to the new version, this work great but I was surprised what it does with the auto increment ID field I have on each of the tables.

If the current database had four records, these would have ID's of
00000 - Red Car
00001 - Blue Car
00002 - Yellow Car
00003 - Pink Car

It I deleted one of these records such as 00002, then it would leave

00000 - Red Car
00001 - Blue Car
00003 - Pink Car

I had expected that when records were added to the upgrade version the tbl_source.copy_records_to() command would create new ID is for each record:-

00000 - Red Car
00001 - Blue Car
00002 - Pink Car

and that I would have to do some fancy footwork to resolve these differences, however what I found was that the old ID's were preserved.

Is this correct and would it always happen (if so it makes life much more easy.) ?

Thanks

John

[masteralone]

John,

I had something similar happen when using A5V4. I was appending an A4 client database with an A5 table. I had to increase the client ID field size and change the beginning letter in the dbf to a different letter in the table. Both fields were auto increment. The expression I used changed the letter, added 3 leading zeros and concatinated the remainder of the field to the record. None of the records autoincremented during the process, each client id was adjusted by the letter and 3 leading zeros.

I also had to use the same expression for all the other .dbfs that had the old client ID format to the new one in basicly the identical tables. Worked like a charm and preserved the links.

I would experiment with different tables and see if you have the same results each time. I am guessing that if you have a value to put in an auto increment field that it doesn't auto increment.

And if it doesn't, hehehe, I think it is a great undocumented trick we can all use at some time or other ;)

[Finian Lennon]

I haven't tried this in V7 but try appending some data that has a problem, such as a record without a value in the autoncrement field. I think then you'll see the field values change. True, it shouldn't happen but ....

[tarlachmoorhouse]

I haven't tried this in V7 but try appending some data that has a problem, such as a record without a value in the autoncrement field. I think then you'll see the field values change. True, it shouldn't happen but ....

In effect what seems to me to be happening, which I am pleased about, is that if the field is set to 'Auto-increment' then when you are using the tbl_source.copy_records_to() command then when the new record is initiated, at the begining of the copy, the value for the auto-increment is entered, then if there is a value in that field in the record being copied then it over writes the auto increment.

The only question I then have (which I need to test) is what happens in my example above with a new record added afterwards, how does A5 work out what is the next nunber in the auto increment, as this is the fourth record added in the upgrade database, so this would psoobly be 00003 but this is already 'taken', if it does it by doing a max() to see that the last used was then this will be OK

In a database created with another package, I solved this by having a autocalulated ID that was based on the software version number as well as the serial number from the table, so if auto increment was reset, it would still remain unique hmmm... might want to do the same thing here for belt and braces.

[Cheryl Lemire]

John,

From the help files:

When you create an auto-increment field rule, Alpha Five creates an index on the field. This ensures that auto-increment field values can never be duplicated.

AND

The user if free to change this "likely" value as long as he enters a number that is higher than any existing value. If the user breaks the sequence and enters a higher value than the likely value, then the sequence for all subsequent records starts with the value entered by the user.