BorlandTalk.com

Martin Kascak · Guest

I'm sorry, maybe I wasn't clear the first time. I'm trying convert the data
from dos application where data are stored in dbf tables, to the similar
application created in Delphi 5 and using Interbase database(gdb). The
databases are not identical and a just want to convert some of the fields,
that are memos in dbf, into varchar fields in gdb database. It's something
new for me, so I'm just trying figured out where to start.
Thanks.

Robert Schieck (TeamB) · Guest

Martin Kascak wrote:

John Herbster · Guest

"Martin Kascak" <vyptech (AT) stoline (DOT) sk> wrote

Martin Kascak · Guest

"John Herbster" <herb-sci1_AT_sbcglobal.net> napísal v správe
news:4070c739 (AT) newsgroups (DOT) borland.com...

Dominic · Guest

Hi Martin
In my opinion, there are three issues here:
Issue 1: Table Mapping, ie DBF.TABLE1 = IB.TABLEA
Issue 2: Field Mapping, ie DBF.TABLE1.FIELD1 = IB.TABLEA.FIELDA
Issue 3: Data Type conversion, ie DBF.TABLE1.FIELD1.DATATYPE = ftMemo;
IB.TABLEA.FIELDA.DATATYPE = ftString

In a perfect world, your table names would be the same, your field names and
positions would be the same and your data types for the respective fields
would be the same but this just doesn't happen too often.
The is a concept that I recently used to convert Paradox to IB.
1. Create a Table Mapping Text File, the format is simply a Tab delimited
list with two columns: SOURCE DESTINATION.
The source column holds the table name of a table in the DBF database, the
destination holds the corresponding table name in IB Database.
2. For each Source table name, create a new tab delimited text file with the
two columns; SOURCE DESTINATION;
where source represents the DBF table name and DESTINATION represents the
corresponding IB table name.
You may want to look at adding extra columns to cater for AutoInc, and/or
default values.

So the logic is
1.For each line in the Table Mapping File, read in the current DBF and IB
Table.
2. For the current DBF table, read the appropriate field mapping file and
process the fields in the list. Depending on what IBX controls you are going
to use, you will need to dynamically create your SQL or InsertSQL
statements.
For example you should be able to read in all the IB field names and do
something like
INSERT INTO TABLENAME
(IBFIELD1,IBFIELD2,IBFIELD3)
VALUES
(:DBFFIELD1,:DBFFIELD2,:DBFFIELD3)
3. Run a simple SELECT * FROM SOURCE to get all the records and fields in
the DBF table.
4. Since you may have less fields in one table then the other, you need to
cater for this:
5. It is them a matter of matching the field names in both tables and data
types to each other.
For data types you can use a simple case statement, like
case DBFTable.Fields[i].Datatype of
ftString: IBQuery.Params[i].AsString := DBFTable.Fields[i].AsString;
end;
Note what out for string truncation, eg if the field in DBF is VARCHAR(20)
and field in IB is VARCHAR(19), a simple Field.AsString := Field.AsString is
going to blow up, so you are going to have to check the target field size as
well.
Also what out for default values, especially check constraints if you are
using them.

I did exactly this when we converted our D1, Paradox App to D6, Interbase.
The excellent thing about this is that if there is an issue with field
mappings, etc, simply change the text files and reprocess.
You may want to do the whole thing in one transaction so that if something
happens you can roll back the whole thing. However in my opinion, always use
a copy of the clean database. even if you roll the whole thing back, the
size of the database grows, which may or may not be an issue. I tend to
replace the database with clean one before each attempted import.
Also, IBConsole has a nice import feature as well, although this is a table
by table process and depending on what version of IBConsole you are using,
is sometimes very unforgiving and not very informative when something goes
wrong.
Hope I have given you something to think about.
Dominic

"Martin Kascak" <vyptech (AT) stoline (DOT) sk> wrote