Interbase DataPump FAQ

• Can you please tell me which databases exactly Interbase DataPump can support?
• I getting "Unable get fields list for table XXX" then trying to get definitions or generate SQL script?
• Can I pump data from my .TXT or .CSV file into my Interbase database?
• Can you build Interbase database from my Oracle/MSSQL/dBase/Paradox etc database (source)?
• How can I pump data from my ODBC source?
• What is the maximum number of records I can pump.
• Will IB DataPump work without BDE (Borland Database Engine) installed on my PC?
• I need to pump data into my Interbase database from many different sources (let's say from MySQL and from set of dBase tables) - how can I do that?
• Let's say, we have following tables at the target database: Table1: DEPARTMENT (ID, NAME) Table2: EMPLOYEE (ID, DEPARTMENT_ID, NAME, SURNAME) foreign key: DEPARTMENT.ID <=> EMPLOYEE.DEPARTMENT_ID The source database looks as follows: Table1: EMPLOYEE (ID, DEPARTMENT_NAME, NAME, SURNAME) Will be possible to efficiently import the data without creating auxiliary tables or columns?
• How can I add records from source table which doesn't already exists in my destination table?
• I have Paradox database where I have tables with different passwords; since Interbase DataPump allows only setup only one password how can make it work with my database?

Q. Can you please tell me which databases exactly Interbase DataPump can support?

A. We collected some statistic based on our latest survey:

ADO   BDE   ODBC   INTERBASE (and clones)  ADsDSOObject DB2 ADABAS D Interbase 4.xx  MSDAORA INFORMIX AcuODBC Interbase 5.xx  MSDASQL INTRBASE Adaptive Server Anywhere Interbase 6.xx  Microsoft.Jet.OLEDB MSACCESS IDCO400 Interbase 7.xx  MySqlProv MSSQL INTERSOLV 32-BIT INFORMIX Firebird 1.xx  SQLOLEDB ORACLE INTERSOLV InterBase ODBC Driver Firebird 2.xx (RC)     SYBASE MYOB ODBC Yaffil     STANDARD (*.dbf, *.db) Microsoft ODBC Driver for Oracle        DB/400 Microsoft Access Driver (*.mdb)           Microsoft Excel Driver (*.xls)           Microsoft Paradox Driver (*.db)           Microsoft Text Driver (*.txt; *.csv)           Microsoft Visual FoxPro Driver (*.dbf)           Microsoft dBase Driver (*.dbf)           MySQL           Oracle           Pervasive           PostgreSQL           SQL Server

And of course all Native Interbase or Firebird sources (including Yaffil clones).
If you do not see your database in this table that is only means that nobody reported about it. In fact we haven't heard yet about any database which Interbase DataPump cannot support.

Q. I getting "Unable get fields list for table XXX" then trying to get definitions or generate SQL script?

A. Your "Select Option" and "Quote Filed Names" settings are incorrect. Please set the proper values and try again.

Q. Can I pump data from my .TXT or .CSV file into my Interbase database?

A. Yes, you can. All you need is setup ODBC alias (or BDE alias if you prefer to use BDE) based on your .TXT or .CSV file and then select that alias as a source in IB DataPump.

Q. Can you build Interbase database from my Oracle/MSSQL/dBase/Paradox etc database (source)?

A. Yes, Interbase DataPump will do it easyly. Please read this document Interbase DataPump Tour from more details.

Q. How can I pump data from my ODBC source?

A. ODBC source can be accessed thru ADO using Microsoft OLE DB Provide for ODBC Drivers or thru BDE. If you have BDE installed on your PC then all ODBC sources will be automatically visible and accessible thru BDE

Q. What is the maximum number of records I can pump.

A. Till now the maximum of about 60 Millions (MSSQL, ADO connection) records pumped in one session was reported. As you can understand here is no limits.

Q. Will IB DataPump work without BDE (Borland Database Engine) installed on my PC?

A. Yes. You need BDE only if you want to pump data from BDE sources. ADO pre-installed on Windows 9x/NT/2000/ME/XP by default. To connect Interbase or Firebird direct access (native drivers) used.

Q. I need to pump data into my Interbase database from many different sources (let's say from MySQL and from set of dBase tables) - how can I do that?

A. All you need to do is to setup separate profile for each data source (in your case one for MySQl and other from dBase files) and then pump data from them.

Q. Let's say, we have following tables at the target database: Table1: DEPARTMENT (ID, NAME) Table2: EMPLOYEE (ID, DEPARTMENT_ID, NAME, SURNAME) foreign key: DEPARTMENT.ID <=> EMPLOYEE.DEPARTMENT_ID The source database looks as follows: Table1: EMPLOYEE (ID, DEPARTMENT_NAME, NAME, SURNAME) Will be possible to efficiently import the data without creating auxiliary tables or columns?

A. Yes - this is possible, please see next step by step instructions

Step 1: Create custom SQL source DEPARTMENT_NAM

--- Custom SQL - source --- select DEPARTMENT_NAME from EMPLOYEE group by EPARTMENT_NAME ---

This will get list of all department names from destination database
Step 2: Create custom SQL destination INS_DEPARTMENT

--- Custom SQL - destination --- INSERT INTO DEPARTMENT (ID, NAME) VALUES (GEN_ID(MY_GEN, 1), :NAME) ---

We assuming that you have generator MY_GEN in your destination table. If you do not have it you need to add one more step before INS_DEPARTMENT with following custom SQL, CREATE_MY_GEN

--- Custom SQL - destination --- CREATE GENERATOR MY_GEN ---

And one step after INS_DEPARTMENT with following custom SQL, DROP_MY_GEN

--- Custom SQL - destination --- /* Interbase/Firebird */ delete from RDB$GENERATORS where RDB$GENERATOR_NAME = 'MY_GEN' --- or --- /* Firebird */ DROP GENERATOR MY_GEN ---

This way you will create temporary generator and drop it once you do not need it anymore.
You will have to create MY_GEN manually when you testing this script because without it INS_DEPARTMENT will compile and not work properly (basically IBPump will not be able to get list of params for INS_DEPARTMENT).

Step 3: Create custom SQL destination INS_EMPLOYEE

--- Custom SQL - destination --- INSERT INTO EMPLOYEE (ID, DEPARTMENT_ID, NAME, SURNAME) VALUES (:ID, (SELECT ID FROM DEPARTMENT WHERE NAME=:DEPARTMENT_NAME), :NAME, :SURNAME) ---

Step 4: Manually set link between:
- destination INS_DEPARTMENT and source DEPARTMENT_NAME
- destination INS_EMPLOYEE and source EMPLOYEE

Nodes INS_DEPARTMENT, INS_EMPLOYEE (and CREATE_MY_GEN, DROP_MY_GEN if avialable) must be checked.

Step 5: Run IBPump and enjoy.
You can download scripts and IBPump profile here UpdateDataExample.zip.

Q. How can I add records from source table which doesn't already exists in my destination table?

A. Let's say you have a table like this one

CREATE TABLE AIRPORT ( AIRPORT_ID INTEGER NOT NULL, NAME VARCHAR(10) NOT NULL );

and you want insert only records with new AIRPORT.NAME values.

To accomplish this you can simply add new stored procedure to source database:

CREATE PROCEDURE INS_AIRPORT (AIRPORT_ID INTEGER, NAME VARCHAR(10)) as begin    if (not exists (select 1 from AIRPORT a where a.NAME = :NAME)) then    INSERT INTO AIRPORT (AIRPORT_ID, NAME)    VALUES(:AIRPORT_ID,:NAME); end

Run Intarbase DataPump and add custom SQL source:

EXECUTE PROCEDURE INS_AIRPORT(:AIRPORT_ID,:NAME,:INT_CODE)

Finally set the link between this custom SQL source and source AIRPORT table and pump data.
You can download example at CopyDataDemo.zip.
Unzip it, run both sql scripts and open and run CopyDataDemo.ibp from Intarbase DataPump.

Q. I have Paradox database where I have tables with different passwords; since Interbase DataPump allows only setup only one password how can make it work with my database?

A. Paradox has has so-called super / master / generic password ("jIGGAe"), you can find more on the net (using www.google.com for example) and then use it to access all tables from your paradox database with this single password.