MySQL Porting Progress
by Kenneth Richards - Wednesday, June 02, 2004
from the Development dept.
Just an update, 99% of the porting work has been completed to make ASP Nuke work with a MySQL database. We now have a system that is compatible with both Microsoft SQL Server and MySQL. The work has gone surprisingly easy and I'm sure many will agree that it has been well worth the effort.
Some interesting annoyances with MySQL that I noticed:
You cannot define a default date value (for a table column) using a function such as CURRENT_DATE. I know there is a timestamp field to provide some of this functionality which I have utilized to help port my application. Unfortunately, nearly every table has two fields which need to be initialized to the current date.
Timestamp fields are returned from the database as a 14-digit string. In order to format this as a date, you have to reformat the string and convert it into a date. Not too much work, but annoying nonetheless.
String concatenation in MySQL is done through a function, not using an operator like SQL Server uses (+). My solution to this was to just do all of the string concatenation on the scripting side and eliminate all of the concatenation in the SQL queries.
I also had to work around the way MySQL uses "limit" to return a limited subset of the result rows. In SQL Server, we use "TOP n" and in MySQL they use "LIMIT n". Getting around this wasn't too hard and I actually prefer the way that MySQL allows you to specify an offset for paging through the result rows.