Hi All

This isn't particularily serious, it's only my test database and I can restore it, but I'd die if this happened on the client site (their data restores are more traumatic).

All the records in one of my tables seem to have gone missing. I can do an rlist on the file, and I get a list of keys but if I edit one of these records, there is no data in it.

This has occurred sometime in the last month and may be related to my testing with Windows 2000 systems. I don't know.

The actual dos files have gone from around 269KB on the .LK and 181KB on the .OV to 16K on the .LK and 0 on the .OV (I think the 16K is from a recent record I created in the file).

I did a verify on the remnant file and it thinks it is ok (No GPFs). I also did a verify on the old (restored) file and this also - No GPFs.

It could be something else I did and can't remember now but if anyone else has had a problem like this, I'd like to know.

Now I have to write a program to check all the other files against the restore, to see how big the problem is. ARGH

Scott

Sounds as though all records other than %RECORDS% have gone… can you edit the dos file directly to verify this?

The Sprezzatura Group

World Leaders in all things RevSoft

Call me superstitious, but do you have any relational indexes on this file? I had a similar thing happen once and it was caused by corrupt relational indexes.

Hi All

I edited the 16K file with notepad, and it looks like %RECORDS% might be the only thing left ie a list of keys.

Yes I think it has relational indexes. So maybe I should do a verify on the !Table.

Data is definitely gone, as reflected in the much smaller file sizes.

I'd be much happier if I knew why the stuff was gone.

Some of my other files have changed size but not quite as dramatically (I did a dir/S ]olddir.txt and ]newdir.txt and used MSWORD to compare the text files - it mostly worked except where it listed the sub dirs in a different order and then it got horribly confused).

Scott

Hi All

The index file for the current (corrupted) file and the index for the backup, both verify ok.

Does anyone know what get.reccount does - I don't think I have source code for it. Anyway it returns 1622 records in the corrupted file (the same number as in the back up file) although there are no records (except %RECORDS%) in the corrupted file. Ie I think it counted the records in the index or %RECORDS%, not the number of records actually in the table/file.

Incidentally I can't open %RECORDS% using system editor in the corrupted file. (I vaguely remember this was related to things like MFS indexs and RightDexs and LeftDexs ie if these weren't right you could either edit or not). The corrupted table has a SI.MFS type index.

And the file did have a relational index. It was related to another table whose definition I have been changing. Except it appears that the corruption occured before I started changing the related table. (another red schnapper)

I think I will copy the back up dos in now, remove all the indexes, save, close, open and put the indexes back.

Scott

why, why, why…

Hi All

I don't know if this is related but…

When I was rebuilding the index, I found that one of the columns offered for btree indexing against my file was

%TEMP.FORM00001BCBEDCCD%

This particular table is one of a set of three that are supposed to look the same ie a temp, a current and a history table, each with the same structured dictionary. So when I add a lot of new fields I had been adding them to the first table in table builder then using copy_row to put the rest of them in DICT.CURRENT and DICT.HISTORY.

This appears to be ok. Everything works ok, but I'm wondering if the %TEMP thingys are related to this technique…

Scott, LMS

Scott,

Yes I think it has relational indexes. So maybe I should do a verify on the !Table.

But relational indexes are not in ! tables.

- Oystein -

Scott ..

It looks as though you might have tested a form with a "Temporary Table" and things got screwed up. My recollection is that a similar thing happened to me a couple of years ago. Since the testing was using "Tutorial Data" that I supply with the application, there is a procedure to clear and re-initialize the Tutorial Files, including rebuilding indexes, so no real harm was done. I think there was a thread relating to this, but I'm not sure at this point.

Don Miller

C3 Inc.

Hi Oystein

So how do relational indexes work and how can they eat my data (if it was indeed the relational index that was the problem?)

How do I check that the thing is ok?

Scott

Scott,

Relational indexes are kept in a data field in the "target" table.

Since a record can only store 64K of data this means they can get full. That's one of the reasons I avoid them. Depends on what kind of data you've got.

Another problem with relational indexes is to understand or remember how they should be applied. Two fields are involved - source and target, and one or two tables. Yes, it's worse than walking and chewing gum at the same time.

Another problem is they affect both the source and target dicts. Sometimes you clear the one and are left with rubbish in the other.

And I'm certain there's more.

- Oystein -

I only use Relational Indexes rarely. My strategy for this is:

Assume my Target Table is Patient and I need to store pointers from another table in a field in this file so that i don't have to go through a bunch of selects to get the associated records from the source table.

I have a file called PATIENT_XREF which has as its primary key the @ID in the PATIENT table. I use this table to store the relational indexes. I have a symbolic field in Patient which is:

@ANS=XLATE('PATIENT_XREF',@ID,1,'X') which retrieves the relationals. In this way, if the Patient master record gets big (since it has a lot of text fields in it), I won't run the risk of exceeding the dreaded 64K limit in unpredictable ways. Also, I can safely delete XREF records when field 1 is empty. It is easy to turn Field 1 in XREF into either browse lists or Active Selects for reports too.

Don Miller

C3 Inc.

Don,

You mention large volumes of master data, but have you got a trick for the situation that the relational index field by itself (or equivalently, the XREF record) exceeds 64K?

- Oystein -

Hi All

Let me see if I have Don's system straight. He maintains a list of keys to some child of the Patient table (like Admissions) in the Patient_Xref. If there is a function created to do this, then it can also include a way of managing xrefs when they get too long. Like the way syslists work.

Ye old HR system that I worked on, we had lots of xref tables and a routine which rebuilt them if they went belly up. Eventually we wrote a "one size does all" write function that given a file name, key and record, would appropriately maintain all associated xrefs. The xrefs mostly got used when people wanted to look up a record - like options in the key field, and we'd feed them the xref list, refined if they typed some leading stuff in. To retrieve a person, they could key part of the person's surname, or if they knew the id, they could key part of that instead and the xref lookup would display the right list. So long as a person's surname didn't look like a bunch of numbers. Imagine the fun if your name was Onetwothree Fourfive.

Scott, LMS

I think you are on the right lines. We do somethging similar in our HR system.

We maintain two types of child table - historical (maintained by an MFS) and relational (maintained by relational index).

Our master table is Employees, and we keep two parallel keyed tables for the historical keys and relational keys.

Eg - on the parallel relational table we have columns entitled Contacts, Qualifications, Skills etc.

We then also have tables called by the same names and in these tables we maintain a copy of the Employees Id. There is then a relational index from the eg Contacts table, Emp Id column to the Relational table, Contacts column.

This Relational table then just maintains lists of keys pertaining to the various tables using the Employees Id as a key - so easily maintainable. If this were to break the 64k limit, then there would have to be a LOT of data.

Make sense ??

Simon

RebusHR

Oystein ..

I have a half-vast solution that we sometimes use. Before the new value is added, the length of the structure is checked (including a @VM). If this is found to be greater than about 50K, then a new primary key is added to the XREF file using the primary key with a suffix (we use an asterisk, but that can be dangerous). This looks a little like temp keys in SYSLISTS or the old LISTS table.

This can get messy quickly ..

The other alternative in this case is to use a 2-part key to a b-tree'd table which can then be fetched using btree_extract.

Don Miller

C3 Inc.

Actually, relational indexes rely on the !file, but not in the way you would think.

When a record is written, the relational indexes are updated immediately. However, if the target is locked, and the update cannot occur, the transaction is placed in the !file for later processing.

The Sprezzatura Group

World Leaders in all things RevSoft

View this thread on the forum...