I recently finished a development project that required me to migrate large amounts of legacy data from an ASP-coded webapp that stored its information on a Microsoft SQL Server.
“No problem,” I told myself. “CakePHP has an MSSQL datasource object I can use. It’ll be easy as cake.” *wink* Uhh…no.
My first problem was that I didn’t have the right libraries installed on my dev server. The production server didn’t matter at this point because all the migration was going to take place on the development server. My dev server runs Ubuntu 10.10 with Apache 2 and PHP 5.
I had to grab the FreeTDS library and for good measure, I grabbed the php5-sybase plugin. They didn’t work for until I reboot the machine.
After a reboot, Cake wasn’t giving me any datasource errors any more so I figured I was good to go! Yay! Nope, not quite yet.
Cake was cooperating with the driver in-so-much as it found it and was able to send SQL commands to the legacy server. Whenever I’d try to run a query though, I’d get the following error:
Warning (512): SQL Error: Invalid object name ‘memberinfo’. [CORE/cake/libs/model/datasources/dbo_source.php, line 684]
I know the ‘memberinfo’ table existed because Cake was able to describe the table in the previous 2 SQL queries immediately previous to the find query.
It took me several hours of research and banging my head against the wall to realize that no one else had come up against this specific issue before. I few had similar issues, but none were identical to mine. That meant it was up to me to solve.
Eventually, I figured out that by running a raw query in Cake, it returned a valid response along with data. The problem ended up being the MSSQL DBO datasource that Cake shipped with. Apparently it is no longer maintained and didn’t work correctly any more with whatever version of SQL Server this other host was running.
So I fixed it. I created a sub-class of Cake’s DboMssql datasource and through the wonders of inheritance that comes with object-oriented progamming, overrode the fullTableName() function which was the cause of the error–I call it: DboMssqli. The ‘i’ means improved. Because it actually works.
The full writeup of the original problem along with the explanation and solution is at Cake Questions.