Installing and upgrading help

Hi David,

I've never used FreeTDS, but do you have some particular reason to use FreeTDS instead of the Microsoft Drivers 3.0 for PHP for SQL Server? 

OK,

Well I've ended up spending a day building FreeTDS 0.91.89 and PHP 5.5.1 under x86 vc11 nts.

Seems to be working OK. I also have PHP 5.5.x versions of php_sqlsrv.dll and php_pdo_sqlsrv.dll on the same server in case somebody settles this sqlsrv Vs FreeTDS debate and I end up on the wrong side.

Thanks for the comments.

-David

This may help someone..

MSSQL
The Moodle 2.5 installation docs here: http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP warns against using the Microsoft drivers for SQL Server for PHP as it has known problems and is therefore not suitable for production servers.

Issue: https://tracker.moodle.org/browse/MDL-37734 (Still current as of July 2013)

“This issue is about to describe the problem happening with current sqllrv moodle driver (using the MS driver for PHP) to use Moodle under SQL*Server databases.
Along the last months, it has been reported in various issues, in different places, and it's affecting more and more, while we add more transactions to code base.”

Workaround: (use FreeTDS instead, while this gets fixed)

Supported Versions: 
Microsoft Drivers 3.0 for PHP for SQL Server provides a self-extracting executable file which provides thread-safe and non-thread-safe drivers for PHP 5.3 and 5.4. http://www.microsoft.com/en-gb/download/details.aspx?id=20098. A Microsoft employee has released version 3.0.2 for PHP 5.5 here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e1d37219-88a3-46b2-a421-73bfa33fe433/unofficial-php-55-drivers-x86

FreeTDS
The Moodle 2.5 installation docs here http://docs.moodle.org/25/en/Installing_MSSQL_for_PHP list various FreeTDS binaries for download from PHP version 5.2.x --> 5.4.x. No binaries exist for 5.5 on that page for download. To use PHP 5.5 a custom build is required.

Build process
Building PHP 5.5.1 with FreeTDS 0.91.89 for moodle 2.5.1 under x86 vc11

Requirements: 
- Visual C 11 (Visual Studio 2012) (Express version OK) (Windows SDK not required)
- Current stable PHP source (http://php.net/downloads.php)
- Current stable FreeTDS source (ftp://ftp.freetds.org/pub/freetds/stable/)
- Most recent binary tools (http://windows.php.net/downloads/php-sdk/)
- PHP dependencies (http://windows.php.net/downloads/php-sdk/) 

Setup PHP environment by following guide:
https://wiki.php.net/internals/windows/stepbystepbuild
- In step 8, bin\phpsdk_buildtree.bat did not create the vc11 folder so I just copied the vc9.

Ensure you can build PHP under this created environment using:

buildconf
configure --disable-all --enable-cli
nmake

Now loosely follow the instructions on building FreeTDS here:
http://docs.moodle.org/dev/Compiling_FreeTDS_under_Windows

Building FreeTDS
If when attempting to build php_dblib.lib from source you get linking errors, it is probably because you are missing some source files in the projects/solutions. Doing a file search in the src folder for the symbol (without the preceding underscore) should highlight where the function definition comes from. Add the file to the project that won’t compile. Rinse and Repeat.
Linking errors: http://permalink.gmane.org/gmane.comp.db.tds.freetds/11009

When php_dblib.lib is compilied and in the php lib folder in your build environment, the last thing is to configure the makefile appropriately. (You may also need to compile libTDS.lib from FreeTDS - I had to.).

I used a number of different configurations while testing. This one worked:
configure --disable-all --disable-zts --enable-cli --with-dblib=shared --with-extra-includes=C:\php-sdk\phpdev\vc11\x86\php-5.5.1-src\freetds\include;C:\php-sdk\phpdev\vc11\x86\php-5.5.1-src\freetds\win32

Check the output from the configure call in case there any skipped dependencies. (like dblib!)

“--with-dblib=shared” is the key line for actually telling make to create an external dll file and not just compile the functionality directly into PHP core.

If while using nmake an unresolved external symbol linker error is reported: e.g. “error LNK2019: unresolved external symbol _tdsdump_log referenced in function _dblib_add_connection” you may need to add libTDS.lib to the libs statement in makefile and rerun make.
http://enyby.blogspot.co.uk/2013/01/eliminate-errors-compile-freetds-on.html. I had to do this.

Use “--enable-zts” or --disable-zts” for thread safety as mentioned.

Then when php_dblib.dll is created put it in the ext folder in your run time environment and enable the extension in your php.ini and you’re good to go!

Environment variables with IIS
One last consideration is the location of your freetds.conf file. If your server will only need one freetds.conf file then you can just use a server environment variable. Other wise you will need to add environment variables to the FastCGI Settings for PHP in IIS. Remember that the FREETDS variable is a path, and not a filename! I did not remember this!

Hi all,

just some comments aiming to clarify a bit the status of both the FreeTDS (mssql) and Microsoft (sqlsrv) drivers.

A. First of all, let me state clearly that, both, right now (Moodle 2.5), continue being supported. And any problem with them, if not impossible, will be fixed.

B. Said that, it's true that current support level is better for both MySQL and PostgreSQL (call it "gold support", although I don't like the nomenclature specially). Then the SQL*Server ones (silver), and finally Oracle (plastic?).

C. There are various reasons for declaring both SQL*Servers a bit beyond the gold ones, all them with their influence, like:

1. Number of installations aka number of reports.
2. Stability of the underlying drivers.
3. Fulfillment of the SQL standards.
4. Proprietary (and paid) nature of the software (driver, rdbms, os...)
5. ...

D. About MDL-37734, it is a really, really nasty limitation of the server itself (incredible I'd say if you ask me), but it has been already "solved by a workaround" in our sqlsrv driver (see MDL-37748). It surely implies memory use will grow a bit for each connection, but it was the only way to go.

And it's exactly the same workaround that FreeTDS has built-in since ages ago (so we did not need to change our mssql driver). 

We are just asking to MS if they would apply the workaround to their driver (like FreeTDS did) instead of having it applied in our (php code) drivers. That would benefit any other application out there trying to handle transactions and recordsets at the same time (something really usual, IMO).

So, at all effects, it's fixed (for Moodle POV). And, that warning in the Docs (that I did not write) is completely inaccurate right now IMO.

More yet, if you want to know if the server is going to work ok in your stack, just run the unit test on it. It will show possible problems for sure. We try hard to add to the tests any query/combination prone to errors.

E. About comparing FreeTDS and MS drivers, well, for me the election is pretty clear. I'm a Mac and need to use FreeTDS (same applies to any non-windows system out there). For people using complete windows stacks I think it has more sense to use the MS driver. Although the TDS (Tabular Data Stream) protocol is stable and solid... it's not "native/original" from MS, but something inherited from Sybase and maybe deprecated some day (remark it's a maybe only).

F. Disclaimer: be noted that I'm not a Windows guy nor expert at all, so surely all you know way more which one performs and suit your needs better for your environments.

So, brief summary:

Both drivers are supported, slightly beyond mysql and postgres (btw, have you considered them?), the horrible problem in MDL-37734 is fixed and, as a general recommendation, be as "native" as you can when building server stacks.

Hope it helps, ciao

PS: David, many thanks for building the PHP5.5 versions of the FreeTDS driver for Windows. Would you mind if I copy them to the Moodle downloads server and link to that location? Mainly to provide a stable location for them and to know how popular it is. TIA!

PS2: I've get rid of the warning in the 23, 24 & 25 versions of the docs (where  the MDL-37734 fix/workaround was applied).

We used to connect  fine through external db with mssql in moodle 2.5

After upgrading php to  5.4.35 (in unix server) we can't connect the users from the external  db (as we used to) we get these errors:

Warning: mssql_connect(): Unable to connect to server: 217.194.204.133:1433 in ......../xxxxx/moodle/lib/adodb/drivers/adodb-mssql.inc.php on line 614
217.194.204.133:1433: Connection error to server 'OURSERVER-IP' with user 'OUR-USER'

(mssql): SELECT * FROM dbo.vMoodleUserData WHERE StudentCode = '246847' AND PasswordInt = '7097'  

Warning: mssql_query() expects parameter 2 to be resource, boolean given in /xxxxxx/moodle_emuna/lib/adodb/drivers/adodb-mssql.inc.php on line 830

Please Help me what can I do to fix this?

In our php erver (unix) we have freetds installed too and in the sql server 2005 (WINDOWS) we used to use the ntwdblib.dll ( I don't know if that has  something to do with that). Do we need to change or update a new driver in the SQL server so it will fit the php 5.4 version? 

What do we need to so exactly? and what is the freetds version in the php server that I need to set? Is it  7.0 , 8.0 or something else?

Hope someone can help me?