MSSQL Support for PHP

This section describes how creating a MSSQL database and prepare the connection for TYPO3.

Creating a database for TYPO3

Open Enterprise Manager, open your server (local in my case) and right click on the Databases node and choose menu “New Database…”:

You may now choose the database name and a few other (optional) settings among which, the Collation name (see below). I did not select some Unicode collation (which I would prefer) but sticked to MSSQL default collation (which is a latin1 derivate) because MS Knowledge Base 239530 tells us that this will require special query rewriting.

Now create a user that will own the TYPO3demo database. As usual for my tutorials, I name it “typo3” with password “typo3”. Please note that I’m using MS SQL Server 2000 and I activated password-only authentication. You may need to create a real Windows user account on your own server.

MSSQL Support for PHP

You may either connect using mssql native driver for PHP5 or using ODBC.

Native MSSQL Driver (mssql extension)

This is easily done with package php5-sybase which will use FreeTDS:

# apt-get install php5-sybase

On Mac OS X, you would use MacPorts and package php5-mssql:

$ sudo port install php5-mssql

ODBC Driver (Debian)

This is easily done with package php5-odbc which

# apt-get install php5-odbc tdsodbc

Then process as described below for Mac OS X.

ODBC Driver (Mac OS X)

On Mac OS X, you would use MacPorts and package php5-odbc and freetds:

$ sudo port install php5-odbc freetds

Then edit/create file /opt/local/etc/freetds/tds.driver.template:

[TDS]
Description = FreeTDS Driver for Linux & MSSQL on Win32
Driver      = /opt/local/lib/libtdsodbc.so
Setup       = /opt/local/lib/libtdsS.so

Then prepare your DSN into /opt/local/etc/freetds/tds.dsn.template:

[TYPO3MSSQL]
Driver      = TDS
Description = Connection to Windows virtual machine
Trace       = No
Servername  = WinXP
Database    = database-name
Port        = 1433
TDS_Version = 8.0

Edit file /opt/local/etc/freetds/freetds.conf:

[WinXP]
  host = 172.16.88.128
  port = 1433
  tds version = 8.0
  client charset = UTF-8

$ cd /opt/local/etc/freetds/
$ sudo odbcinst -i -d -f tds.driver.template
$ sudo odbcinst -i -s -l -f tds.dsn.template

Those commands are the official way of modifying files /opt/local/etc/odbcinst.ini and /opt/local/etc/odbc.ini but you may of course modify them manually.

Testing the System

It is possible to test the connection with isql:

$ isql -vv TYPO3MSSQL username password

But it never worked for me until I found the underlying cause (Google was of no help although many users had this problem). I always got following error:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[ISQL]ERROR: Could not SQLConnect

The trick is to export variable ODBCINI:

$ export ODBCINI=/opt/local/etc/odbc.ini

Another test you may perform before going on configuring your TYPO3 website is with a simple PHP script:

<?php
$con = odbc_connect('TYPO3MSSQL', 'username', 'password')
	or die('could not connect to server: ' . odbc_errormsg() . "\n");
 
$query = 'SELECT * FROM be_users';
$result = odbc_exec($con, $query);
 
while (odbc_fetch_row($result)) {
	$field1 = odbc_result($result, 1);
	$field2 = odbc_result($result, 2);
	echo 'Field 1: ' . $field1 . "\n";
	echo 'Field 2: ' . $field2 . "\n";
}
 
odbc_close($con);
?>

If you had to export the ODBCINI variable, you likely will have to do it too for Apache2, otherwise TYPO3 will fail to connect to your MSSQL database with the same error message.

Edit file /opt/local/apache2/bin/envvars and add this to the end:

ODBCINI="/opt/local/etc/odbc.ini"
export ODBCINI

Then restart Apache.

PHP Configuration

Last but not least, edit your php.ini file:

[ODBC]
;odbc.default_db    =  Not yet implemented
;odbc.default_user  =  Not yet implemented
;odbc.default_pw    =  Not yet implemented

; Allow or prevent persistent links.
odbc.allow_persistent = On

; Check that a connection is still valid before reuse.
odbc.check_persistent = On

; Maximum number of persistent links.  -1 means no limit.
odbc.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
odbc.max_links = -1

; Handling of LONG fields.  Returns number of bytes to variables.  0 means
; passthru.
odbc.defaultlrl = 4096

; Handling of binary data.  0 means passthru, 1 return as is, 2 convert to char.
; See the documentation on odbc_binmode and odbc_longreadlen for an explanation
; of uodbc.defaultlrl and uodbc.defaultbinmode
odbc.defaultbinmode = 1

and this in the MSSQL section:

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textlimit = 2147483647

; Valid range 0 - 2147483647.  Default = 4096.
mssql.textsize = 2147483647