My default language
UK and Ireland
US and Caribbean
Moving Data Between InterBase Databases Using a Heterogeneous Join.
Abstract: Moving Data Between InterBase Databases Using a Heterogeneous Join.
Problem: Moving data from a table in one database to a table in another database may be desirable for various reasons: - Recovering data in a damaged database - Database replication - Custom implementation of shadowing Solution: InterBase does not perform INSERT's to a table in a database using data taken from a table in a different database. The solution given below is only one of several possible solutions that uses clients running the BDE and Delphi, and uses the heterogeneous INSERT capability of the BDE. This solution requires Delphi 2.0 or higher using the Developer, Client/Server or Enterprise SKU, and the BDE with SQL Links for InterBase. 1. Start the BDE Configuration Utility. 2. On the Aliases page create new aliases or configure exsisting aliases to access the desired InterBase database(s) (*.gdb file(s)). 3. When finished save the changes and exit the BDE Configuration Utility. If using TDatabase components in Delphi is desired instead of using BDE aliases do steps 4 and 5. 4. Start Delphi and open up a new or existing application to a Delphi form. If using TDatabase components in place of BDE aliases is desired proceed on to step 5, otherwise to use BDE aliases to access the InterBase database(s) proceed to step 11. 5. Place two database components on the form (it will be assumed that there names are Database1 and Database2). 6. Double-click on the Database1 component to bring up the "Form1.Database1 Database" dialog. 7. In this dialog type in a name in the Name edit control (in this example the Name (actually the DatabaseName) property of this component will be "aaa"), and set the Driver name to be INTRBASE. 8. Click on the Defaults button. This will fill in the Parameter overrides listbox. In the listbox set the properties of SERVER NAME, USER NAME, and PASSWORD to appropriate values. In this example they will be set to: SERVER NAME=c:ibserverexamplesemployee.gdb USER NAME=SYSDBA PASSWORD=masterkey 9. Uncheck the Login prompt to not be presented the Login dialog when then Connected property is set to True. This step is optional. 10. Click on OK. Repeat steps 5 to 10 for the Database2 component (in this example the DatabaseName property of the Database2 component will be "bbb"). 11. Place a TQuery component on the form (in this example the name of the TQuery component will be "Query1"). 12. In the Object Inspector, scroll to and click on the SQL property, then click on the "..." button to open up the SQL string editor. 13. Type in the string: 'INSERT INTO ":aaa:Country" SELECT * FROM ":bbb:Country" ' (without the single quotes, and including the double quotes), and click on OK. If BDE aliases are being used replace the text strings of 'aaa' and 'bbb' with the names of the appropriate BDE aliases. Also, other syntax possiblities for the INSERT command can be used, though this examples is using the syntax shown above. 14. Place a pushbutton on the form. 15. Double-click on the button to set up an Onclick procedure for the button. If TDatabase components are used in the form type in the following lines of code: Database1.Open; Database2.Open; Query1.ExecSQL; otherwise type in the following line of code: Query1.ExecSQL; 16. Click on -File | Save- menu choices if desired, to save the changes. 17. Click on the -Run | Run- menu choices to run the application. 18. When the form with the button on it appears, click on the button to perform the INSERT. If login dialogs appear type in the user name and password, once both connections happen the insert will be performed. WARNING: The following items can affect data that is inserted into the target table: - Any BEFORE INSERT triggers and AFTER INSERT triggers or stored procedures called by the triggers will be activated when data is inserted into the target table. This can alter data as it is INSERTed into the table (this includes data in key fields). Consider performing the command "ALTER TRIGGER
INACTIVE" where appropriate to make a trigger inactive, and prevent changing of the data when it is added into the target table. - Any indexes that are unique or are primary keys may cause a Delphi EDBEngineError exception to be generated if any duplicate key field entries are inserted into the target table. The command "ALTER INDEX
INACTIVE" will make the index inactive and prevent the exception from happening, however this command will likely need be done after careful consideration since unique and primary key indexes often have a specific purpose. Also, consider using the TBatchMove component (or the DbiBatchMove() BDE API function call) where any duplicate record is sent to a different table (which will be a local Paradox table). - Any foreign key references may cause a Delphi EDBEngineError exception to be generated if there are not comparable key field entries in the parent table. Again, consider carefully the option of making the index inactive or using the TBatchMove component (or the DbiBatchMove BDE API function call). PERFORMANCE: The following item can be done to increase performance: - To increase performance for bulk INSERTs use the "ALTER INDEX
INACTIVE" command on indexes that are not primary keys and are not unique. After the INSERT is compeleted activate the index and the index will be recomputed.
Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.
Installation & Registration
Installation & Registration
Bugs & Suggestions
Audio & Video
Registered User Downloads