Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 21, 2005

Migration to MySQL with SQLyog ver 4.1. - Page 3

By Peter Laursen

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. 

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM