ALTER TABLE error (entries in table doesn't match entries in database)

[montypython1]

Greetings,

I frequently need to add fields to one of my specification tables within a database. After using AFTER TABLE, the system generates the following error message: "The entries in table "tablename" does not match entries in database). Apparently ALTER TABLE only works on stand-alone tables.

How do I programmatically modify both the TABLE and the DATABASE fields, so that I do not receive this message?

Thank you in advance for any suggestions.

Dave Higgins

 

[Olaf Doschke]

How do you work on the table?

ALTER TABLE is for altering both database and free tables. But you need to have both the database and the table in exclusive access. And if you have a development database (local) and a production databae (on a server share) what you can't do is alter the local table, copy it's files to the share. You'd need to also copy DBC,DCT,DCX files then, because altering a database table does change both the dbf files and the dbc files.

Bye, Olaf.

 

[montypython1]

Hi Olaf,

This particular table is seldom worked on by the user. Instead, it is simply a specification file, which contains information that is unique to each of my customers. When I add an enhancement, it typically requires that I add a few fields to the table. It always works fine when testing the program using a newly modified "spec" table that contains all of the new fields.

My problem occurs if the customer decides to install the enhancement, and then installs an OLD self-installing "spec" file afterwards (note: I distribute this small "spec" file to make it easier on me when I distribute the program ... otherwise I would have to compile multiple versions of the program for each of my customers). The OLD "spec" file may not contain all of the newly added fields, which is why I test for each new field, and then perform and ALTER TABLE, if necessary.

The following may or may not be relevant, but I noticed something strange when looking at my project:
The DBF in question is part of the DBC, however it is listed in my project under the section called "Free Tables" (rather than under the "DataBase" section. When I look at the structure, it shows that it IS tied to the appropriate DBC. Also, the table contains fields that use long field name descriptions (rather than the 10 character limit of a stand-alone table), so it must be tied to the DBC, right?

Again, this may not have any bearing on my issue, but am open to ideas.

Thanks again,
Dave

 

[DougHennig]

Hi Dave.

If the table belongs to a DBC (and it sounds like it does), then the problem is that modifying its structure (which you do) modifies both the DBF and the DBC. When the user installs both, everything is fine. When they then overwrite the new DBF with an old one, you now have an out-of-sync situation that's exactly what the error message says. At this point, you can no longer open the table, so you can't use ALTER TABLE to modify its structure.

You have a few choices:

- Don't let the user overwrite the table. Instead, provide a utility program that copies records from the old table to the new one.

- Don't use a table in a DBC. This means losing your long field names so you'll have to modify any code referencing those names, but at least you won't have an out-of-sync problem any more.

- Use Stonefield Database Toolkit (

http://www.stonefield.com/sdt.aspx)

to ensure the table structure is always up-to-date. It can easily handle out-of-sync situations.

Doug

 

[Imaginecorp]

The OLD "spec" file may not contain all of the newly added fields, which is why I test for each new field, and then perform and ALTER TABLE, if necessary.

Yes you can alter a table for a free table as well as a database table.
Does your program, open the database exclu and then set the dbc as the current one prior to the alter ?
If not open the database, set the database then check for which fields (columns) have to be added, and add them...

 

[montypython1]

Thank you Olaf, Doug and Imaginecorp for you quick replies.
I will try your suggestions.

I wonder if the problem might go away if I do not distribute that particular table with the update, and simply use the ALTER TABLE command on their current table?

Thanks,
Dave

 

[Imaginecorp]

I wonder if the problem might go away if I do not distribute that particular table with the update, and simply use the ALTER TABLE command on their current table?

Yes;
Its pretty simple: something like (see help);

cDbc = dbc()
open database <<dbc>> exclu
set database to <<dbc>>
use <<table>> in (select 0) exclu
Alter table <<table>> add column field1 C(20)
Alter table <<table>> add column field2 C(20)
......
use in <<tablename>>
close databases all
open database (cdbc) shared
set database to (cdbc)

 

[Olaf Doschke]

Yes, Dave, simply altering their database will avoid the problem. Whyt you do now is like altering the table and only deploying the changed DBC. A second alter table on thier old table then fails, because DBC already has the new fields, but their table not. This mismatch raises the error.

If you do the ALTER TABLE on their correct but outdated DBC version it works like it works for you at the development DBC.

Bye, Olaf.

 

[montypython1]

That sounds like a simple and logical solution. Thank you Olaf, Doug and Imaginecorp for your assistance!

Dave Higgins