Dev 1.x

    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: mssql
              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'    => 'mssql',
                    '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": "mssql",
                    "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    = mssql
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>mssql</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: mssql
              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'    => 'mssql',
                    '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": "mssql",
                    "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    = mssql
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>mssql</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: mssql
              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'    => 'mssql',
                    '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": "mssql",
                    "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    = mssql
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>mssql</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.

    ↟ Jump To Top