Problem with replace command... 4

[Mandy_crw]

I have this code... but i wonder sometimes it doesnt perform replacing... particularly in alias tsulat... please help... thanks...

CASE This.Parent.cboSFBBU.List(This.Parent.cboSFBBU.ListIndex,1) = "Others" AND this.parent.cbopp1.VALUE = 1

Aders = INPUTBOX("Particulars:", "Log in Credential","", 15000, 'Timed Out', 'Canceled')

SELECT trans
IF FLOCK()
APPEND BLANK
REPLACE TRANS.IDNUM WITH csrDemo.idnum
REPLACE trans.transcode WITH "OT"
REPLACE trans.transtype WITH "PAYMENT"
REPLACE TRANS.TRANSAMT WITH this.parent.text14.value
REPLACE TRANS.DEYT WITH DATETIME()
REPLACE trans.particular WITH aders
REPLACE tsulat.pothers WITH tsulat.pothers + this.Parent.text14.value

MESSAGEBOX("Payment " + ALLTRIM(TRANSFORM(this.parent.text14.value, "###,###,###.##")) + " Recorded to OTHERS!",0+64,"Chenevu")

 

[Mike Lewis]

Mandy,

This is a familiar issue, and I'm sure I have discussed it with you before (look back over some of your recent threads).

The problem is that your last REPLACE is updating a field in Tsulat, even though the selected work area is Trans. The REPLACE will indeed update Tsulat, but the command is scoped to the current work area, which is Trans. Remember, the default scope of REPLACE is the next record. So if Trans is at end of file, or beyond the last record in a filtered set, or on a deleted record, then nothing will happen.

The advice is always to use the IN clause in a REPLACE. So, in this case: [tt]REPLACE tsulat.pothers WITH .... [highlight #FCE94F]IN Tsulat[/highlight][/tt].

A couple of other issues come to mind:

- If the user cancels the Input Box or if the Input Box times out, the value of Aders will not be set. But you are still doing the updates, which is presumably not what you want. (And by the way, there are good reasons never to use a timeout with an input box or a message box, but let's leave that out of the discussion for now.)

- If you fail to get a lock on Trans, you updates won't run. You should be testing for that, and taking appropriate action.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rick C. Hodgin]

I would advise a couple changes:

1. Use a single REPLACE statement where possible. It's faster.
2. Explicitly select your work areas. It's easier to identify what's taking place for ongoing maintenance.

SELECT TRANS
REPLACE IDNUM      WITH csrDemo.idnum, ;
        transcode  WITH "OT", ;
        transtype  WITH "PAYMENT", ;
        TRANSAMT   WITH this.parent.text14.value, ;
        DEYT       WITH DATETIME(), ;
        particular WITH aders

SELECT tsulat
REPLACE pothers WITH pothers + this.Parent.text14.value

Two other things I do, are:
1. Use names other than text14, so that it has contextual meaning.
2. Store the value of this.parent.text14.value to a variable, and reference the variable by its contextual name in code.

It's better for ongoing maintenance to be a little inefficient, and a lot more clear in code.

--
Rick C. Hodgin

 

[TamarGranor]

My two cents here:

1) ALWAYS use the IN clause with REPLACE, so there's no ambiguity about which work area you're replacing in.

2) NEVER allow a single REPLACE command to replace fields in more than one table. If you take this advice, there is never a reason to put the alias on the "left-hand side" (that is, field named before WITH). It's my view that writing REPLACE alias.field WITH something is confusing and should thus be avoided.

So my version of this would be:
[pre]
REPLACE IDNUM WITH csrDemo.idnum, ;
transcode WITH "OT", ;
transtype WITH "PAYMENT", ;
TRANSAMT WITH this.parent.text14.value, ;
DEYT WITH DATETIME(), ;
particular WITH aders ;
IN trans


REPLACE pothers WITH pothers + this.Parent.text14.value IN tslat[/pre]

Tamar

 

[Chris Miller]

I'd not throw out the possibility of multi workarea replaces, I'd just require SET RELATÌONS and in case that's a 1:n match also SET SKIP TO set, so that you have exactly one detail record for a head record in which you do replace detail values. The only downside is, that this SKIP TO setting stays on the same head record for each of the detail records, so you do n times the same replacement on them, if you don't cover that case by only including head record fields in a first of a series of replaces.

Chriss

 

[GriffMG]

I wouldn't want to contradict the other posters, but personally I like one line per field replaced, only because I can then find all the replaces for 'pothers' throughout a project
really quickly - it may, nominally, slow things in the compiled app... but it's also good for debugging, because you get error messages related to a specific field being replaced
based on the line number rather than having to work out which field out of ten or whatever had the typo against it...

SELECT TRANS
APPEND BLANK
REPLACE IDNUM 		WITH CSRDEMO.IDNUM
REPLACE TRANSCODE 	WITH "OT"
REPLACE TRANSTYPE 	WITH "PAYMENT"
REPLACE TRANSAMT 	WITH THIS.PARENT.TEXT14.VALUE
REPLACE DEYT 		WITH DATETIME()
REPLACE PARTICULAR 	WITH ADERS

SELECT TSULAT
REPLACE POTHERS 	WITH POTHERS + TRANS.TRANSAMT

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

It can really have a big impact on performance, for example on audit trail that copies a whole record into a history of records, that generated 6 records in transhistory instead of 1. But even whitchout that, updating indexes 6 times instead of once, checking field and table rules 6 times instead of once. RI checks are more commonlyused than audit trail as VFP offers an RI builder, that also costs 6 timesmore checks than necessary.

It's not true you don't get pointed out which field causes a problem, if that's the problem, see this example:

CREATE CURSOR test (f1 i, f2 i, f3 i)
APPEND BLANK
REPLACE f1 WITH 1, f2 WITH 2, g3 WITH 3

You get "variable g3 is not found. While that's besides the point as it's the g3 field not existing, not a variable, you just need to keep in mind how VFP resolves names and prioritizes fields over variables, so it looks for variables last and when even not finding them reports a variable as missing. That's a bit of an annoyance and could perhaps be better, you get pointed to g3 and even without knowing about the name resolution of VFP can see this is the problematic part. By the way, the same applies if you have a field g3 and try to replace f3. In your head don't read "variable" in error mesages, but "thing that has the name X".

You also can easily search for pothers and get a list of all code handing that field in any way. Which is more practical when not just mending the one occurrance of the typo in the line reported by error handling but look for any line that has the misspelled name, because the same typos have a high probability.

Then there is intellisense and extensions of that to know about the database you use and its table and field and view and other names.

Chriss

 

[GriffMG]

That was a pretty comprehensive slam Chris B-), but I'll stick to my approach, I like being able to search for 'REPLACE POTHERS' and be certain of finding all of them and
only having to work through that short list. I do suppose that if I improved my regular expressions skills I could find lines starting with REPLACE and contain POTHERS...
B-)



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

I didn't meant this as a slam, just to point out what you're missing out. Seems you've already decided to stick with what you know a long time ago.

You could also use GoFish

https://github.com/mattslay/GoFish

which has the wildcard matching you surely also know with * and ?.

https://github.com/mattslay/GoFish

I could go much further and say the love for VFP of many is just based of wrong understandings of other development stacks. First of all, yes they're all rather stacks and not just a single tool like VFP. But you also surely know about the one or other famous tool VFP developers like to add in, Refox, Stonefield, Crystal Reports, FRX2Any, Amyuni PDF Suite, Cryptor, Konxise, virtual PDF printers. And then ActiveX controls from DBI.

The performance of VFP is a legend, but also a wrong one by now. Here is an article that debunked the myth - though not fully - of the string processing performance of VFP vs .NET (here)

https://www.codemag.com/article/030054/Comparing-VFP-String-Performance-to-.NET-String-Performance

It's hard, also for me, I'm also no youngster anymore, to catch up with anything new that has a steep learning curve.

If I'd reorientate myself now I'd not look just for any other programming language but for platforms. That could mean apps, that could mean web or ARM based development. Languagewise Going back To C++, nowadays C++17 with roadmap for 20/23/26 being already paved in a predictable way. And no, you don't have to do lots of things from scratch.

Chriss

 

[ManniB]

Isn't it recommended to use INSERT INTO instead of APPEND BLANK and then REPLACE? In a multi-user environment after an APPEND BLANK you have to check if the record is locked to make sure no other user has locked the table/record in the meantime. This is not the case with INSERT INTO, because a new record is created and the values are inserted in the fields in the same command, hence more secure and less code. Please correct me if I'm wrong

Manni

 

[Chris Miller]

I was just demonstrating the error message you get rom a REPLACE with wrong field names, but ye, an INSERT has several advantages, only checks table and field rules once (if you have them), only updates indexes once. If you append blank you already act on the DBF, buffered or not, and create an autoinc and several default values and index entries that later needs to be updated. In SQL the thought of a default value is not necessary a value a field has to have first, it's just a value you get when you don't specify a value in the INSERT. Only the primary key default is actually quite important and autoinc has done a great improvement as that really minimizes problems with integer keys, it's becoming a readonly field, too, which also removes a source of problems. And in many cases they're also good to use, and easiest to generate.

But REPLACE also is a command sometimes better easier to use then an UPDATE, you don't always deal with new records when you REPLACE.

It also becomes an equivalent to UPDATE with some benefits when you do it in a remote view or updatable cursor of several kinds, though in that case the lack of backend connection regarding the newrecord means you don't get default values the database would generate. Especially from SQL-Server. That's why uniqueidentifiers are fine as primary key to a remote backend, as you can generate this independently from the database and have a final pk already.

And then it's not true you need to lock a record after APPEND BLANK. Just like after an INSERT your record pointer is on that new record. And yes, it's not locked, but another user doing another APPEND BLANK (or also INSERT, as you can mix this) will have his record pointer on that new record, not yours.

So you would need to do something weird like GO BOTTOM to have a collision with concurrent users. What you need to watch out for are indexes that need to be unique, multiple APPEND BLANKS should give different values to such indexes and the fields or expressions that define them, of course. So say you have a candidatekey on a name, then you can't have 2 blank names, of course and a default like "new" also only works once.

I've seen a lot of systems that cope with that by not indexing uniquely or really just working on the basis of having a little user base where usually only one person is working in data, at least on one table, so there are no such cases.

Such a weakness is easily found when you browse a DBF and CTL+Z twice. An example is the northwind.dbc. Open that sample database, USE customers, BROWSE and then do CTRL+Y twice (or do two APPEND BLANKs). So is that bad design? Well, it's usable if you know how to do things in entry and edit forms. It's not really built for APPEND BLANK, that's for sure.

But I wouldn't give much for running time differences. It's more the problems like indexes, bloat effects due to two values (default/final) also in memos, index bloat and such things.

Chriss

 

[ManniB]

Chris, thank you for the detailed explanation. I didn't realize that the record pointers are always on the new record after APPEND BLANK, even if different users are doing it at almost the same time.

We had a problem once when doing APPEND BLANK followed by REPLACE in a network. The result was, that the first record in the table was replaced instead of the blank one, so data was lost. I can't remember exactly the details right now.. So now we first check, if the record is locked or switch to INSERT INTO instead.

I see the problem with unique indexes.

I've also made some performance tests and didn't find much of a difference between APPEND BLANK - REPLACE and INSERT INTO.

Thanks,
Manni

 

[Chris Miller]

I can only imagine the record pointer moving if there is any RELATION involved. That is if the table you APPEND BLANK is the endpoint of a RELATION. Record pointers are part of a workarea, not part of a file. so that's always a local issue, if it is an issue. For it to happen, the pointer in the other table that effects the record pointer in the table you add to has to change, relations aren't reacting to a new record at the endpoint, even if it matches, only an action in the startpoint of a relation locates the first match in the endpoint workarea. If that only happens sometimes it might be because of something only coming together rarely.

But INSERT isn't prone to record pointer movements by relations, too. It's limited to the same client/session, though, as setting a relation to a dbf doesn't sets it to the file, but to the workarea, so that's also always just local. But it could be separated as far as two forms working in the same datasession. But not in another process, even less so on another client.

Oh, and a LOCATE would be the simplest explanation of positioning on recno 1, if there is no filter set. That also could come from anywhere in the same process and datasession. An INSERT would have the advantage that it does add the record and fill it in the same command, there's no timer that can act right between APPEND BLANK and a REPLACE. But that would also rarely happen, even if a timer is due to its interval, I think VFP only executes timer events when it becomes idle. The fear of timers intercepting something would require to allow it happening by a doevents.

Chriss

 

[ManniB]

Chris, there was no relation involved and there were only free tables used (no dbc).

If like you tell, the reason can only be found in the local datasession, then this narrows down the problem. At the time, we suspected that APPEND BLANK wasn't executed correctly, maybe because of a network problem (the table was physically located on another PC), or another instance of the exe has accessed and locked the table, but the REPLACE command after APPEND BLANK was executed so a record was overwritten at the point where the record pointer was before (at the top of the table). Is this conceivable?

Thanks,
Manni

 

[Chris Miller]

Up to now, I said a relation can only cause a local problem.

Of course you can (deliberately or not) leave the new record and go elsewhere in the session/client that does the APPEND BLANK and you can - again deliberately or not - go to the bottom of a dbf in another client to write into a record that was just appended.

But you're saying the session that did the append wrote to record 1 instead of the new one. No other client or session can move your record pointer, that can only be caused locally.

If you can rule out that there is any way to move the record pointer after the APPEND BLANK, the only other reason the client writes to the DBFs record number 1 would be a glitch in the network. After all the client has no physical ownership of the HDD write head, so anything between client and the file could cause it to move to record 1. But that's a very specific position at offset HEADER() - the size of the DBF header. That's VFP runtime internal knowledge. It's also no constant, as the header size varies with number of fields, of course.

So it's much more probable that's related to VFP, it would be strange if a glitch in the OS or network hardware would move the pointer in the file to the exact position of record 1 (that's not the record pointer but common to any file writing - see FSEEK).

So we're back to something in the client code to go top, that's LOCATE a RELATION, a GO TOP, a GO 1 (perhaps programmed instead of SELECT 1) could also be a grid class that has a GO TOP to prevent showing up empty because the record pointer is at EOF and that's showing an empty row in row 1 of the grid.

But it must be something local. And I'd only bet on some code doing that, not just a hardware or network glitch. So now I think I thought that down to the bottom.

Just one question left: Was it a one time only thing or did it occur and only occur with record number 1?

Chriss

 

[ManniB]

Chris, thank you for your insights, actually if I'm remembering it correctly, there was an index active on a field like last name. And the records wrongfully replaced where always the ones where the last name begins with an A, it happened several times. There are actually some GO TOP and GO BOTTOM commands in the code, so you probably have worked it out correctly, that it was never a network glitch, but something in the local code causing the problem under some very seldom circumstances.

 

[Mandy_crw]

Thank you everyone for all your insights... I've read all your points and im learning much about m project.... thanks again everyone...