Convert Foxpro 2.x table to VFP 9

[gkratnam]

How do I convert an old foxpro 2.x table to a VFP9 version? Opened the table in VFP and added a field and did re-index. Would that do it? How do I check that it was updated?

This is the table structure.

Project_id N(10)
Task_id N(10)
User_id C(10)
Notes M
Bill_hours N(8,3)
isBillable L

I don't want to change the existing fields' type. Since the code is referencing them in many places. First step is to update the table and any new fields will use the VFP data type (Eg: after_hours: DateTime; Bill_id: I. etc...)

Please guide me with this. Thanks!

 

[Mike Lewis]

The short answer is: Nothing.

If you are not adding any fields or changing any data types, you can continue to use the table in VFP just as you did in 2.x. And it will still work in 2.x.

If you later add a field, or change a data type, and the new data type is VFP-specific (datetimes, etc), then you will no longer be able to open the table in 2.x, but you will be able to continue to use it as normal in VFP. The same is true if you add the table to a database. But I'm assuming none of that will be an issue in this case.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Having said that, one issue that you (or your users) might come up against is the code page dialogue. In short, if you open a 2.x table in VFP, and the table is not marked with a code page, VFP might display a dialogue asking you to select a code page. Clearly, you won't want your users to see that.

The solution is to execute SET CPDIALOG OFF before you open any tables.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

And you asked how you can check if the table is 2.x or VFP. Just call SYS(2029) - check the Help for details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rick C. Hodgin]

If you want to convert it, you can do this:

* In VFP9
SELECT * FROM old_table.dbf INTO TABLE new_table.dbf

It will convert your 10-digit memo fields in old_table to the 4-digit form in new-table, and assign the default code page.

--
Rick C. Hodgin

 

[gkratnam]

Thank you both!

For the converted table without type changes, Sys(2029) returned 245 (Previous versions of FoxPro with a memo field). Yes, I can open it in both versions.

Then tested another table with adding an Integer, Sys(2029) returned 48 (Visual FoxPro with or without a memo field). No longer able to open in old version. It is all make sense now.

Tried Rick's suggestion. Table size is about 400,000KB(dbf+fpt) with 2.4 million records. It took almost an hour to do the conversion.

Is there any difference in performance based on these conversion methods? Does one better than the other?

Thanks!

 

[Rick C. Hodgin]

Make sure you USE EXCLUSIVE when accessing large data like that. It will go faster. Especially on a network.

Is there any difference in performance based on these conversion methods? Does one better than the other?

Probably not. If you have multiple memo fields, having to retrieve only 4 bytes per memo field in the actual DBF compared to 10 in the old DBF will save a little. But apart from the code page dialog messages you get when opening the 2.x table, there's no real advantage.

As far as functionality, you can bring the table into a database container and get longer field names, triggers, etc. There are advantages there, but in terms of standalone table use ... no.

--
Rick C. Hodgin

 

[Chris Miller]

The other obvious advantage is new field types once you convert. But you know when you do ALTER TABLE or edit a table in the table designer, this also means making a full copy of the dbf/fpt anyway, just like SELECT * INTO TABLE. So when you plan extensions, you can convert and extend in a single step with an ALTER TABLE sql and that will write a new header in the needed format.

There's also cpzero. Which I think just removes codepage info from the header and doesn't make file changes.

One way to have more control about codepage changes would be to exprot to csv and then read that back, but it's problematic with memos and needs precision in date formatting within the csv, about century yes/no, what's used as separator and in which order you have MDY or DMY or even YMD. When you have the textfile you can use many more tools even outside of VFP to care for text conversions and any specialties that have been used. VFP manages to do one ANSI codepage to another obviously only with the common set of characters. Most often that isn't even about the language specific ANSI codepages but DOS vs Windows.

For DOS vs Windows it's a good step to not just make the fast codepage header information switch but actually convert all text data and whatever detail differences are in other data types.

Chriss

 

[gkratnam]

Tried the conversion with SET EXCLUSIVE, it still took about the same amount time (about ah hour), but it is not an issue. All good.

Thanks for all the help and knowledge sharing!

 

[Chris Miller]

The only thing that could make this faster is having the data local on a faster drive.

converting the DBF just like ALTER table is creating a new table header and index definitions, then writing to it record by record, with indexes built up row by row at that time, what can make this a bit faster is deleting all index tags, then converting and then recreating indexes, as index creation on a table with all its data in takes less than when it's created record by record, even though that's what still happens, it's not interleaved by creating the records themselves, adding to the FPT etc. switching back and forth between the DBF, FPT and CDX files.

I'm not sure but think this also has to do with creating an index cached and with much memory usage. This also is indicated by the special corner case off creating a binary index type (for logical fields) on a large table failing with insufficient memory, but maybe only is true because that's not storing a index tree but the bitmap of the logical values. Anyway, experience still tells me indexing after packing an unindexed DBF or creating a table without indexes, then adding initial data and then indexing takes less time.

All that will not save essential time, though, it differs by a few percent only. It takes at minimum the time it takes to read the data and rewrite it to a new file. And the bottleneck in that is the network speed, if you do it on a table stored on a network share and don't run this on the server.

It also doesn't pay to copy data to a local drive, alter/convert and then store back the result. A windows server is a great way to do some server side things in VFP locally.

Chriss

 

[gkratnam]

Thanks for sharing the knowledge Chriss.

SELECT * FROM oldtable into TABLE newtable

This command created DBF and FPT only. No index CDX were created.

Anyway, the timing is not a big deal. This table conversion is just a one time thing. I am good with all of your feedback.

Thanks!

 

[Chris Miller]

Oh, then you may be missing to copy over indexes. But SELECT INTO TABLE never copies CDXes, even if they exist.

The better way to copy a table is by using it, then do COPY TO file WITH CDX, if you don't mind this to take longer than creating the indexes from scratch.

Chriss