Renaming tables within a DBC

[Mike Lewis]

What's the best way to programmatically rename the tables in a database container?

I thought RENAME TABLE would do the job. But it turns out that it only renames the table objects within the DBC, not the physical files (DBFs, CDX, FPTs).

I've tried using RENAME TABLE to rename the table objects, and then a straight RENAME to rename the physical files. But when I tried to open a table, it said that the backlink had been broken.

I know I can solve that problem by doing a VALIDATE DATABASE and then resolving the backlinks manually. But I want to do this in a PRG which will be run by non-techie users.

Anyone got any ideas about how I can do this?

Thanks in advance.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Mike
Have you tried

set default to c:\temp
CREATE DATABASE
CREATE TABLE temp (cField1 C(10),(same stru as other)
append from c:\dbfDir\dbf2del
CLOSE TABLES

set default to c:\dbfDir
OPEN DATABASE mydbc
SET DATABASE TO myDbc
REMOVE TABLE Dbf2Del
ERASE Dbf2Del.*
CREATE TABLE Dbf2Del (cField1 C(10),(same stru as other)
append from c:\temp\temp


David W. Grewe Dave

 

[white605]

Is the backlink that gets broken the .path property referred to in dbgetprop() and dbsetprop()? If so, could you reset it programatically.
wjwjr

 

[Mike Lewis]

David,

So, you're suggesting that I create a temporary database with the same structure as the existing, add all the existing data to it, recreate the tables (with the new names) in the existing database, then copy the data back.

I can see that that would work. But it seems a heavy-handed solution. There is a large number of tables involved -- too many to write all the CREATE TABLEs by hand. I would need some way to automatically generate the CREATE TABLEs from the existing structures (bearing in mind that the tables also have field properties, validation rules, etc). Not impossible, but a fair bit of work.

I'll follow up your idea, but only if I can't find a simpler solution.

White605,

Your idea looks like it's worth investigating. I'll look into it and report back.

I must say I am surprised how difficult this whole thing is. I would have thought the whole point of RENAME TABLE would be to wrap up all the steps needed to rename a table in a DBC. I can't help feeling I'm missing something obvious here.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Mike,

While I don't usually work with free tables and not with DBCs (so I am not the right person to answer your original question), and while I agree that David's solution seems to be correct but heavy-handed, I have to mention that you don't really have to write all the CREATE TABLEs by hand.

Wouldn't either pair of COPY STRUCTURE EXTENDED + CREATE FROM, or AFIELDS() + CREATE TABLE FROM ARRAY do the job here?

 

[Stella740pl]

Sorry. What I actually meant to say was, "I don't usually work with free tables and not with DBCs."

 

[AndyKr]

Mike

You cannot rename BOTH the physical files and the table name inside the DBC with one command. The two things are not actually related in any way.

The only way I know to do this is to remove the table from the DBC, then rename it and then add it back. That is easy enough to do programmatically, but of course you will lose any column names that are more than 10 characters long in the process.

Another possibility is to create a new DBC and use SQL to move the tables, with new names, into it. This way all you lose is the indexes and, presumably, you could re-create them easily enough.

Otherwise I don't know of any way to do it reliably.


----
Andy Kramek
Visual FoxPro MVP

 

[Mike Lewis]

Stella,

Thanks for your comment.

Andy,

Thanks to you as well.

You said:
<< You cannot rename BOTH the physical files and the table name inside the DBC with one command. >>

My problems wasn't to do both of those with one command. The problem was to avoid the interactive VALIDATE DATABASE.

I'll try your idea of creating a new database and copying the tables to it. Looks like I can do that with COPY TO <table name> DATABASE <database name>. I'll haver programmatically extract the index info in order to recreate it (using ATAGINFO() perhaps), but that's OK.

As I said earlier, I just wanted to make sure there was no simpler, more obvious, way of handling the whole thing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndyKr]

Mike

If you are going to copy the tables, then jsut use the "PRODUCTION" caluse on the COPY to get the indexes over too.

USE [table]
COPY TO [new-name] DATABASE [new-dbc] PRODUCTION

----
Andy Kramek
Visual FoxPro MVP

 

[Mike Lewis]

Andy,

PRODUCTION does indeed copy the indexes. I'd forgotten about that.

However, the next problem is that COPY TO doesn't copy any of the field or table properties (comments, captions, validation rules, etc).

I wonder if I should use COPY STRUCTURE EXTENDED / CREATE FROM / APPEND FROM. It seems long-winded, but if it's the only way ....

You mentioned using SQL to move the tables into a new DBC. Did you meand SELECT .... INTO TABLE? If so, that would have the same problem of not copying the field and table properties.

Maybe I should be looking for a different approach. Perhaps I can hack the DBC in some way.

White605 suggested using DBSETPROP() to change the DBC's Path property. I tried that, but it turns out the property is read-only.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[AndyKr]

Hi Mike

>> Maybe I should be looking for a different approach. Perhaps I can hack the DBC in some way

Well, I suppose you could, it's just a table after all, but you'd still have to rename all the files.

As for the SQL approach, you are correct, it won't get the indexes, or the rules etc...


----
Andy Kramek
Visual FoxPro MVP

 

[Mike Lewis]

Just to finish off this topic, here's what I ended up doing:

1. Used RENAME to rename the physical files (DBFs, CDX, and FPTs).

2. Hacked the DBC: Changed the ObjectType field to reflect the new table names.

3. Hacked the DBC: Did a STRTRAN() on the Property field to change the embedded table name.

Although the above steps worked OK for me, I do not recommend it as a general solution -- especially step 3. The Property field contains binary data, and doing string manipulations (sucn as STRTRAN()) on binary data is never a good idea. I got away with it this time, but might not in the future.

Thanks again to all of you for your help. I'm surprised that this has proved so difficult, but at least I can now tell the client that it's done.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk