Saturday, September 12, 2009

Porting a web site from MS SQL to MySQL - Part 2

In a previous post, I outlined a plan to port the Build-It-Blocks web site from MS SQL to MySQL.  I plan to run the Windows stack (Windows, .asp, and IIS) on MySQL via the MyODBC connector.  Being a Red Sox fan, this feels like getting David Ortiz to hit with A-Rod's bat, but I digress.  So far, things are going smoothly:

1. Install MySQL and MyODBC

I chose to install MySQL from the zip file, primarily because I wanted the ability to leave the Windows Server in a known good state if something went wrong; by doing a manual install I knew I would be able to "un-install" everything if necessary.  Also, all of my experience with MySQL has been on Linux, so I'm comfortable with the manual install process.  Using this post for reference, I installed MySQL with no trouble.  I then downloaded and installed MyODBC from the MySQL website, also with no problems.

2. Export the MS SQL Database to MySQL

Stephen Wong, a student at Cornell, successfully exported the existing database from MS SQL to MySQL as follows:
  • Set up a DSN using Control Panel -> Administrative Tools -> Data Sources (ODBC)
  • Open the MS SQL enterprise manager and navigate to the desired database via the tree on the right.
  • Using the export tool start the table export wizard.
  • Select the desired database in MS SQL.
  • Select the corresponding database in MySQL using the proper ODBC.
  • The wizard will tell you if any tables didn't transfer properly.
3. Port the existing .asp files to use MySQL

Having no prior experience with .asp, my approach was to learn just enough to modify the database calls and leave the rest of the code unchanged.  Fortunately, there was only one place where the existing .asp code connected to the database.  Here's the relevant code snippet:

    ' define the datasource variables
    ' datasource = "blocks"
    ' dsUser = "moduledev"
    ' dsPass = "biy311"
   
    ' create and open the database connection
    Set conn = Server.CreateObject("ADODB.Connection")
    ' Change DSN to a MySQL DSN
    ' conn.Open = "DSN=" & datasource & ";UID=" & dsUser & ";PWD=" & dsPass
    conn.Open "DSN=mysql_dsn"

I commented out the line that connected to the MS SQL database, and added one line to connect to the MySQL DSN, and it worked!  I then tried to connect using a connection string instead to eliminate the dependency on a correctly named and configured DSN, but I could not get it to work, so I decided to move on and come back to it later.

I reviewed the rest of the .asp code, and as far as I can tell all of the database calls use stored procedures; something like:

    sqlStr = "exec sp_GetAllFunctions"
    set rs = Server.CreateObject("ADODB.RecordSet")
    rs.Open    sqlStr, conn, adOpenKeyset


To port to MySQL, all that needs to be done is to replace "exec" with "call" for every database call in all the .asp files, which is pretty straightforward.


The next step is to port all the stored procedures to MySQL.   Based on the expert and welcome advice from Roland, Sheeri, and Dan, who commented on my previous post, I suspect porting the stored procedures may be the most difficult part.  But, I like challenges!

2 comments:

Roland Bouman said...

Hi!

great, I'm glad you succeeded taking the first few steps! I'm following this with interest - keep it up :)

kind regards,
Roland

Log Buffer said...

"Porting a web site from MS SQL to MySQL is the second part of a series from Tom Hotchkiss [...]"

Log Buffer #162

Post a Comment