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.
Delphi Packages
Enabling Technology
Customizations
Identifier Name Changes
Files
Pending
Contact
Modification History
Credits
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.
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 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.
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.
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). |
This is a list of pending changes:
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).
G_
. Now loads and saves
the name map together with the script. If you were
born on March as I was, happy birth-month!MigrationUnit.pas : 162
of the current release.Extra-special thanks go to the IBPhoenix team, who have given us space on their server to host a homepage.
The MS SQL and Access To InterBase Wizard was brought to you by: