Development

HowToConnectToMSSQLServer

You must first sign up to be able to contribute.

Version 18 (modified by base, 8 years ago)
--

Linux Host

To access MSSQL Server from symfony 1.2 the PDO driver dblib is utilized.

Do a php -m from the command line and make sure you have pdo_dblib listed. Next modify/create the following files:

config/databases.yml

all:
  propel:
    class:       sfPropelDatabase
    param:
      phptype:   mssql
      classname: MssqlPropelPDO
      dsn:       dblib:host=12.34.56.78;dbname=YOURDB
      username:  xxx
      password:  xxx
      encoding:
      pooling:   true

config/propel.ini

propel.database             = mssql
propel.database.driver      = dblib
propel.database.createUrl   = dblib:host=12.34.56.78;dbname=YOURDB
propel.database.user        = xxx
propel.database.creole.url  = ${propel.database.url}
propel.database.password    = xxx
propel.database.encoding    = utf8
propel.dateTimeClass        = MssqlDateTime

MSSQL Server returns datetimes in a format that strtotime doesn't handle so we need to extend DateTime? to account for it.

lib/MssqlDateTime.class.php

<?php
class MssqlDateTime extends DateTime
{
  public function __construct($datetime='now', DateTimeZone $tz = null)
  {
    //if the date is bad account for Mssql datetime format
    if ($datetime != 'now' && strtotime($datetime) === false)
    {
      $datetime = substr($datetime,0, -6).substr($datetime,-2);
    }

    if($tz instanceof DateTimeZone)
    {
      parent::__construct($datetime,$tz);
    }
    else
    {
      parent::__construct($datetime);
    }
  }
}

dblib doesn't support transactions so we need to add a workaround for transactions, last insert ID, and quoting

Fix by sarhugo in ticket http://trac.symfony-project.org/ticket/5577

lib/MssqlPropelPDO

<?php

class MssqlPropelPDO extends PropelPDO {
  /**
   * Begin a transaction.
   *
   * It is necessary to override the abstract PDO transaction functions here, as
   * the PDO driver for MSSQL does not support transactions.
   */
  public function beginTransaction()
  {
    $return = true;
    $opcount = $this->getNestedTransactionCount();
    if ( $opcount === 0 ) {
      $return = self::exec('BEGIN TRANSACTION');
    }
    $this->incrementNestedTransactionCount();
    return $return;
  }

  /**
   * Commit a transaction.
   *
   * It is necessary to override the abstract PDO transaction functions here, as
   * the PDO driver for MSSQL does not support transactions.
   */
  public function commit()
  {
    $return = true;
    $opcount = $this->getNestedTransactionCount();
    if ($opcount > 0) {
      if ($opcount === 1) {
        $return = self::exec('COMMIT TRANSACTION');
      }
      $this->decrementNestedTransactionCount();
    }
    return $return;
  }

  /**
   * Roll-back a transaction.
   *
   * It is necessary to override the abstract PDO transaction functions here, as
   * the PDO driver for MSSQL does not support transactions.
   */
  public function rollBack()
  {
    $return = true;
    $opcount = $this->getNestedTransactionCount();
    if ($opcount > 0) {
      // If we're in a transaction, always roll it back
      // regardless of nesting level.
      $return = self::exec('ROLLBACK TRANSACTION');

      // reset nested transaction count to 0 so that we don't
      // try to commit (or rollback) the transaction outside this scope.
      $this->nestedTransactionCount = 0;
    }
    return $return;
  }

  public function lastInsertId($seqname = null) {
    $result = self::query('SELECT SCOPE_IDENTITY()');
    return (int)$result->fetchColumn();
  }

  public function quoteIdentifier($text)
  {
    return '[' . $text . ']';
  }

  public function useQuoteIdentifier()
  {
    return true;
  }
}

Note: As of symfony 1.3, the files under the lib/vendor/ directory are not autoloaded anymore by default. Add a new entry in the autoload.yml configuration file (if above classes were placed in %SF_LIB_DIR%/vendor/mssql):

autoload:
  mssql:
    name:      mssql
    path:      %SF_LIB_DIR%/vendor/mssql
    recursive: on

There is also a bug in the propel-generator when setting datetime it is using a timestamp format that isn't accepted my MSSQL.

Propel ticket http://propel.phpdb.org/trac/ticket/714

To fix it edit plugins/sfPropelPlugin/lib/vendor/propel-generator/classes/propel/engine/platform/MssqlPlatform.php and add this method:

	/**
	 * Gets the preferred timestamp formatter for setting date/time values.
	 * @return     string
	 */
	public function getTimestampFormatter()
	{
		return 'Ymd H:i:s';
	}

NB : It's a little mistake in Propel ticket. Waiting date format by SQL Server is 'Ymd' and not 'Y-m-d'

There is another propel bug in sfPropelPlugin/lib/vendor/propel/adapter/DBMSSQL.php

The bug causes problems when trying to page joined tables. Propel ticket http://propel.phpdb.org/trac/ticket/453 with patch.

Another propel bug with inserts. Propel tries to set the auto-increment value to null. This isn't valid. Propel ticket http://propel.phpdb.org/trac/ticket/736 with patch.

Clear the cache and then build the model


Windows Host

The PHP site does not recommend the driver pdo_dblib http://php.chinaunix.net/manual/zh/ref.pdo-dblib.php to access MSSQL on Windows, but rather use ODBC. Moreover, Microsoft will not support dblib in future versions of MSSQL and it is not included with SQL server 2005 and 2008.

I have had more luck with the ODBC driver to connect to MSSQL.

Microsoft released a new PHP driver for MSSQL, but it relies on ODBC and is not compatible with PDO. http://blogs.msdn.com/sqlphp/archive/2008/07/28/microsoft-sql-server-2005-driver-for-php-v1-0-has-released.aspx

I would recommend using MySQL instead of MSSQL with Symfony any time though, the drivers have a lot less bugs because more people use them and care.

To use the ODBC driver for MSSQL with Doctrine, I had to use a trick because I could not make it work by just using the databases.yml settings.

The trick is to open a PDO ODBC connection in the application config or a migration if it is where you need it and pass the PDO handle to Doctrine after the connection is open.

Detailed instruction for Windows XP:

  • First create a system data source in the ODBC from Control Panel>Administrative Tools>Data Sources.

Here, you enter your connection parameters and test the connection. Let's say you call that Data Source 'localexpress'.

  • In your application's configuration class:
<?php

class frontendConfiguration extends sfApplicationConfiguration
{
  public function initialize()
  {     
    
    $manager = Doctrine_Manager::getInstance();     	
    $dbh = new PDO("odbc:localexpress");
    $conn = $manager->connection($dbh, "my_db");
    $conn ->setOption ('username', 'sa');
    $conn ->setOption ('password', 'pass');
    $conn ->setOption ('dsn', "odbc:localexpress");
  }
}

With ODBC, I did not have problems with datetimes.

I mostly used ODBC to import MSSQL data to MySQL and the only difficulty was to convert some ntext fields to UTF8. I my case, there were no international character and I was still getting errors, so I had to convert ntext to varchar(max) before import.


User comments:

Other notes
1. It is also possible to convert a field on the fly using the following syntax:

SELECT jobid, cast ( JobDescription AS TEXT ) as JobDescriptionCasted, jobname

2. Additional PHP/ODBC connection settings when migrating/extracting data from MSSQL:

  ini_set("odbc.defaultlrl", 10000000); // Max read in PHP should be 10 MiB
  // Without this reads are limited to 4096, could not get pictures or other binary text larger than 4 KB

  // Run this before runing any query
  $req = odbc_exec($conn, "SET TEXTSIZE 10000000");


3. You may face a problem when data-loading (propel) with the value NULL in the PKs, this snippet solves this to tables with 'ID' in it's PK name.

BasePeer.php:273
			$newQualifiedCols = array();
			foreach ($qualifiedCols as $qualifiedCol) {
				if(substr($qualifiedCol, -2) != 'ID')
					$newQualifiedCols[] = $qualifiedCol;
			}
			$qualifiedCols = $newQualifiedCols;


4. The above example will only work if you have no other fields that end in "ID" in your tables. The below is what work best for us. It has been tested against non autoIncrement PKs and also multiple column PKs. This also limits itself to only effect MSSQL given that you use the class name DBMSSQL as directed above.

BasePeer.php:273
			if ($adapter instanceof DBMSSQL && !$criteria->keyContainsValue($pk->getFullyQualifiedName())) {
					$newQualifiedCols = array();
					foreach ($qualifiedCols as $qualifiedCol) {
						if(trim($qualifiedCol) != trim($pk->getFullyQualifiedName())) {
							$newQualifiedCols[] = $qualifiedCol;
						}
					}
					$qualifiedCols = $newQualifiedCols;
			}


5. Consider that binary timestamp columns are not directly related to date/time, UPDATE queries won't work with these columns so consider removing those from your schema.yml (my schema was generated from mssql dbase and included those columns)