• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
CodeIgniter 2.02, PHP 5.36, Apache 2, MS SQL Server 2008 on Windows

#1
[eluser]tedroche[/eluser]
My customer runs WAMP: Windows, Apache, MS SQL Server 2008 as the database backend, PHP 5.36 and CodeIgniter 2.02. There were some issues. I'd like to share some of our workarounds and hope to get some of our work folded back into the mainline code. Many eyes make shallow bugs, and I'd appreciate the community support as well as the chance to give something back. If there's interest, I'll discuss with the client. The issues we ran into included:

1. PHP 5.3 no longer ships an mssql module. (http://us3.php.net/manual/en/intro.mssql.php)

2. The Microsoft supplied "PHP Driver for SQL Server" is Windows only and 32-bit only. (http://msdn.microsoft.com/en-us/library/cc296172.aspx)

3. That means you need to use the 32-bit WAMPServer (http://www.wampserver.com/en/) or equivalent, in order to use the 32-bit driver. However, this will work with 64-bit SQL Server and will install on 64-bit Windows OSes.

4. We forked the CodeIgniter mssql driver set and created an sqlsrv set similar to, but different from: http://www.kaweb.co.uk/uncategorized/mss...deigniter/
- affected_rows() needed some reworking - using result_id rather than conn_id
- _execute() needed additional params for SELECT, but not INSERT, UPDATE, DELETE
- _limit() does not have the same concept as MySQL (number and offset) and we reworked a work-around found on these forums.

If I'm the only one deploying on WAMP, I'll just keep going. If there's interest, especially in getting it into the actual product code, I'll see if I can persuade the client that this would be all upside for them.

#2
[eluser]Aken[/eluser]
I'm pretty surprised a SQLSRV driver hasn't been added yet, given that SQLSRV first existed over three years ago.

I built my own in early 2010 when employed full-time, so unfortunately I can't just grab and share that code. Hopefully your client will allow the code to be shared, so the unfortunate Windows-based users can have a good solution without pulling their hair out.

The info about the driver only being 32-bit is good. I never thought of that when installing it on our servers (luckily they weren't 64 bit in the first place).

The LIMIT issue can be a pain considering that even different versions of SQL Server support it in different ways. 2005 doesn't handle it well at all, and needs funky query shenanigans to try and pull a specific offset chunk. 2008 handles it a lot better, but still not anywhere near as pretty as MySQL or others.

Luckily I don't work on Windows anymore so I don't have to pull my hair out myself, but I can see how it could save other people a lot of frustration. You should definitely try and convince your client to share.

#3
[eluser]tedroche[/eluser]
Although the code is a bit arcane, the solution in the thread http://ellislab.com/forums/viewthread/160626/ seemed to provide a workable LIMIT command to support LIMIT X,Y as well as LIMIT X MySQL compatibility.

The 32-bit/64-bit issue had us stumped for a while, as the Apache error messages were arcane. We found a few threads on MS support forums. Apparently, MS developers don't consider the 64-bit Apache/PHP API stable enough yet. We were running a 64-bit database on a 64-bit server, so thought that was the way to avoid incompatibility. Apparently not.

We started with the CI mssql driver, and have been debugging issues as they came along. I will say, the MS driver does come with a well-documented API and sample code.

As for working in WAMP, well, that wasn't my first choice, either, and I'd generally discourage clients from that solution for the same reasons we've run into: limited support due to the unusual combination of tools, undiscovered incompatibilities, and the general cost, expense and overhead of supporting a Windows server.

#4
[eluser]Aken[/eluser]
Keep in mind that the row_number() solution as recommended in that thread is only valid for SQL Server 2005 and higher. I recall using 2000 or 2003 or something like that, where it was unavailable to me. I ended up needing to call a certain number of rows (way higher than the limit), and then basically using array_chunk() to pull the section I needed. Waste of resources.

#5
[eluser]andriansandi[/eluser]
I have downgraded my XAMPP version to 1.7.3 and mssql function working.

#6
[eluser]tedroche[/eluser]
Yes, if it's possible for you to specify the versions of software on the server, you can stick with the older versions, although there are reasons why newer versions have come out; bug fixes and security fixes will be missing from the older versions.

But if you're stuck with the newer versions of PHP on Windows with MS SQL Server, you'll need to look at the new "Microsoft Drivers for PHP for SQL Server" and that's what I'd like to see pushed into the CodeIgniter framework, so we all have it as an option.

As for SQL Server versions, Aken, yes, it's hard to keep up with Microsoft and their versions, but for support and security fixes, it's pretty important to keep up with the current version or the previous one. In calendar year 2011, we shouldn't be running SQL Server 2000 or 2003. Easy to say, not always easy for the customer to implement.

#7
[eluser]Aken[/eluser]
I very much agree. Unfortunately when it's out of your control what systems and software you have to work with, you have to make due with what you're given. If it were up to me, the place that I had to deal with Windows at would've had a LAMP stack immediately.

#8
[eluser]giberti[/eluser]
I provided a pull request to bitbucket.com #11) for a working sql server driver based off of the Microsoft sqlsrv driver. The current CI head had a roughed out version but I needed one that was working. It should work with older sqlsrv drivers but I've only tested with the more current 2.0 driver.

https://bitbucket.org/ellislab/codeignit...or-windows

#9
[eluser]tedroche[/eluser]
Thanks, Erik. I wasn't sure what the protocol was here for trying to get someone's attention. I haven't worked with BitBucket or Mercurial much. although they look pretty similar to GitHub and git, since they're both DVCS. Provided I can get the client's approval, I'll set up an account and get my code posted.

#10
[eluser]giberti[/eluser]
I opened my bitbucket.org account so I could contribute it! I'd love to see your approach. I tried to keep functional parity with the other drivers so I had to do some manipulation of the query result object in the result class to get num_rows etc without clobbering performance.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.