Lotus 123: Fail to lock file, artihmetic errors

[Jorge Zaldívar]

I am using vDos 2018.05.01 to try and run a payroll application (driven by Lotus 123 macros). Two problems found so far:

1) No file locking. If I open the WK1 file on a real machine and try to open it on a VM (VMWare Workstation Player 12), I get an "Open the file without a reservation" warning, and viceversa. Not so with vDos. I can open the WK1 file at the same time on a real machine (or a VM) and on vDos. It doesn't matter which "computer" opens the file first. 

2) For today's payroll there is a $0.04 difference in the total depending on whether I open the WK1 file on a real machine, a VM (same result as real machine) or vDos (different result). Apparently, there's a $0.03 difference for one employee and a $0.01 difference for other. There may be more differences that cancel each other out, though.

I had tried vDosPlus before and found the same two problems, I was expecting them to be corrected in this new version of vDos.

Regards,

[Jos]

1) Are you sure you’re actually opening the SAME file in the VM and vDos. You would be the very first to report an issue with file locking.

2) There was an error in the first release (and previous version) with vDos FPU correcting semi integers of a DataEase library. That was fixed by a silent update, you can download and install vDos again.

[Jorge Zaldívar]

I downloaded vDos again (from www.vdos.info/vDosSetup.exe) and installed. Problems persisted. Calculations are still off by $0.04, and I can open a Lotus 123 file even if it's open by another instance.

I was pretty sure I was opening the same file (via a batch file) but, to be sure, I created a brand new file and (without closing it) tried and opened it using the other instance. I was able to do that. It didn't matter if I created the file on vDos and opened it on VMWare or viceversa. The files sit on a network share mapped to a drive letter (if that would make a difference).

I don't know why this hasn't been reported earlier... Perhaps because here the issue is not so much about being able to share but the opposite (prevent someone from opening a worksheet that has already been opened by someone else)?

Regards,

[Jos]

For the $0.04 discrepancy I would need some testcase to reproduce that (or simular).

You also have to help me out with “opening the same file”. Using Lotus 2.2, I only see Retrieve: The file is opened, read in, and closed. It isn’t kept open so there would no file locking going on, only perhaps for the duration of reading in the file.

File locking is no separate function: When a program opens a file, it passes on what it wants to do with the file (read and/or write), and what actions are allowed to others that already have the file open or will open it. Restricting actions to/by others is often used, though no issues reported with that.

Jos

[Jorge Zaldívar]

Thanks for your response, Jos.

Re: Difference in calculations.

I am attaching a scaled-down version of the spreadsheet (XNOM.wk1 (34.69 KB)). It took me a while because the original one is kind of big and with a lot of relationships between the several sections... when I deleted some sections, cells that referred to those sections evaluated to error values and the errors propagated. I managed to finally delete most of the material irrelevant to the issue in case.

Also, originally columns Q to AN are hidden, I redisplayed them for your convenience.

I detect the $0.04 difference at cell P20, which can be traced to different values at cells P5 and P6. In turn, you can trace the differences to cells AJ5 and AM6.

Difference at AJ5 relates to differences at AD5, AE5 and AF5, and so on.

Difference at AM6 comes from the evaluation of a formula

I'll comment on the exclusive opening of WK1 files in a different post.

Regards,

[Jos]

When I open the file in Lotus 2.2, I get:
P20 (20,694.41), P5 (756.84) and P6 (736.46) look fine. I guess the rest also.
You’re sure using version 2018.05.01?

Jos

[Jorge Zaldívar]

That's also what I get when I open the file under vDos.

However, when I open the file using a real machine (or a VMWare VM) I get different numbers for cells P20, P5 and P6:



I may not have explained myself correctly: The discrepance is between the results obtained under vDos as compared to those obtained under a real machine.

As for being sure I'm using 2018.05.01, I'm as sure as I can be. It's a fresh installation of a file I downloaded from the official site on Saturday. There's no version information on the filename itself but the version reported by Windows is 2018.05.01:


Regards,

[Jos]

I thought you meant the discrepancy was with the horizontal totals of N5-O5 and N6-O6 to the sum of the three columns.

The error is caused by the cells AD5, AE5 and AF5. To demonstrate:

Obviously A5 should calculate to 0.02, not 0.01. Now to find out how 123 implements rounding and why it goes wrong. Too busy right now, I’ll give it a try end this week.

Jos

[Jorge Zaldívar]

Yes, something funny is going on:


It's precision-related, isn't it? In the previous screenshot, the values on column A were entered manually. If I calculate them adding 0.01 to the cell above, I get different results:


I know 0.01 is not a power of 2. The real problem here is lack of consistency. On a real machine, precision is also an issue, but the results are affected differently than under vDos:

[Jorge Zaldívar]

Now, regarding the file lock.

I don't know how this is handled by the OS, but in "Lotus 123 parlance" when a file is opened, a "reservation" is put on it. So that when another user tries to open the same file they will receive a warning:


So that they are aware that they can browse the file but that they won't be able to save any changes they make.

A reservation is not released until you close the file, either by quiting 123 (/Quit), opening another file (/File Retrieve, Lotus 123 can't open several files simultaneously), or "erasing" it (/Worksheet Erase, it's the equivalent to close). Or you can release the reservation manually without closing the file (/File Admin Reservation Release).

Usually, you want to have the reservation until you're done with the file and close it. Potential of data loss occurs otherwise, say, for example:

1. User A opens the file, makes changes.
2. User B opens the file, saves, closes.
3. User A saves, closes.

User B's changes will be lost without anyone being any the wiser.

I noticed the issue of no reservation being put on a WK1 file opened under vDos when working with the payroll application. I was working on it on my computer (A) so I was positive I had it opened, I moved from my desk and then I had to check something from it, so I opened it from another computer (B) (I was prepared to open it without a reservation, i.e., read-only) but I was given no warning, how come!


I returned to my desk expecting to find my computer off from some accidental power loss, but no, my computer was on and the file was there, open!

The results from my tests show that:

If I first open the file on a real machine or a VM (VMWare Player), I can't open it on another real machine or VM without receiving the "open file without a reservation" warning. However I can open it under vDos without any warning.

If the file is opened first on vDos, I can open it on a real machine or a VM or another vDos without any warning. That's not how it's supposed to work.

[Jorge Zaldívar]

Found something, may be relevant.

On a real machine, or a VM, if I open two instances of Lotus 123, I can open the same file on both instances provided the file sits on a local drive. If the file resides on a network share, I cannot open the same file in both instances.

When I say I can or can't open the file, I mean "without receiving the open without a reservation warning".

Perhaps vDos is treating all disk (even network shares) as local?

[Jos]

First, it shouldn’t matter if a file is local or not. Some applications open files actually exclusively if they are local. It’s single user, no others to concern with, or even another version/license is needed for multiuser/network.
Second, vDos doesn’t set privileges at opening files on local or networked drives, the DOS program does so.

Where to find “Open a file” in Lotus 123, I only see Retrieve.

Jos

[Jorge Zaldívar]

/File Retrieve is how you open a file... That's it. There's no File Open command.

[Jos]

Retrieve would be Open – Read – Close, all short-lived. I’ll have a look at that…

Jos

[Jos]

It’s no good to use floating point numbers (the FPU), if you actually need fixed precision numbers. In my DOS days I just didn’t, but you’re tied to Lotus.

You’ll get into problems with a=1; b=a+0; a-b not being 0 (or some alike demonstration). Getting worse by a DOS program not having a real FPU, but a simulated one (in vDos), relying on that (also simulated) of the VC runtime.

The simple @round(cell, 2) generates 34 FPU instructions. It boils down to 0.015 not being exactly 0.015, but 0.01499….44 in VC doubles. I corrected the result of the offending FPU multiply operation. Don’t care that much about real floating point numbers, so I’ll add a normalization routine to FPU operations in the next vDos version (not just FPU multiply).

Retrieving a file seems OK, Lotus opens the file, locks it contents by record locking, reads in the contents, unlocks the lock, and closes the file. Something would be wrong with Lotus logic when to lock, or determine if a file is remotely hosted?