Using Propel With MSSQL Server

    Propel has support for Sybase ASE and MSSQL server 2005 and above. There are several different options available for PDO drivers in both Windows and Linux.

    Windows

    Windows has 4 different driver implementations that could be used. In order of support: pdo_sqlsrv, pdo_sybase, pdo_mssql, and pdo_odbc.

    pdo_dblib can be built against either FreeTDS (pdo_sybase) or MS SQL Server (pdo_mssql) dblib implementations. The driver is not a complete PDO driver implementation and lacks support for transactions or driver attributes.

    pdo_sqlsrv

    This is a driver released in August 2010 by Microsoft for interfacing with MS SQL Server. This is a very complete PDO driver implementation and will provide the best results when using Propel on the Windows platform. It does not return blobs as a resource right now but this feature will hopefully be added in a future release. There is also a bug with setting blob values to null that Propel has a workaround for.

    Sample dsn's for pdo_sqlsrv:

    <dsn>sqlsrv:server=localhost\SQLEXPRESS;Database=propel</dsn>
    <dsn>sqlsrv:server=localhost\SQLEXPRESS,1433;Database=propel</dsn>
    <dsn>sqlsrv:server=localhost,1433;Database=propel</dsn>
    

    Sample configuration file for pdo_sqlsrv:

    propel:
      database:
          connections:
              bookstore:
                  adapter: mysql
                  classname: Propel\Runtime\Connection\ConnectionWrapper
                  dsn: sqlsrv:server=localhost,1433;Database=propel
                  user: my_db_user
                  password: s3cr3t
                  attributes:
      runtime:
          defaultConnection: bookstore
          connections:
              - bookstore
      generator:
          defaultConnection: bookstore
          connections:
              - bookstore
    <?php
    
    return [
        'propel' => [
            'database' => [
                'connections' => [
                    'bookstore' => [
                        'adapter'    => 'mysql',
                        'classname'  => 'Propel\Runtime\Connection\ConnectionWrapper',
                        'dsn'        => 'sqlsrv:server=localhost,1433;Database=propel',
                        'user'       => 'my_db_user',
                        'password'   => 's3cr3t',
                        'attributes' => []
                    ]
                ]
            ],
            'runtime' => [
                'defaultConnection' => 'bookstore',
                'connections' => ['bookstore']
            ],
            'generator' => [
                'defaultConnection' => 'bookstore',
                'connections' => ['bookstore']
            ]
        ]          
    ];
    {
        "propel": {
            "database": {
                "connections": {
                    "bookstore": {
                        "adapter": "mysql",
                        "classname": "Propel\Runtime\Connection\ConnectionWrapper",
                        "dsn": "sqlsrv:server=localhost,1433;Database=propel",
                        "user": "my_db_user",
                        "password": "s3cr3t",
                        "attributes": []
                    }
                }
            },
            "runtime": {
                "defaultConnection": "bookstore",
                "connections": ["bookstore"]
            },
            "generator": {
                "defaultConnection": "bookstore",
                "connections": ["bookstore"]
            }
        }
    }
    [propel]
    ;
    ; Database section
    ;
    database.connections.bookstore.adapter    = mysql
    database.connections.bookstore.classname  = Propel\Runtime\Connection\ConnectionWrapper
    database.connections.bookstore.dsn        = sqlsrv:server=localhost,1433;Database=propel
    database.connections.bookstore.user       = my_db_user
    database.connections.bookstore.password   = s3cr3t
    database.connections.bookstore.attributes =
    
    ;
    ; Runtime section
    ;
    runtime.defaultConnection = bookstore
    runtime.connections[0]    = bookstore
    
    ;
    ; Generator section
    ; 
    generator.defaultConnection = bookstore
    generator.connections[0] = bookstore
    <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
    <config>
        <propel>
            <database>
                <connections>
                    <connection id="bookstore">
                        <adapter>mysql</adapter>
                        <classname>Propel\Runtime\Connection\ConnectionWrapper</classname>
                        <dsn>sqlsrv:server=localhost,1433;Database=propel</dsn>
                        <user>my_db_user</user>
                        <password>s3cr3t</password>
                    </connection>
                </connections>
            </database>
            <runtime>
                <defaultConnection>bookstore</defaultConnection>
                <connection>bookstore</connection>
            </runtime>
            <generator>
                <defaultConnection>bookstore</defaultConnection>
                <connection>bookstore</connection>
            </generator>
        </propel>
    </config>

    pdo_sybase

    When built against FreeTDS dblib it will be called pdo_sybase. This requires properly setting up the FreeTDS freetds.conf and locales.conf. There is a workaround for the lack of transactions support in the pdo_dblib driver by using MssqlDebugPDO or MssqlPropelPDO classes.

    c:\freetds.conf

    [global]
      client charset = UTF-8
      tds version = 8.0
      text size = 20971520
    

    c:\locales.conf

    [default]
      date format = %Y-%m-%d %H:%M:%S.%z
    

    Sample dsn's for pdo_sybase:

    <dsn>sybase:host=localhost\SQLEXPRESS;dbname=propel</dsn>
    <dsn>sybase:host=localhost\SQLEXPRESS:1433;dbname=propel</dsn>
    <dsn>sybase:host=localhost:1433;dbname=propel</dsn>
    

    Sample configuration file for pdo_sybase:

    propel:
      database:
          connections:
              bookstore:
                  adapter: mysql
                  classname: Propel\Runtime\Connection\ConnectionWrapper
                  dsn: sybase:host=localhost:1433;Database=propel
                  user: my_db_user
                  password: s3cr3t
                  attributes:
      runtime:
          defaultConnection: bookstore
          connections:
              - bookstore
      generator:
          defaultConnection: bookstore
          connections:
              - bookstore
    <?php
    
    return [
        'propel' => [
            'database' => [
                'connections' => [
                    'bookstore' => [
                        'adapter'    => 'mysql',
                        'classname'  => 'Propel\Runtime\Connection\ConnectionWrapper',
                        'dsn'        => 'sybase:host=localhost:1433;Database=propel',
                        'user'       => 'my_db_user',
                        'password'   => 's3cr3t',
                        'attributes' => []
                    ]
                ]
            ],
            'runtime' => [
                'defaultConnection' => 'bookstore',
                'connections' => ['bookstore']
            ],
            'generator' => [
                'defaultConnection' => 'bookstore',
                'connections' => ['bookstore']
            ]
        ]          
    ];
    {
        "propel": {
            "database": {
                "connections": {
                    "bookstore": {
                        "adapter": "mysql",
                        "classname": "Propel\Runtime\Connection\ConnectionWrapper",
                        "dsn": "sybase:host=localhost:1433;Database=propel",
                        "user": "my_db_user",
                        "password": "s3cr3t",
                        "attributes": []
                    }
                }
            },
            "runtime": {
                "defaultConnection": "bookstore",
                "connections": ["bookstore"]
            },
            "generator": {
                "defaultConnection": "bookstore",
                "connections": ["bookstore"]
            }
        }
    }
    [propel]
    ;
    ; Database section
    ;
    database.connections.bookstore.adapter    = mysql
    database.connections.bookstore.classname  = Propel\Runtime\Connection\ConnectionWrapper
    database.connections.bookstore.dsn        = sybase:host=localhost:1433;Database=propel
    database.connections.bookstore.user       = my_db_user
    database.connections.bookstore.password   = s3cr3t
    database.connections.bookstore.attributes =
    
    ;
    ; Runtime section
    ;
    runtime.defaultConnection = bookstore
    runtime.connections[0]    = bookstore
    
    ;
    ; Generator section
    ; 
    generator.defaultConnection = bookstore
    generator.connections[0] = bookstore
    <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
    <config>
        <propel>
            <database>
                <connections>
                    <connection id="bookstore">
                        <adapter>mysql</adapter>
                        <classname>Propel\Runtime\Connection\ConnectionWrapper</classname>
                        <dsn>sybase:host=localhost:1433;Database=propel</dsn>
                        <user>my_db_user</user>
                        <password>s3cr3t</password>
                    </connection>
                </connections>
            </database>
            <runtime>
                <defaultConnection>bookstore</defaultConnection>
                <connection>bookstore</connection>
            </runtime>
            <generator>
                <defaultConnection>bookstore</defaultConnection>
                <connection>bookstore</connection>
            </generator>
        </propel>
    </config>

    pdo_mssql

    When built against MS SQL Server dblib the driver will be called pdo_mssql. It is not recommended to use the pdo_mssql driver because it strips blobs of single quotes when retrieving from the database and will not return blobs or clobs longer that 8192 characters. The dsn differs from pdo_sybase in that it uses a comma between the server and port number instead of a colon and mssql instead of sybase for the driver name.

    Sample dsn's for pdo_mssql:

    <dsn>mssql:host=localhost\SQLEXPRESS;dbname=propel</dsn>
    <dsn>mssql:host=localhost\SQLEXPRESS,1433;dbname=propel</dsn>
    <dsn>mssql:host=localhost,1433;dbname=propel</dsn>
    

    pdo_odbc

    Currently pdo_odbc cannot be used to access MSSQL with propel because of a long standing bug with the MS SQL Server ODBC Client. Last update on 8/3/2010 was that it would be resolved in a future release of the SQL Server Native Access Client. This bug is related to two php bugs (Bug #44643 and Bug #36561)

    Linux

    Linux has 2 driver implementations that could be used: pdo_dblib, and pdo_obdc.

    pdo_dblib

    pdo_dblib is built against the FreeTDS dblib implementation. The driver is not a complete PDO driver implementation and lacks support for transactions or driver attributes. This requires properly setting up the FreeTDS freetds.conf and locales.conf. There is a workaround for the lack of transactions support in the pdo_dblib driver by using MssqlDebugPDO or MssqlPropelPDO classes.

    Redhat: /etc/freetds.conf Ubuntu: /etc/freetds/freetds.conf

    [global]
      client charset = UTF-8
      tds version = 8.0
      text size = 20971520
    

    Redhat: /etc/locales.conf Ubuntu: /etc/freetds/locales.conf

    [default]
      date format = %Y-%m-%d %H:%M:%S.%z
    

    Sample dsn's for pdo_dblib:

    <dsn>dblib:host=localhost\SQLEXPRESS;dbname=propel</dsn>
    <dsn>dblib:host=localhost\SQLEXPRESS:1433;dbname=propel</dsn>
    <dsn>dblib:host=localhost:1433;dbname=propel</dsn>
    

    Sample configuration file for pdo_dblib:

    propel:
      database:
          connections:
              bookstore:
                  adapter: mysql
                  classname: Propel\Runtime\Connection\ConnectionWrapper
                  dsn: dblib:host=localhost:1433;dbname=propel
                  user: my_db_user
                  password: s3cr3t
                  attributes:
      runtime:
          defaultConnection: bookstore
          connections:
              - bookstore
      generator:
          defaultConnection: bookstore
          connections:
              - bookstore
    <?php
    
    return [
        'propel' => [
            'database' => [
                'connections' => [
                    'bookstore' => [
                        'adapter'    => 'mysql',
                        'classname'  => 'Propel\Runtime\Connection\ConnectionWrapper',
                        'dsn'        => 'dblib:host=localhost:1433;dbname=propel',
                        'user'       => 'my_db_user',
                        'password'   => 's3cr3t',
                        'attributes' => []
                    ]
                ]
            ],
            'runtime' => [
                'defaultConnection' => 'bookstore',
                'connections' => ['bookstore']
            ],
            'generator' => [
                'defaultConnection' => 'bookstore',
                'connections' => ['bookstore']
            ]
        ]          
    ];
    {
        "propel": {
            "database": {
                "connections": {
                    "bookstore": {
                        "adapter": "mysql",
                        "classname": "Propel\Runtime\Connection\ConnectionWrapper",
                        "dsn": "dblib:host=localhost:1433;dbname=propel",
                        "user": "my_db_user",
                        "password": "s3cr3t",
                        "attributes": []
                    }
                }
            },
            "runtime": {
                "defaultConnection": "bookstore",
                "connections": ["bookstore"]
            },
            "generator": {
                "defaultConnection": "bookstore",
                "connections": ["bookstore"]
            }
        }
    }
    [propel]
    ;
    ; Database section
    ;
    database.connections.bookstore.adapter    = mysql
    database.connections.bookstore.classname  = Propel\Runtime\Connection\ConnectionWrapper
    database.connections.bookstore.dsn        = dblib:host=localhost:1433;dbname=propel
    database.connections.bookstore.user       = my_db_user
    database.connections.bookstore.password   = s3cr3t
    database.connections.bookstore.attributes =
    
    ;
    ; Runtime section
    ;
    runtime.defaultConnection = bookstore
    runtime.connections[0]    = bookstore
    
    ;
    ; Generator section
    ; 
    generator.defaultConnection = bookstore
    generator.connections[0] = bookstore
    <?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
    <config>
        <propel>
            <database>
                <connections>
                    <connection id="bookstore">
                        <adapter>mysql</adapter>
                        <classname>Propel\Runtime\Connection\ConnectionWrapper</classname>
                        <dsn>dblib:host=localhost:1433;dbname=propel</dsn>
                        <user>my_db_user</user>
                        <password>s3cr3t</password>
                        <attributes></attributes>
                    </connection>
                </connections>
            </database>
            <runtime>
                <defaultConnection>bookstore</defaultConnection>
                <connection>bookstore</connection>
            </runtime>
            <generator>
                <defaultConnection>bookstore</defaultConnection>
                <connection>bookstore</connection>
            </generator>
        </propel>
    </config>

    pdo_odbc

    pdo_odbc using UnixODBC and FreeTDS. This should be supported in propel but with ubuntu 10.04 and php 5.2.x any statement binding causes apache to segfault so I have not been able to test it further. If anyone has any additional experience with this please post information to the propel development group. If you would like to experiment there are some instructions you can follow here for getting it setup on ubuntu.