ALTER TABLE ADD field

[wgcs]

Funny thing:

This works fine (VFP6):
CREATE CURSOR cursorname ( LongFieldName C(10) )

But, then try to add a long field to it:
ALTER TABLE cursorname ADD FIELD anotherLongFieldName C(10

and it fails...

Looks like I have to make my temp cursor as a table in a DBC if it's going to have long fields added to it....

Anyone know of any alternatives? Is VFP8 the same?

 

[craigsboyd]

Yes.

From VFP Help File:

ALTER TABLE may not produce consistent results when used with Visual FoxPro cursors created using the CREATE CURSOR command. In particular, you can create a Visual FoxPro cursor with features (long field names, for example) that would normally be available only with tables that are part of a database container. Because ALTER TABLE saves a temporary copy of the cursor, the rules that apply to free-tables then apply, and any features requiring database support are lost or changed in an unpredictable manner. Therefore, as a general rule, you should avoid using ALTER TABLE with Visual FoxPro cursors unless you have tested and understood the outcome.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[wgcs]

Ah, well, that is pretty explanatory.

It just means that I've got a more difficult situation, since I hoped I could build a temporary cursor, with arbitrary (and long) field names that would only exist in a form's private datasession.

If I make it a table in the local db, then I've got to track what name it has (since several forms can be open at the same time, using each their own temp-tables).

The other option, of course, is to build one Long CREATE CURSOR command... but it might be too long, since I have about 100 fields... I'll have to test.

 

[ramani]

Hi

Instead of a table you can create views on the fly and delete them also. That will not harm.

CREATE SQL VIEW vq_myView AS ;
SELECT myField1, myField2 ;
FROM myDBC!acmas ;
WHERE myCondition ;
ORDER BY myOrder

Here you can set a temporary DBC as your default scratch DBC and create a view from another DBC table or a free table. You can also add or delete temp. tables to this scratch DBC if long field names are required.

But as my own principle, I always try to use only field names of less than 10 chars. Probably I developed these habits over DOS days. So I always create SQL cursors which are very very easy to handle and with VFP7/8 much more convenient since READWRITE class is available.





ramani
(Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[wgcs]

I have the same habit of using short field names,
however I'm developing a flexible end-user application that will store data in a one data-item-per-record table, which, for the user to edit the data, has to be de-normalized to display in a grid.

So, each data-item-type is identified with a unique "fieldname" that will be used when denormalizing.

Several sets of data are stored together, so, potentially, one person might be associated with 5+ sets of data, totaling hundreds of "fieldname"s, but only one set is to be denormalized at a time, limited to a total of 250 or so fields per "set" (since a cursor/table is limited to 255 fields).

I just tested, and my initial test "set", which should be my biggest, has 98 "fieldnames", producing a CREATE CURSOR command about 1700 chars long, and does execute properly through macro replacement. So, My problem is solved.

Thanks!

 

[keepingbusy]

May I add a question here about ALTER TABLE?

Tried a search on the site, but cant find anywhere how you can a change a field name e.g. from ADDRESSES to LOCATIONS. I dont need to change the type just the field name.

Any suggestions please
(VisFox Version 6)

Lee

Alone we can do so little, together we can do so much

 

[ramani]

If the table belongs to a DBC container then..

ALTER TABLE myTable ADD COLUMN locations C(15) ;
DEFAULT ADDRESSES
ALTER TABLE mytable DROP COLUMN addresses

If it is a free table..

ALTER TABLE myTable ADD COLUMN locations C(15)
REPLACE ALL locations WITH addresses
ALTER TABLE mytable DROP COLUMN addresses

Suitably change the C(15) to whatever value you want..



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[keepingbusy]

Hi Ramani

It was a free table and your suggestion shown with changing the names as you mentioned, has solved the problem easily:

If it is a free table..

ALTER TABLE myTable ADD COLUMN locations C(15)
REPLACE ALL locations WITH addresses
ALTER TABLE mytable DROP COLUMN addresses


Many thanks to you (again!)

Lee

Alone we can do so little, together we can do so much

 

[andyrice]

The ALTER TABLE command has a RENAME COLUMN clause.
That works for free tables.

Andy Rice
San Diego, CA

 

[keepingbusy]

Hi Andy

Thank you for that. One small problem I forgot to mention was how do I indentify a field name in table then use the rename column as you've suggested. Example:

If we have a table called mytable with a field name called ABCDE, I want to be able to recognise this and then change it as below. The reason for this is because on occassions the field name change but the C(15) will always stay the same and the field will always be a character name. Hope that makes sense and thanks again

ALTER TABLE myTable ADD COLUMN locations C(15)
REPLACE ALL locations WITH ABCDE
ALTER TABLE mytable DROP COLUMN ABCDE

Lee

Alone we can do so little, together we can do so much