Methods supported and applied by the Migration Tool.
ODBC-standard is about 15 years old, the ODBC-implementation with many database
systems has some non-standard issues (for instance, non-standard data-types
used for internal purposes, non-standard formats of temporary tables). All of
these issues have been sorted out. The ODBC-import tool will import what it
should import and no more than that. "Workarounds" for known bugs
have been implemented with some commonly used ODBC-drivers.
Migration Tool uses ODBC-connections only. JDBC and other (proprietary)
connection methods are not supported and there are no plans of supporting any other connection
Tool has been extensively tested with many different database systems:
personal/office-suite type databases like Microsoft Access and Dbase as well as common
Database Server systems like MS SQL Server, Oracle, Sybase etc.
We will now
delve more deeply into what was briefly introduced with the Northwind-example
above. If you had no difficulty in understanding what you have been reading up
to now, I propose that you read on. If not, then try out some rather
simple examples for yourself before continuing from here.
a more detailed look at what is new with the SQLyog Migration Tool.
ODBC-import with SQLyog
is now implemented in the code of the SJA (SQLyog Job Agent) (sja.exe and not
sqlyog.exe in terms of filenames).
It will now import not
only DATA but also METADATA.
It offers a wide range
of general options for the import.
It offers various
options to transform data during import and options for filtering of which
should be imported.
It implements a
"trigger" or "write-back" functionality.
feature, similar to the SQLyog/SJA "Notifications Service," has been
implemented with the new ODBC-import tool.
it in some more detail.
1) ODBC-import with SQLoyg
is now implemented in the code of the SJA.
the SJA ?
might know, SJA is a standalone command-line MySQL client available for WINDOWS
and LINUX (the Windows version, however, can be started from inside the SQLyog
GUI as well as we have just seen above). The SJA will run on Windows and Linux,
but will connect to any MySQL server running on any OS. That is the meaning of
the term "MySQL client."
ODBC-import tool connects to a MySQL server using the native MySQL C-api
(compiled into SJA executable). That MySQL is the "target" of the
import. The "source" of the import is any ODBC-compliant database. It
uses the Windows standard ODBC-management, so any DSN types such as
"user-DSN," "system-DSN" and "file-DSN" are all
possible. If Windows can access and accepts the ODBC-driver, SJA can and does
ODBC-import tool has the same advanced connectivity features as the SQLyog GUI
itself and the other tools implemented with the Windows' version of the SJA:
different tunnelling options and the ability to work behind proxies. The
different tunnelling options allow you to connect to your MySQL server even if
your ISP/Admin disallows remote connections.
OBDC-import is only available is the Windows version of the SJA as of now. I
cannot tell if it will ever be coded into the Linux-version. I don't think it
is "just around the corner." I do not know much about
ODBC-implementation on LINUX but I believe that to connect to an ODBC-source
from Linux would probably take a completely rewritten version of the code of
the Migration Tool. That is opposite to the situation when SQLyog (and the SJA)
connects to MySQL, because both MySQL-server and client implementations on
different platforms are very similar.
release of SQLyog 4.1, SJA now executes 4 different types of <jobtypes>.
Besides the new one with SQLyog 4.1 <importjob> there are the three
"old" ones as well: <backupjob>, <notifyjob> and
<syncjob>. That I use this XML-like terminology to describe the job types
( or <jobtypes> to stay in XML) is no coincidence, since SJA reads its
parameters from an XML-file.
the SJA-based ODBC-import tool.
ODBC-import is now implemented in the code of the SJA means that you will not
have to start the SQLyog program to do an ODBC-import because the SJA can be
run from command-line and more important:
The SJA itself can be
scheduled using the Windows Scheduler (or any other (third-party) scheduler
running as a service with the OS).
The SJA can be run in
batch-mode from a Windows .bat-file. That .bat-file of course can contain any
legal OS/System command and any other executable that can run in batch mode,
and passing of a parameter to the executables follows the standard of the OS.
For instance if you want to import from an Access database to MySQL using the
SQLyog/SJA trigger functionality (that we will describe later) you might want
to make a copy of the database(.mdb)-file. The windows .bat file will then be
copy myaccs.mdb myaccs.bak y
(where the y parameter answers "yes"
to the question whether a previous myaccs.bak should be overwritten)
There can also be
several instances of the SJA in one .bat file. This way you can import from
many various sources into a MySQL-database from one .bat file containing more
SJA-instances. That the .bat-file itself can be scheduled. Moreover, it may
contain other SJA-jobtypes as well of which each has its own jobfile. For
instance, importing (using Migration Tool) from an Oracle Database into MySQL
ver 4.1, importing from a MySQL 3.23 server (also using Migration Tool), and
importing from another MySQL 4.1 server (where I would generally recommend use
of the Synchronization Tool and not the Migration Tool simply because the
checksum's algorithm of the Synchronization Tool is more efficient than using
ODBC) could be done as one batch job. Therefore, if you happen to own a factory
producing cars and another producing apple juice and even one more and even
if each of those uses different databases you will be able to transfer all
relevant financial data from all of those different places to one central MySQL
database when you want to!
The SJA can be launched
form an external program capable of launching an external program, no matter if
it is a scripting language such as PHP or a compiled application written in C
or Delphi. That gives you even more advanced options than the batch-mode
ability depending on what you are able to do from that other programming
reads its parameters from an XML-file, readable and editable by humans as well
as other programs. The "wizards" built into SQLyog let you create a
job-file for all job types. You can save it or execute it immediately, as you
like. You only need to have a little bit of experience with mark-up languages
to be able to understand or edit an SJA job file.
also open any existing job file from the appropriate GUI-"wizard",
change the parameters as needed and save or execute it. Personally, I prefer to
use the GUI for "small" tasks and the command line for "longer-running"
2) Import of DATA and METADATA.
of METADATA and not only DATA is a major achievement of the new ODBC-import
tool. The term "METADATA" means "DATA about DATA." It is
the data stored in the database itself about the organisation of data--table
definitions, information about all sorts of indexes and that sort of thing. How
these METADATA are organized and stored differ with various databases, but any
database engine will need to store METADATA some way. The new ODBC-import tool
reads them all.
METADATA it reads and imports are
including of course unique indexes
what is normally associated with the term "METADATA."
when importing to MySQL version 5, it does not attempt to import
It does not
matter whether these database features are supported or not by the source
As far as
Stored Procedures and Triggers are concerned, that is OK with me! Except for
the most trivial examples, I do not think I would ever trust an automatic
import/conversion tool anyway, unless it had proven very reliable! However, as
far as Views are concerned I think that could be implemented safely whenever
the ODBC-driver allows for it. So that is one of my wishes for the future.
General options for the import
If you are
familiar with the SQLyog/SJA MySQL-to-MySQL Synchronization tool, then you also
know what to expect to find here. Basically, the same options are implemented
with both tools. There are options to choose whether to append imported data to
an existing database or table or whether to delete old data before the start of
the import. There are also options for what to do in case of errors cancel
the job or simply go on with the next row of data. In addition, in case you
have chosen to append data to an existing database, there are options for what
then to do in case a Primary Key or Unique Index conflict occurs during import:
keep the old data or replace the data with the one from the ODBC-source.
options too whether foreign keys and indexes should be imported. Simply uncheck
both if you want to import DATA only and NO METADATA.
Transformation and Filtering options.
transformation option lets you transform the names of tables and columns and
the column-definition data type for each column of data during import. This is
very practical for many reasons the most obvious is the simple one that
field-length definitions with similar data types on various databases differ.
This is most obvious with the VARCHAR-type but applies to other types as well.
The ODBC-driver used for the import will "propose" a conversion of
the data-type, but it might not be the one you want! If the ODBC-driver
"proposes" a char(40) as the data type to create with MySQL simply
change it to a varchar(60) if you like.
You can do
a little experiment yourself to understand the usefulness of this facility. If
you have a MySQL and a MS Access database available, try to EXPORT from Access
to MySQL (using the MyODBC-driver from MySQL) and compare the result with the
one you get when IMPORTing into MySQL (using the Microsoft ODBC driver for
Access and SQLyog, but without making use of the transform option). If you
never did that comparison before you will probably be surprised how much
ODBC-drivers disagree on how a data type from one database is best created with
transformation option of the new SQLyog import tool it is YOU that is in
control not a piece of software that you cannot configure. There is no need
to edit any strangely formatted file to do any transform of data types or any
renaming of tables or columns. It is all available from the GUI-wizard and from
an easily understandable XML-file as well, if you prefer.
that data may be truncated by MySQL when importing, if you specify a too short
data type. In case there is a conflict with the transform option you have
chosen and the data, the MySQL Server is in control. Data might be truncated or
converted (if MySQL considers it safe) or an error may arise the rules of
MySQL decide! In some cases, what happens could depend on the MySQL
know your data so much yourself, that you know what you are doing when using
the transform option!
filtering options available with the new ODBC-import Tool allows for
"horizontal" as well as "vertical filtering." Together,
they constitute what I would call a "grid filtering."
What I call
"horizontal filtering" is simply an ability to select/deselect tables
and columns for the import. That is done from a checkbox in the GUI-wizard.
What I call
"vertical filtering" is an ability to select/deselect rows for the
import. The selection of rows to import is done by specifying a WHERE-clause to
execute against each row on the source. It is identical to the implementation
of the SQL_WHERE with the SQLyog/SJA Synchronization Tool except for one
difference. This difference is that the WHERE-clause of the Migration Tool is
executed with the ODBC-source. Therefore, the syntax must follow the syntactic
rules of the SQL-"dialect" of the source-database (that most likely
is not MySQL) and the ODBC-driver used for import. With the Synchronization
Tool, both source and target are identical or "almost identical" -
MySQL-versions, so here you will not have to take the differences of various
SQL-"dialects into account. We have seen one example already: that
Microsoft Access allowed us to use the '*' character as a wildcard in the
"trigger" functionality is the ability to perform changes to the
ODBC-database from where data is imported. The changes take place with each row
of the source after data has been saved in the MySQL-database. The option is
available and configurable for each individual column.
functionality simply sends an SQL-statement to the source database. The
statement will be executed on the row that has just been imported to MySQL. The
two SQL types of statements available as of now are DELETE and UPDATE.
trigger option simply deletes that row of data from the source after it has
been saved to the MySQL database. Not so much more to write about this one, I
trigger option lets you perform UPDATES to the source database on the row in
operation. You simple enter the "last half part" of a normal SQL
Note that what
you enter as an UPDATE-trigger sent to the source database must follow the
syntactic rules of the SQL-"dialect" of the source database, as was
the case with WHERE-clause. Thus, not only constants but also functions and expressions are possible
- actually, any expression that is legal with the syntax of the SQL-dialect of
the ODBC-source and the ODBC-driver used for the import will work. With
MS-Access you can enter "date()" and the current date will be written
to the source provided that the date()-function is valid with the
column-definition. In addition, you might increment a
"mycolumn"-column by entering "mycolumn+1" as the
UPDATE-trigger to execute against the "mycolumn"-column.
this is a trigger-functionality of the SJA itself. It has nothing to do with
any (native) trigger functionality that might exist with the database imported
from or with the trigger functionality of MySQL version 5. It is
something of its own. And very unique actually!
notifications' feature, similar to the SQLyog/SJA "Notifications
Service," has been implemented with the new Migration Tool. It lets you
send an email notification informing you of the result of the ODBC-import. The
message is configurable--you can choose to either always send a message or only
to send a message in case of an error. This notifications' feature is
particularly useful when used with scheduled imports. In addition, it can be
very useful with imports that are started manually. For instance, you can set
up a "rule" with the mail-client receiving the message, telling that
mail-client that these messages should all be moved to a certain folder in the
mailbox folder system. The messages gathered there provide you with complete
logging of all ODBC-imports (and other SJA jobs as well, if you want).