Application almost complete

[Michael Humby]

I am in the process of placing the final pieces into a very large puzzle. I hope to be marketing the application this week as a runtime app using my multi user license. It has taken me 9 months to develop and I have picked up valuable tips and information from this message board. Thanks to all subscribers.

However,having read other recent board experiences on deleting multiple records and loosing indexes, I now have two major concerns.

1. My app has an annual routine that deletes all last years child records from 4 separate tables. My tests work fine but I see several message board references to problems with mutiple deletes.

2. Recent messages discuss problems with indexes dissapearing. Although, this has never happened to me, my application will be useless if any of these two problems occurred on my clients machines.(I have included a maintenance window to update indexes)

Can anyone please reassure me that these problems are the exception and not common.

Michael

[Peter.Wayne]

RE: Application almost complete

If you simply put a
batch_begin()
and
batch_end()
around your routine to do the annual deletion, you won't have any prolblems with the multiple deletions. The batching commands detach the indexes during the script and rebuild them at the end of the script.

[Jim Rowlen]

RE: Application almost complete

re # 1:
Where one of our users is allowed to delete a record, we use the attached form. It has a browse to show the records where the user presses [Ctrl][M] to mark each record they want to delete. A buttonclick then deletes the records using xbasic, and another button packs the file. If anyone else is accessing the file the pack function will fail. Peter's comments about using the batch statements will probably help here. The form is pretty basic and self explanatory.

re # 2:
Make sure your users know how to use WinZip to make safety copies. We use an abbreviation (our major revision ID for the database - currently OpV8) an -xxx for the number (ie : -001, -002, -178, etc) of the safety so that we have maintained all safties back to our initial work in v1, well over 1500 archived. We then copy the safety to an archive directory where we keep the most recent 10 or so. All are burned onto CD and stored off-site (with the most recent 2 or 3 CDs at my house). This way, if the indexes fry, I can unzip the table in another directory, copy the indexes, paste them into the current copy of the table, and rebuild them. If the building burns down, we are back in service as soon as I can bring 2 of my home systems in as server and workstation for people on the floor while we drive across town to pick up a new server and a couple more workstations. You can have the company's 800 number transferred to your home and work there for a day or two while looking for a new building if necessary and not lose any business.

[Jim Rowlen]

RE: Application almost complete

re # 1:
Where one of our users is allowed to delete a record, we use the attached form. It has a browse to show the records where the user presses [Ctrl][M] to mark each record they want to delete. A buttonclick then deletes the records using xbasic, and another button packs the file. If anyone else is accessing the file the pack function will fail. Peter's comments about using the batch statements will probably help here. The form is pretty basic and self explanatory.

re # 2:
Make sure your users know how to use WinZip to make safety copies. We use an abbreviation (our major revision ID for the database - currently OpV8) an -xxx for the number (ie : -001, -002, -178, etc) of the safety so that we have maintained all safties back to our initial work in v1, well over 1500 archived. We then copy the safety to an archive directory where we keep the most recent 10 or so. All are burned onto CD and stored off-site (with the most recent 2 or 3 CDs at my house). This way, if the indexes fry, I can unzip the table in another directory, copy the indexes, paste them into the current copy of the table, and rebuild them. If the building burns down, we are back in service as soon as I can bring 2 of my home systems in as server and workstation for people on the floor while we drive across town to pick up a new server and a couple more workstations. You can have the company's 800 number transferred to your home and work there for a day or two while looking for a new building if necessary and not lose any business.

[csda1]

RE: Application almost complete

Peter,

Just to clarify what you said a bit, so no one will use them incorrectly. The batch_begin and batch_end do not detach the indexes. What they do is place a temporary file lock on a table's processes and place those files in an exclusive, non-sharing mode.

When a process (operation) such as an append, delete, etc occurs, their operation is completely different in this mode as opposed to shared mode. This includes updating all potentially affected indexes (those that have fields that possibly would have changes to them, etc.) after the process completes.

For large changes, this is fastest. For changes that affect only a small portion of the table(s) records, shared mode is faster as it updates after each record has changes to it.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Michael Humby]

RE: Application almost complete

Jim,

Have downloaded your sample thanks. You mention "If our indexes fry". Has this actually happened in your experience with Alpha or are your back up precautions anticipating the possibility. I have read messages on the board where indexes dissapear and become corrupt though it has never happened to me.

I would like to think that my fear of losing indexes are unwarranted.

Michael

[Jim Rowlen]

RE: Application almost complete

The reason our indexes fry occasionally is probably because we have a bad habit of pushing a development tool to (and frequently beyond) its capabilities. We've done this with DAX (an interactive telephone-to-database-to-telephone tool), Teleform, and a few others. The good thing about this is that we've gotten on beta test teams for Ram Reseaerch, Teleform, Semantis, Delrina, Alpha Software, and a number of others; I've been a beta tester since the mid 80's (hardware and software). This helps in getting features added to final releases and gettig to see what's coming out next so we can further position our company to be the industry leader in utilizing technology. The bad thing is that it's a lot of extra work, especially when you push and break something and have to develop work arounds.

I've found that the most common thing that happens when indexes get messed up is that long index names get truncated to 10 chars. Abbreviating and leaving out vowels is an old trick that lets you overcome this, as does spelling phonetically (Lox instead of Locks, for example). Another, which I haven't had time to fully test and confirm, is that defining an index as
FieldA + FieldB
instead of
FieldA+FieldB
(ie: putting in the extra space for readability) might cause problems. A5 doesn't put the spaces in, and I've noticed that in addition to truncation the indexes defined using the spaces tend to disappear more often than those without.

Restructuring a table, especially if you move the new field to its logical position (ie: f_name before l_name), or removing a field, can lead to all sorts of problems after defining any layouts or adding data. My most recent experience with that was at 4 this morning when things got so corrupted that not only did I lose the indexes but the forms as well. (That's another reason for safeties : programmer stupidity - should have changed the layout in a new empty table, appended data, pastyed forms).

Programs a like impetuous little kids. They're always growing, never finish changing, and will probably cause problems. The more complex the program/app, the more likely this will happen. Making sure that the user knows how to and makes/maintains safeties is just another tool the programmer should use to attempt to minimize the pain the user will experience.