Symfony2 MSSQL support with pdo_dblib

We now maintain a library for connecting to a MSSQL database, since we are using it in one of our Symfony2 applications. You can find the code here:

https://github.com/LeaseWeb/LswDoctrinePdoDblib

If you are trying to use Doctrine 2 (for Symfony 2) to connect to MSSQL server from a Linux machine, you might encounter the same problems we did. To be able to connect to MSSQL from a Ubuntu Linux server you will have to install “php5-sybase” (Sybase / MS SQL Server module for php5).

$ sudo apt-get install freetds-bin php5-sybase

Add the following lines to “/etc/freetds/freetds.conf”, and make sure you set it to talk the right MSSQL protocol. The TDS protocol version should be 8.0 for MSSQL server 2005 and up, which may be confusing. Also replace “{mssql-server-ip-address}” with your MSSQL servers IP address (without the brackets).

    [mssql_freetds]
        host = {mssql-server-ip-address}
        port = 1433
        tds version = 8.0
        client charset = UTF-8
        text size = 20971520

If you only connect to one server, you can also set the global TDS protocol version to 8.0 like this:

     [global]
         # TDS protocol version
         tds version = 8.0

Now test that your freetds is working by using the command line client. You have to replace {username} and {password} with your username and password (without the brackets):

$ tsql -S mssql_freetds -U {username}
Password: {password}
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> quit

If you specify in “app/config/parameters.ini” that you want to use the “pdo_dblib” driver like this:

database_driver="pdo_dblib"

You will see the error:

[Doctrine\DBAL\DBALException]
The given 'driver' pdo_dblib is unknown, Doctrine currently supports only the following drivers:
pdo_mysql, pdo_sqlite, pdo_pgsql, pdo_oci, oci8, ibm_db2, pdo_ibm, pdo_sqlsrv

This is why you should install our LswDoctrinePdoDblib bundle, and following the instructions, you should comment out the “driver” and add a “driver_class” like this:

    # Doctrine Configuration
    doctrine:
        dbal:
            #driver:   %database_driver%
            driver_class:   Lsw\DoctrinePdoDblib\Doctrine\DBAL\Driver\PDODblib\Driver

After that you should be able to run this command without any errors:

$ php app/console doctrine:database:create

Now do not expect everything to work flawlessly, since MSSQL is not officially supported, and Doctrine is very picky in what it does and does not accept as database structure. However, this should get you started. Post your experience and questions in the comments. Good luck!

Share

Tuning Zend framework and Doctrine

In principle, the combination of Zend Framework with Doctrine is not too difficult. But first let’s talk about the preparations. According to the author of Zend Framework, the default file structure of project can be a bit more optimal.

Here is the default structure of the Zend Framework project files:


/
  application/
    default/
      controllers/
      layouts/
      models/
      views/
  html/
  library/

It can often be that you will have a number of applications (e.g., frontend and backend ), and you want to use the same model for them. In this case, it can be a good practice to create your models folder in library/, in which case the new structure would look as follows:

/
  application/
    default/
      controllers/
      layouts/
      views/
  html/
  library/
    Model/

In addition, the folder models/ is renamed to Model. We now proceed as follows:

  1. Download a fresh copy of Doctrine-xxx-Sandbox.tgz from the official website.
  2. Copy the contents of the lib/folder from the archive to our project library/ folder.
  3. Create another folder bin/sandbox/ in the root of our project and copy the rest of the archive there (except models/ folder and the index.php file).

Now the structure of our project should look like this:

/
  application/
    default/
      controllers/
      layouts/
      views/
  bin/
    sandbox/
      data/
      lib/
      migrations/
      schema/
      config.php
      doctrine
      doctrine.php
  html/
  library/
    Doctrine/
    Model/
    Doctrine.php

Clear the content of the folder bin/sandbox/lib/ – we now have the library in another place.
Now it’s time to configure the Doctrine to work with new file structure.

Change the value of the constant MODELS_PATH in the file bin/sandbox/config.php::

SANDBOX_PATH . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'library' . DIRECTORY_SEPARATOR . 'Model'


Next, change the connection settings for the database. Change the value of the DSN constant to reflect your database settings. For example, if you use the DBMS MySQL, the DSN might look like this:

'mysql://root@localhost/mydbname'


Configure include_paths on the first line in the config file, so our script can find files on new locations:

set_include_path( '.' . PATH_SEPARATOR . '..' . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . 'library' . DIRECTORY_SEPARATOR . PATH_SEPARATOR . '.' . DIRECTORY_SEPARATOR . 'lib' . PATH_SEPARATOR . get_include_path());

Then connect the main Doctrine library file directly after installation paths, and set the startup function:

require_once 'Doctrine.php';

/**
 * Setup autoload function
 */
spl_autoload_register( array(
    'Doctrine',
    'autoload'
));

Continue reading Tuning Zend framework and Doctrine

Share