Importing existing data into new table

[Paul E. Schoen]

I have a D4 Pro application that uses dBase files, and need to change the
structure of a table to add a new field and keep the contents of the
existing data. I planned to do so by supplying a new table "OrtRes1.dbf"
with the new structure and then import the data from the original
"OrtRes.dbf". For a new installation, I would just supply the new table and
if the old table is not present, the import would not be required.

At first I inserted a new field at a convenient location, but when I tried
to import the data by using BatchMove, it copied OK up to the new field and
then ignored the remaining fields. I also had problems using the batDelete
parameter. The only way I could find to handle this was to use
TBatchMove.Mappings to specify each field, which seemed cumbersome. So I
changed the structure so that the new field was at the end, and it seemed to
work OK:

If FileExists( OLDRESULTSFILE ) and FileExists( RESULTSFILE ) then begin
//1
if Application.MessageBox( 'Import old data from ' + OLDRESULTSFILE +
' to ' + RESULTSFILE + '? ', 'Import Results', MB_YESNO ) = ID_YES then
try //2
tblResOld := TTable.Create(self);
tblResOld.TableType := ttDBase;
tblResOld.TableName := OLDRESTABLENAME; // File name without
extension
tblResOld.Open;
CopyFile( RESULTSFILE, RESBACKUP, TRUE ); // Save new file
tblRes.EmptyTable; // Clear data
tblRes.BatchMove( tblResOld, batAppend ); // Update new file with old
data
tblResOld.Close;
if not RenameFile( OLDRESULTSFILE, OLDRESBACKUP ) then // Save
original file
Application.MessageBox( 'Error Renaming File', OLDRESULTSFILE,
MB_OK );
finally
tblRes.Close;
if tblResOld <> nil then
tblResOld.Free;
end; //-2
end; //-1

Another way I might handle this would be to check the structure of the
existing database and add the new field if not present. This would also
eliminate the need to change the filename. What is the "best" way to do
this?

Thanks,

Paul

[a***@aol.com]

Paul

You don't seem to be getting any assistance. I'm not big on databases,
but long ago I did well on dBase files and have the MFoxPlus program.

If you have access to dBase or similare then IMO the easiest way would
be to insert the field using DBase, You COPY MyBase.dbf to
MyBaseStr.dbf STRUCTURE EXTENDED. This provides a DBase file of the
_fields_, one record per field then you insert a record of the new
field into that database, and then you CREATE MyBaseNew FROM
MyBaseStr. Then you USE MyBaseNew and APPEND FROM MyBase & all records
would be added.

If you're really stuck I would be prepared to do this if you sent me
the files (unless they are impossibly big). I'd have to joggle my
memory a bit but it might work <g>.

Of course if your dBase files are a much later version, then my
MFoxPlus might not work on them.

Alternatively the dBase file structure is fairly simple and one could
write a modifying app using streams. www.wotsit.org has details
of .dbf file structure among its many others.

Alan Lloyd

[Paul E. Schoen]

Post by a***@aol.com
Paul
You don't seem to be getting any assistance. I'm not big on databases,
but long ago I did well on dBase files and have the MFoxPlus program.
If you have access to dBase or similare then IMO the easiest way would
be to insert the field using DBase, You COPY MyBase.dbf to
MyBaseStr.dbf STRUCTURE EXTENDED. This provides a DBase file of the
_fields_, one record per field then you insert a record of the new
field into that database, and then you CREATE MyBaseNew FROM
MyBaseStr. Then you USE MyBaseNew and APPEND FROM MyBase & all records
would be added.
If you're really stuck I would be prepared to do this if you sent me
the files (unless they are impossibly big). I'd have to joggle my
memory a bit but it might work <g>.
Of course if your dBase files are a much later version, then my
MFoxPlus might not work on them.
Alternatively the dBase file structure is fairly simple and one could
write a modifying app using streams. www.wotsit.org has details
of .dbf file structure among its many others.

I appreciate the suggestions and offer to help, but I have no problem
converting my own dBase file OrtRes.dbf to the new structure with the added
field (and also one field changed from 20 characters to 200). I can do this
by using the DatabaseDesktop, but it has compatibility problems in Vista and
it crashes the BDE engine until I do a restart. I can also make the changes
to the table structure using OpenOffice Calc, which essentially presents the
dBase file as a spreadsheet.

But I need to have my customers update their unique files, which contain
test results that they have saved over several weeks time, so I need the
application to do this after they have installed the new version and run it
the first time. The method I came up with works OK, but I'd rather have the
new field among those at the start of the record rather than at the end.

What I am unsure about is whether it is better to provide a new dBase file
with a different name and the new structure, and then import the existing
data to this new file, or leave the file name the same and simply add the
new field programmatically. I thought it would be cleaner to simply import
the existing data into a new, blank table. But the BatchMove function does
not automatically match field names except with a cumbersome mapping
parameter for all existing fields, and there are quite few fields, I think
it may be best to restructure the table in the application and keep the
original file name.

Thanks,

Paul

[Paul E. Schoen]

Post by Paul E. Schoen
I appreciate the suggestions and offer to help, but I have no problem
converting my own dBase file OrtRes.dbf to the new structure with the
added field (and also one field changed from 20 characters to 200). I can
do this by using the DatabaseDesktop, but it has compatibility problems in
Vista and it crashes the BDE engine until I do a restart. I can also make
the changes to the table structure using OpenOffice Calc, which
essentially presents the dBase file as a spreadsheet.
But I need to have my customers update their unique files, which contain
test results that they have saved over several weeks time, so I need the
application to do this after they have installed the new version and run
it the first time. The method I came up with works OK, but I'd rather have
the new field among those at the start of the record rather than at the
end.
What I am unsure about is whether it is better to provide a new dBase file
with a different name and the new structure, and then import the existing
data to this new file, or leave the file name the same and simply add the
new field programmatically. I thought it would be cleaner to simply import
the existing data into a new, blank table. But the BatchMove function does
not automatically match field names except with a cumbersome mapping
parameter for all existing fields, and there are quite few fields, I think
it may be best to restructure the table in the application and keep the
original file name.

I've given this more thought, and it may be more complex than it seemed at
first. It looks like I will need to make a copy of the existing table, then
clear and restructure the original table by accessing its TFieldDefs and
adding the new field where desired and changing the properties of the field
that needs 200 characters. Then I would need to set up the parameters for a
batch move from the new table, to map the field names. Once done, the new
table could be discarded.

I found a freeware component that supposedly will do the job. Looking at the
code makes me think that this is indeed a major project and not just a few
lines of code. Here is the link to the download, if anyone wants to look:
www.torry.net/db/access/db_bde/tfieldupdate.zip.

Maybe my first idea is the way to go, but instead of changing the
application to use the new database with the new name, just use batchcopy to
save the results to OrtRes1.dbf, and then rename OrtRes.dbf to OrtRes.dbk,
and then rename the restructured database to OrtRes.dbf. I can live with the
new field being at the end, which will save the mapping of 35 fields. Yup,
that works for me.

Paul

[a***@aol.com]

On 7 July, 21:59, "Paul E. Schoen" <***@pstech-inc.com> wrote:
<snip>

Post by Paul E. Schoen
At first I inserted a new field at a convenient location, but when I tried
to import the data by using BatchMove, it copied OK up to the new field and
then ignored the remaining fields. I also had problems using the batDelete
parameter. The only way I could find to handle this was to use
TBatchMove.Mappings to specify each field, which seemed cumbersome.

<snip>

Paul

AFAICS from my D3 source code, that is the way it does it. The
TTable.Mappings (a TStrings, each item in the form of
<destname>=<sourceName> which you must supply for non-identical field
structures) tells the TBatchMove.Execute how to copy the fields. If
there is no TTable.Mappings TString then it assumes the field-
structures are identical, and chokes if they are not.

Alan Lloyd