Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Modifying Tables on Many Systems

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Modifying Tables on Many Systems

    Let's say I do an application and sell it to (100) customers. I then come out with an update to the application that involves adding a couple of fields to an existing table, and let's say, increasing the size of one of the existing fields.

    How do I deploy that to the existing users. Is there an automated process for this, or do I have to send the new table structure with a different name and then import the data from the existing table to the new table.

    Just trying to think ahead.

    Regards,

    Scott

  • #2
    Re: Modifying Tables on Many Systems

    Scott

    You would have to develop your own routine or you might be able to use the A1-Update Utility at this site:

    http://www.a1software.net/


    Regards,

    Jeff

    Comment


    • #3
      Re: Modifying Tables on Many Systems

      Jeff,

      Thanks for the lead. I'll check it out.

      Regards,

      Scott

      Comment


      • #4
        Re: Modifying Tables on Many Systems

        Hi,

        I find this tread very interesting, I'm evaluating Alpha Five Platinum and this is a very issue for me. żIs it possible that some experimented Alpha Five developer guide me whit the following questions?

        1. In the maintenance proceess of an application when the data model changes, żIs there a way to change the data on production environment?

        2. żIs it possible that some experimented Alpha Five developer explain me the process for upgrade an application in a production environment?

        2. I went to the AlphaToGO site, but did not see the upgrade tool, żIs there a similar product from Alpha Five product catalog?

        Thanks in advance,


        Rod MG

        Comment


        • #5
          Re: Modifying Tables on Many Systems

          I know I've posted something like this before but I couldn't find it so here it is again. It shows a few different "field updates" that can easily be accomplished via xbasic in the autoexec routine.

          This is actually a selection of examples from a 478 line long update routine in the autoexec of a program that has been "under development" since about 2002. The routine was much longer until I cleaned some of it out about 6 months ago. In other words, I do this a LOT and have been doing it for years - and it works just fine. I have similar, but shorter, routines in many of my larger apps.

          I put this code in my autoexec and, if fields are being added/updated, everyone has to exit the application at the client location when the update is installed. They only have to exit the application for a couple of minutes. All I have to do is tell them where to download my update routine and tell them to make sure everyone has closed the app before they actually install the update. As soon as the update is installed, everyone can restart. The autoexec runs and updates the fields when the first person starts the app. When subsequent users start the app, the initial check determines that the last field to be added already exists and skips the rest of the routine.

          NOTE: These examples are only showing how to add/update fields. If there is no need to add or change fields, the users do not need to exit the application when the update is installed. The next time they start the application it will read the network optimize number and update their shadowed version automatically. If the update is something they really want, they can shut down and restart right away. If not, they can keep working with the old version until the next time they restart the app.

          As noted in the remarks, I leave these routines in for at least a year (usually longer because I'm too lazy to take them out) in case someone wants to install an older backup. If they restore old data to a newer version of the application, the fields need to be updated to avoid errors.

          Yes, it's unusual to restore old data but I recently had to do a temporary restore of some 6 month old data so the customer could get a report he forgot to print that month. We might have been able to do it with the current data but, in this particular case, it was faster and easier to just run a current backup, restore the old data, run the report, and restore the current data. Since my field update routines were still in place, it was a non-issue.

          I also found some other relevant threads while searching:
          http://msgboard.alphasoftware.com/al...4&postcount=11
          http://msgboard.alphasoftware.com/al...40&postcount=6
          http://msgboard.alphasoftware.com/al...ata+dictionary
          http://msgboard.alphasoftware.com/al...ight=add+field
          http://msgboard.alphasoftware.com/al...d+field+update

          Code:
          '########################################################################
          '########################################################################
          ' Update fields/tables.
          ' Check the most recent modification first. If it is already
          ' completed, skip to 'Done'. (If the most recent is done, obviously 
          ' everything else must have been done also.)
          ' == NOTE: All mods for a table should be kept together if the table was 
          ' modified earlier. The earlier mods need to be kept for at least a year 
          ' because (a) some users of generic apps may not have updated yet and 
          ' (b) they won't be able to restore and use old data files if they aren't 
          ' updated correctly.
          ' First changes MUST to come first within each table.
          '########################################################################
          '########################################################################
           
          'I *ONLY* restructure the main tables on the server - never the shadowed 
          'tables. Maybe it would work but I don't want to take any chances.
          DIM datapath as C
          IF a5.Get_Master_Path() = ""
              datapath = a5.Get_Path()+chr(92)
          ELSE
              datapath = a5.Get_Master_Path()+chr(92)
          END IF
           
          'Check LAST table and LAST field updated first. If it has already been 
          'done, skip the rest. Don't need to use "datapath" to get field names.
          fld_list = a5_get_fieldnames( "Ord_Item.dbf" )
          IF "Dwnld_Dtf"$fld_list '2008-09-20
              GOTO Updates_done
          END IF
           
          'You may want to add a Please Wait dialog here but adding new fields is 
          'usually quite fast. It takes longer if you need to also update the values.
           
          '---------------- Clients table
          fld_list = a5_get_fieldnames( "Clients.dbf" )
          '2007-01-20
          IF .not. ("Sgn_Typef"$fld_list)
              new_flds = <<%list%
          Sgn_Typef,C,30,0
          Pst_Sizef,C,15,0
          Pst_Clrf,C,14,0
          Pst_Armf,C,14,0
          %list%
              a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
          END IF
           
          '2008-04-10 Add fields to Clients. (NOTE - Date is AFTER previous update.)
          IF .not. ("Cc_Namef"$fld_list)
              new_flds = <<%list%
          Cc_Namef,C,30,0
          Cc_Streetf,C,20,0
          Cc_Zipf,C,10,0
          cc_cvvf,C,5,0
          %list%
              a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
          END IF
           
          '---------------- Ord_Item table
          fld_list = a5_get_fieldnames( "Ord_Item.dbf" )
          '2008-09-16 Add Dwnld_Dtf field.
          IF .not. ("Dwnld_Dtf"$fld_list)
              new_flds = <<%list%
          Dwnld_Dtf,D,8,0
          %list%
              a5_add_fields_to_table( datapath + "Ord_Item.dbf", new_flds )
          END IF
           
          '---------------- Order_Statusw
          '2007-04-13 Change width of field in order_statusw.
          tps = table.open("order_statusw")
          fld = tps.field_get( "Stat_Typef" )
          fwid = fld.width_get()
          tps.close()
          IF fwid = 8
              Old_list = <<%list%
          Stat_Typef,C,8,0
          %list%
              New_list = <<%list%
          Stat_Typef,C,13,0
          %list%
              a5_changefieldsize( datapath + "order_statusw.dbf", old_list, new_list )
          END IF
           
          '---------------- Installers
          'July 2007 - Add and update new fields.
          fld_list = a5_get_fieldnames( "Installers.dbf" )
          IF .not. ( "eml_addrf"$fld_list )
              new_flds = <<%list%
          Eml_Addrf,C,40
          Activef,L,1
          %list%
              a5_add_fields_to_table( datapath + "Installers.dbf", new_flds )
              'Initialize the "Activef" field as True.
              tpi = table.open("installers")
              tpi.index_primary_put("")
              tpi.fetch_first()
              WHILE .not. tpi.fetch_eof()
                  tpi.change_begin()
                  tpi.Activef = .T.
                  tpi.change_end()
                  tpi.fetch_next()
              END WHILE
              tpi.close()
          END IF
           
          '---------------- Program_Options
          IF .not. ("Foto_Pathf"$flist) '2007-08-16
              new_list = <<%list%
          Foto_Pathf,C,60,0
          %list%
              a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
              tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
              tpp.change_begin()
              tpp.Foto_Pathf = datapath + "Photos" + chr(92)
              tpp.change_end()
              tpp.close()
          END IF
           
          IF .not. ("daylightf"$flist) '2007-12-06
              new_list = <<%list%
          time_zonef,c,8,0
          daylightf,L,1,0
          %list%
              a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
              tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
              tpp.change_begin()
              tpp.time_zonef = "Eastern" 'Eastern,Central,Mountain,Pacific
              tpp.daylightf = .F.
              tpp.change_end()
              tpp.close()
          END IF
           
          '2008-07-03 Added just one field so didn't use the crlf() list.
          IF .not. ( "spec_msgf"$flist )
              a5_add_fields_to_table( datapath + "program_options.dbf", "spec_msgf,C,254,0" )
          END IF
           
          'End of adding fields
          '*****************************************************************************
          '*****************************************************************************
          '*****************************************************************************
          '---------------
          Updates_done:
          '---------------
           
          'A routine to change field sizes. This doesn't have to be embedded in the 
          'previous "add fields" routines but it can.
          '---- AGAIN - LEAVE FOR AT LEAST A YEAR so user can restore older backups if necessary.
          '---- 2008-01-23 Update width of Sign_locf field.
          '2008-07-03 Change to check field width by using a lookup - much faster 
          'that way! (Much faster in "computer time" - users probably won't notice 
          'the extra few milliseconds.)
          sloc = lookupc( "F", 1, "sign_locf", datapath + "Ord_Item.dbf", "" )
          fwid1 = len( sloc )
          IF fwid1 <> 11
              'Change width of Sign_locf field in Ord_Item 2008-01-23.
              tpx = table.open( datapath + "Ord_Item.dbf" )
              fld1 = tpx.field_get( "Sign_locf" )
              fwid1 = fld1.width_get()
              tpx.close()
              IF fwid1 <> 11
                  Old_list = "Sign_locf,C," + ltrim(str(fwid1)) + ",0"
                  New_list = "Sign_locf,C,11,0"
                  a5_changefieldsize( datapath + "Ord_item.dbf", old_list, new_list )
              END IF
          END IF
           
          '*****************************************************************************
          '*****************************************************************************

          Comment


          • #6
            Re: Modifying Tables on Many Systems

            Cal,

            Thanks for the code. I'll study it and try to apply when necessary.

            Regards,

            Scott

            Comment


            • #7
              Re: Modifying Tables on Many Systems

              Thanks Cal.

              Comment


              • #8
                Re: Modifying Tables on Many Systems

                Is the run engine cliient/server?
                Can the shadow feature be turned off so code does not need to be copied to each machine?
                Is it better to just use SQL tables to elminate having to recreate tables?

                Comment


                • #9
                  Re: Modifying Tables on Many Systems

                  First, welcome to Alpha and the forum.

                  Is the run engine cliient/server?
                  I haven't used it and I'm not sure of this at all but I believe the correct answer is that most SQL databases are client/server but the run engine itself is not client/server.

                  Can the shadow feature be turned off so code does not need to be copied to each machine?
                  First, you don't "turn off" shadowing; you either create a shadowed application or you don't. If you don't want to use it and don't mind your applications running a bit slow, go ahead and run the application directly from the server. HOWEVER, please don't be silly enough to try running A5 itself from the server. It can be done but it will be REALLY, REALLY slow - and maybe worse.

                  How slow it is without shadowing will depend on your application and even more on your server. As best I recall, most data entry goes just as fast and most forms opened only slightly slower. But things like long loops and rebuilding indexes usually took MUCH longer when not network optimized (shadowed). Again, I could be wrong - maybe the extra time for those long loops and indexes was just a networking issue and not a shadowing issue. (Sorry but it's hard to be sure since I see no reason not to shadow and therefore almost never run directly off the server from workstations. I only have one customer that does that and I don't see them very often.)

                  What reason would there be for not shadowing? And why worry about copying the code to each machine? Not copying it to the workstation doesn't really protect anything because someone could copy it from the server just as easily as from the workstation. If you are worried about someone stealing your code, protect it with a password or "compile" it into an aex file.

                  Also, FWIW, runtime is NOT equal to "shadowed". An application can be shadowed and still run with a full version of A5. In most cases a shadowed application is run with the runtime so there's a tendency to link the two together but they are actually two separate things.

                  Is it better to just use SQL tables to elminate having to recreate tables?
                  Recreate tables??? I'm not sure what you're asking here. I've been known to add new fields to tables but never to "recreate" them. Even if you use SQL tables, I don't imagine that a field that never existed will magically appear just because it's an SQL table. (Unless SQL has gone one step beyond the computer on Star Trek, I don't think it has the ability to read our minds and add missing fields yet.:))

                  Comment


                  • #10
                    Re: Modifying Tables on Many Systems

                    Hi Cal:

                    Thanks for the code! I've already employed it in my first vertical app I'm developing in Alpha and it tests great. As a long time FoxPro and Visual Studio programmer I'm very impressed by the community support here at Alpha. Keep up the good work and I'll try and reciprocate the good will by paying it forward.

                    Thanks,
                    Brad
                    Brad Weaver, President
                    ComputerAid International
                    Ottawa ON Canada
                    Versailles KY USA
                    www.compuaid.com

                    Comment


                    • #11
                      Re: Modifying Tables on Many Systems

                      Cal,
                      IF "Dwnld_Dtf"$fld_list '2008-09-20
                      Where to you get that date from and when? I am assuming it is the date of the .dbf???
                      Thanks.
                      Ernie

                      Comment


                      • #12
                        Re: Modifying Tables on Many Systems

                        Sorry for the confusion. That date, '2008-09-20 in this case, is just a comment (note the ' at the beginning of the date) to show the date that I added/updated this IF statement. I do that for each IF statement in this section so there's no confusion on my part when I add or delete other IF statements for the same table. The fields have to be added in sequence in case someone restores an old backup or if it has been a few build levels since they updated.

                        That line just says:
                        IF the field named "Dwnld_Dtf" is found in the list of fields for that table, just skip the rest because that's the last field that was added. If the last field added is already there, obviously the rest should be, too.

                        The other IF statements will add the field(s) IF the field is NOT there yet.
                        Last edited by CALocklin; 12-01-2008, 02:46 PM.

                        Comment


                        • #13
                          Re: Modifying Tables on Many Systems

                          Originally posted by CALocklin View Post
                          I know I've posted something like this before but I couldn't find it so here it is again. It shows a few different "field updates" that can easily be accomplished via xbasic in the autoexec routine.

                          This is actually a selection of examples from a 478 line long update routine in the autoexec of a program that has been "under development" since about 2002. The routine was much longer until I cleaned some of it out about 6 months ago. In other words, I do this a LOT and have been doing it for years - and it works just fine. I have similar, but shorter, routines in many of my larger apps.

                          I put this code in my autoexec and, if fields are being added/updated, everyone has to exit the application at the client location when the update is installed. They only have to exit the application for a couple of minutes. All I have to do is tell them where to download my update routine and tell them to make sure everyone has closed the app before they actually install the update. As soon as the update is installed, everyone can restart. The autoexec runs and updates the fields when the first person starts the app. When subsequent users start the app, the initial check determines that the last field to be added already exists and skips the rest of the routine.

                          NOTE: These examples are only showing how to add/update fields. If there is no need to add or change fields, the users do not need to exit the application when the update is installed. The next time they start the application it will read the network optimize number and update their shadowed version automatically. If the update is something they really want, they can shut down and restart right away. If not, they can keep working with the old version until the next time they restart the app.

                          As noted in the remarks, I leave these routines in for at least a year (usually longer because I'm too lazy to take them out) in case someone wants to install an older backup. If they restore old data to a newer version of the application, the fields need to be updated to avoid errors.

                          Yes, it's unusual to restore old data but I recently had to do a temporary restore of some 6 month old data so the customer could get a report he forgot to print that month. We might have been able to do it with the current data but, in this particular case, it was faster and easier to just run a current backup, restore the old data, run the report, and restore the current data. Since my field update routines were still in place, it was a non-issue.

                          I also found some other relevant threads while searching:
                          http://msgboard.alphasoftware.com/al...4&postcount=11
                          http://msgboard.alphasoftware.com/al...40&postcount=6
                          http://msgboard.alphasoftware.com/al...ata+dictionary
                          http://msgboard.alphasoftware.com/al...ight=add+field
                          http://msgboard.alphasoftware.com/al...d+field+update

                          Code:
                          '########################################################################
                          '########################################################################
                          ' Update fields/tables.
                          ' Check the most recent modification first. If it is already
                          ' completed, skip to 'Done'. (If the most recent is done, obviously 
                          ' everything else must have been done also.)
                          ' == NOTE: All mods for a table should be kept together if the table was 
                          ' modified earlier. The earlier mods need to be kept for at least a year 
                          ' because (a) some users of generic apps may not have updated yet and 
                          ' (b) they won't be able to restore and use old data files if they aren't 
                          ' updated correctly.
                          ' First changes MUST to come first within each table.
                          '########################################################################
                          '########################################################################
                           
                          'I *ONLY* restructure the main tables on the server - never the shadowed 
                          'tables. Maybe it would work but I don't want to take any chances.
                          DIM datapath as C
                          IF a5.Get_Master_Path() = ""
                              datapath = a5.Get_Path()+chr(92)
                          ELSE
                              datapath = a5.Get_Master_Path()+chr(92)
                          END IF
                           
                          'Check LAST table and LAST field updated first. If it has already been 
                          'done, skip the rest. Don't need to use "datapath" to get field names.
                          fld_list = a5_get_fieldnames( "Ord_Item.dbf" )
                          IF "Dwnld_Dtf"$fld_list '2008-09-20
                              GOTO Updates_done
                          END IF
                           
                          'You may want to add a Please Wait dialog here but adding new fields is 
                          'usually quite fast. It takes longer if you need to also update the values.
                           
                          '---------------- Clients table
                          fld_list = a5_get_fieldnames( "Clients.dbf" )
                          '2007-01-20
                          IF .not. ("Sgn_Typef"$fld_list)
                              new_flds = <<%list%
                          Sgn_Typef,C,30,0
                          Pst_Sizef,C,15,0
                          Pst_Clrf,C,14,0
                          Pst_Armf,C,14,0
                          %list%
                              a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
                          END IF
                           
                          '2008-04-10 Add fields to Clients. (NOTE - Date is AFTER previous update.)
                          IF .not. ("Cc_Namef"$fld_list)
                              new_flds = <<%list%
                          Cc_Namef,C,30,0
                          Cc_Streetf,C,20,0
                          Cc_Zipf,C,10,0
                          cc_cvvf,C,5,0
                          %list%
                              a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
                          END IF
                           
                          '---------------- Ord_Item table
                          fld_list = a5_get_fieldnames( "Ord_Item.dbf" )
                          '2008-09-16 Add Dwnld_Dtf field.
                          IF .not. ("Dwnld_Dtf"$fld_list)
                              new_flds = <<%list%
                          Dwnld_Dtf,D,8,0
                          %list%
                              a5_add_fields_to_table( datapath + "Ord_Item.dbf", new_flds )
                          END IF
                           
                          '---------------- Order_Statusw
                          '2007-04-13 Change width of field in order_statusw.
                          tps = table.open("order_statusw")
                          fld = tps.field_get( "Stat_Typef" )
                          fwid = fld.width_get()
                          tps.close()
                          IF fwid = 8
                              Old_list = <<%list%
                          Stat_Typef,C,8,0
                          %list%
                              New_list = <<%list%
                          Stat_Typef,C,13,0
                          %list%
                              a5_changefieldsize( datapath + "order_statusw.dbf", old_list, new_list )
                          END IF
                           
                          '---------------- Installers
                          'July 2007 - Add and update new fields.
                          fld_list = a5_get_fieldnames( "Installers.dbf" )
                          IF .not. ( "eml_addrf"$fld_list )
                              new_flds = <<%list%
                          Eml_Addrf,C,40
                          Activef,L,1
                          %list%
                              a5_add_fields_to_table( datapath + "Installers.dbf", new_flds )
                              'Initialize the "Activef" field as True.
                              tpi = table.open("installers")
                              tpi.index_primary_put("")
                              tpi.fetch_first()
                              WHILE .not. tpi.fetch_eof()
                                  tpi.change_begin()
                                  tpi.Activef = .T.
                                  tpi.change_end()
                                  tpi.fetch_next()
                              END WHILE
                              tpi.close()
                          END IF
                           
                          '---------------- Program_Options
                          IF .not. ("Foto_Pathf"$flist) '2007-08-16
                              new_list = <<%list%
                          Foto_Pathf,C,60,0
                          %list%
                              a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
                              tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
                              tpp.change_begin()
                              tpp.Foto_Pathf = datapath + "Photos" + chr(92)
                              tpp.change_end()
                              tpp.close()
                          END IF
                           
                          IF .not. ("daylightf"$flist) '2007-12-06
                              new_list = <<%list%
                          time_zonef,c,8,0
                          daylightf,L,1,0
                          %list%
                              a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
                              tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
                              tpp.change_begin()
                              tpp.time_zonef = "Eastern" 'Eastern,Central,Mountain,Pacific
                              tpp.daylightf = .F.
                              tpp.change_end()
                              tpp.close()
                          END IF
                           
                          '2008-07-03 Added just one field so didn't use the crlf() list.
                          IF .not. ( "spec_msgf"$flist )
                              a5_add_fields_to_table( datapath + "program_options.dbf", "spec_msgf,C,254,0" )
                          END IF
                           
                          'End of adding fields
                          '*****************************************************************************
                          '*****************************************************************************
                          '*****************************************************************************
                          '---------------
                          Updates_done:
                          '---------------
                           
                          'A routine to change field sizes. This doesn't have to be embedded in the 
                          'previous "add fields" routines but it can.
                          '---- AGAIN - LEAVE FOR AT LEAST A YEAR so user can restore older backups if necessary.
                          '---- 2008-01-23 Update width of Sign_locf field.
                          '2008-07-03 Change to check field width by using a lookup - much faster 
                          'that way! (Much faster in "computer time" - users probably won't notice 
                          'the extra few milliseconds.)
                          sloc = lookupc( "F", 1, "sign_locf", datapath + "Ord_Item.dbf", "" )
                          fwid1 = len( sloc )
                          IF fwid1 <> 11
                              'Change width of Sign_locf field in Ord_Item 2008-01-23.
                              tpx = table.open( datapath + "Ord_Item.dbf" )
                              fld1 = tpx.field_get( "Sign_locf" )
                              fwid1 = fld1.width_get()
                              tpx.close()
                              IF fwid1 <> 11
                                  Old_list = "Sign_locf,C," + ltrim(str(fwid1)) + ",0"
                                  New_list = "Sign_locf,C,11,0"
                                  a5_changefieldsize( datapath + "Ord_item.dbf", old_list, new_list )
                              END IF
                          END IF
                           
                          '*****************************************************************************
                          '*****************************************************************************


                          Cal, I don't know how to use your code, the situation is, what if i installed an app for a client,then after client used for say about 1 month, i decided to change some field rules in my computer/ development machine or maybe added some fields, what will I do with your code so that I can change the client's dbf's structure and field rules?
                          you said put it in the autoexec of the app,

                          Comment


                          • #14
                            Re: Modifying Tables on Many Systems

                            Originally posted by CALocklin View Post
                            I know I've posted something like this before but I couldn't find it so here it is again. It shows a few different "field updates" that can easily be accomplished via xbasic in the autoexec routine.

                            This is actually a selection of examples from a 478 line long update routine in the autoexec of a program that has been "under development" since about 2002. The routine was much longer until I cleaned some of it out about 6 months ago. In other words, I do this a LOT and have been doing it for years - and it works just fine. I have similar, but shorter, routines in many of my larger apps.

                            I put this code in my autoexec and, if fields are being added/updated, everyone has to exit the application at the client location when the update is installed. They only have to exit the application for a couple of minutes. All I have to do is tell them where to download my update routine and tell them to make sure everyone has closed the app before they actually install the update. As soon as the update is installed, everyone can restart. The autoexec runs and updates the fields when the first person starts the app. When subsequent users start the app, the initial check determines that the last field to be added already exists and skips the rest of the routine.

                            NOTE: These examples are only showing how to add/update fields. If there is no need to add or change fields, the users do not need to exit the application when the update is installed. The next time they start the application it will read the network optimize number and update their shadowed version automatically. If the update is something they really want, they can shut down and restart right away. If not, they can keep working with the old version until the next time they restart the app.

                            As noted in the remarks, I leave these routines in for at least a year (usually longer because I'm too lazy to take them out) in case someone wants to install an older backup. If they restore old data to a newer version of the application, the fields need to be updated to avoid errors.

                            Yes, it's unusual to restore old data but I recently had to do a temporary restore of some 6 month old data so the customer could get a report he forgot to print that month. We might have been able to do it with the current data but, in this particular case, it was faster and easier to just run a current backup, restore the old data, run the report, and restore the current data. Since my field update routines were still in place, it was a non-issue.

                            I also found some other relevant threads while searching:
                            http://msgboard.alphasoftware.com/al...4&postcount=11
                            http://msgboard.alphasoftware.com/al...40&postcount=6
                            http://msgboard.alphasoftware.com/al...ata+dictionary
                            http://msgboard.alphasoftware.com/al...ight=add+field
                            http://msgboard.alphasoftware.com/al...d+field+update

                            Code:
                            '########################################################################
                            '########################################################################
                            ' Update fields/tables.
                            ' Check the most recent modification first. If it is already
                            ' completed, skip to 'Done'. (If the most recent is done, obviously 
                            ' everything else must have been done also.)
                            ' == NOTE: All mods for a table should be kept together if the table was 
                            ' modified earlier. The earlier mods need to be kept for at least a year 
                            ' because (a) some users of generic apps may not have updated yet and 
                            ' (b) they won't be able to restore and use old data files if they aren't 
                            ' updated correctly.
                            ' First changes MUST to come first within each table.
                            '########################################################################
                            '########################################################################
                             
                            'I *ONLY* restructure the main tables on the server - never the shadowed 
                            'tables. Maybe it would work but I don't want to take any chances.
                            DIM datapath as C
                            IF a5.Get_Master_Path() = ""
                                datapath = a5.Get_Path()+chr(92)
                            ELSE
                                datapath = a5.Get_Master_Path()+chr(92)
                            END IF
                             
                            'Check LAST table and LAST field updated first. If it has already been 
                            'done, skip the rest. Don't need to use "datapath" to get field names.
                            fld_list = a5_get_fieldnames( "Ord_Item.dbf" )
                            IF "Dwnld_Dtf"$fld_list '2008-09-20
                                GOTO Updates_done
                            END IF
                             
                            'You may want to add a Please Wait dialog here but adding new fields is 
                            'usually quite fast. It takes longer if you need to also update the values.
                             
                            '---------------- Clients table
                            fld_list = a5_get_fieldnames( "Clients.dbf" )
                            '2007-01-20
                            IF .not. ("Sgn_Typef"$fld_list)
                                new_flds = <<%list%
                            Sgn_Typef,C,30,0
                            Pst_Sizef,C,15,0
                            Pst_Clrf,C,14,0
                            Pst_Armf,C,14,0
                            %list%
                                a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
                            END IF
                             
                            '2008-04-10 Add fields to Clients. (NOTE - Date is AFTER previous update.)
                            IF .not. ("Cc_Namef"$fld_list)
                                new_flds = <<%list%
                            Cc_Namef,C,30,0
                            Cc_Streetf,C,20,0
                            Cc_Zipf,C,10,0
                            cc_cvvf,C,5,0
                            %list%
                                a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
                            END IF
                             
                            '---------------- Ord_Item table
                            fld_list = a5_get_fieldnames( "Ord_Item.dbf" )
                            '2008-09-16 Add Dwnld_Dtf field.
                            IF .not. ("Dwnld_Dtf"$fld_list)
                                new_flds = <<%list%
                            Dwnld_Dtf,D,8,0
                            %list%
                                a5_add_fields_to_table( datapath + "Ord_Item.dbf", new_flds )
                            END IF
                             
                            '---------------- Order_Statusw
                            '2007-04-13 Change width of field in order_statusw.
                            tps = table.open("order_statusw")
                            fld = tps.field_get( "Stat_Typef" )
                            fwid = fld.width_get()
                            tps.close()
                            IF fwid = 8
                                Old_list = <<%list%
                            Stat_Typef,C,8,0
                            %list%
                                New_list = <<%list%
                            Stat_Typef,C,13,0
                            %list%
                                a5_changefieldsize( datapath + "order_statusw.dbf", old_list, new_list )
                            END IF
                             
                            '---------------- Installers
                            'July 2007 - Add and update new fields.
                            fld_list = a5_get_fieldnames( "Installers.dbf" )
                            IF .not. ( "eml_addrf"$fld_list )
                                new_flds = <<%list%
                            Eml_Addrf,C,40
                            Activef,L,1
                            %list%
                                a5_add_fields_to_table( datapath + "Installers.dbf", new_flds )
                                'Initialize the "Activef" field as True.
                                tpi = table.open("installers")
                                tpi.index_primary_put("")
                                tpi.fetch_first()
                                WHILE .not. tpi.fetch_eof()
                                    tpi.change_begin()
                                    tpi.Activef = .T.
                                    tpi.change_end()
                                    tpi.fetch_next()
                                END WHILE
                                tpi.close()
                            END IF
                             
                            '---------------- Program_Options
                            IF .not. ("Foto_Pathf"$flist) '2007-08-16
                                new_list = <<%list%
                            Foto_Pathf,C,60,0
                            %list%
                                a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
                                tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
                                tpp.change_begin()
                                tpp.Foto_Pathf = datapath + "Photos" + chr(92)
                                tpp.change_end()
                                tpp.close()
                            END IF
                             
                            IF .not. ("daylightf"$flist) '2007-12-06
                                new_list = <<%list%
                            time_zonef,c,8,0
                            daylightf,L,1,0
                            %list%
                                a5_add_fields_to_table( DataPath + "program_options.dbf", new_list )
                                tpp = table.open( DataPath + "program_options.dbf", file_rw_shared )
                                tpp.change_begin()
                                tpp.time_zonef = "Eastern" 'Eastern,Central,Mountain,Pacific
                                tpp.daylightf = .F.
                                tpp.change_end()
                                tpp.close()
                            END IF
                             
                            '2008-07-03 Added just one field so didn't use the crlf() list.
                            IF .not. ( "spec_msgf"$flist )
                                a5_add_fields_to_table( datapath + "program_options.dbf", "spec_msgf,C,254,0" )
                            END IF
                             
                            'End of adding fields
                            '*****************************************************************************
                            '*****************************************************************************
                            '*****************************************************************************
                            '---------------
                            Updates_done:
                            '---------------
                             
                            'A routine to change field sizes. This doesn't have to be embedded in the 
                            'previous "add fields" routines but it can.
                            '---- AGAIN - LEAVE FOR AT LEAST A YEAR so user can restore older backups if necessary.
                            '---- 2008-01-23 Update width of Sign_locf field.
                            '2008-07-03 Change to check field width by using a lookup - much faster 
                            'that way! (Much faster in "computer time" - users probably won't notice 
                            'the extra few milliseconds.)
                            sloc = lookupc( "F", 1, "sign_locf", datapath + "Ord_Item.dbf", "" )
                            fwid1 = len( sloc )
                            IF fwid1 <> 11
                                'Change width of Sign_locf field in Ord_Item 2008-01-23.
                                tpx = table.open( datapath + "Ord_Item.dbf" )
                                fld1 = tpx.field_get( "Sign_locf" )
                                fwid1 = fld1.width_get()
                                tpx.close()
                                IF fwid1 <> 11
                                    Old_list = "Sign_locf,C," + ltrim(str(fwid1)) + ",0"
                                    New_list = "Sign_locf,C,11,0"
                                    a5_changefieldsize( datapath + "Ord_item.dbf", old_list, new_list )
                                END IF
                            END IF
                             
                            '*****************************************************************************
                            '*****************************************************************************


                            Cal, I don't know how to use your code, the situation is, what if i installed an app for a client,then after client used for say about 1 month, i decided to change some field rules in my computer/ development machine or maybe added some fields, what will I do with your code so that I can change the client's dbf's structure and field rules?
                            you said put it in the autoexec of the app,

                            Comment

                            Working...
                            X