Alter Table question

Does ALTER TABLE work with cursors as well as tables? The following two
lines:

Select cFamilyname from People into cursor Trythis Readwrite
Alter table Trythis add column Anything C(20)

fails with 'Invalid operation for this cursor'.

However if I try it with a different table, creating a different cursor with
different fieldnames it consistently works. I have checked both tables,
rebuilt them from scratch etc to try to see if there is anything different
between them, apart from names.

The foxpro help doesn't mention cursors, but Hackers Guide 7 states that it
will work with cursors, allbeit the wording says

"ALTER TABLE works not only on tables, but also on cursors built with CREATE
CURSOR"

so I am wondering whether it can or can't be relied on to work with cursors
created via SELECT or whether I am doing something wrong. I have tried in
both vfp7 and vfp9

Many thanks

Stephen

Craig
Fri Jan 12 10:36:23 CST 2007

No, it won't work with cursors created with SELECT.

--
----
Craig Berntson
MCSD, Visual FoxPro MVP
Salt Lake City Fox User Group
"Stephen Ibbs" <stephen@datadevelopments.co.uk> wrote in message
news:Og79eamNHHA.3312@TK2MSFTNGP03.phx.gbl...
> Does ALTER TABLE work with cursors as well as tables? The following two
> lines:
>
> Select cFamilyname from People into cursor Trythis Readwrite
> Alter table Trythis add column Anything C(20)
>
> fails with 'Invalid operation for this cursor'.
>
> However if I try it with a different table, creating a different cursor
> with different fieldnames it consistently works. I have checked both
> tables, rebuilt them from scratch etc to try to see if there is anything
> different between them, apart from names.
>
> The foxpro help doesn't mention cursors, but Hackers Guide 7 states that
> it will work with cursors, allbeit the wording says
>
> "ALTER TABLE works not only on tables, but also on cursors built with
> CREATE CURSOR"
>
> so I am wondering whether it can or can't be relied on to work with
> cursors created via SELECT or whether I am doing something wrong. I have
> tried in both vfp7 and vfp9
>
> Many thanks
>
> Stephen
>
>

Stephen
Fri Jan 12 10:46:51 CST 2007

Many thanks Craig - I will select into a table instead.

Stephen

"Craig Berntson" <craig@craigberntson.com> wrote in message
news:%23YtBNfmNHHA.320@TK2MSFTNGP06.phx.gbl...
> No, it won't work with cursors created with SELECT.
>
> --
> ----
> Craig Berntson
> MCSD, Visual FoxPro MVP
> Salt Lake City Fox User Group
> "Stephen Ibbs" <stephen@datadevelopments.co.uk> wrote in message
> news:Og79eamNHHA.3312@TK2MSFTNGP03.phx.gbl...
>> Does ALTER TABLE work with cursors as well as tables? The following two
>> lines:
>>
>> Select cFamilyname from People into cursor Trythis Readwrite
>> Alter table Trythis add column Anything C(20)
>>
>> fails with 'Invalid operation for this cursor'.
>>
>> However if I try it with a different table, creating a different cursor
>> with different fieldnames it consistently works. I have checked both
>> tables, rebuilt them from scratch etc to try to see if there is anything
>> different between them, apart from names.
>>
>> The foxpro help doesn't mention cursors, but Hackers Guide 7 states that
>> it will work with cursors, allbeit the wording says
>>
>> "ALTER TABLE works not only on tables, but also on cursors built with
>> CREATE CURSOR"
>>
>> so I am wondering whether it can or can't be relied on to work with
>> cursors created via SELECT or whether I am doing something wrong. I have
>> tried in both vfp7 and vfp9
>>
>> Many thanks
>>
>> Stephen
>>
>>
>
>

Cindy
Fri Jan 12 11:25:47 CST 2007

Hi Stephen,

Does this work for you:

Select cFamilyname, Space(20) As Anything ;
from People into cursor Trythis Readwrite

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com


>>> Does ALTER TABLE work with cursors as well as tables? The following two
>>> lines:
>>>
>>> Select cFamilyname from People into cursor Trythis Readwrite
>>> Alter table Trythis add column Anything C(20)

Stephen
Fri Jan 12 17:18:31 CST 2007

Thanks Cindy - I had grossly simplified the problem. Imagine two tables
People and Group where people belong to different groups. The people table
has a foreign key into the lookup table of groups. I want to create a matrix
grid with people in the rows, and each groups a separate column with e.g. an
'X' in each column to show which person is in each group. I was creating a
cursor of people and then planned to scan through the lookup list and add a
field for each group to the people cursor. It was then a simple matter to
add the 'X's as appropriate. I ran into the problem when trying to add the
fields, reduced the code all down and then wondered whether it was because
it was a cursor not a table.

Sincerely

Stephen

"Cindy Winegarden" <cindy@cindywinegarden.com> wrote in message
news:%23QpPX9mNHHA.4244@TK2MSFTNGP04.phx.gbl...
> Hi Stephen,
>
> Does this work for you:
>
> Select cFamilyname, Space(20) As Anything ;
> from People into cursor Trythis Readwrite
>
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy@cindywinegarden.com
>
>
>>>> Does ALTER TABLE work with cursors as well as tables? The following two
>>>> lines:
>>>>
>>>> Select cFamilyname from People into cursor Trythis Readwrite
>>>> Alter table Trythis add column Anything C(20)
>
>

Roger
Fri Jan 12 19:14:26 CST 2007

"Stephen Ibbs" <stephen@datadevelopments.co.uk> wrote in message
news:Og79eamNHHA.3312@TK2MSFTNGP03.phx.gbl

> Does ALTER TABLE work with cursors as well as tables?

Yes, provided that long field names are not included
in the result cursor.

> The following two lines:
>
> Select cFamilyname from People into cursor Trythis Readwrite
> Alter table Trythis add column Anything C(20)
>
> fails with 'Invalid operation for this cursor'.

OK, try:

Select cFamilyName As famname into cursor Trythis Readwrite
Alter table Trythis add column Anything C(20)

Just tested and it worked fine for me.

HTH

-Roger

Olaf
Sat Jan 13 03:15:35 CST 2007

Hi Stephen,

ALTER TABLE works on cursors created with SELECT too,
the big diffference is not that. You also can have problems,
when you initially created the cursor with CREATE CURSOR.

Help on ALTER TABLE says:
ALTER TABLE might not produce consistent results when used with Visual FoxPro cursors created by the CREATE CURSOR command. In
particular, you can create a Visual FoxPro cursor with features, such as long field names, that are normally available only with
tables that are part of a database container. ALTER TABLE saves a temporary copy of the cursor, so the rules that apply to free
tables also apply, and any features requiring database support are lost or changed in an unpredictable manner. Therefore, you
should generally avoid using ALTER TABLE with Visual FoxPro cursors unless you have tested and understood the outcome.

So all in all it's simplest to either create cursor as wanted in
the first place. In your situation it would be simplest to first
determin how many fields you'd need and build up a CREATE
CURSOR string and execute it by macro expansion.

There is a wizard for that kind of cross tables, in which
rows are determined by one table and columns by another.
Take a look at _GENXTAB or the crass-tab wizard.

Another possibility to change Cursor is
AFIELDS(laFields,"curOld")
*manipulate laFields
CREATE CURSOR curNew FROM ARRAY laFields
Append from dbf("curOld")

For adding a field you need to add an array row. Can be
as easy as:
CREATE CURSOR cuField (cNewField C(20))
Afields(laNewfield,"curField")

Now add the row of laNewfield to the other array. You
should only adjust laNewfield(1,12), the long table name.

Bye, Olaf.

Fred
Sat Jan 13 10:50:25 CST 2007

I don't think it will work on any SELECT cursor that contains a fieldname
longer than 10 characters.

--
Fred
Microsoft Visual FoxPro MVP


"Stephen Ibbs" <stephen@ibbs.org.uk> wrote in message
news:OqiO5$pNHHA.4912@TK2MSFTNGP02.phx.gbl...
> Thanks Cindy - I had grossly simplified the problem. Imagine two tables
> People and Group where people belong to different groups. The people table
> has a foreign key into the lookup table of groups. I want to create a
> matrix grid with people in the rows, and each groups a separate column
> with e.g. an 'X' in each column to show which person is in each group. I
> was creating a cursor of people and then planned to scan through the
> lookup list and add a field for each group to the people cursor. It was
> then a simple matter to add the 'X's as appropriate. I ran into the
> problem when trying to add the fields, reduced the code all down and then
> wondered whether it was because it was a cursor not a table.
>
> Sincerely
>
> Stephen
>
> "Cindy Winegarden" <cindy@cindywinegarden.com> wrote in message
> news:%23QpPX9mNHHA.4244@TK2MSFTNGP04.phx.gbl...
>> Hi Stephen,
>>
>> Does this work for you:
>>
>> Select cFamilyname, Space(20) As Anything ;
>> from People into cursor Trythis Readwrite
>>
>> --
>> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
>> cindy@cindywinegarden.com
>>
>>
>>>>> Does ALTER TABLE work with cursors as well as tables? The following
>>>>> two lines:
>>>>>
>>>>> Select cFamilyname from People into cursor Trythis Readwrite
>>>>> Alter table Trythis add column Anything C(20)
>>
>>
>
>

Stephen
Sat Jan 13 12:01:54 CST 2007

Many thanks all for the help and suggestions. My post unfortunately used a
long fieldname as the example, but in fact the actual tables involved do
not. I have just found the results inconsistent so I have decided not to
SELECT to a cursor but to a table, then use ALTER TABLE as required. This
solution had the added benefit that it only required the change of one word
in the original code !! I just have to remember to close and delete the
table at the form's release.

Sincerely

Stephen

"Olaf Doschke" <b2xhZi5kb3NjaGtlQHNldG1pY3MuZGU.strconv.14@t-online.de>
wrote in message news:eoa9t4$u5h$00$1@news.t-online.com...
> Hi Stephen,
>
> ALTER TABLE works on cursors created with SELECT too,
> the big diffference is not that. You also can have problems,
> when you initially created the cursor with CREATE CURSOR.
>
> Help on ALTER TABLE says:
> ALTER TABLE might not produce consistent results when used with Visual
> FoxPro cursors created by the CREATE CURSOR command. In particular, you
> can create a Visual FoxPro cursor with features, such as long field names,
> that are normally available only with tables that are part of a database
> container. ALTER TABLE saves a temporary copy of the cursor, so the rules
> that apply to free tables also apply, and any features requiring database
> support are lost or changed in an unpredictable manner. Therefore, you
> should generally avoid using ALTER TABLE with Visual FoxPro cursors unless
> you have tested and understood the outcome.
>
> So all in all it's simplest to either create cursor as wanted in
> the first place. In your situation it would be simplest to first
> determin how many fields you'd need and build up a CREATE
> CURSOR string and execute it by macro expansion.
>
> There is a wizard for that kind of cross tables, in which
> rows are determined by one table and columns by another.
> Take a look at _GENXTAB or the crass-tab wizard.
>
> Another possibility to change Cursor is
> AFIELDS(laFields,"curOld")
> *manipulate laFields
> CREATE CURSOR curNew FROM ARRAY laFields
> Append from dbf("curOld")
>
> For adding a field you need to add an array row. Can be
> as easy as:
> CREATE CURSOR cuField (cNewField C(20))
> Afields(laNewfield,"curField")
>
> Now add the row of laNewfield to the other array. You
> should only adjust laNewfield(1,12), the long table name.
>
> Bye, Olaf.
>

Anders
Sun Jan 14 11:45:45 CST 2007

CREATE CURSOR people (name char(2), fk Int)
INSERT INTO people VALUES ('aa', 1)
INSERT INTO people VALUES ('bb', 1)
INSERT INTO people VALUES ('aa', 1)
INSERT INTO people VALUES ('aa', 2)

CREATE CURSOR Groups (pk Int, group Char(5) )
INSERT INTO Groups VALUES (1, 'group1')
INSERT INTO Groups VALUES (2, 'group2')

SELECT People.name AS name, Groups.group,'X';
FROM People JOIN Groups ON People.fk = Groups.pk ;
GROUP BY 1,2 ORDER BY 1,2 ;
INTO CURSOR Q1

DO (_Genxtab) WITH 'Matrix'
BROWSE LAST NOWAIT

Result:
Name Group1 Group2
aa X X
bb X

-Anders


"Stephen Ibbs" <stephen@ibbs.org.uk> skrev i meddelandet
news:OqiO5$pNHHA.4912@TK2MSFTNGP02.phx.gbl...
> Thanks Cindy - I had grossly simplified the problem. Imagine two tables
> People and Group where people belong to different groups. The people table
> has a foreign key into the lookup table of groups. I want to create a
> matrix grid with people in the rows, and each groups a separate column
> with e.g. an 'X' in each column to show which person is in each group. I
> was creating a cursor of people and then planned to scan through the
> lookup list and add a field for each group to the people cursor. It was
> then a simple matter to add the 'X's as appropriate. I ran into the
> problem when trying to add the fields, reduced the code all down and then
> wondered whether it was because it was a cursor not a table.
>
> Sincerely
>
> Stephen
>
> "Cindy Winegarden" <cindy@cindywinegarden.com> wrote in message
> news:%23QpPX9mNHHA.4244@TK2MSFTNGP04.phx.gbl...
>> Hi Stephen,
>>
>> Does this work for you:
>>
>> Select cFamilyname, Space(20) As Anything ;
>> from People into cursor Trythis Readwrite
>>
>> --
>> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
>> cindy@cindywinegarden.com
>>
>>
>>>>> Does ALTER TABLE work with cursors as well as tables? The following
>>>>> two lines:
>>>>>
>>>>> Select cFamilyname from People into cursor Trythis Readwrite
>>>>> Alter table Trythis add column Anything C(20)
>>
>>
>
>

Stephen
Sun Jan 14 13:39:13 CST 2007

Many thanks indeed Alders - I had to alter group char(5) to group char(6)
to get the example to work, but a completely different way of tackling the
problem. I had forgotten all about _genxtab.

Sincerely

Stephen
"Anders Altberg" <A@A> wrote in message
news:%230EaZPAOHHA.448@TK2MSFTNGP04.phx.gbl...
> CREATE CURSOR people (name char(2), fk Int)
> INSERT INTO people VALUES ('aa', 1)
> INSERT INTO people VALUES ('bb', 1)
> INSERT INTO people VALUES ('aa', 1)
> INSERT INTO people VALUES ('aa', 2)
>
> CREATE CURSOR Groups (pk Int, group Char(5) )
> INSERT INTO Groups VALUES (1, 'group1')
> INSERT INTO Groups VALUES (2, 'group2')
>
> SELECT People.name AS name, Groups.group,'X';
> FROM People JOIN Groups ON People.fk = Groups.pk ;
> GROUP BY 1,2 ORDER BY 1,2 ;
> INTO CURSOR Q1
>
> DO (_Genxtab) WITH 'Matrix'
> BROWSE LAST NOWAIT
>
> Result:
> Name Group1 Group2
> aa X X
> bb X
>
> -Anders
>
>
> "Stephen Ibbs" <stephen@ibbs.org.uk> skrev i meddelandet
> news:OqiO5$pNHHA.4912@TK2MSFTNGP02.phx.gbl...
>> Thanks Cindy - I had grossly simplified the problem. Imagine two tables
>> People and Group where people belong to different groups. The people
>> table has a foreign key into the lookup table of groups. I want to create
>> a matrix grid with people in the rows, and each groups a separate column
>> with e.g. an 'X' in each column to show which person is in each group. I
>> was creating a cursor of people and then planned to scan through the
>> lookup list and add a field for each group to the people cursor. It was
>> then a simple matter to add the 'X's as appropriate. I ran into the
>> problem when trying to add the fields, reduced the code all down and then
>> wondered whether it was because it was a cursor not a table.
>>
>> Sincerely
>>
>> Stephen
>>
>> "Cindy Winegarden" <cindy@cindywinegarden.com> wrote in message
>> news:%23QpPX9mNHHA.4244@TK2MSFTNGP04.phx.gbl...
>>> Hi Stephen,
>>>
>>> Does this work for you:
>>>
>>> Select cFamilyname, Space(20) As Anything ;
>>> from People into cursor Trythis Readwrite
>>>
>>> --
>>> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
>>> cindy@cindywinegarden.com
>>>
>>>
>>>>>> Does ALTER TABLE work with cursors as well as tables? The following
>>>>>> two lines:
>>>>>>
>>>>>> Select cFamilyname from People into cursor Trythis Readwrite
>>>>>> Alter table Trythis add column Anything C(20)
>>>
>>>
>>
>>
>
>