PHP 5.4 on Linux: How to connect with MS SQL Server 2008?

php, sql-server-2008, utf-8, zend-framework

Solution

The answer for my question is: Use freeTDS! Theres a parameter for charset on it:

[MyDSN]
    host = <<ip>>
    port = <<port>>
    # use 8.0 for newer versions of SQLSERVER
        tds version = 8.0
        # text size don't need to be such a high value, its just an example
        text size = 4294967295
        client charset = UTF-8

On Zend Framework, configure your connection like this:

;; BANCO DE DADOS LINUX
database.adapter                = PDO_MSSQL
database.params.pdoType         = dblib

database.params.host            = MyDSN
database.params.dbname          = <<dbname>>
database.params.username        = <<username>>
database.params.password        = <<passwd>>
database.params.driver_options.charset = UTF-8

database.isDefaultTableAdapter  = true

It solves the problem! ;)

Problem

I have a Linux server Debian 6, with Apache 2.2 and PHP 5.4 installed. I need to connect my application with a MS SQL Server 2008. My application is using Zend Framework 1.11 and charset UTF-8 (I'll have users from all places in the world and they will put data in their own language). FRIST, I tried to use Microsoft SQL Server ODBC driver for Linux. It says is only for Red Hat, but I follow these instructions to install: http://www.codesynthesis.com/~boris/blog/2011/12/02/microsoft-sql-server-odbc-driver-linux/ I could connect and make some selects on it, but I couldn't insert data on it. I got a problem on binding parameters on pdo statements. Insert data like the following didn't work: ``` $stmt = $conn->prepare("insert into mar_regions (name) values (:name)"); $resp = $stmt->execute(array(':name' => $param)); ``` But if I used like the this, it works: ``` $stmt = $conn->prepare("insert into mar_regions (name) values ('".$param."')"); $resp = $stmt->execute(); ``` So I gave up from this driver, because my application no ZF 1.11 will not work if this. SECOND, I try to use PDO Driver for FreeTDS. This one works ok and I could use on my ZF 1.11 application. But then, I got one more problem: charsets. I configure my freeTDS.conf to use UTF-8, change my tables to use NVARCHAR insted of VARCHAR and could insert utf-8 data like this: ``` $stmt = $dbh->prepare("insert into mar_teste (name) values (N'ンから初・配信 € зеленый банан ÀÀÀÀáááááá')"); $resp = $stmt->execute(); ``` But, on my ZF 1.11, I can't pass this 'N' attribute on querys! So my application still didn't work. As you can see I tried everything. So my question is: How to connect from linux, using ZF 1.11 charset UTF-8, on MS SQL Server 2008?

Original source