Transfering data to amended database tables 1

[SimplyES]

In further developing one of my projects I have added a few extra fields here and there to some of the tables and changed the width of some fields in other tables. I am thinking, if I take a copy of the DBC from my development folder, open that DBC and ZAP all the tables, copy the 'live' data from the DBC currently in use to the just-ZAPped tables (using APPEND FROM) and then copy the newly updated DBC into the live environment.
Questions: 1) Can I have two DBCs open at the same time? 2) Will the above theory work in practice without dumping backlinks, formating etc? 3) Is there anything else I might not have thought of?!
Thanks

 

[Mike Lewis]

Are you saying that the extra fields and the amended fields are in the tables in the new DBC but not in the old DBC? If so, I can't see how that would work. By ZAPing the old, and then doing APPEND FROM the new, you would only be copying the data, not the new structures.

If I've understood it right, what you need to do is to drop the tables from the old DBC, then add in the new tables. You can use DROP TABLE and ADD TABLE respectively. But there is a snag. When you drop a table form a DBC, it will be converted to a free table, which means you will lose any of the DBC-specific features, such as long field names and field properties.

If the table does not have any DBC-specific features, you can drop it from one DBC and the and then add it to the other. But not otherwise.

You might be able to achieve your goal by using the GENDDBC.PRG program that comes with VFP. I don't have any recent experience of that, but there is a help topic which describes it, and the source code is available for you to study.

To answer your other question: yes, you can have two DBCs open at the same time - even DBCs with the same name (provided they are in different folders). But I'm not sure if that would help, as you can't drag objects from one database to another.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

I think what SimplyES is saying is that he'll zap the tables in the new copy of the dev database (which has the changes) and then append the data from the production database into those tables.

SimplyES, that should work as long as none of the changes you've made requires you to manipulate the existing data in some way to make it fit the new fields. From your description, that you've added some fields and changed widths of others, you shouldn't need to manipulate any of the data.

Tamar

 

[Steve Meyerson]

SumplyES,

I often make changes to dbf structures on my clients' machines without accessing their data. Not sure that is what you need. But if it is, I can post an abbreviated version of a method I use to do exactly that.

Steve

 

[Rajesh Karunakaran]

Hi SumplyES,

The method that you described is exactly the same what I do usually.
When there is a structure change, we copy the new data set (it may or may not have some sample/test data) at the client's pc and one routine zaps each table (those which are transactions) in the new set and append the data from the client's live table. Of course, this routine will run only after disconnecting all users and the database in Exclusive mode.

Yes, of course, as Steve said, if major data manipulation is not required. In that case, I believe, this method is the easiest, straight and error free approach. Our experts here, please post your observations/opinions.

Dear Steve,
You mean to say, you're able to make changes to your live database while users are still working on it?
Could you share some more details? All would like to see that I believe.

Rajesh

 

[Mike Lewis]

For what it's worth, the way I've approached this problem in the past is to write a program that creates the database (using CREATE DATABASE, CREATE TABLE, INDEX ON, DBSETPROP(), etc.). I run the program in the first instance to set up a database for development. As the database develops and changes during development, I modify the program accordingly. Then, when the system is ready to go live, I run the program again to generate the live database.

Although I hope this might be of use to anyone starting a new project from scratch, it would be difficult to do it retrospectively (but I have also used Stonefield Database Toolkit to achieve that goal with an existing application).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Colin Burton]

I have used XML files to store details about each table - fields, indexes, triggers etc.
the application will whenever started look at these tables and determine if there have been any changes to the current structures.

if there have been, then users are warned to stay out ( or more simply the client is told, just to run one application copy as the structures have changed ) - and then a maintenance process initiated to apply all required changes

this has always worked for me on a practical level.
#
Colin

 

[SimplyES]

A whole lot of really useful/helpful responses. Thanks to all - confidence boosted. One question still unanswered - can I have to DBCs open at the same time to manage the data exchange? Obviously the table naming needs to be retained throughout - I assume that if they are in different folders/directories and I use the full pathname at all times I should be OK?

Steve: A look at your method might well have some useful tips. Tks.

 

[Mike Lewis]

can I have to DBCs open at the same time to manage the data exchange?

I answered that in my first reply, above. The answer is Yes. You can even open two DBCs with the same name at the same time, provided they are in different folders.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SimplyES]

Mike: You did, indeed. Apologies. I obviously pondered the rest of what you said so thoroughly I forgot to read on! Tks

 

[Mike Lewis]

I obviously pondered the rest of what you said so thoroughly I forgot to read on!

Nicely put.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Dear Mike,

Even though the current process works for us, with some manual processes in between, since some time, I wanted to go for an exclusive version upgrade routine to do the job. Btw, you're handling the DBC Stored Procedures through APPEND PROCEDURE in your program?

Rajesh

 

[Steve Meyerson]

You mean to say, you're able to make changes to your live database while users are still working on it?

No. I didn't make that clear. Other users can't be using that data. In some cases the current data might not even "fit" into the new structure. This could make it impractical, in which case it may require a different approach.

However, if it's practical, my particular approach (there are many others) in general creates a method called from the main form's Init() method for use with dbfs. The updated structure is listed between TEXT TO cNewStru...ENDTEXT. Ex: CustName C(60).

ALINES is used to parse the name, type, width and decimals from each line of cNewStru. The 4 properties of each field are compared to the corresponding 4 properties of the user's table using AFIELDS.

If there is any difference, data from the user's table is appended to a temp table created from cNewStru, copied to user's table name (and path), and reused either shared or exclusive as needed.

Steve

 

[Chris Miller]

you're handling the DBC Stored Procedures through APPEND PROCEDURE in your program?

I can't answer for Mike, but if you replace the DBC/DCT/DCX files that also replaces the stored procs with the new ones, no need to do an APPEND PROCEDDURES.

That's not a main point, though, if you have stored proccs. Most of the time you then have things like triggers implementing referential integrity etc and would need to append in a specific order to not break referential integrity rules while you copy the data from an old to a new database structure.

That's when a new DBC without stored procs is an idea for the append phase. Finally, you're reestablishing the stored procs back into the DBC by use of APPEND PROCEDURES, that's surely working. You can still have issues with the DBC after it's upgraded, for example if the stored procs do something on the topic of audit trail data history logging, etc and the tables used for that are separate and need new or larger fields, too. You may say that's an upgrade error, but you actually may wont to keep historic data in the historic format/structure, too, so you need to handle the case of having multiple history files for each structure a table has in its lifetime.

Chriss