MS SQL to MySQL Conversion

Just a few notes I have regarding some considerations to keep in mind when moving from MS SQL to MySQL. Most of these are related to programming in ASP.

Check data types for appropriate conversion. Of special concern:

• VARCHAR only allows 255 character in MySQL as opposed to 4000 in MS SQL (depending on the version). Use a TEXT data type instead.
  
• DATETIME does not allow for dynamic defaults in MySQL. Use TIMESTAMP instead, which can be set to update to the current time on insert and optionally on update.
• Take note of DATETIME columns from MS SQL. A precision time may be exported that doesn't import into MySQL. It's easiest to use a regex to trim the time after the seconds value.
• BLOB data types (including TEXT) won't import into MySQL using MyODBC and DTS. Easiest way to get around this is to export to a CSV/TAB file and import into MySQL.

In ASP note the following concerns:

• Check for any table name quoting. MySQL uses backticks (`).
• Check the cursor type, cursor location, and lock type on any recordset objects. Some options have to be enabled in the DSN to allow certain cursor types (see CursorTypes, LockTypes, and CursorLocations). The following guidelines apply:
  • location: client; type: static - recommended for most connections. Static is the only supported cursor type on a client cursor.
  • location: server; type: forward only - fast read.
  • location: server; type: dynamic - while this cursor type can be specified it is not supported by MySQL natively. The ODBC driver performs extra communication functionality to simulate this type, resulting in significant overhead.
  • The keyset cursor type is not supported at all.
  • When not performing updates use a read only lock, otherwise use an optimistic lock.