record number past end of table error after upgrade from v10

[Briddba]

Hello,
Since upgrading from v10 to v12, a few previously successful operations are now saying "Error Running �.Operation - Record number is past end of table". It's happening on some, but not all, of our Appends and Posts (we run a nightly script that includes many).

I've attached one example in a test adb, 'Upgrade_v12.adb'. Our actual adb is too unwieldy, and this tiny one exhibits the exact same error.
In the test adb, Purchst.dbf contains 19488 records. Pkl_prch.dbf contains 0 records.

The operation 'ps_Purchhst_2_pklprch1' is supposed to append all records from Purchhst.dbf to Pkl_prch.dbf. Every time I run it, I get the error.

The equivalent operations in our actual .adb had been working fine for over a year. As soon as we upgraded to v12, I got the errors. Nothing else in our environment changed on the day we did the upgrade.

A few other notes:
1. Our Dev and Runtime machines are all patched with the release from 12/4
2. My Dev is Version 12.2, Build 1794
3. I tried running the append placing both dbf's in the same folder and got the same error.
4. I re-created these tables and the operation from scratch in this test adb (after trying them first on copies).
5. There are no field rules on either of the tables.
6. I can grasp a little code, but we don't use it to write our applications - it's 99% Action Scripting
7. Most interestingly, after I zipped the test adb, I extracted it in another folder, ran the append using A5v10, and it worked perfectly.

Any ideas of what else I could look at?
Thank you very much!

[Stan Mathews]

Re: record number past end of table error after upgrade from v10

Your sample doesn't include the pkl_prch table due to it being in another directory.

[Briddba]

Re: record number past end of table error after upgrade from v10

Sorry - should have specified that you need to add it to the adb from the included sandybox/ops_test_folder2 - that's where the pkl_prch.dbf is. I wanted to have it in a separate folder to simulate the actual conditions.

Thanks

[Stan Mathews]

Re: record number past end of table error after upgrade from v10

Strange.

Same results here. The debault browse showed no records.

Then I manually added a bogus record and the append worked. Emptied and packed the table, again the append doesn't work. (This is in V11)

[Tom Cone Jr]

Re: record number past end of table error after upgrade from v10

Brien,

Several things seem odd to me.

1) your transaction table is in the workspace directory, but your master table is in a subdirectory of a subdirectory of the workspace directory. This seems backwards to me.

2) your append specification is for "Unique" records, using a single field link. Yet your transaction table does not contain unique link field values. There are many records with duplicate link field values. How is the append operation supposed to determine which one is to be "appended"?

3) your append specification is for "unique" records, but the master table is empty. There are no records there for the "unique" comparison to process. This, coupled with the fact that the link field values are not unique in the transaction table, is apt to cause confusion. I would never ask Alpha to append Unique records if the master table were empty. Seems pointless. Do you mean to initially append all the transaction records? That's going to lead to trouble, too, since your link field values are not unique.

I say again. Which records are you trying to append?

-- tom

[Briddba]

Re: record number past end of table error after upgrade from v10

Tom,
Here are the answers to your questions. I'll try to give you the info you need, but spare you the vagaries of our particular set-up.

1) The transaction table (purchhst, in the example) exists in an adb used by one department. It is a list of purchases (PARTGROUPS) made by individuals (EMID's and XEMID's). The master table (pkl_prch) exists in a directory that is used to pass the purchase information to another department via another set of operations. Simply put, our company is divided into two "sides", and information is passed from one to another via a "pickle in the middle" adb, hence the name pkl_prch.

2) Many EMID's purchase many Partgroups. In this particular case, all we care about is one of those purchases - that's why it's Unique. A person works for a company, and once that person makes a purchase for their company, that company gets tagged with a purchase. We only need to know about one.

3) The master table is empty because it gets zapped prior to the append. The append is meant to send over one EMID's purchase to pkl_prch (there is a second set of operations that handles the XEMID purchases - let's forget about them for now!). Does that answer your question about uniqueness? The "master" table in this case is kind of a misnomer in that it's just a transit point to a real master table that exists in another adb.

Finally, I'd just like to ask again why this might work in v10 but not in v11 (per Stan's test) or v12? Is it just that Alpha has gotten more sensitive to wacky database stuctures and finally called us out? It may not be elegant, but it has worked for quite a while. I'm more than willing to try to re-structure things if needed, but I did want to make sure that was required before I set to it.

Happy to give you more info if you need/want it - thanks for the help!
-Brien

[WokingJon]

Re: record number past end of table error after upgrade from v10

Then I manually added a bogus record and the append worked. Emptied and packed the table, again the append doesn't work. (This is in V11)

Brien, As adding a dummy record worked for Stan could this be a quick and easy way to solve your problem - empty & pack master, add dummy record, run append, delete dummy record.

[Tom Cone Jr]

Re: record number past end of table error after upgrade from v10

Brien, thanks.

I can confirm the behavior you're seeing using v12. I suggest you submit a formal bug report using the example you supplied here, with supplemental instructions telling them how to configure the sample on their system. It's likely their setup is different from yours, so the paths you used in your sample won't exist there if you see what I mean.

In addition to what Stan discovered, another approach here would be to preprocess the "transaction table" by running a summary operation against it to create a result table containing a single record for each unique emid. Then change your existing append to "ALL" records, and use the new result table as transaction.

Yet another approach would involve writing xbasic code to step through the transaction table to find and copy each unique emid record. This would make it unnecessary to use the append operation at all.

[Steve Wood]

Re: record number past end of table error after upgrade from v10

If you change the linking key to "str(emid,8,0)" on both sides, it will work.

To figure that out, I created a brand new Append operation in V12. I noted that it took my numeric key fields (named ID, size 8), and converted it to str(ID,8,0) in the Append operation.

[Tom Cone Jr]

Re: record number past end of table error after upgrade from v10

Hey, Steve, that's good to know.

Why do you suppose they do that?

This may be required when the append op is set to "Unique".... converting all parts of the link expression to character for consistency. Would make the link field comparison code they had to write easier if all parts of the link expression were of the same data type.

The fact that it mangles comparisons when the master table is empty is still a bug, in my opinion, so I hope a formal bug report is submitted.

[Briddba]

Re: record number past end of table error after upgrade from v10

Thanks for the input, everyone.

Since changing the linking key per Steve's post would be the fastest/easiest, I'll try that one first as soon as I get a chance (hopefully this evening).

I'll also submit my test adb w/instructions in a bug report on Monday, since it sounds like that would still be appropriate.

I'll post here again with my results once I've made the changes and given things a try - again, thanks to all!
-Brien

[Al Buchholz]

Re: record number past end of table error after upgrade from v10

FWIW

I noticed the jump from v10 to v12.

So I tried v11 - works like v12.

The str() workaround also works in v11.

[Briddba]

Re: record number past end of table error after upgrade from v10

Complete success!
I updated the linking keys in all 9 of my failing operations (7 posts and 2 appends) to the str() format and my script ran all the way through with no errors. I didn't even re-create the operations, just added the additional syntax. Now that I think about it, I realize that whenever I use the Genie to write the operation, it uses that str() format rather than just the fieldname, so I'm definitely going to re-standardize how I enter my linking keys when I'm not using the Genie. I figured that the two ways were interchangeable since I'd not had trouble before...

So yet another big thanks to everyone for the input and help in getting this set of operations back in working order. I'll still submit the bug report on Monday in the event that this might point to something that can or needs to be "fixed".

If I've left out any details that might help the thread, let me know and I'll update - otherwise, thanks again and cheers!
-Brien

[Briddba]

Re: record number past end of table error after upgrade from v10

FYI - I submitted the bug report on Dec 9 and heard back from Selwyn the same day that a fix would be available in a pre-release update as of Dec 10.