Avatar of Pavel Celba
Pavel CelbaFlag for Czechia

asked on 

Occassional errors in VFP 9

Hi experts,

I would like just your opinion to this subject... How often do you observe some occassional error in VFP 9? The last example at my side is:
		SELECT tmp58orders
		IF TYPE('stornomark')='U'
			ALTER table tmp58orders ADD stornomark c(1)
		ENDIF 
		IF TYPE('typemark')='U'
			ALTER table tmp58orders ADD typemark c(1)
		ENDIF 
		REPLACE stornomark WITH 'S' FOR s__storno
		REPLACE typemark WITH 'M' FOR s__type<2
		REPLACE typemark WITH 'S' FOR s__type=2

Open in new window

tmp58orders is a writable cursor created by SQL SELECT (one output row only) and the error reported was:
1) Variable stornomark does not exist   ...  in the first REPLACE command
2) Variable s__type does not exist  ... in the second and third REPLACE commands
... more missing columns...

This code works in 99.9% of cases but it seems the cursor has totally different structure than I could suppose this time... and the first ALTER TABLE command failed without any error report whereas the second one succeeded...

I have just one explanation: "the fox is drank"...

It is also possible the first ALTER TABLE command crashed resulting in some unexpected data structure, but who knows...

So the only remaining question: How othen does this happen to you?
FoxPro

Avatar of undefined
Last Comment
Pavel Celba
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

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

Open 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.
Avatar of Cyril Joudieh
Cyril Joudieh
Flag of Lebanon image

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.
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

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.
SOLUTION
Avatar of Cyril Joudieh
Cyril Joudieh
Flag of Lebanon image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

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.
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

ASKER

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!
FoxPro
FoxPro

Visual FoxPro (VFP), and its predecessor FoxPro, is a data-centric, object-oriented, procedural, database programming language and IDE from Microsoft last released in 2007 that still has some active use due to its low cost of deployment and fairly rapid development. In 2008, Microsoft released a set of add-ons for VFP's xBase components to allow interoperability with various Microsoft technologies. It allows data processing against its native file-based data tables or database servers such as SQL Server.

11K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo