ALTER TABLE not completing the process

[sdocker]

Using FREE TABLES (not in a DBC), I have to drop 150 Columns and then add 110 Columns.

The code in processed each time the app is invoked. Sometime it works fine.

Occasionally, it comes back with the message:
Alias: CLDATA
Error number: 12
Error message: Variable 'Cash_1' not found
Line number of error: 303 [This is the first DROP COMMAND]

The message makes sense because the alias is incorrect.
What I can't understand is why the alias() is CLDATA, as it always encounters the SELECT MCDATA statement

SELECT MCADTA

*-* Put field names in array.
AFIELDS(FieldNames)  && Column 1 is the Field Name, Column 2 is the Field Type, Col 3 is the Field Width

IF ASCAN(FieldNames,'INVMBEG',-1,-1,1,1) = 0    && Not found
    USE_TYPE = .T.   && Files must be opened exclusively.
    DO OPEN_FILES WITH USE_TYPE IN MDFILES

    ALTER TABLE MCDATA DROP COLUMN CASH_1  {This is the line number of the error]
    ALTER TABLE MCDATA DROP COLUMN MS_1
    ALTER TABLE MCDATA DROP COLUMN AR_1
    ALTER TABLE MCDATA DROP COLUMN INV_1
    ALTER TABLE MCDATA DROP COLUMN OCA_1
    ALTER TABLE MCDATA DROP COLUMN LAND_1
[indent] .[/indent]
[indent] .[/indent]
[indent] .[/indent]
[indent] ALTER TABLE MCDATA ADD COLUMN INVMBEG N(8) NULL      &&  Next time, ASCAN() will find and processing will exit the "IF STATEMENT"[/indent]
    ALTER TABLE MCDATA ADD COLUMN INVTBEG N(8) NULL
    ALTER TABLE MCDATA ADD COLUMN INVMACQ N(8) NULL

Thanks,
Sam

 

[sdocker]

Also, The OPEN FILES statement opens 3 tables. But since each ALTER COMMAND includes the table name, so why is the alias significant?

 

[Tore Bleken]

May I ask why your application needs to make these changes every time it starts? To me it sounds like a bad design, but I am sure that my first impression is wrong.

 

[Mike Lewis]

Sam, what exactly is your OPEN_FILES procedure doing? The reason I ask is that MCDATA is already open when you call it (otherwise you would not have been able to the AFIELDS()).

And I agree that the alias name should not be significant. ALTER TABLE takes a physical table name, not the alias.

Also, the error message is presumably generated by your custom error-handler. It is that error-handler that is reporting the alias. Where does it get the alias from, if it is not the same as the table name?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[sdocker]

Tore,

May I ask why your application needs to make these changes every time it starts? To me it sounds like a bad design, but I am sure that my first impression is wrong.

It doesn't make these changes every time. After the first time, if it worked properly, the field name that ASCAN searches for would be found and the rest of the IF statement would be ignored

Mike,
MCDATA is open , but NOT exclusively, which I thought was necessary to alter it.

The alias CLDATA is one of 4 tables that are open.
The error handler gets it from ALIAS().

Sam

 

[Tore Bleken]

[pre]Use In mcadta && to make sure that it's closed
Select 0
Use mcdata Exclusive
If Empty(Field([INVMBEG]))
Alter Table mcdata Drop Column cash_1;
Drop Column MS_1;
Drop Column AR_1;
Drop Column INV_1;
Drop Column OCA_1;
Drop Column LAND_1;
Add Column INVMBEG N(8) Null;
Add Column INVTBEG N(8) Null;
Add Column INVMACQ N(8) Null
Endif
[/pre]

 

[sdocker]

Thanks for all your replies.

By reading your replies and trying alternate solutions, I uncovered a damaged file header. When i restored the file from a backup, it works fine.

Sorry for all the trouble. You were really helpful.

Appreciatively,
Sam

 

[Tore Bleken]

A damaged file header should have been reported as a critical error by VFP. Make sure to have the line "Set TableValidate to 3" (or a higher value) early in your startup program.

 

[Olaf Doschke]

What error number message do you get?
What if you put a breakpoint before the ALTER.

You can ALTER a cursor (aliasname), if the data is queried into a cursor READWRITE (at least NOFILTER), otherwise ALTER on the ALIAS is to be used with much caution, see

http://fox.wikis.com/wc.dll?Fox~AddColumnProblem

You want to alter the DBF, so make it ONE single ALTER statement (as Tore gave) that makes all alterations in one go, just like you shouldn't use multiple REPLACEs of the same record. These things can be and should always be combined and since ALTER rewrites files the performance change is drastically, even for just some small table files, as creating a file, any size, has an overhead and timing on top of just the write time of the file body. Additionally, problems as with fast succession of PACKs occur with multiple ALTERs.

Since you already get an error in your first ALTER, the file system shouldn't play a role. But since you check your changes on the second to last field name only, you likely have a dbf that only was partly altered before. Also a result of doing multiple alterations.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[sdocker]

Thanks again.

Good advice.

I'll learn about Table Validation.
Sam

 

[Mike Lewis]

I am surprised that a damaged header caused that particular problem. I would have thought you would have become aware of the error at the time of opening the table. The fact that the error only occurs occasionally suggests that you can't be sure the problem has gone away.

Regarding the design issue, would it make more sense to maintain two separate tables? Rather than constantly modifying the structure, why not keep the two tables in place and simply copy the data to the one you wish to use in the current session? Or would it be possible to have a single table containing both sets of columns, and to use two views to access them?

I don't know anything about your application, but it does seem heavy-handed to be dropping and adding so many columns every time you run the program.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Table validation has nothing to do with that if you have SET TABLEVALIDATE in mind.

Edit:
What you do here will get messy with a database (or directory of free tables, that doesn't matter), when you make checks and changes in the places of code short before using a database. this is not the job of that classes, if you think of OOP it's a wrong thought the separation of concerns in conjunction with data access classes mean these access classes are responsible for putting the data into the structure they need. On the contrary, DB version is a separate concern that needs separate handling, especially as a database isn't just a bunch of files and tables, data is related and so the whole database structure should be under maintenance with db version control related classes of your application or setup. You make it too complicated or too simple if you think everything related to one specific DBF has to be handled by one module of code.
I realize this is the way it is.

So this is part of an application initialisation to know the database version and with what features it can run on older versions or what updates (ALTERs and more, maybe also inserting default data, transforming data, splitting, joining etc.) is necessary to upgrade a database. And after that initialisation, only the features working with the current DB version should run.

And this can get messy whenever you did some manual changes to data for one customer but not for all. So database management and versioning are just as important as keeping source code history or in short SVC (software version control). For DBCs you have the almost official DBCX standard (shortly mentioned in

http://fox.wikis.com/wc.dll?Wiki~DBCX)

Stonefield Database Toolkit established. Means there is a solution for that you wouldn't need to do it your way. But if you do, you fail on half run alterations. Any state between the first and second last alter doesn't let ASCAN see that new field and since you don't check every field you will fail on the DROP, yes, of course, if that already ran in a previous run but not to the end with the second to last ALTER.

Added: I have a personal totally different take on that which works with creating a temp sub-dir with the newest DB structure, moving (unchanged) or appending data (when columns were added or dropped) from the old versions complete with backup and restore in case of failure. Besides, with single customers and large corporations, I have worked for in the past I kept this DB management separate and done during nights, not within the application itself. In the later phases with SQL Server, this was then included into administrative options, since you actually can do some more DB alterations even during use of an application, as MSSQL databases are not structured in single files per table but indeed "just" update metadata system tables with ALTER TABLE. If you work with version named views you can do some "wonders" and don't need to do every single DB change at some dedicated deadline or even weekend.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[sdocker]

To clear things up.

The application prints several pages of data for submission to various agencies.
Every few years somebody decides to changes the information required. The table has a limit of the number of fields, so we do it this way.

AS it turns out, a colleague at another office had already DROPPED the fields and the errors occurred when my code tried to DROP them again. I believe he did a little tinkering; hence the damage.

Apologies.

Sam

 

[Olaf Doschke]

Yes, that explains it, as you only check for INVTBEG you risk dropping a non-existing field.

If you really wanted to stay with every single alteration, you'd need to make extensive checks, eg:

USE_TYPE = .T.   && Files must be opened exclusively.
DO OPEN_FILES WITH USE_TYPE IN MDFILES

IF ASCAN(FieldNames,'CASH_1',-1,-1,1,1) >0    && field found
    ALTER TABLE MCDATA DROP COLUMN CASH_1
ENDIF
IF ASCAN(FieldNames,'MS_1',-1,-1,1,1) >0   && field found
    ALTER TABLE MCDATA DROP COLUMN MS_1
ENDIF
IF ASCAN(FieldNames,'INVMBEG',-1,-1,1,1) = 0    && Not found
   ALTER TABLE MCDATA ADD COLUMN INVMBEG N(8) NULL      &&  Next time, ASCAN() will find and processing will exit the "IF STATEMENT"
ENDIF

Besides, you could check TYPE(field)

It's not recommended anyway, as said, multiple alter table statements when you just want one overall change are a bad idea.

The combined change will even stronger depend on the initial condition being all fields you drop existing and all fields you want to add not existing. In general, an overall ALTER TABLE does not depend on fields not mentioned at all but is typically very specific for the current structure.

The approach of Stonefield is to compare DBCX metadata and generate ALTER TABLE statements depending on differences. That's therefore not at all depending on whatever current structure, as long as the metadata about it is correct and as long as the metadata of the new database structure is correct. But it only works for DBCs.

Just think about how unstable your process is. It's not a good way to handle this, as you see it depends on not touching tables manually additional to what the code should do.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name