Migration from MySQL to MSSQL our solutions

Last post I did had all of my feelings, and despair, and now I am sure those were out of sheer negligence and mostly because I was unaware of the MS SQL product. Though I still do not agree with certain things like the row_count workaround for the limit feature, and escaping of quotes. But for our project we could more or less maintain a streamline by using the same code base for MySQL, SQLite3 and MSSQL 2005. Will try to explain this across a couple of posts.


We were already using a wrapper which had a structure more or less as given in the listing.

class sqlDb {

public $link;

function __construct($dsn){
/* parse the dsn and create the connection */
}

function query($query){
/* native query call */
}

function perform($table, $data, $act = 'insert', $parameters = ''){
/* create query from associative array $data, to insert or update $table depending on parameters */
}

/* some wrappers for *_fetch_row, *_fetch_array, *_fetch_object, *_fetch_fields, *_num_rows, *_free_result etc */

/* some custom functions utilizing some of the above to
fetch result sets as colums, arrays of arrays,
arrays of objects etc */

}

So all queries were being passed through the sqlDb::query function, and for MSSQL, I just modified the whole set and created a new class defenition. But with query rewriting, using regular expressions and str_replace. Some of the core parts are explained herewith, the code analysis will follow in a later post after I get the consent from the team, who helped me with the migration. I cannot forget the services of Vishnu, Anju and Parameswaran all Saturnites as of now and Bijumon though he left the company, without which I would not have reached the position of maintianing a php codebase intented for MySQL to transparently adopt MSSQL as the database.

I should thank SQLWays, which inspired me to do the migration and to their trial version which taught me of the existance of sqlcmd. Thanks to Microsoft for the 14 day unregistered trial of Windows 2003 (Sad it is no more available), and the Express edition. The development environment is configured on a virtual server running on Virtual Box on top of Ubuntu 9.10 server edition. The trial version of Aqua Datastudio is really great, and I would love to use that continuously. The wizards to create triggers on MsSQL connections were quite useful for the smoot migration of the project.