SQL Offline

MySQL

MySQL Driver 5.2w mysql-connector-odbc-5.2.4-win32.msi Windows (x86, 32-bit) Connector-ODBC. Note that the MSI without -ansi- in the name supports Unicode.
MySQL Driver (GPL License) Connector/ODBC 3.51.30 Windows (x86, 32-bit)
SQL Language: MySQL 5.5 Reference Manual

You can change from Unicode to ANSI by replacing 5.2w with 5.2a in the connection string. If both drivers are installed, Unicode (5.2w) is preferred.

Column Type

MySQL Column Type Information (Text).

mysql column types
MySQL provider types

The column type will be provided to the CREATE TABLE command. Additional qualifiers can be added. For example

timestamp DEFAULT CURRENT_TIMESTAMP

will specify a column that defaults to the time the record was created.

Primary Keys

If you have a single primary key column, you can select the KEYCOLUMN flag, and a primary key will be created as the table is created. If any column type has the ISAUTOINCRIMENT flag set, it will automatically become the primary key.

If you are using multiple columns for primary keys, use File/Data Link to define a Primary Key index after creating the table. With MySQL, an index flagged as a primary key will automatically be called PRIMARY, regardless of the name indicated in the Data Link dialog.

Initial Catalog

MySQL DataSource
Initial catalog setting of the Data Link Properties dialog.

When using the Data Link properties dialog, the {MySQL ODBC 3.51 Driver} client does not recognize the INITIAL CATALOG setting. SQL Offline will copy the INITIAL CATALOG property to the DATABASE property. However, if you have used the ODBC Data Source Administrator (Windows Administrative Tools) or constructed a connection string manually, be sure to set DATABASE and leave INITIAL CATALOG blank.

Sample Migration

Consider loading Categories from Nwind.mdb, as in the Crash Course Tutorial . Replace the connection string with a link to a MySQL server database. A Write Data command would create a table as follows:

CREATE TABLE `Categories` (
`CategoryID` integer auto_increment PRIMARY KEY NOT NULL,
`CategoryName` varchar(15) NOT NULL,
`Description` longtext,
`Picture` longblob)

and indexes using:

CREATE UNIQUE INDEX `CategoryName` ON `Categories` (`CategoryName`)

The ISAUTOINCRIMENT flag implies PRIMARY KEY on the column so there is no need for

ALTER TABLE `Categories` ADD PRIMARY KEY (`CategoryID`)

to create the primary key. After creating the table, Write Data Preview (SQL) would produce

-- updating Categories (CategoryID, CategoryName, Description, Picture)
UPDATE `Categories` SET `Picture`=0x151C240002D05FE WHERE `CategoryID`=3
-- update complete (1 of 8 rows had changes)

if Picture were replaced with a new sample picture for Confections. To load a sample picture (shown truncated above), select cell 3,4 (Confections, Picture) and pick Edit Memo/Load . Enter http://inters.com/samples/Memo.bin for the filename.

Sample SQL from NWind.Categories CategoriesMySql.sql (text) .