MS SQL and Access To InterBase Wizard

See You Space Cowboy Edition

This document describes the Microsoft SQL To InterBase Wizard. This wizard is a free, open-source tool to migrate MS SQL databases to InterBase databases. It also supports migrating Access databases.

For the latest version, frequently asked questions (FAQ), source code, user guide, and how-to scenarios, visit our new homesite at http://www.ibphoenix.com/sql2gdb.

Marcelo will be taking a break from the wizard development. For now, I cannot say much, but I will keep everyone informed by posting some information on my website, over here. You will also find development articles, ramblings, and personal stuff over there.

Index

Delphi Packages
Enabling Technology
Customizations
Identifier Name Changes
Files
Pending
Contact
Modification History
Credits


Packages

As of the fifth release, the wizard uses Delphi packages. These are files similar to Windows libraries, but are specific to Delphi executables. They can be downloaded from, for example, http://www.xlprueba.com.ar/ib/delpak5.zip. Check the Wizard's hompage for a list of resource locators.

Once you have downloaded this file, you should decompress it and place the files in your system folder. If you have Windows 95/98, this is typically C:\Windows\System\; if you have Windows NT/2000, it's C:\WinNT\System32\.

The packages used are:

Just to show how a good citizen I am, I quoteth from the deploy.txt file:

2.2 INPRISE Runtime Packages
----------------------------
In accordance with the General Terms That Apply to Compiled
Programs and Redistributables, you may redistribute
INPRISE-supplied runtime packages only for the purpose of
executing application programs created with Delphi. You may
not modify these packages in any way. By default the
redistributable runtime packages are installed in the
\Windows\System directory (for Windows95) or
\Windows\System32 (for Windows NT).

Although this software may include packages other than
those listed below, only the packages listed below are
redistributable.

You may NOT redeploy INPRISE-supplied design-time-only
packages. You may not recompile or redistribute any
INPRISE-supplied components or libraries for use by other
developers.

Packages that you develop may not have the same names as
INPRISE-supplied packages. If you distribute component
libraries or other packages, it is recommended that you
avoid using the default DCLUSR50 package name, since this
will cause naming conflicts with other developers.


Enabling Technology

The tool is built with Borland Delphi 5, and uses ActiveX Data Objects to access the source database, and IBExpress component to access the target database. These decisions were made because:

To all of you who have written components to access InterBase, my apologies for being such a wimp. You are, of course, welcome to modify the tool to use your own data access components.


Customizations

Customizations can be made by the user to any script. If all content is deleted, the script does not get executed.

There are a number of name mappings which can be made automatically, to keep compatibilty with InterBase 5.6.

There are other customizations which can be made; for a full explanation, see the User Guide.


Identifier Name Changes

Because Microsoft Access and SQL Server allow identifiers to have spaces in them, names sometimes have to be changed. The way the names are changed are controlled by the MapMethods property of the TNameMap object. There are three different methods: mmUnderscores, mmRemoveWhiteSpace, and mmUseQuoted. The first will replace all spaces with underscores. The second will simply remove all whitespace, and leave the words one after the other. The last will use quotes around the identifier; this would be the ideal solution, but it is only supported in InterBase 6+. In the first two cases, also, the identifier may be a reserved keyword, in which case the suffix Col or Table is appended.


Files

File Name Description
ADODB_TLB.pas This file is the imported type library of the ADO library. This is for you guys out there without ADOExpress
AdoUtils.pas This file is a smaller version of an ADO unit of routines I have. However, my own version uses ADOExpress, so it's not included in this distribution. If you want the whole thing, ask me and I'll post it on CodeCentral.
ADOX_TLB.pas This file is the imported type library of the ADO Extensions library.
Base64Unit.pas This file has a routine to encode a binary string into a base64 string, according to RFC 2045. The routine was menat to be used when writing a blob to an output file during migration. Unfortunately, it doesn't work, but I'd like to propose it as an enhancement to the engine.
CheckLstUtils.pas This unit holds a bunch of check-listbox routines.
ConvertInfoUnit.pas This unit holds the convertion information object, which is a singleton used throughout the site, residing in the singleton Flow object. This object is responsible for generating the scripts to be executed, and maintaining information about user input.
The NameMap object, which resides in the ConvertInfo object, is used to manage the mappings generated through identifier mangling for SQL dialect compatibility.
CustomizeMigrationFrameUnit.pas This frame allows the user to customize the scripts which will be run.
CustomizeScriptsFrameUnit.pas This frame allows the user to customize some code generation options, like dialect versiona and whether to create generators for autonumber fields.
DefaultValuesFormUnit.pas This form is used to specify the default values to be used in place of NULLs in the source database. Note that the UI explains the mapping, so it should be updated together with the code.
DragDropUnit.pas This unit holds the TDropTargetManager component, which is a helper object for OLE drag and drop operations. It can be installed, but it's not embedded as a component so developers don't have to install it in a package.
FormsUtils.pas This unit holds a bunch of useful form routines.
FreeUDFLib_setup.sql This file holds the declarations used to automatically declare (duh!) the FreeUDF user-defined functions in the destination database.
IBCheckUpdateUnit.pas This unit checks for updates on the Internet.
IBUpdate.htm This file describes the update document format.
IBUpdateUnit.pas This manages the Internet document used by IBCheckUpdateUnit.pas
MainFormUnit.pas This is the main form of the project. It manages the navigation buttons.
MigrateDBFrameUnit.pas This is the last frame, responsible for the feedback during migration. It delegates the work to the flow object.
MigrationUnit.pas This unit declares the TMigrationManager class, which is responsible for all the pumping, executing the scripts of the TConvertInfo object it is assigned.
MLRFileIteratorUnit.pas This unit is part of a library of mine with classes and routines. This particular unit holds classes and routines related to the very common task of iterating over filenames.
MLRTextColEditorUnit.pas This unit holds the fixed-width column editor control.
readme.htm This file.
reserved.txt Keywords reserved by InterBase. This is needed when renaming columns or tables.
SelectIBFrameUnit.pas This frame allows the user to select an InterBase database, specify that an existing one should be overwritten, and specify the user name and password. The defaults are sysdba and masterkey.
SelectSourceFrameUnit.pas This frame allows the user to select an ADO source for the convertion. The ADO source should be an MS SQL database, but because I plan to extend this wizard for other ADO-enabled databases, this is not enforced.
SQL2GDBCmdLineUnit.pas This unit is used to handle the wizard-flow when run as a commnad-line utility.
SQL2GDBConstsUnit.pas This unit holds a number of constants which can be reused throughout the application. They also make localization easier. Not that the wizard is localized, though. But it'd be nice.
source.txt If this file exists, it will be used to initialize the source connection string.
SQL2GDB.dpr This is the Delphi project file. It expects all project files to reside in the same folder.
sql2gdb_udfs.htm This documentation file explain how the wizard uses UDF declaration files.
scripthead.sql This is a text file which is written as a header when generating a full script; by default, it has a warning for the user to update the system path and set his user name and password.
TextDelimiterFrameUnit.pas This frame is used to allow the user to select text file delimiters and other options.
TextFieldsFrameUnit.pas This frame is used to allow the user to set all final details for text files, then save the schema.ini file and close the wizard.
TextFormUnit.pas This unit holds the main form to host the text schema sub-wizard, and the flow of steps within this sub-wizard.
TextModelUnit.pas This unit holds the text schema information, some utility routines to work with the text format in the files, and the schema guessing support.
TextPreviewFrameUnit.pas This frame allows the user to select a text file to define and previews its content.
UpdateInfoBuilder\*.* New for the Birthday Boy edition, this files are used to create a simple GUI tool to edit the files with update descriptions for the wizard.
WelcomeFrameUnit.pas This frame simply displays a welcome message, and allows the user to select a previous migration or launch the text schema sub-wizard.
WizFlowUnit.pas This is the most important unit. It used to hold three classes: TWizardFlow (exposed through the global Flow variable), TConvertInfo and TNameMap. The latter two, however, have been moved into ConvertInfoUnit.
The Flow object is responsible for managing the user interface and lead the user through the steps, triggering certain actions at certain transitions (see the ForwardFrom procedure).

Pending

This is a list of pending changes:


Contact

To contact me for errors, suggestions or improvements, write to marcelo.lopezruiz@xlnet.com.ar (Marcelo Lopez Ruiz), tbennet1@tampabay.rr.com (Todd Bennet), jan.bakuwel@ams.greenpeace.org (Jan Bakuwel), or hc@descartes.fr (Henri Cesbron).


Modification History


Credits

The MS SQL and Access To InterBase Wizard was brought to you by: