Development

HowToConnectToMSSQLServer (diff)

You must first sign up to be able to contribute.

Changes between Version 18 and Version 19 of HowToConnectToMSSQLServer

Show
Ignore:
Author:
KRavEN (IP: 199.67.140.44)
Timestamp:
09/15/10 15:05:25 (7 years ago)
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • HowToConnectToMSSQLServer

    v18 v19  
     1For symfony 1.4 it is best to use the sfPropel15Plugin http://www.symfony-project.org/plugins/sfPropel15Plugin as it has MANY MSSQL fixes over the Propel 1.4 that comes with symfony 1.4. 
     2 
     3Before starting look at the propel docs for using MSSQL and get everything setup correctly: http://www.propelorm.org/wiki/Documentation/1.5/MSSQL-Server 
     4 
    15== Linux Host == 
    26 
    3 To access MSSQL Server from symfony 1.2 the PDO driver dblib is utilized. 
     7To access MSSQL Server from symfony 1.4 the PDO driver pdo_dblib is utilized. 
    48 
    59Do a php -m from the command line and make sure you have pdo_dblib listed. 
    812config/databases.yml 
    913{{{ 
     14dev: 
     15  propel: 
     16    param: 
     17      classname:  MssqlDebugPDO 
     18      debug: 
     19        realmemoryusage: true 
     20        details: 
     21          time:       { enabled: true } 
     22          slow:       { enabled: true, threshold: 0.1 } 
     23          mem:        { enabled: true } 
     24          mempeak:    { enabled: true } 
     25          memdelta:   { enabled: true }   
     26 
     27test: 
     28  propel: 
     29    param: 
     30      classname:  MssqlDebugPDO    
     31 
    1032all: 
    1133  propel: 
    2547propel.database             = mssql 
    2648propel.database.driver      = dblib 
    27 propel.database.createUrl   = dblib:host=12.34.56.78;dbname=YOURDB 
     49propel.database.url         = dblib:host=12.34.56.78;dbname=YOURDB 
    2850propel.database.user        = xxx 
    29 propel.database.creole.url  = ${propel.database.url} 
    3051propel.database.password    = xxx 
    31 propel.database.encoding    = utf8 
    32 propel.dateTimeClass        = MssqlDateTime 
    3352}}} 
    34 '''MSSQL Server returns datetimes in a format that strtotime doesn't handle so we need to extend DateTime to account for it.''' 
    35  
    36 lib/MssqlDateTime.class.php 
    37 {{{ 
    38 <?php 
    39 class MssqlDateTime extends DateTime 
    40 { 
    41   public function __construct($datetime='now', DateTimeZone $tz = null) 
    42   { 
    43     //if the date is bad account for Mssql datetime format 
    44     if ($datetime != 'now' && strtotime($datetime) === false) 
    45     { 
    46       $datetime = substr($datetime,0, -6).substr($datetime,-2); 
    47     } 
    48  
    49     if($tz instanceof DateTimeZone) 
    50     { 
    51       parent::__construct($datetime,$tz); 
    52     } 
    53     else 
    54     { 
    55       parent::__construct($datetime); 
    56     } 
    57   } 
    58 } 
    59 }}} 
    60 '''dblib doesn't support transactions so we need to add a workaround for transactions, last insert ID, and quoting''' 
    61  
    62 Fix by sarhugo in ticket [http://trac.symfony-project.org/ticket/5577] 
    63  
    64 lib/MssqlPropelPDO 
    65 {{{ 
    66 <?php 
    67  
    68 class MssqlPropelPDO extends PropelPDO { 
    69   /** 
    70    * Begin a transaction. 
    71    * 
    72    * It is necessary to override the abstract PDO transaction functions here, as 
    73    * the PDO driver for MSSQL does not support transactions. 
    74    */ 
    75   public function beginTransaction() 
    76   { 
    77     $return = true; 
    78     $opcount = $this->getNestedTransactionCount(); 
    79     if ( $opcount === 0 ) { 
    80       $return = self::exec('BEGIN TRANSACTION'); 
    81     } 
    82     $this->incrementNestedTransactionCount(); 
    83     return $return; 
    84   } 
    85  
    86   /** 
    87    * Commit a transaction. 
    88    * 
    89    * It is necessary to override the abstract PDO transaction functions here, as 
    90    * the PDO driver for MSSQL does not support transactions. 
    91    */ 
    92   public function commit() 
    93   { 
    94     $return = true; 
    95     $opcount = $this->getNestedTransactionCount(); 
    96     if ($opcount > 0) { 
    97       if ($opcount === 1) { 
    98         $return = self::exec('COMMIT TRANSACTION'); 
    99       } 
    100       $this->decrementNestedTransactionCount(); 
    101     } 
    102     return $return; 
    103   } 
    104  
    105   /** 
    106    * Roll-back a transaction. 
    107    * 
    108    * It is necessary to override the abstract PDO transaction functions here, as 
    109    * the PDO driver for MSSQL does not support transactions. 
    110    */ 
    111   public function rollBack() 
    112   { 
    113     $return = true; 
    114     $opcount = $this->getNestedTransactionCount(); 
    115     if ($opcount > 0) { 
    116       // If we're in a transaction, always roll it back 
    117       // regardless of nesting level. 
    118       $return = self::exec('ROLLBACK TRANSACTION'); 
    119  
    120       // reset nested transaction count to 0 so that we don't 
    121       // try to commit (or rollback) the transaction outside this scope. 
    122       $this->nestedTransactionCount = 0; 
    123     } 
    124     return $return; 
    125   } 
    126  
    127   public function lastInsertId($seqname = null) { 
    128     $result = self::query('SELECT SCOPE_IDENTITY()'); 
    129     return (int)$result->fetchColumn(); 
    130   } 
    131  
    132   public function quoteIdentifier($text) 
    133   { 
    134     return '[' . $text . ']'; 
    135   } 
    136  
    137   public function useQuoteIdentifier() 
    138   { 
    139     return true; 
    140   } 
    141 } 
    142 }}} 
    143  
    144 Note: As of symfony 1.3, the files under the lib/vendor/ directory are not autoloaded anymore by default. 
    145 Add a new entry in the autoload.yml configuration file (if above classes were placed in %SF_LIB_DIR%/vendor/mssql): 
    146 {{{ 
    147 autoload: 
    148   mssql: 
    149     name:      mssql 
    150     path:      %SF_LIB_DIR%/vendor/mssql 
    151     recursive: on 
    152 }}} 
    153  
    154 There is also a bug in the propel-generator when setting datetime it is using a timestamp format that isn't accepted my MSSQL. 
    155  
    156 Propel ticket http://propel.phpdb.org/trac/ticket/714 
    157  
    158 To fix it edit plugins/sfPropelPlugin/lib/vendor/propel-generator/classes/propel/engine/platform/MssqlPlatform.php and add this method: 
    159  
    160 {{{ 
    161         /** 
    162          * Gets the preferred timestamp formatter for setting date/time values. 
    163          * @return     string 
    164          */ 
    165         public function getTimestampFormatter() 
    166         { 
    167                 return 'Ymd H:i:s'; 
    168         } 
    169 }}} 
    170 '''NB : It's a little mistake in Propel ticket. Waiting date format by SQL Server is 'Ymd' and not 'Y-m-d'''' 
    171  
    172 There is another propel bug in sfPropelPlugin/lib/vendor/propel/adapter/DBMSSQL.php 
    173  
    174 The bug causes problems when trying to page joined tables. 
    175 Propel ticket http://propel.phpdb.org/trac/ticket/453 with patch. 
    176  
    177 Another propel bug with inserts.  Propel tries to set the auto-increment value to null.  This isn't valid. 
    178 Propel ticket http://propel.phpdb.org/trac/ticket/736 with patch. 
    17953 
    18054Clear the cache and then build the model 
    18357== Windows Host == 
    18458 
    185 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. 
    186 Moreover, Microsoft will not support dblib in future versions of MSSQL and it is not included with SQL server 2005 and 2008. 
     59To access MSSQL Server from symfony 1.4 the PDO driver pdo_sqlsrv is utilized. 
    18760 
    188 I have had more luck with the ODBC driver to connect to MSSQL. 
     61Do a php -m from the command line and make sure you have pdo_sqlsrv listed. 
     62Next modify/create the following files: 
    18963 
    190 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] 
     64config/databases.yml 
     65{{{ 
     66dev: 
     67  propel: 
     68    param: 
     69      classname:  DebugPDO 
     70      debug: 
     71        realmemoryusage: true 
     72        details: 
     73          time:       { enabled: true } 
     74          slow:       { enabled: true, threshold: 0.1 } 
     75          mem:        { enabled: true } 
     76          mempeak:    { enabled: true } 
     77          memdelta:   { enabled: true } 
    19178 
    192 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. 
     79test: 
     80  propel: 
     81    param: 
     82      classname:  DebugPDO 
    19383 
    194 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. 
    195  
    196 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. 
    197  
    198 '''Detailed instruction for Windows XP:''' 
    199  
    200  - First create a system data source in the ODBC from Control Panel>Administrative Tools>Data Sources. 
    201 Here, you enter your connection parameters and test the connection. Let's say you call that Data Source 'localexpress'. 
    202  
    203  - In your application's configuration class: 
    204  
    205 {{{ 
    206 <?php 
    207  
    208 class frontendConfiguration extends sfApplicationConfiguration 
    209 
    210   public function initialize() 
    211   {      
    212      
    213     $manager = Doctrine_Manager::getInstance();          
    214     $dbh = new PDO("odbc:localexpress"); 
    215     $conn = $manager->connection($dbh, "my_db"); 
    216     $conn ->setOption ('username', 'sa'); 
    217     $conn ->setOption ('password', 'pass'); 
    218     $conn ->setOption ('dsn', "odbc:localexpress"); 
    219   } 
    220 
     84all: 
     85  propel: 
     86    class:       sfPropelDatabase 
     87    param: 
     88      phptype:   sqlsrv 
     89      classname: PropelPDO 
     90      dsn:       sqlsrv:server=12.34.56.78,1433;Database=YOURDB 
     91      username:  xxx 
     92      password:  xxx 
     93      encoding:  null 
     94      pooling:   true 
    22195}}} 
    22296 
    223 With ODBC, I did not have problems with datetimes. 
    224  
    225 I mostly used ODBC to import MSSQL data to MySQL and the only difficulty was to convert some ntext fields to UTF8. 
    226 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. 
    227  
    228  
    229 ---- 
    230  
    231 User comments: 
    232  
    233 __Other notes__[[BR]] 
    234 1. It is also possible to convert a field on the fly using the following syntax: 
     97config/propel.ini 
    23598{{{ 
    236 SELECT jobid, cast ( JobDescription AS TEXT ) as JobDescriptionCasted, jobname 
     99propel.database             = sqlsrv 
     100propel.database.driver      = sqlsrv 
     101propel.database.url         = sqlsrv:server=12.34.56.78,1433;Database=YOURDB 
     102propel.database.user        = xxx 
     103propel.database.password    = xxx 
    237104}}} 
    238105 
    239 2. Additional PHP/ODBC connection settings when migrating/extracting data from MSSQL: 
    240 {{{ 
    241   ini_set("odbc.defaultlrl", 10000000); // Max read in PHP should be 10 MiB 
    242 }}} 
    243  
    244 {{{ 
    245   // Without this reads are limited to 4096, could not get pictures or other binary text larger than 4 KB 
    246  
    247   // Run this before runing any query 
    248   $req = odbc_exec($conn, "SET TEXTSIZE 10000000"); 
    249 }}} 
    250 [[BR]] 
    251 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. 
    252  
    253 {{{ 
    254 BasePeer.php:273 
    255                         $newQualifiedCols = array(); 
    256                         foreach ($qualifiedCols as $qualifiedCol) { 
    257                                 if(substr($qualifiedCol, -2) != 'ID') 
    258                                         $newQualifiedCols[] = $qualifiedCol; 
    259                         } 
    260                         $qualifiedCols = $newQualifiedCols; 
    261 }}} 
    262 [[BR]] 
    263 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. 
    264  
    265 {{{ 
    266 BasePeer.php:273 
    267                         if ($adapter instanceof DBMSSQL && !$criteria->keyContainsValue($pk->getFullyQualifiedName())) { 
    268                                         $newQualifiedCols = array(); 
    269                                         foreach ($qualifiedCols as $qualifiedCol) { 
    270                                                 if(trim($qualifiedCol) != trim($pk->getFullyQualifiedName())) { 
    271                                                         $newQualifiedCols[] = $qualifiedCol; 
    272                                                 } 
    273                                         } 
    274                                         $qualifiedCols = $newQualifiedCols; 
    275                         } 
    276 }}} 
    277 [[BR]] 
    278 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) 
     106Clear the cache and then build the model