How to use Zend Framework 2 with MS SQL Server on

Question

What, if it exists, is the canonical way to use ZF2 with MS SQL Server on a non-Windows OS?

From what I can tell from the documentation, only the Sqlsrv driver is officially supported, which only works on the Windows platform.

In ZF1, we used the Pdo_Mssql adapter and specified the pdoType as dblib. I can't find any references to doing anything similar in ZF2.

Apparently there was a Pdo\Mssql driver some time ago which was removed during a major refactoring, but I don't see a currently documented way of using Pdo_Dblib.

According to the adapter documentation above, you can set the driver in the adapter config to Pdo=OtherPdoDriver, but there's no documented examples of this usage. Does that mean you can use Pdo=Pdo_Dblib or Pdo=dblib and it will work automagically?

I've found passing references to a PDO ODBC driver, which would be a usable alternative, but can't find any documentation or code references.

Answer 1

Yes, you can use odbc to connect. It is not currently officially supported, but you can get it working fairly easily, with minimal modifications.

The first thing you need to do, is make sure you can connect using the basic PHP PDO functions. So, do something like this:

$resource = new PDO('odbc:driver=FreeTDS;dbname=MYDB;Server=127.0.0.1;Port=8090;UID=Testuser;PWD=testpass;', 'Testuser', 'testpass', array());
$s = $resource->prepare('SELECT * FROM TEST_TABLE');
$r=$s->execute();
var_dump($r);

You'll need to change the host, port, user, pass, table name etc. for your settings, but you can use this to make sure you have those details right. Once you know what you are doing to connect, you can access it with Zend, like this:

$configArray = array(
    'driver' => 'pdo_odbc',
    'driver_options' => array('driver' => 'FreeTDS'),
    'platform' => 'Mssql',
    'dbname' => 'MYDB',
    'host' => '127.0.0.1',
    'port' => 8090,
    'user' => 'Testuser',
    'pass' => 'testpass'
);

$adapter = new Zend\Db\Adapter\Adapter($configArray);

$q = new Zend\Db\Sql\Select();
$q->from('TEST_TABLE');
$sql = $q->getSqlString($adapter->platform);
$r = $adapter->query($sql);
echo $r->getSql()."\n";
$result = $r->execute();
while(($res = $result->next()) !== false){
    print_r($res);
}

However this will not actually work, for two reasons. First, the platform - Zend currently doesn't have an mssql platform; it defaults to sql92, which will not work, because it quotes identifiers in "", when mssql needs them to be quoted with []. So you'll need to copy the Zend/Db/Adapter/Platform/Sql92.php as Mssql.php, and change it to use [ and ] instead of ".

Second, because the dsn for connection to odbc requires the driver field, which zend currently doesn't support. You'll need to open up the Driver/Pdo/Connection class and modify the connect function, to add $dsn[] = "driver={$options['driver']}"; and replace $dsn[] = "host={$hostname}"; with $dsn[] = "server={$hostname}"; when $pdoDriver = odbc.

Note that the other answers are posted by people who do not actually understand how the Zend 2 library works - if you specify a value for 'dsn', then the other fields are ignored. I have NO idea where they got the idea of 'pdotype', as that is not a field that appears anywhere in the code.

Answer 2

You can use a pdo connection with pdotype set to dblib (with loaded pdo_dblib extension). sqlsrv is only available for Windows.

Here you can see how freetds and unixodbc is configured to connect to a MS SQL Server. http://featurebug.blogspot.de/2011/01/mac-os-x-php-zend-server-ce-freetds-and.html

UPDATE: Here is an example how to use a connection string to connect:

$db = new Zend\Db\Adapter\Adapter(
    array(
        'driver'        => 'Pdo',
        'dsn'            => 'dblib:host=mssql_freetds;',
        'username'       => 'mssql_username',
        'password'       => 'mssql_password',
        )
);

Answer 3

Install the php5-sybase. Make sure that freetds is installed. You should have a conf file under /etc/freetds called freetds.conf.

In your freetds.conf file you should setup your mssql connection like this:

[MyMsSqlServer]
host = symachine.domain.com
port = 5000
tds version = 5.0

Then, (using the ZF2 Albums Tutorial as an example here) you set up the adapter like this:

return array(
    'db' => array(
        'driver'         => 'Pdo',
        'dsn'            => 'dblib:host=MyMsSqlServer;dbname=zf2tutorial',
        'charset'        =>  'UTF-8',
        'username'       => 'username',
        'password'       => 'password',
        'pdotype'       => 'dblib',
    ),
    'service_manager' => array(
        'factories' => array(
            'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
        ),
    ),
);

See if this gets you anywhere and let me know. I had a bitch of a time getting this to work, and I am more then happy to help anyone else that is having issues with this!!!