From the
transform-dialogue you choose which tables and columns to import, and you can
change the name of each table and column and the data type "proposed"
by the ODBC-driver if you like. Of course, the data type must be valid
with the data! But you can replace, for instance, a "proposal"
of the ODBC-driver to create a char(40) data type in the MySQL-database with a
varchar(60) if you like. Here I have chosen to replace the "proposal"
of the ODBC-driver to create an INT-column with my own idea of what the data
type with MySQL should be: a BIGINT. Also, note that the InnoDB table
type is chosen. You need to do that if you want to import foreign keys.
It is in the
"DDL/DML" that you select or deselect the import of metadata.The other options
here are not relevant this time since we are importing to an empty MySQL
database. If there were already data in the database that we were
importing to, it would be here that you should decide if you would drop the data and
it also would be here that you should decide what to do in case of a
conflict with the data already in the MySQL database and the newly
imported data.
Note
that this is done for each column independently. They should not be hard
to understand.
To get an
idea of the trigger option you could choose to delete one column from a table
in the source and you could spot a string type variable in the source and enter
a constant string like 'a string' (note it must be surrounded by quotes!) as a
trigger value for update. Open the respective Northwind tables after
import and you will find that one column of data has been deleted and one has
had its data overwritten with 'a string'. In the example below, I 'reset'
the region-information about customers to some default value
"N/A." Also, note that you can parse the SQL (the
UPDATE) that the trigger consists of, by pressing the "Parse"-button.
Note:
triggers are only fired against the rows that are imported. So, that lead us to
the WHERE clause, which is used for the selection of rows for import.
Here you
enter an SQL WHERE statement that will select the rows to be imported.
The keyword WHERE is inserted by the program automatically so do not write
it! It looks like the picture above. For table
"employees" I chose only to import employees who have a 'lastname'
ending on 'son' (Hansson, Peterson etc.) Moreover, as I said just above, the
WHERE also controls (indirectly) where trigger functionality should be applied.
If no WHERE is specified, all rows will be imported (in SQL no WHERE means
EVERYWHERE J), and
specified triggers will execute on all rows. In addition, here you can
parse the validity of the SQL-statement before proceeding to next step. Note
that with Microsoft Access the use of the Windows' wildcard '*' is legal in an
SQL-statement. Most ODBC-databases would reject this because the
corresponding standard SQL wildcard is '%'.
We are now
ready to finish the last details: configuration of error handling,
email-settings for notification (account-info and the e-mail subject) and
finally to decide if the job-description that we just accomplished should be
executed immediately from the GUI, saved as an XML jobfile or scheduled as a
task with the Windows scheduler. It should all speak for itself.
Above:
error handling and general notifications' option.
Configuration
of email options: account information (above) and e-mail subject (below).
Press
"Next" and the import will start immediately. If you choose to save
instead, you can open the saved file from the first dialogue in the wizard at a
later point of time, step through the wizard, change things as you like and
again have the option to execute, schedule or save. If you choose to
schedule and later want to change the point(s) of time that the job is
executed, you can do that directly from "Scheduled Tasks" in the
Windows' Control Panel.
Finally,
let us have a look at the MySQL database created as a result of what we just
did. In the DATA-pane of SQLyog we see that all the data of the table
"products" is there:
In
addition, the table was imported with all 'metadata" as we requested and
as we can see in SQLyog OBJECTS-pane:
However, no
data from table "employees" was imported because there is no employee
named "Hansson" or "Peterson" or the like. You can
check yourself that the "employees" table is empty.
IE 7
Firefox 2.0