Alpha Video Training
Results 1 to 27 of 27

Thread: Avoid corruption of linkage??

  1. #1
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Avoid corruption of linkage??

    I have yet to find a clear statement of the V5 limits on the # of fields a record can have and the limit to a records length. However, I have a table whose record has 883 fields and whose length is 10,900 according to "properties" and after successfully adding about 10 new fields I could no longer restructure the table, even if it was to delete a field. A5 would begin processing the restructure but would then get stuck and crash.

    Having a backup of the shorter table, I am still OK. I assume I am going to have to break the table into 2 linked tables. Other than a auto-incrementing number (essentially using Cal's and others way, not the built in way) there is no field or combination of fields that uniquely identify a record. My concern is the linkages may get messed up, as I have had this happen in the past, and once it happens fixing it is hopeless except for the most recent backup.

    On the assumption that I need to break the table into 2 linked tables (that I can use as if they were one), does anyone have any experience (words of wisdom, cautions, recommendation, etc.) they can share on this issue?

    Ray Lyons

    PS, In posting this I found out what happens if one forgets to put in a subject for these postings. Like do it all over again! That Send Message button needs to do some validating.

  2. #2
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Ray, A record w/836 fields and field length 10K ??? Not a solution I would consider suitable for any database.

    Sounds like a better choice would be a Word doc or PDF. Then just use A5 to manage the stored files.

    This will give you much happier result in the long run.

    Marc
    www.a5solutions.com

  3. #3
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    >> Not a solution I would consider suitable for any database. Sounds like a better choice would be a Word doc or PDF.

  4. #4
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Ray, you may be correct. I only question your design model. I'm quite familiar with amortization tables and they are never stored as a record. They are are generated on the fly based on terms and interest rate in reports or as "output" in temporary tables.

    If you are trying to input payments or generate invoices these would be individual records grouped by customer.

    What are you trying to accomplish that requires 800+ fields per record?

    Marc
    www.a5solutions.com

  5. #5
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,648

    Default RE: Avoid corruption of linkage??

    Ray,

    In v.4 at least:

    1,023 fields per record
    19,600 bytes per record

    Peter

  6. #6
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    Peter, yeah, I thought that applied to v5 also, but maybe not?

    Marc,

    No, there are no amortization tables involved at all. Ironically, the major reason there is such a large record is because the previous version of the app (which made use of several linked tables) was too susceptible to corruption from links getting crossed in a network environment. “Too susceptible” in this case meant one time in a year, but that one time was such a disaster that I felt it was worth trying to do away with linkage problems by trying to get as much as possible into one large record. This whole area, by the way, is why I can’t wait for the promises of V6.

    As an alternative I have toyed with the idea of having the users enter or modify bunches of data via forms with only variables that would then update individual fields in records in a number of tables. But for reporting and some other processing, these records would still have to be linked in a set. Alas, my paranoia about some of that processing possibly causing linkage corruption made me go with trying the one large record approach before possibly going down other roads.

    I was under the impression that I was still more than 100 fields short of A5's record field limit (I thought it was 1023 and much more than my 10k+ size). So it was very disappointing to have things bomb out at around 900 fields, meaning I may have to go back to several linked records after all the work of trying to make one work. I thought maybe others might have some great words of wisdom about how to avoid linkage problems, or maybe a good way to manage two linked records that are essentially one. But I am afraid that in a previous version of this app and in others that I may have already invoked most of the ideas that have been presented here on this subject. Just thought I would see if anyone had any new ideas, or could point me to a good old one. I think I have a pretty good home brewed auto-increment scheme for making a unique record identifier (thanks largely to many others on this board and at Learnalpha.com).

    As a point of interest, the database program I import from has at least 10,000 fields for each for each mortgage client. Interestingly, it saves each “record” as a single file, and uses the file name as the way to uniquely identify a “record.” It then has a number of indexes that allow the user to find and open the file needed. For most purposes it never opens anything but one client file plus a number of program support files it needs to be able to work on the one client file. Also, it clearly does not waste space, as dbf does, when a field is empty or partially empty, so file size remains quite small. I assume that when reporting is done on more than one file, it probably opens a file, gets what it needs for a report, closes it and opens another and so on and then generates a report. It’s an interesting approach but I have no idea what database engine it uses except that it clearly is not dbf nor does it use Access or anything else I can recognize.

    Ray

  7. #7
    "Certified" Alphaholic
    Real Name
    jim chapman
    Join Date
    Apr 2000
    Posts
    1,779

    Default RE: Avoid corruption of linkage??

    Hello Ray,

    >>the major reason there is such a large record is because the previous version of the app (which made use of several linked tables) was too susceptible to corruption from links getting crossed in a network environment.

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,311

    Default RE: Avoid corruption of linkage??

    Ray,

    Your design will quickly fill the bandwidth on the local network since presumably a lot of fields filled with blank spaces will be shuttling back and forth whenever you do a query or run a report. (This comment assumes that not all 883 fields will be needed in every report, but that all 883 fields will have to be moved across the network even if you want a simple customer list).

    -- tom

  9. #9
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    Jim, this is the sort of words of wisdom (experience) I was looking for.

    Thanks.

    Ray

  10. #10
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    Tom,

    True, but 1) there isn't a lot of reporting going on during the day, and 2) don't we have the same issue if the vast majority of those fields are spread across a number of records in a number of tables in a set and reports generally have to be done at the set level in order to grab the data the report needs? I don't see much if any difference. Am I missing something?

    Once again, many of us needed to get away from the dbf structure a long tiome ago.

    Ray

  11. #11
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Ray Wrote >>>Interestingly, it saves each “record” as a single file, and uses the file name as the way to uniquely identify a “record.”

    Ray That is my point exactly. I think you may be misinterpreting the output data as fields when in reality they are documents assembled for export.

    One of my clients is a very large brokerage firm with over $10M per month in mortgage loans. I have worked on both the Myers and Point Brokerage Management systems. These are the two leaders of automated loan processing in the brokerage business.

    While they have dozens of loan types and associated addendum info, including import web services for realtime lending rate input, none of the forms consist of more than 30 or 40 input fields and other disclosures are only single field memo type input.

    They are both custom relational ASP mySQL web projects. If you are having coruption of your linking I would not fault A5. I would rather look at your "homegrown auto increment" script and your overall design approach.

    Both Myers and Point are considered highly versatile yet in reality they are quite simple basic relational models. If you look at your relational model and find it problematic a flat file is not the solution. I think you may want to simplify your design and get back to A5's built in features. They work quite well for me on a number of high traffic networks.

    Just my oppinion :)

    Good Luck

    Marc
    www.a5solutions.com

  12. #12
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    Well, the program I referred to is the Calyx Point program which started out life as a DOS program and is almost certainly not the Point Brokerage Management sytem you mentioned. And the documents I import from simple text files exported from Point, but the Point data files contain all the data for the loan in question, and that can have a humongous number of fields and has nothing to do with a ASP mySQL web project since it is a standalone program that I can and do run entirely unconnected to the Internet.

    But that is all beside the point. You say "I would rather look at your "homegrown auto increment" script and your overall design approach." I didn't make myself clear enough on this: When the app experienced the problem it was using A5's built-in
    auto-increment function, and I am pretty sure that was at least part of what led to the disaster. My "homegrown" one is what I am using now, and it will never be known if using it would have prevented the problem in the old version of the app.

    And my app is not 100% flat file. The table with the large record still has to be part of a small set in order for some entry and reports to be accomplished. But now let's suppose far more tables all linked in a set. Yes, there would be a fewer fields in some of the records, but it would not be a huge reduction. And there would be at least several linked reocords that would have to be accessed to. for example, run a report.

    Bottom line is that to do much of anything you would still have to access a large number of fields whether they be in one record or 12 linked records. So I don't see how having them in one record is such a sin, especailly since record linking through aut0-increment fields is clearly a problem with A5 operating in a network environment. Maybe you are are the exception to the rule of never haveing such problems, and maybe I should have you build my apps so I can retire, but you have failed to convince me that trying the road I have gone down is such a bad idea.

    Ray

  13. #13
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Except that on a network a table with 800 fields will be locked to all other users when user1 is editing field1 which will shut down the dozens of other forms based on the table needing entries at other workstations.

    Marc
    www.a5solutions.com

  14. #14
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    Ah, but in the part of the mortgage game I deal with (and for mortgage data using Point, for that matter) two users almost never have reason to access the same record, much less edit it. And editing a field locks the record, not the entire table, doesn't it? My God, if it was the latter nothing would be possible with A5!

    Ray

  15. #15
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Avoid corruption of linkage??

    I've been developing commercial apps since 1980 using a variety of development tools.The ugliest problems I have ever encountered have been when I have used a tool or operating system at or close to some kind of limit.They are many times inconsistent and difficult to troubleshoot.
    A normal function like adding new fields is giving you a problem. For me that would be all it takes. I'd break up the record.
    As for caution using xbasic to create a unique linking field, "do not count on the unique field rule".There is a multi-user flaw involved.Instead use some code in the "cansave" event of the form used to add or edit the record.There is a thread about this and I think a small article in the Newsletter.

  16. #16
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Absolutely, Hence the universal use of relational data modeling. A record may contain a hundred tables. When you edit a field in a table the table is locked to other users until you save and exit the table. This protects you from data corruption and is true across all relational DBs.

    In striaght forward terms: Each form should be based on its own table in the set. The linking field should be completely seperate from the data, typically a seperate independant 16 digit number hash in each table.


    There could be dozens of reasons your linking became corrupt from improper distribution of tables to improper implementation of linking field criteria to a power glich during an edit.

    If you encountered only one disaster in a year why didn't you have a simple backup procedure in place to restore your system and go?

    There are a number of tried and true database design principles that you may want to investigate. Star Configuration, Key fields, and data protection just to name a few critical concepts.

    I return to my original point: A table with 800 fields is a non starter, I would not even consider it an option.

    Marc
    www.a5solutions.com

  17. #17
    Member
    Real Name
    ray lane
    Join Date
    May 2001
    Posts
    550

    Default RE: Avoid corruption of linkage??

    Hi John

    I would think if their was a flaw Alpha would of fixed it
    Ware did you see this?

    Ray

  18. #18
    Member
    Real Name
    ray lane
    Join Date
    May 2001
    Posts
    550

    Default RE: Avoid corruption of linkage??

    Hi All

    I am with you Marc I do the same thing I make a 16 Chr field using auto increment and the field is hidden from the use every table I create this field is the first one
    And I label it auto_inc_id I have seen no trouble with
    This and the user can't change it so the link is solid

    Ray

  19. #19
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Yup, That's the way to do it.

    Cheers

    Marc
    www.a5solutions.com

  20. #20
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default RE: Avoid corruption of linkage??

    Concept is explained in Newsletter #14 April 1st
    Article entitled:
    "Unique Field Values in Multi-User Apps"

  21. #21
    Member
    Real Name
    ray lane
    Join Date
    May 2001
    Posts
    550

    Default RE: Avoid corruption of linkage??

    Hi John

    Thank you for the reply

    Ray

  22. #22
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    Yep,

    That's the reason for the top most post here. But if I were not near the limits, I still do not see what the big deal is with a large number of fields in a record, especially for the kind of app I am dealing with.

    Ray Lyons

  23. #23
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    >>A record may contain a hundred tables. When you edit a field in a table the table is locked to other users until you save and exit the table. This protects you from data corruption and is true across all relational DBs.>There could be dozens of reasons your linking became corrupt from improper distribution of tables to improper implementation of linking field criteria...>If you encountered only one disaster in a year why didn't you have a simple backup procedure in place to restore your system and go?>There are a number of tried and true database design principles that you may want to investigate.>I return to my original point: A table with 800 fields is a non starter, I would not even consider it an option.

  24. #24
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    If you think that using the A5 built-in auto-incement (my assumption about your meaning, sorry if it is incorrect) and hiding it from users means "the link is solid," I suggest you do a search of this and the V4 board. The link that failed on me met the criteria you state and it got all fouled up. Others have had similar experiences, as a search would show. That you have not yet had a problem means nothing about what may be around the bend.

    Ray Lyons

  25. #25
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Ray, I know that email comes off a bit harsh but I certainly dont mean to condecend. I have no idea how experienced you are and I dont think your full of #!$@%. I only refer to what your trying to do on this question.

    You can open the forms all you want but you wont be able save changes while others have the table in edit mode, A very high probability with all the forms sharing the same table.

    A5 has built in backup features you can build into your app, probably a good idea!

    You can certainly build a table with all or most of your 800 fields. And it will work as a single user app fairly well. But you started this discussion focused on networking this solution and your going to find that you are creating more potential for problems than you started with.

    each to his own
    Marc
    www.a5solutions.com

  26. #26
    Member
    Real Name
    Marc King
    Join Date
    Dec 2002
    Location
    California
    Posts
    702

    Default RE: Avoid corruption of linkage??

    Your correct that the record is locked and not the table. My apologies. But there are other major drawbacks to using a mega table. Primarily given your 1.3 MB record size your goint to have all that data downloading to the local user system each time a form is opened. Multiply that by ten or twenty users and you can bring the network to its knees. Also any operation that queries or collects data accross all records is going to cause very longs wait times (application lock) for all users even though the needed data for the operation may be totally unrelated to specific often used input forms.

    Marc
    www.a5solutions.com

  27. #27
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default RE: Avoid corruption of linkage??

    At the risk of dragging a dead horse through the muck, my previous version of the app (multiple tables with records linked in sets) and what I 'll probably end up with now (fewer, possibly, but still multple tables linked in at least one if not more sets) have some but not many table only based forms such that the data involved in a set does not have to be dragged across the network. However, in most cases, the forms simply must at least display data from several tables in a set, or have set based lookups. Unless I am wrong, that will have more or less the same impact on the network traffic that my one large record scheme would have had (actually that large record still needed to be in a set too, but the point was the set had a table with a large record). I can't think of a way around it, can you?

    Some common, seldom changed support tables could be moved to each workstation, but A5 likes everything in one folder except for dictionaries and shadow tables moved under network optimizing. Still, I wonder why having some support tables on a workstation wouldn't work, A5 general rules aside? In theory it should work. I may experiment with this.

    Comment if you like, but this horse is long since dead and dragging it around much more is a dubious enterprise.

    Ray Lyons

Similar Threads

  1. Auto-Filling to avoid typing
    By Raheel Ahmed in forum Alpha Five Version 5
    Replies: 2
    Last Post: 10-31-2004, 09:40 AM
  2. Can I avoid Alpha-generated message box?
    By Kellen Furness in forum Alpha Five Version 5
    Replies: 6
    Last Post: 11-04-2003, 09:05 PM
  3. Avoid a new record in a form
    By Claudio_V in forum Alpha Five Version 4
    Replies: 4
    Last Post: 02-04-2002, 12:38 PM
  4. Avoid the registry
    By Mike Doty in forum Alpha Five Version 4
    Replies: 4
    Last Post: 04-25-2001, 10:32 AM
  5. avoid blank records
    By bobsk in forum Alpha Five Version 4
    Replies: 1
    Last Post: 05-14-2000, 07:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •