How to copy a table between projects?

[k2a]

Hi all,
Could someone give me a tip on how to transfer a table (customer) from one database to another database with the entire contents? Would it be necessary to first convert the table (customer) into a free table?
Regards
Klaus

 

[Mike Lewis]

You are asking two differnt questions. Do you want to copy the table between projects (which is the title of your thread)? Or do you want to transfer a table between databases (which is the text of your question)?

Copying between projects is easy. Just open both project side by side, and copy the table from one to the other. Note that that does not create a copy of the table. It only creates a new reference in the destination project, to the same table.

If you want to transfer the table from one database to another, you can either drag and drop between the database windows, or use the command [tt]COPY TO <filename> DATABASE <database name>[/tt]. But when you do that, you will lose all the database-specific settings such as long field names, field and table properties, captions, etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Correction. To transfer a table to another database, you can't drag it to another database window. You can only drag it between projects, which simply creates a new reference to the table, as noted above. If you want a second copy of the table, use the [tt]COPY TO[/tt] command (and loose all the long field names, etc).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

In the bigger context, this points out the need to have the same database (schema and data) in several projects. And that speaks for separating the database itself into its own project.
Other projects may reference it, all they need is the DBC filename to open it at the appropriate time of application initialization. Notice you can have multiple projects open in the same VFP IDE (window) or start VFP twice or more.

Otherwise, COPY TO is the solution and doesn't drop off long field names, if you use the DATABASE clause:

https://docs.microsoft.com/en-us/previous-versions/visualstudio/foxpro/17x1yfx5(v=vs.71)

You have a small problem, if the two DBCs have the same name, just other paths: You need a third DBC of different name, as COPY TO can only target a specific DBC, when it has a specific name. That points out why a separate DBC project is a good idea, you can and should have copies of your whole database, as test and development versions.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

COPY TO is the solution and doesn't drop off long field names, if you use the DATABASE clause:

Just to be clear: copying to another database does not drop long field names. But it does drop all the other table properties, such as input mask, caption, validation rules and comments. (Sorry if I got that wrong before.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Not sure, but if that fails there is
a) AFIELDS
b) CREATE TABLE FROM ARRAY (using the array AFIELDS generates)
c) APPEND FROM DBF to finally copy over the data

There also is COPY STRUCTURE EXTENDED.

Abd then there is GenDBC to copy over the full structure without data and finally you can append that tale by table.

Or

Simply copy over the whole database in the version with the additional table.

Or (as already said)

Share databases in multiple projects by making them their own separate projects.

Not to forget to mention Stonefield Toolkit and the DBCX definition of database metadata usable to sync database structures.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[k2a]

Thanks for your quick responce.
That's a lot of information and now i have to check what fits me best.
Regards
Klaus

 

[Olaf Doschke]

I assume you have two copies of the same database and extended one (say the original) and now "curse" about the nature of database tables not to be easily transferred by file copies.

Well, you can work on the level of mere file copies.

Under the precondition, that the two databases were equal (in structure and stored procs, anything regarding the DBC itself) before creating a new table in one of them. You then can copy DBC files and the files of the one new table and have that new table added to the old unchanged database copy, too. Even though it may have other/more data, nothing is lost.

This code is not necessary, it just demonstrates, that this works:

Cd Getenv("TEMP")
*Close Tables all
Close Databases All
Set Safety Off

* some directory preparations
If !Directory("tablecopytest")
   Mkdir tablecopytest
Endif
Cd tablecopytest

If !Directory("original")
   Mkdir original
Endif
If !Directory("copy")
   Mkdir Copy
Endif

Cd original
Erase *.*
Cd ..\Copy
Erase *.*
Cd..

* create two identical DBCs by creating one of them and the other by a full copy:
Local lcDBC

lcDBC = ".\original\tablecopydbc"+Sys(2015)
Create Database (lcDBC)
Create Table .\original\copytest1 (iID Int Autoinc, cSomedatafield c(10), Primary Key iID Tag pix)
Close Tables All
Close Databases All
Copy File .\original\*.* To .\Copy\*.*

* Now, create a new table in the original DBC:
Open Database (lcDBC)
Create Table .\original\copytest2 (iID Int Autoinc, copytest1ID Int, Primary Key iID Tag pix,;
   foreign Key copytest1ID Tag ct1ix References copytest1 Tag pix)
Close Databases All
* And now only copy over the dbc files and the new table files
* (notice lcDBC only has the stem name of the database without file extensions)
* 1. copy dbc
Copy File (lcDBC+".*") To .\Copy\*.*
* 2. copy new table
Copy File .\original\copytest2.* To .\Copy\*.*

The reasoning is simple: Just copying the dbf files and opening it in its new path you get an error, as the other DBC doesn't know this table, but you just need to also copy the DBC files, then these missing information about the new table are "transferred" to the other DBC, too. Well, not really transferred, the whole DBC is transferred.

Of course, there is a situation this does NOT work for: When you once had the same databases from a copy of the original one and then make different changes to both the original database and the copied database, you can't merge that just with file copies.

Besides that, it's also nice to know a database doesn't need all its tables to be able to OPEN DATABASE without error. If your customer has one dbf defect he can give you that dbf files additional to DBC files and you have all you need to analyze this, you don't need a whole database and all its files. The dependency only is from table to database, not from database to table. Because tables don't contain any info the database needs, the database contains long field names, default values etc and the dbf a backreference.

Anyway, this doesn't mean a simple recipe, as in general if some time has passed since two databases evolved in different paths, you usually have things in one DBC missing in the other and vice vera, so both DBCs need to be extended with info from the other. That's why I recommend having a separate database project and sharing that with several projects or at least have that database project as the main original template you copy over into projects.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[k2a]

No, Olaf the databases are not the same and i just need only the clients table in my new database.

OK, first off all I tried to use the Copy To command, but got stuck using it.
Next used the Copy File command and copied the .dbf, .cdx, .ftp, tbk files into .pjx folder and added that table.
However, trying to open or modify the table ends in an error: Primary key property in valid, please validate database.
The database validation did not fix it.

Don't know how to fix the Primary key issue.

So my question still remains, how to copy clients from database ABC (old project) to customers into the database XYZ (new project).

Regards
Klaus

 

[Olaf Doschke]

Yes, since your XYZ DBC doesn't have info about the dbf it doesn't help to copy it over and ADD TABLE.

CYOP TO will work this way:

OPEN DATABASE C:\pathtoabc\ABC.dbc
OPEN DATABASE C:\pathtoxyz\XYZ.dbc
USE ABC!clients
COPY TO C:\pathtoxyz\clients.dbf DATABASE XYZ WITH CDX

I don't think this will strip off anything, but according to Mike you lose things like captions, Table/Field rules, you might check the details. But you will have an XYZ!clients table and client data in that table.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

Klaus, you say you want to copy your client table to your "new" database. If this really is a newly-created database, then there might be a simpler option.

Simply copy the entire database (that is, the DBC, DCX and DCT, as well as all the DBFs, FPT and CDX) to a new directory. Keep all the names the same. You can use COPY FILE for that, or simply do it interactively in Windows.

Then, in the new database, delete all the tables except the client table. Do NOT delete the individual files. Instead, use DROP TABLE <tablename>. If the database contains any views, delete those as well, using DROP VIEW.

You will end up with a new database, containing your client table, with all its table and field properties intact, including long field names.

The only snag is that there is no easy way of renaming the new database. If you simply renamed the DBC, DCX and DCT, you would lose the connection with the client table. That in turn means you must keep the new database in a different directory to the original. If you can live with those restrictions, I think the above method will do what you want.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[TamarGranor]

If your goal is to get all the data from one Customer table into a new copy of that table that you can then add to a different database, use a query and then ADD TABLE. YOu need to substitute your actual folder names here.

SELECT * ;
  FROM OldFolder\Customer ;
  INTO TABLE NewFolder\Customer

SET DATABASE TO NewDatabase
ADD TABLE NewFolder\Customer

Tamar

 

[k2a]

Thanks Olaf your Copy To worked right away and i don't see any striping at all.
Great thanks to Mike and all of your for the quick responses trying to help me.
Regards
Klaus

 

[Olaf Doschke]

I don't know what stripes you expected, striping off has another meaning.

Anyway, you'd need to look into the table properties. Look at that table in the table designer to see missing rules, captions, trigger calls.

If I copy a table from sample data (like this categoreis table from Northwind) into a new dbc, those things are actually lost.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

... use a query and then ADD TABLE ...

Yes, that will transfer the data, but it won't retain the long field names or the field and table properties.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You can get over everything from a table to another database when you start off with AFIELDS, it just takes an awful lot more steps because in itself it just creates the information, needs a CREATE TABLE which then still hasn't copied over data. But then, what do you have from trigger calls, if the new DBC doesn't have the stored procs called? There's no good solution really.

If you're about partial designs of your databases you want to recycle, the best idea would be using the GENDBC tool on all your databases and then manually pulling together the parts of the code you need for specific tables or sets of tables. Code is still the most versatile way to recreate the same database structures.

What still makes me wonder whether Mikes latest advice to copy the whole database and get rid of what you don't want to keep would be another good solution, or altering other tables and adding new ones there. Because when you don't just want to keep a table structure of clients but also the data, that points out to me the new database still is about the same customer, same application, just a newer modernized version of it, and you do cover that with database updates aka alter table statements, not by creating a new database and pulling over single tables.

There could be good reasons to keep the old database structure and data as an archive and start over with a copy you alter for a new version, eg with a major VFP version upgrade and rewrite of the application. I also programmed 2 very extensive database migration applications which in themselves took half a year to write and test and you do create a DBC with new structure and pull over data with transformations, typically, mainly what I did was having an OOP framework which worked through data from both perspectives, depending on what better applied, either going through the new tables and pulling together data from the old DBC, mainly by [tt]INSERT INTO newdbc!newtable SELECT ....query FROM olddbc!oldtable[/tt] sometimes with [tt]JOIN[/tt]s or the other way around go through the list of old tables and spread out the data from there, where it has to go.

If an application isn't just a bunch of tables such data migrations can be an enterprise on their own. On top of that, if you move to MS SQL Server along the way.

OK, I'll calm down. You know best you just needed that one table back in your new database and that's it. Anyway, you typically don't need one table from another database, data is typically related and so has depedencies with other data and triggers for referential integrity involved and more and you don't operate one table from a source database to a target database, that's not a basic operation needed, you need an ETL process, export/trasnform/load, when you go from data for mature app v1.6 to app rewrite v2 or even from standard to your custom app or from your custom app to standard software backends. But when you go from app v1.5 to v.16 that should only need a few table alterations, just like you only modify some code and maybe add a feature to your app, you modify a few tables and add some new.

So the summary essence of all this is it's understandable VFP has no transfer-DBF-from-one-DBC-to-another command or the SQL language doesn't have that.

And as you likely have seen, the transfer from PJX 1 to 2 even is automatic, the PJXes only refer to the DBCs, the table of the DBCs are in the treeview when you drill down, you don't put all the single tables into a PJX, that's only necessary for free tables. And in that case, it's nice you can simply pick a list of files in Windows Explorer and drag them into project manager window and they're added where they belong to by their file extension.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

It would also be useful to know whether this is a one-off requirement, or part of an on-going application. If the former, I think my suggestion of copying the whole database and deleting the unwanted bits would be the simplest. But if not, a more programmatic approach would be needed.

By the way, I said earlier "there is no easy way of renaming the new database. If you simply renamed the DBC, DCX and DCT, you would lose the connection with the client table." To clarify that, you can't rename the database from within the IDE, for example, by right-clicking on the database within the project and then selecting Rename. That will give an error, because the database is always open at that point - and you can't rename an open file. Nor is there a RENAME DATABASE command.

What you can do is rename the physical DBC, DCX and DCT files, but that will cause a broken backlink in the table, resulting in an error message when you try to open the table. However, the error message includes Locate, Delete and Cancel buttons. If you choose Locate, you can then navigate to the DBC, and that will restore the backlink. Again, this solution is appropriate if this is a one-off requirement that you can deal with interactively.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

By the way, I said earlier "there is no easy way of renaming the new database. If you simply renamed the DBC, DCX and DCT, you would lose the connection with the client table."

Yes, we had a thread about that, let me see... thread184-1744720

But I also successfully renamed a database with the Rename context menu item of the database item in a project manager. Which includes renaming the backlinks in all database tables. As mentioned here:

https://social.msdn.microsoft.com/F...atabase-name-in-vfp?forum=visualfoxprogeneral

Pavel was also surprised this works. It has its difficulties, as the database has to be closed and the project manager keeps a database open when its node is expanded.

Overall those less manageable dependencies of DBC DBFS are surely also reasons why some prefer bare free tables.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Mike Lewis]

But I also successfully renamed a database with the Rename context menu item of the database item in a project manager. Which includes renaming the backlinks in all database tables.

OK. After reading those links, it's starting to come back to me.

Firstly, when you try to use the context menu within the project manager to rename a database, you will probably get a "File in use" error. That's because the database is open at that time. Even if you have not explicitly opened any tables, or done anything else to open the database (such as opening the MODIFY DATABASE window), the database will probably be open simply because the database's tab in the project manager has been expanded.

Executing CLOSE DATABASES [ALL] does not help. The database is still open behind the scenes, even if the database's tab in the project manager is no longer expanded.

Secondly, if the tab is expanded, and you then close the project manager and then open it again, the database will still be open. That's also true if you quit VFP and then go back in. The project manager remembers the fact that the tab was expanded last time, and even if you don't take any further action, it will again open the database behind the scenes.

What you have got to do is to collapse the database tab, then close and re-open the project. At that point, the database will be closed and you can go ahead and rename it. And, as Olaf pointed out above, when you do that, the backlinks in all the contained tables will be updated, so everything will be fine.

I hope I've explained that clearly and am not just confusing the issue.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

The silly thing is that I knew all this stuff once, but had forgotten that I knew it. I had even forgotten that I once wrote a long article about the whole subject of backlinks, which included a discussion on renaming databases and tables. If anyone is interested, it is at

http://www.ml-consult.co.uk/foxst-47.htm

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads