Fox 2.6 Append From 2

[jlg13]

Hi Experts,

I have a table "CUSTOMER" that contains a bunch of data fields such as FIRST, LAST, EMAIL, and STATE. However I noticed that my STATE field has no data. I have another table "TEMP" that contains that STATE information and I would like to update the CUSTOMER table. The STATE table contains FIRST, LAST, EMAIL, STATE for validation purposes.

So essentially I want to take the data in field temp.state and append it to customer.state as long as one of these conditions exist
customer.email = temp.email
customer.first+" "+customer.last = temp.first+" "+temp.last

I'd like to execute this as a small program so I can do this process regularly and quickly while I figure out how to fix the problem of STATE being blank in the first place...

Thanks,
Joe

 

[Mike Lewis]

Or, what about this command to set the field to spaces:

[tt]BLANK FIELDS State[/tt]

But was that available in 2.6?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Avvordng to Q99095, yes.

And this also tells thats the initial state after APPEND BLANK and state wasn't set yet.
And ISBLANK(field) for null values in 2.6 fields, which must differ from today's NULL storage. As today ISBLANK() is .f. for Null fields.

I don't know if the knowledgebase is not precise or VFP nowadays doesn't behave as fp2.6 for 2.6 dbfs.
At least creating a fox2x dbf and appending blank

Create cursor crstest (state c(20) null)
Copy To nulltest type fox2x

Use nulltest.dbf
Append Blank 
? 'empty, isnull, isblank, Space(Len(state))'
? Empty(state), IsNull(state), IsBlank(state) , state==Space(Len(state))

What VFP9 creates and populates now behaves equal to spaces, also when you add DEFAULT null or DEFAULT .NULL., which fp 2.6 wouldn't know.
But VFP can define a cursor with default .NULL., of course, then save as fox2x and then append blank does not create .null. within the field.

I actually remember back in VFP6 or 7 EMPTY() and ISBLANK() could differ depending on whether the field was never set or not, but in VFP9 I don't see a case where isblank differs from empty().

It's clear empty() also judges tabs as whitespace, and CR and LF, but none of those would be a default, you'd explicitly need to set a field to contain such whitespace, so a string of spaces in the width of a field is a good replacement for EMPTY() in case of index usage, checking EMPTY() has still good cases when you need on detect other whitespace, too, ie for parsing code.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[jlg13]

Hi Guys,

I seem to be caught in some kind of loop. Below is the final code.,
I got rid of the original line 9 Local lcEmptyValue
When line12 is not commented out, the system hangs (the lovely never ending hour glass)
When commented out, it's a definite loop. In the status bar i see a series of changing numbers/TOTAL Record Number (493)
LINE 12 lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))

Extra Info... I do not have any indexes on the LM_STATE table. do I need to index on email, f_name and l_name?
Set Relation? Sorry, my knowledge is minimal...

PROCEDURE Ma_Test
lcMsg = "Update LM_STDNT.STATE FIELD"
=MSGBOX(lcMsg,"MA_TEST PROGRAM",gbOK+giInfo)
* activate window debug
* set step on
lcMsg = "Make sure LM_STATE table is correct!"
lcMBTitle = "Ready to Update LM_STDNT?"
IF MSGBOX(lcMsg,lcMBTitle,gbYN+giQmark+gf2nd) = grYes
USE LM_STATE in 0
USE LM_STDNT in 0
SELECT LM_STDNT
* lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))
SCAN FOR EMPTY(State)
SELECT LM_STDNT
LOCATE FOR (LM_STDNT.email = LM_STATE.email) OR LM_STDNT.f_name = LM_STATE.f_name AND LM_STDNT.l_name = LM_STATE.l_name
IF FOUND()
REPLACE LM_STDNT.STATE WITH LM_STATE.STATE
ENDIF
ENDSCAN
CLOSE ALL
ENDIF
RETURN

 

[Mike Lewis]

Joe,

Before we go any further, may I make a suggestion - and a request.

When you post long a piece of code in a forum message, please format it with [ignore]

[/ignore] tags. You can do that either by typing the tags manually or by highlighting the code and the clicking on the Code button in the toolbar (immediately above the messaging area).

The reason to do that is to preserve the indentation in the code, which makes the logic easier to follow. It also has the benefit of applying a mono-spaced font, which is generally preferable for computer code.

If you look back at other posts in this thread, you will see several cases where this has been done.

In addition, it is a good idea to insert blank lines, so as to break the code up into logical chunks. And you should also break any particularly long lines into two or more shorter lines, using a semi-colon as a continuation character (such as in your LOCATE command). This avoids the reader having to horizontally scroll the window.

I have taken the liberty of applying the above suggestions to the code in your most recent post. This is the result:

PROCEDURE Ma_Test
lcMsg = "Update LM_STDNT.STATE FIELD"
=MSGBOX(lcMsg,"MA_TEST PROGRAM",gbOK+giInfo)

* activate window debug
* set step on
lcMsg = "Make sure LM_STATE table is correct!"
lcMBTitle = "Ready to Update LM_STDNT?"

IF MSGBOX(lcMsg,lcMBTitle,gbYN+giQmark+gf2nd) = grYes
    USE LM_STATE IN 0
    USE LM_STDNT IN 0
    SELECT LM_STDNT
    * lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))

    SCAN FOR EMPTY(State)
        SELECT LM_STDNT
        LOCATE FOR (LM_STDNT.email = LM_STATE.email) OR LM_STDNT.f_name = LM_STATE.f_name AND ;
            LM_STDNT.l_name = LM_STATE.l_name
        IF FOUND()
            REPLACE LM_STDNT.State WITH LM_STATE.State
        ENDIF
    ENDSCAN

    CLOSE ALL
ENDIF

RETURN

I hope you will agree that this is much clearer and easier to follow than your version of the same code.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Regarding the line:

[tt]lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))[/tt]

As far as I can see, that line will make no difference to your code, regardless of whether or not it is commented out. What you are doing here is creating a variable (lcEmptyValue) and storing a string of spaces in it. The variable is not used anywhere else in your code, and so the line has no effect on the result.

That doesn't explain why the system appears to be hanging. I can't off hand see any reason for that.

I see you have coded this as a procedure. Do you have some more code somewhere that is calling that procedure? Or are you running it directly from the command window?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike,

Weren't we going through this already? I also see it's not used anymore as Joe adapted your advice to use EMPTY(State), it should better stay at SCAN FOR STATE==lcEmptyValue to make sense again. And just to be clear: It does not depend on an index on state to work, it will find empty state field without an index, too, and it won't matter much, unless you would have ten thousands or even millions of LM_STDNT records. I wouldn't write code that has a prerequisite without then telling how to get there. And last not least STATE==lcEmptyValue (or when == doesn't work STATE=lcEmptyValue) is also not slower then EMPTY without an index. EMPTY considers more than = comparison and so it actually is overused, mainly because it gives a little semantic meaning to the code, it's illustrative to look for some EMPTY field. But empty simply means the field contains just spaces, so we actually only need EMPTY() for when a) different data types could be checked for being empty, empty is not only working for strings. It's not the case here, and it's a bit lazy to use a more general function when you already know the specific case is about empty strings.

And if you don't have an index on state, then create one, Joe. That already propels the finding of rows with empty state.

I spot what you did completely wrong here: You LOCATE while you SELECT LM_STDNT, You locate the info of the same first LM_STATE record (which you never touch) from in LM_STDNT. It's no wonder you get no progress, you messed it up.

The code scans for students with no state. So you're fine with the position in the students' table and now want to find the record in TEMP, in LM_STATE as you later told us. Both mine and Mikes code SELECT TEMP in the scan loop body before the locate, that translates to SELECT LM_STATE, not SELECT LM_STDNT. You already have a student at hand with no state set and you want to locate and find the matching state => LM_STATE! You have to look in the right direction.

On top of that, but as a consequence of it you swapped around the matching conditions, which doesn't matter very much as a=b is equivalent to b=a, but VFPs matching differs a bit from that and makes matching depending. At this point, it would be best if field widths of the fields involved would be the same, not one email field c(40) and the other c(30), for example.

And last not least you didn't bracket the combined condition on last and first name. It's working because OR has precedence over AND, but I wouldn't rely on that. Whenever you would extend the conditions this should continue to work and thus it would just be good to bracket the partial conditions that belong as one, in this case the separate comparisons of first and last name always need to be combined into one boolean result of the fullname matching. The precendece of AND does that in this overall condition just ORing one more condition about the mail, but if it gets more complex this could have all kinds of side effects, if you miss out on bracketing then, so just bracket in advance, it also makes code better readable:

Procedure Ma_Test
   lcMsg = "Update LM_STDNT.STATE FIELD"
   =MSGBOX(lcMsg,"MA_TEST PROGRAM",gbOK+giInfo)
   * activate window debug
   * set step on
   lcMsg = "Make sure LM_STATE table is correct!"
   lcMBTitle = "Ready to Update LM_STDNT?"
   If MSGBOX(lcMsg,lcMBTitle,gbYN+giQmark+gf2nd) = grYes
      Use LM_STATE In 0
      Use LM_STDNT In 0
      Select LM_STDNT
      lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))
      Scan For LM_STDNT.State==lcEmptyValue
         * [highlight #EF2929]Select LM_STDNT[/highlight]
         [highlight #FCE94F]Select LM_STATE[/highlight]
         Locate For ([highlight #FCE94F]LM_STATE[/highlight].email = [highlight #FCE94F]LM_STDNT[/highlight].email) Or [highlight #FCE94F]([/highlight][highlight #FCE94F]LM_STATE[/highlight].f_name = [highlight #FCE94F]LM_STDNT[/highlight].f_name And [highlight #FCE94F]LM_STATE[/highlight].l_name = [highlight #FCE94F]LM_STDNT[/highlight].l_name[highlight #FCE94F])[/highlight]
         If Found()
            Replace State With LM_STATE.State [highlight #FCE94F]in LM_STDNT[/highlight]
         Endif
      Endscan
      Close All
   Endif
   Return

The order of a=b vs b=a also is important for the optimization. Foxpro looks for indexes in the table located in anyway, but it's much more suggestive as the seek operation resulting when you think of finding the student email value (on the right hand side) in the state (temp) table email.

And after all that, since now LM_STATE is selected when you do the replace, it becomes important to use the IN clause of the REPLACE. And it's advisable to do so always, even when you know the workarea you replace in is selected. Specifying REPLACE LM_STDNT.State is actually guiding to the right workarea in simple cases, but the usage of REPLACE is not using fully qualified names, but split them in field name names only in the single [tt]field WITH sourcevalue[/tt] clauses and then finally specify IN workarea to tell of which workarea all the fields are on the left hand side of all the [tt]field WITH sourcevalue[/tt] clauses. The sourcevalues indeed are best fully qualified names, when the value isn't just a literal value but has a source, especially when its a field with same name, so there it is correct.

It makes it redundant to say REPLACE LM_STDNT.field WITH x IN LM_STDNT as the IN already addresses the LM_STDNT workarea. VFP will understand that you don't mean LM_STDNT.LM_STDNT.State.
It makes it dangerous to REPLACE LM_STDNT.field WITH x without an IN clause when another workarea is currently selected as there always is the implicit IN clasue IN "current workarea". That's a very stringent way Foxpro works in a current workarea. But if now LM_STATE is selected and you REPLACE LM_STDNT.State WITH LM_STATE.STATE, what should the implicit LM_STATE.LM_STDNT.State mean? You'll be lucky Foxpro then actually interprets that the way you intend it to mean LM_STDNT.State, but I wouldn't rely on it in more complex situations with more open tables.

You may also first manually SELECT LM_STDNT again, and do what the SCAN loop will do anyway, as the REPLACE Actually also should work in LM_STDNT. It's that table, that's missing the state. But again, that also means you can't hope to find the missing state it in the LM_STDNT dbf, can you. The locate for finding the state must be in LM_STATE, that should be as clear as it can be. So you have a scanloop that switches back and forth between two workareas, as the LOCATE has no IN clause and can't be made to Locate in another table than the currently selected one.

See how much I need to explain. People always complain SQL is complex and not easy to understand, but when you're not in the mindset about current workareas this is even harder to see then what a more or less complex SQL query does.

Finally, if you actually would need to locate for the state information in other LM_SDNT records, this would have an awful effect, do you spot it? it's totally possible the info could be in another LM_STDNT record. But then you iterate rows of LM_STDNT and then jump forth and back in that table. How should a loop on that ever find an exit? You're moving the loop pointer within the loop. This is the recipe for endless loops and that's what you did encounter. In the end you just mistranslated what was given to you. You had all the time to give us the real names in the first place and then wouldnÄt mistranslate this back to the real names.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Just by the way, Mikes amended code still has this most blatant error f locating in LM_STDNT.

You are already at a record in LM_STDNT and want to find the LM_STATE to get the State.

Anyway, it reminds me of the fact Griff had this suggestion/question:

If temp has all the records in it that are in your original database, perhaps you could zap the original and just append from

You already reacted to this saying

the temp database only has the few fields and was created to help get the customer.dbf updated.

So we know a bit more about temp just being a subset of fields. It's still unclear, what records are in there. If you only have the few students in LM_STATE, that need to be updated in LM_STDNT then it also would make sense to SCAN through them and LOCATE in LM_STDNT. Especially if this is the few corrective rows, then you want to process all of them. Mike and I concentrated on the other point of view, on the fact you want to correct LM_STDNT with empty state info. You might not even have all missing info in LM_STATE.

It would actually be good to tell such things, as we can only make assumptions about the number of rows and which rows matter the most. With the information as given both Mike and me concluded to first search for LM_STDNT with empty state is important to possibly mend those it was the safest way to handle this, we could be sure this is working. When you have all corrective records you could get in LM_STATE then this drastically changes the best strategy. Because it actually removes the effort to first find a LM_STDNT missing its state information, we already know every row of LM_STATE has corrective information that needs to be sorted to the right LM_STDNT record.

See? Giving out the problem statement in the most relevant way also is important. Because I can easily see how you mistranslated our solutions if you have that in front of you and in your mind. But you didn't tell us. Then it makes almost no sense to scan through all, perhaps thousands of LM_STDNT when all you have is a few LM_STATE data corrections.

What is known from the outset of the fields existing in LM_STATE about LM_STDNT mail and name, it's clear there is a 1:1 relationship between them no matter what fields more each table has, so you can tackle it from both sides. Actually, as you are not specific about that it could also be LM_STATE has multiple records for an email, but far less likely.

It makes sense to tackle it from the side that has fewer rows in it. But then, when you turn it around, also watch out whether you really want to update an LM_STDNT with LM_STATE.state, when LM_STDNT already has a state. Another reason both Mike and me got the idea independently to tackle this from the LM_STDNT side and first find rows there missing information.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[jlg13]

Hi Olaf,

Thanks so much for the detail, the explanation is very educational for me. I admit, I will have to read it a few times to let it sink in..

I just want to make sure to remind you we are dealing with Fox 2.6 here. I took the code above and received a compile error.

Replace State With LM_STATE.State in LM_STDNT
Error in line 18: Unrecognized phrase/keyword in command.

Did I miss something that I was supposed to adjust from the code displayed?

Thank you,
Joe

 

[Mike Lewis]

Joe, I'm not sure which is line 18, but if it is the REPLACE, it might be because the IN clause wasn't supported in 2.6. But I have now way of checking that. (Don't you have some documentation for 2.6? If so, when you get this type of error, you should always check the docs to see the correct syntax.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[jlg13]

Hi Mike, you are correct, it's the Replace line. Apologies. I edited my last post to include the code.
RE: Documentation: It's all at my dad's house however, I suspect I could find that on online. Next would be understanding it (Novice)

 

[Mike Lewis]

In the absence of an IN clause, you would have to explicitly select the student table before doing the REPLACE, and then select the state table again afterwards:

Select LM_STATE
Locate For (LM_STATE.email = LM_STDNT.email) Or (LM_STATE.f_name = LM_STDNT.f_name And LM_STATE.l_name = LM_STDNT.l_name)
If Found()
    [highlight #FCE94F]Select LM_STDNT[/highlight]
    Replace State With LM_STATE.State 
    [highlight #FCE94F]Select LM_STATE[/highlight]
Endif

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Sorry about tha, Joe, but it should also be clear were working on never before encountered territory here (from my side) or long left.
That the VFPX help has 2.6 help topics helps a bit, but for example it does not have the historic description of REPLACE, as REPLACE still is a current command, and has more features now, it seems.

That's again something I would never have expected, but then again LOCATE doesn't support an IN clause even today. Also really an annoyance.
Yes, Mike sorted that out, then you have to explicitly SELECT LM_STDNT first before doing the REPLACE without the IN clause.

The rest of it would be to have some indexes but before we need to guess what works in 2.6 and what not just use that now and see whether it's sufficiently fast already.
I don't think yoú have to reselect LM_STATE after the replace, as Mike does, because next up is the ENDSCAN and that does that itself. Unless Fox2.6 differs from that again.
So, it actually can't hurt, but it would be optional, if you can simply check what this tells you about automatically selecting the scanned workarea:

CREATE CURSOR crsTest (f1 int)
APPEND BLANK
CREATE CURSOR crsLoop (f2 int)
APPEND BLANK
APPEND BLANK
SCAN
   ? ALIAS()
   SELECT crsTest
ENDSCAN

And I don't know if ALIAS() is available.
If SCAN/ENDSCAN automatically reselects the crsloop that was selected at first entry of the scan..endscan loop, the alias displayed will always be crsLoop. If not, the alias will be crsTest for the second iteration.

Edit: If ENDSCAN would SKIP 1 in whatever is the current workarea and exit at EOF() there, you would expect just one output of "crsLoop" and no second iteration. If ENDSCAN does SKIP in crsLoop but doesn't select it you would still get two iterations and a switch of the alias name. So such a test code would reveal that behavior as you also don't have the docs at hand.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

I did a few more tests of REPLACE behavior and am puzzled a bit about the last REPLACE here.

On Error ? Lineno(), Message()
Clear

Create Cursor crsOne (iInt Int, iInt1 Int)
Insert Into crsOne Values (10,11)
Create Cursor crsTwo (iInt Int, iInt2 Int)
Insert Into crsTwo Values (20,22)

? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 10,11,20,22

Select crsOne
* Skip 1
* ambiguous name, but unambiguos in the current workarea
Replace iInt With 30
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 30,11,20,22

* unambigous name in the other workarea
Replace iInt2 With 40 && fails with "Variable 'iint2' is not found"
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && values as before

* fully qualified name addressing
Replace crsTwo.iInt2 With 50
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 30, 11, 20, 50 && stays 22 after SKIP 1, though

* explicit workarea addressing with IN (only in modern FoxPro)
Replace iInt2 With 60 in crsTwo
? crsOne.iInt, crsOne.iInt1, crsTwo.iInt, crsTwo.iInt2 && 30, 11, 20, 60

I intentionally create two fields of same name in the two cursors and one differing to see what REPLACE addresses. As expected it works in the current workarea. That's why I also expected the error about the third replace of iInt2, because all the time crsOne is selected current workarea and has no iInt2 field. But when you actually address it with fully qualified name it is set.

I thought I know REPLACE only works by the rules
1. Act on fields of the current workarea or workarea given by IN clause (not available in 2.6, obviously)
2. Act on other workareas when a relation is set between current and other workareas

It seems to be enough to be in any current not on end-of-file position (which is third rule) and you can act on the current record of any open workarea by the fully qualified name. On one side that opens up a lot of possibilities, but also a lot of possible misbehavior. At least your replace can't bleed into other workareas unintentional just by the same field names and you can only address other workareas intentional by the full name. But indeed you can. At least in VFP9.

So you could even keep LM_STATE the current workarea and REPLACE LM_STDNT.State WITH LM_STATE.State

With the IN clause available I would rewrite that as REPLACE State WITH LM_STATE.State IN LM_STDNT, maybe just because I'm more used to that addressing. Partly as a defense of the case that could break all code depending on the current workarea to be correct: When a timer event switches to another workarea without resetting that. Partly as it's verbose and documents better what's done, as the last SELECT workarea may be many lines up in the code. REPLACE LM_STDNT.State WITH LM_STATE.State would also cover all aspects of being verbose and is self-documenting explicitly, but the difference is about which EOF is important. You'll not be able to replace something at EOF anyway, but see what happens when you uncomment the SKIP 1. As you're now on EOF('crsOne') no replace does anything, despite the erroneous still causing an error. A replace hitting EOF in crsOne doesn't replace there - because there is no data to replace in a nonexisting row. It's okay, but slips through silently. The replace of the fully qualified field name crsTwo.iInt2 also is not happening, because EOF('crsOne') is hindering that, we're not one EOF('crsTwo'), though, and thus a replace IN crsTwo is possible and works with an IN clasue available.

That's a very delicate detail, and you have no timer working in parallel, especially no such rogue timer doing unforgivable things. So you don't depend that much on the IN clause anyway. You're also not at EOF in both workareas by definition, the one you scan is not at EOF, the other in which you located AND found something is also not at EOF - Found is nothing but checking NOT EOF, actually. But that this works is new to me. And it should also be clear why I prefer IN workarea to have full control which EOF() is of importance.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

All very interesting, Olaf. To be honest, I've never felt sure about the use of alias.fieldname as the target for the replace, as in:

SELECT csrOne
REPLACE csrTwo.Int2 WITH 50

I've seen the same behaviour that you described where the replacement appears to have worked until you skip. I've generally avoided using an alias.fieldname in this way, and tend to always explicity use the IN clause.

The rest of your code appears to work exactly as I would expect.

Regarding your point re using a timer. That seems to be a strong argument for saying that a timer event should never do things like change the work area or the index order; it should only ever do things that can have no effect anywhere else, such as displaying a message.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

I agree with you on the derived rules for timer, but then for example I do work on tables in VFPTransactions and the timer I use triggers a logque to do something. Most of the objects involve will work in different datasessions, but for reasons of participating in a transaction, I also work in the current datasession, effectively. What I never touch itself is the table triggering the dbc triggers, I only make a copy of its record with SCATTER and then leave it to other (inherited) trigger code to establish referential integrity or other things like cascading actions. I also cascade this trigger, but only to log tables and delay actions by queueing them.

But with referential integrity stored procedures you have another category of code that acts triggered by changes in workareas and that can be triggered simply ba the scan loop leaving off a record with a row buffered value, so it also has no usual call and is an event happening.

It's obviously important to set things back in timers. How sure can you be about third party code or an application you work on for a job? Indeed such things should be avoided at all costs. I still see no reason to relax the conditions on the other side, outside of timers. Just because I set up strict rules for timers, there are always unpredictable things, even other developers changing your code. If you do both you will have the least possibility problematic situations ever occur. Wearing a seatbelt and driving below speed limits as a driver and looking before crossing a street as a pedestrian. Why should you disregard one thing?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[jlg13]

Gents,

THANK YOU...

Just wanted to paste the final code below that worked great. Thank you both for all this information and effort. Not only did you help solve the problem but the discussion was very educational for me. I know how to figure out how to get State to update along with other data so I don't have to run this program weekly to keep syncing...

I was wondering if either of you could share information on how difficult the task would be to migrate from 2.6 to VFP 9.0. I have 2 platforms that run on fox 2.6 and one of them is VERY basic and would probably not require much work at all... literally one PRG supported by a couple of startup prgs and a series of screens. My dad worked on that for a few months, a few days per week as needs arose. The other platform is a series of 30+ prgs and screens and operates a "Billing" company. Just curious of your thoughts...

Procedure Ma_Test
   lcMsg = "Update LM_STDNT.STATE FIELD"
   =MSGBOX(lcMsg,"MA_TEST PROGRAM",gbOK+giInfo)
   * activate window debug
   * set step on
   lcMsg = "Make sure LM_STATE table is correct!"
   lcMBTitle = "Ready to Update LM_STDNT?"
   If MSGBOX(lcMsg,lcMBTitle,gbYN+giQmark+gf2nd) = grYes
      Use LM_STATE In 0
      Use LM_STDNT In 0
      Select LM_STDNT
      lcEmptyValue = SPACE(LEN(LM_STDNT.STATE))
      Scan For LM_STDNT.State==lcEmptyValue
      Select LM_STATE
      Locate For (LM_STATE.email = LM_STDNT.email);
       Or (LM_STATE.f_name = LM_STDNT.f_name And LM_STATE.l_name = LM_STDNT.l_name)
      If Found()
        Select LM_STDNT
        Replace State With LM_STATE.State 
        Select LM_STATE
      Endif 
      Endscan
      Close All
   Endif
   Return

P.S. MIKE.... Sorry I did not follow your instruction above about pasting code. I will review and learn for next time. Thank you again

 

[Olaf Doschke]

First, it's not hard to try.

When you open a project copy in VFP9 you get prompted for doing a visual or functional conversion. I think if I understood someone correctly you better not go through that, but simply add your SPR (screens) etc. as is to a new VFP0 PJX and thus stay with screens. You'll need to then do without the screen designer and work on the SPR code itself.

I never had to deal with a real conversion. So perhaps rather wait for Mikes and others advice. What I can point out is that along the way VFP changed a few things cause screens to look much different in VFP,
one thing to do is to turn off theme support of VFP by setting _SCREEN.Themes = .F.

With _SCREEN.Themes = .T. one bug with screens is that the interface may disappear and only appear once you move the mouse over inputs, etc. You might have reports and they surely will not use new features so SET REPOTBEHAVIOR 80 or keep it at that, unless you develop new reports if there are any at all now.

Then, there is also SET ENGINEBEHAAVIOR about SQL, but I think we don't need to talk about that.

-----------

About posting code, you can still change your post, there is an edit link at the bottom right of your post. When you're in the editing page you'll first see the post as it shows on site then the "source code" of it in the textarea with toolbar as known. Just select your code in the area and click on the code icon. It's the <> brackets on a sheet of paper icon, between the person with a speech bubble (quote) and the yellow gift wrapped with a red ribbon (spoiler) You can also use that spoiler icon. Just kidding, it'd put your code in a box that hides its content unless you select it.

These icons add the necessary tags around your selection, which also is valid for the simpler ones like writing bold or italic or underlined text, it always acts on the selection, with the usual irregular things like clicking on the image icon starts a dialog/process to upload a picture and that's put where the cursor is.

When I post I let the code icon create the open/close tags and then copy my code in between. Or I simply type them.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

It's a very long time since I had to convert a program from 2.x to Visual, so what I am about to say subject to my ageing memory.

Converting PRGs is relatively easy. In fact, most of the time, you won't have to change anything at all. The code should work almost exactly the same. There a few cases where backward comp ability has been broken, but they probably won't affect you. The biggest danger is that your 2.6 code might use certain names for variables or fields that have since become reserved words. These will become apparent as you run your code.

Converting screens is more problematical. In short, you won't be able to use your old SCX files, as these have an entirely different format. However, your generated SPRs should continue to run. These are essentially program files incorporating @/SAY and @/GET commands, and these work the same as before.

If you try to open an old SCX in VFP's form designer, it will give you the option of a functional or a visual conversion. Neither is satisfactory. I won't go into the reasons (unless you really want me to). I will just repeat what I said above. Discard your old SCXs (and SCTs) and just run the SPR.

All of the above will provide a route to your conversion. But a much better approach would be to re-design your application from scratch - or at least the visual parts of it. Learn how to use the VFP form designer and the many visual controls that come with VFP (textboxes, buttons, pageframes, combo boxes, etc/). Then create a form for each of your old screens. You will be able to copy and paste much of the snippets from your old screens into the relevant methods and events of your new forms, but it really does make sense to design the forms themselves from the ground up.

A similar approach is suitable for reports. Get to know the VFP report designer, and create a new set of reports.

Good luck with this. It's not a particularly easy process, but it is do-able. Just jump in and make a start. Be sure to keep a separate copy of your old programs so that you can continue to use these during the course of the conversion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Dan Olsson]

I agree with Mike and Olaf (as so often) but I recall doing a visual conversion ca 25 years ago. It "kind of" worked initially, but maintaining those "forms" is pretty tough.
That is mainly because the converter for some reason puts all forms (called pages) into a formset (called pageframes). The result of this is that you can't just click on a textbox (or anything) and get that selected. You have press Ctrl and click to get through.

 

[Mike Lewis]

I think you're right, Dan. That was one of the issues that I remember seeing. Also, in the visual conversion, all the code snippets from the screen were placed in a PRG file, and it was a hassle trying to match that up with the relevant events and methods of the form. I'm definitely glad I chose not to go down that route.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads