runtime update with fields

[netgeorger]

May be someone can clarify this once and for all. I have looked extensively on the board
But have missed something as what I have done doesn�t work. I want to update a runtime
Application on the other machine I have at the office. I have gone and put in some new fields
And a few new scripts. To upgrade the application I first separated out all the DBF files and FPT and CDX files and then copied what was left into a folder compressed it and put that folder Into the application folder on the runtime machine where I decompressed it and then copied all on top of the application files. I then got everything I had built, new form objects and looks and fields scripts . But all the new fields I implemented are in error. Naturally I have data on the runtime machine that is not on my developer machine. I think I have to copy the dbf file over to the runtime machine to ultimately get the new fields corrected. However, I don�t want to delete or Overwrite data or loose a year or so of data that are in the runtime dbfs. What is the sure fire way to handle this so my new fields can be added to the application without overwriting or losing the data in the dbf files on the runtime machine.
Thanks for any insight you can lend
Sincerely George Kenney

[Keith Hubert]

Re: runtime update with fields

Hi George,

If you can get to import a script into the runtime machine, create one that writes new fields to an exiting table. Do a search in the Help file for tbl.FIELD_ADD(), this I would recommend as the best way forward not to lose data in an existing table.

[CALocklin]

Re: runtime update with fields

George, you've said some scary things but I don't think you've created any serious problems yet.

First things first - MAKE SURE YOU HAVE A SEPARATE BACKUP COPY OF EVERYTHING ON THE SERVER.

You said, "Naturally I have data on the runtime machine that is not on my developer machine." However, I'd say that the word "Naturally" isn't applicable at all in this case. It is normally more correct to say, "Naturally there is no actual data on my workstation." (Note the use of the term workstation.)

However, maybe this is just symantics. Let's first clarify terminology:
Runtime - a copy of the A5 program that does not allow editing of code or restructuring tables. The runtime program could be put on the server as well as a workstation.(FWIW: Previous runtimes didn't allow editing layouts either.)
Workstation - a computer that is linked to a server and normally uses the data that is stored on the server. The workstation might have a full copy of A5 or a runtime copy of A5 although most workstations are usually set up with the runtime. (I know of several workstations that have the full version. I also know of several servers that have the runtime installed.)
Shadowed - This is another term that is actually separate from both of the above. A "shadowed" installation can actually be put on the server or on a workstation and can be used with either the runtime or the full version of A5. A shadowed installation is just one where the local data files 'point' to the real data in a different folder. Usually the 'different folder' is also on a different computer but it doesn't have to be. (However, there is also no reason for it to be on the same computer other than, perhaps, testing an installation routine.)

Now, do you INTENTIONALLY have data on the workstations that is NOT EVER to be on the server? This is rare but it does happen in some special situations. Changing table structure is more complicated if you have tables like this that need to be updated. However, I've never seen any server based application where more than one or two "personal" tables were on the workstation. I'm not talking about someone simply "entering data from the workstation" because the normal action in this case is for A5 to see that the .dbf file on the workstation is only a 'pointer' file (187 bytes) that points to where the real data is on the server and then the data ends up on the server.

Assuming you have shadowed workstations with no real data on them (the normal situation), you haven't done anything dangerous up to the point described. Removing the .DBF, .CDX, and .FPT files before copying your updates to the servers was exactly what you should do.

But, while we are talking about copying files around, let me add a couple warnings about the .ADB file:
1. NEVER copy the .adb file from the server to the workstation. The .adb file is different for the "master" (server) database than it is on shadowed workstations. (Again, note that a workstation doesn't have to be shadowed but it usually will be. If it isn't shadowed but still accesses the data on the server, then there won't be any data files or dd*/se* files on the workstation at all. It may also run slower.) If you copy the one from the server to the workstations, the workstations will not refresh properly - but I don't think this is your problem.
2. NEVER, NEVER, NEVER copy the .adb file the other direction - from a shadowed workstation to the server. If you do, I hope you have a backup of the original .adb file or are prepared for a fairly complicated rebuild. My recommendation: NEVER copy anything from the workstation to the server unless you are very careful and absolutely sure of what you are doing.

I think your problem is simply one of restructuring the tables on the server before installing the update. Actually, I'm pretty sure that restructuring them now, after the update was installed, will also solve your problem. There are many ways to do this....

One method would be for you to edit the tables that are already on the server and add the new fields - of course, this assumes that you have a full copy of A5 on the server and can halt work on the server temporarily while you update them. This can be done before installing the actual update files. I believe it can also be done afterward (now) and everything will appear normal as soon as the new fields are added. This is probably your easiest and fastest way this time.

Another method some people use is to create a new, empty table with the new structure, append the old data to the new table, delete the old table, and rename the new table to the original name. I believe this pretty much requires the developer to be present (in person or remotely) when making this type of update - but I could be wrong. (I'm sure it could be automated but I don't think most people bother.)

My preferred method is to put the field updates in the autoexec script so they will be automatically updated the first time the new application is run. Then I tell the users they must all close out for a couple minutes while I install the update. The new files are installed and the first user to start the application will update the fields without even knowing it.

Another advantage to this method is that they can install an old backup and it will automatically be updated to the new structure when the application is restarted. Otherwise the old data won't work right with the newer application - as you have seen - and the backup will be difficult at best to work with. And, a backup that can't be used isn't worth much.

Here are sample routines of mine. (There are similar examples on other threads as well. You can also get much more complex but these are the basics and handle 90% of my table structure updates.)

 
[COLOR=blue]'Get the "datapath" so we know where the REAL data is - in case this [/COLOR]
[COLOR=blue]'is running from a workstation. (In other words, yes, this can be run [/COLOR]
[COLOR=#0000ff]'from a workstation as well as the server.)[/COLOR]
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
 
[COLOR=blue]'Check LAST table and LAST field updated first. If it has already been done, skip the rest.
[/COLOR]fld_list = a5_get_fieldnames( "Program_Options.dbf" )
IF "open_frstf"$fld_list '2009-05-23
  GOTO Updates_done
END IF
 
[COLOR=blue]'---------------- Add new fields to PENDING_ORDS table[/COLOR]
fld_list = a5_get_fieldnames( "Pending_Ords.dbf" )
'2009-01-226 Add Zipf field.
IF .not. ( "Zipf"$fld_list )  [COLOR=blue] 'NEVER add without checking to see if it's there first!
[/COLOR]  new_flds = <<%list%
Zipf,C,10,0
%list%
  a5_add_fields_to_table( datapath + "Pending_Ords.dbf", new_flds )
END IF
 
[COLOR=blue]'---------------- Add new field to CLIENTS table[/COLOR]
 
[COLOR=blue]'When multiple fields have been added at different times, make sure they [/COLOR]
[COLOR=blue]'are added in the correct sequence. I keep them in the list in case someone [/COLOR]
[COLOR=blue]'needs to install an old backup that doesn't have those fields yet.[/COLOR]

 
[COLOR=blue]'This also shows how multiple fields can be added and how the field(s) can [/COLOR]
[COLOR=blue]'be update afterward if necesssary.
[/COLOR]fld_list = a5_get_fieldnames( "Clients.dbf" )
[COLOR=blue]'2008-04-10 Add fields to Clients.[/COLOR]
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
[COLOR=blue]'2009-02-23
[/COLOR]IF .not. ( "Rnw_afterf"$fld_list )
  new_flds = <<%list%
Rnw_afterf,N,2,0
%list%
  a5_add_fields_to_table( datapath + "Clients.dbf", new_flds )
  'Set the default to -1.
  tpc = table.open("clients")
  tpc.index_primary_put("")
  tpc.fetch_first()
  tpc.batch_begin()
  WHILE .not. tpc.fetch_eof()
    tpc.change_begin()
    tpc.Rnw_afterf = -1
    tpc.change_end()
    tpc.fetch_next()
  END WHILE
  tpc.batch_end()
  tpc.close()
END IF
 
[COLOR=blue]'---------------[/COLOR]
Updates_done:
[COLOR=blue]'---------------[/COLOR]

Final note: ALWAYS add new fields at the end of the list. I think a short study of the above code will reveal why.

EDIT: Sorry, I just noticed that when I did the copy/paste thing for the example, I left it so the example checked for a field in my Program_Options table. This was the last field that was added in my application. However, I took out that portion of the script. It really doesn't matter for the purpose of the example as long as you realize that there would normally be a routine that would add that field. I took that part out because my whole script is now 501 lines long and the Program_Options portion is 212 of that.

[netgeorger]

Re: runtime update with fields

Thanks for your reply your response is pure gold.
I really appreciate the information
and it will come to good use.
I did�t really understand the Adb files or the shadow on a
network and your response and code is very helpfull.
I have been working with a developer and a single runtime
on another machine. In the future I will set up my server
situation so my solution was to simply to copy back and forth.
I copied everything new to the runtime except the dbf cdx and
fpt this updated the look and didn�t affect the data. after that
I simply zipped the database took it to my developer added the fields and then took it back to my single runtime with a new location for the new database. I used my
developer at one time to input about three quarters of the data.
Anyway I didn�t know the ends and outs of updating a runntime.
and had an Idea there was a simple solution I couldn�t really see. In the end the update is something that has to be carefully planned and executed especially in a server environment which is what I have learned from this as I am sure others will appreciate.
Thank you
George Kenney