Occassional errors in VFP 9

I never ALTER TABLE with cursors, because of this passage from the help:

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.

This is not about a cursor you created by a query, still ALTER TABLE might better only be applied to tables.

Is s__storno a variable? What about s__type? We know VFP reports a missing variable when a field is missing, as it can't judge from a name expression what it is. Since field names are prioritized over variables, VFP looks for variables last and then reports a missing variable, though a field is meant.

Not sure if ALTER TABLE always has the new table as currently selected alias, your REPLACEs depend on that. I always prefer the syntax with IN alias.

How about using other approaches?
1. Make sure the fields exist:
SELECT ..., ' ' as stornomark, ' ' as typemark ....INTO CURSOR tmp58orders

2. Create the fields separate:
Create Cursor curMarks (recno I, stornomark C(1), typemark C(1))

With the second approach you relate the side cursor to the main cursor tmp58orders without an index by record number.

Sample code:

s__storno =.f.
s__type=0

Create Cursor tmp58orders (orderid I, customerid I)
Insert Into tmp58orders Values (1,10)

Create Cursor curMarks (iRecno I, stornomark C(1), typemark C(1))
Insert into curMarks (iRecno) Select Recno() From tmp58orders
Set Relation To iRecno  Into tmp58orders 

REPLACE stornomark WITH 'S' FOR s__storno IN curMarks
REPLACE typemark WITH 'M' FOR s__type<2 IN curMarks
REPLACE typemark WITH 'S' FOR s__type=2 IN curMarks

Select allOpen in new window

That would even work with multiple records in tmp58orders. Of course the relation is not needed for a single record, but if done, you can even populate a grid with records as if there only was a single cursor, you just have to set the grid recordsource to the side cursor curMarks. You can then display tmp58orders fields as well as curMarks fields using the colums controlsources, as the grid scans through curMarks and the relation will move the record pointer in tmp58orders, also a REPLACE IN curMarks could have tmp58orders fields in it's FOR condition, the relation makes sure the right tmp58orders record is checked for the FOR condition.

Bye, Olaf.

Actually since you are creating the table at run time, you can easily have the fields ready from the start. If the table it huge, it will be much faster as well if you create them from the start.

I would also prefer generating the fields in the query, much simpler. To make sure you get the right field type and to specify, whether it should be nullable you could also use CAST, of course.

One more thing specifically about the "Variable not found" error: I also experienced this in cases I am sure the field exists, from time to time. And I also had to judge this as "the fox is drunk".

In some cases I found out another alias was active, so the field couldn't be found. Activating a grid does select it's recordsource workarea, for example. But in many cases debugging with the datasession open showed me, that's not always the reason for that error. That seems to be a bug digged too deep to reproduce it.

Bye, Olaf.

I was rather talking about Select ... CAST(' ' as C(1) NULL) as typemark... to generate the field as wanted, not about detecting the missing field.

Indeed TYPE('table.field') is better to test for the field.

Independent of that, the error also occurred to me in cases the field preexisted in a table for sure and was neither generated by the query or by ALTER TABLE, and even in case of a SQL-Select query with the table in it's FROM clause, not only in REPLACEs.

Bye, Olaf.

Thanks for your answers...

... the question was not about the ALTER TABLE on cursors... but about occassional errors in VFP.  :-)

Above ALTER TABLE is just one example (the most recent one) but it can be almost anything. These errors do not happen every time but every week, maybe every day. And the question rather was how often do you observe an error which is not easy to explain?

OK, you've focussed on ALTER TABLE so I'll explain why it is used this way:

The original cursor was produced in VFP COM application on the server then it was stored to a file, zipped and delivered to the client workstation.  Workstation unzips the file creates the cursor and offers it to the client part of the application.

Above topology means the COM app update requires to disconnect all working users... If we need some quick change at the client side then we may decide to update the cursor at the client side which is this case. (The change then stays in the app for ever...)

I know when it should be possible to use ALTER TABLE on the cursor but it seems
SELECT *, newCol1, newCol2 FROM cCursor INTO cCursor would do the job better way probably.

ALTER TABLE in my example works fine all the time and this was the first time it failed. There were no work area change during the command, it even works in Private datasession (just two cursors are open).

I don't follow the "rule" to use IN clause in every possible command. I know it is recommended by several authorities BUT
SELECT workarea  command never failed to me. And the unattended workarea change means program error which is easier to trace without all these IN clauses.

The TYPE("workarea.field") is better than TYPE("field") probably because it avoids possible conflicting memory variables. Our application does not use memory so heavily so the conflict would be visible at the first run.

Also all names used in my example were table fields.

Yeah, we could write another Hacker's Guide to VFP... Debugger seems to be VFP independent product sometimes...  It also reports non-existing events.

I know the Christof's GREAT paper.

BTW, Christof placed Guineu to open source: https://bitbucket.org/cwollenhaupt/guineu 

Thanks for all your contributions!