The Interbase DataPump is a freeware program that reads and converts any ADO/BDE/ODBC source to an Interbase / Firebird database.

I prefer this convesion product because it produces an SQL script that can be archived as part of the project's CM.

• Does not run on Windows 98, ok on Windows xp
  Exception EIntfCastError in module IBPump.exe at 0007B388.
  Interface not supported.

• Does not run on Windows 98, ok on Windows xp
• Does not convert array fields - interbase to interbase converted xxx(4) [5] to a blob

  Does not copy Domains

  Failed on the example interbase database

• Failed to produce a Foxpro index based on a calculated value

• Run one of the BDE configuration programs (I prefer Database Explorer)
• Define a BDE link to the Foxpro database
• Execute IBPump.exe
• For the source database, select BDE Source and select the alias you created above. Press the Test Connection button.
• Set Select Option to select * from table and do not check Quote Field Names (the suggested configuration for dBase tables via BDE uses double quotes and fails)
• Enter info for the destination database ... including the username and password (These will be used to create the new database)
• Set the SQL Dialect to 1 (the default is 3)
• Press Build SQL Script - this opens a new dialog box
• Enter a script filename (*.sql) and database filename (*.fdb)
• Use Save Profile... to save your settings.
• Press Start!
• This produces an SQL script that can be used to create new Firebird tables.

• If the *.fdb file already exists, manually delete it before starting ISQL. This may require closing IBPump.exe first.
• Select Start / Programs / Interbase / Interbase Windows ISQL (this runs InterBase Interactive SQL)
• Select File / Run an ISQL Script...
• Select the script from above and select "save the results to a file"
• Once this script runs without errors, close the Generate SQL Script dialog box and use Database Explorer to verify that the tables were created. To locate the errors, search for Statement failed or SQLCODE.
  • SQLCODE = -607 - Name longer than 31 characters

• In the Destination database section of the Interbase Datapump, press Test Connection to verify everything is ok.
• In the Interbase Datapump, select the Step 2: Order tab.
• Press Get Definitions
• Press Build Relations (this does nothing if you rename the tables)
• If any tables are not linked, drag them from the Source (right hand list) to the corresponding destination table (left hand list). Blue is good, red means a problem.
• For those tables where you renamed one or more fields, you need to manually associate the corresponding fields. In the left hand pane, drag the source field to the corresponding destination field.
• Make sure that all the source tables are blue.
• Make sure that all the destination tables are checked and have a blue link.
• Save this profile so that you can use it again.
• select the Step 3: Pump tab.
• Enter a filename
• Press Start

• Execute IBPump.exe
• For the source database properties, select ADO Source
• Click the button with 3 dots next to Source DB
  • On the Provider tab, select Microsoft Jet 4.0 OLE DB Provider
  • On the Connection tab, enter the fully qualified path to your *.mdb file (press the button with 3 dots)
  • I left User Name = Admin and Blank Password checked
  • Use Test Connection to verify the connection
  • Close the Data Link Properties dialog box
• Clear the User Name and Password
• Press the Test Connection button - it should pass
• Set Select Option to select * from table and do not check Quote Field Names
• Enter info for the destination database (*.fdb) ... including the username and password (These will be used to create the new database)
• Set the SQL Dialect to 1 (the default is 3)
• Press Build SQL Script - this opens a new dialog box
  • Enter a script filename (*.sql) and database filename (*.fdb)
  • Set the SQL Dialect to 1 (the default is 3)
    Note: Dialects 2 & 3 always place double quotes around table and field names and Interbase Interactive SQL version 5 fails with double quotes.
  • Set Duplicate Index Names to add table name TABLE_INDEX (many tables have an index called PrimaryKey ... but this causes many names to be more than 31 characters )
  • Set Case of field/table names to leave it as is
  • Set StringField if Length < to 6 (I use a lot of 5 character fields)
  • Use Save Profile... to save your settings.
  • Press Start!
  • This produces an SQL script that can be used to create new Firebird tables.

The other main problem is field (column) names that are too long - >31 characters - but they won't be reported until you try to run the script via ISQL.

CREATE GENERATOR GEN_Alias_Details_tbl_Alias_Purpose_ID;
                 123456789 123456789 123456789 ^23456789

Also check your indices - those with several fields are not handled properally.

CREATE UNIQUE INDEX Unique_Alias_Purpose ON Alias_Details_tbl Alias;

CREATE UNIQUE INDEX Unique_Alias_Purpose ON Alias_Details_tbl (Alias, Purpose);

There is also a problem with duplicate indices - after the conversion, most tables have 2 indices on the primary key because defining a primary key automatically defines an index aand this datapump does not handle that correctly. Be sure to delete the second index.

ALTER  TABLE "xyz_tbl" ADD CONSTRAINT "xyz_tbl_PrimKey" PRIMARY KEY ("xyz_ID");
CREATE INDEX "xyz_ID" ON "xyz_tbl" "xyz_ID";

I also had to delete (comment out) the following line.

SET SQL DIALECT 1;

Once the errors are fixed, continue the same as the example above.

• Create a new Paradox table
• "Borrow" the structure of the FoxPro table
• Save the new table
• "Add" record from the FoxPro table to the Paradox table

• No data was transferred,
• No data was written to the report file
• The following error was displayed

  Format '%s' invalid or incompatible with argument.

This was fixed by changing the SQL dialect from 3 (the default) to 1.

Dialect	Feature
1	Supports only a Date field Does not use double quotes in queries.
3	Supports Date, Time, and Timestamp fields Places table and field names in double quotes, appears to support spaces in field and table names

	Interbase 5	Interbase 6
Supported dialect	1	1
To run scripts	Interbase Windows ISQL	IBConsole / Windows ISQL

• Select Start / Programs / Interbase / Interbase Windows ISQL (this runs InterBase Interactive SQL)
• Select File / Run an ISQL Script...

• Select Start / Programs / Interbase / IBConsole
• Select Tools / Interactive SQL... (this runs InterBase Interactive SQL)
• In the Interactive SQL window, select Query / Load Script...

SQL Dialect Invalid
Invalid client dialect 3

isql -i "G:\Firebird Databases 2\Samples_Test.sql" -o xx.txt

Use CONNECT or CREATE DATABASE to specify a database
Statement failed, SQLCODE = -607

unsuccessful metadata update
-Name longer than database column size
Statement failed, SQLCODE = -607

unsuccessful metadata update
-Name longer than database column size
Statement failed, SQLCODE = -607

URL: http:// cpcug.org / user / clemenzi / technical / Databases / Firebird / DataPump.html