Using Propel With MSSQL Server

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:

sqlsrv:server=localhost\SQLEXPRESS;Database=propel

sqlsrv:server=localhost\SQLEXPRESS,1433;Database=propel sqlsrv:server=localhost,1433;Database=propel

Sample runtime-conf.xml for pdo_sqlsrv:

 id="bookstore">
  sqlsrv

DebugPDO sqlsrv:server=localhost,1433;Database=propel username password

Sample build.properties for pdo_sqlsrv:

propel.database = sqlsrv
propel.database.url = sqlsrv:server=127.0.0.1,1433;Database=propel

pdo_sybase¶

When built against FreeTDS dblib it will be called pdo_sybase. This requires properly setting up the FreeTDS freetds.conf andlocales.conf. There is a workaround for the lack of transactions support in the pdo_dblib driver by using MssqlDebugPDO orMssqlPropelPDO 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:

sybase:host=localhost\SQLEXPRESS;dbname=propel

sybase:host=localhost\SQLEXPRESS:1433;dbname=propel sybase:host=localhost:1433;dbname=propel

Sample runtime-conf.xml for pdo_sybase:

 id="bookstore">
  mssql

MssqlDebugPDO sybase:host=localhost:1433;dbname=propel username password

Sample build.properties for pdo_sybase:

propel.database = mssql
propel.database.url = sybase:host=localhost:1433;dbname=propel

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:

mssql:host=localhost\SQLEXPRESS;dbname=propel

mssql:host=localhost\SQLEXPRESS,1433;dbname=propel mssql:host=localhost,1433;dbname=propel

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:

dblib:host=localhost\SQLEXPRESS;dbname=propel

dblib:host=localhost\SQLEXPRESS:1433;dbname=propel dblib:host=localhost:1433;dbname=propel

Sample runtime-conf.xml for pdo_dblib:

 id="bookstore">
  mssql

MssqlDebugPDO dblib:host=localhost:1433;dbname=propel username password

Sample build.properties for pdo_dblib:

propel.database = mssql
propel.database.url = dblib:host=localhost:1433;dbname=propel

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.

reference : http://propelorm.org/Propel/cookbook/using-mssql-server.html