| · | Supported database engine native (either directly through the client libraries or ODBC/ADO)
|
| · | Open DataBase Connectivity (ODBC)
|
| · | ActiveX Data Objects (ADO)
|
By using ODBC or ADO connectivity, you can transfer data to and from any other database engine.
Also have a look at the command-line DataPump.
Below is a screenshot and explanation of different on-screen items:
Data Pump screen
As you can see in the above example, we have connected to a source database via ADO (1) and are connected to an InterBase database called 'finance-dest' (2). Both schemas are fetched (3 & 4) and one table mapping has been made (4).
Table & Column Mappings
Such a mapping is easily created by dragging the source table to the destination table and dropping it there. After such a drop, an attempt will be made to match column name - this matching process can be adjusted on the Options tab - it does a case insensitive match and filters any non-standard characters such as ' # $ _ etc...
Any columns that don't get matched will stay empty until you drag 'n drop a source column to the destination column.
The transfer order can be controlled via the UP/DOWN arrow buttons on the DataPump toolbar. This can be very useful if your target database has foreign key constraint defined.
Constant Expressions
Besides source database values, you can also choose to use a constant or expression of some kind that results in a valid value and uses valid SQL for that particular database engine. Do a context-mouse click on a column to enter such an expression.
Expression Example
In the above example, you can see that while transferring data, the destination column ACCOUNTS.ACT_ID will be filled by using a generator. Any expression that results in a valid SQL value is possible here.
Where Clauses
For the source table, you can also add a custom SQL WHERE clause to filter data for the destination database. Do a right-mouse click to enter such a WHERE clause.
A WHERE Clause
The above WHERE clause results in ENDBALL records being transferred with a value for SUMS above 10000.
ODBC Notes
Using an ODBC datasource requires a registered ODBC data source name. These can be created from the Windows Control Panel applet for ODBC datasources. This applet can also be reached via the 'Edit ODBC Datasources' button next to the ODBC Datasource drop down combo.
The Edit ODBC Datasources button
ADO Notes
| · | An ADO connection requires a quite complex connection-string. By clicking the 'Edit Connectionstring', a dialog will popup that lets you select from different ADO drivers and select your settings that will be passed to the ADO connection.
|
| · | If you have tables & columns that use non-standard characters like spaces, select the SQL syntax that you want to use from the drop down box.
|
Running Notes
By default, the Data Pump will open an additional connection to your databases and optimize the running process for concurrency - that is, you will be able to continue working with Database Workbench. If you don't mind a stalled Database Workbench instance, click the arrow next to the Start Data Transfer Process and select Optimize for Speed from the menu.
Command Line DataPump
The DataPump has a command line runner as well - see Using The Command Line DataPump.