I found very little on Symfony and SQL Server on the world library (Google). I got bits and pieces that gave me an idea of what was wrong.
Issues
1. It seems Doctrine 1.2.x doesn´t support the new Microsoft Driver and PHP 5.3 doesn´t support the mssql driver any more. You have two choices connecting via ODBC to SQL Server and keep PHP 5.3 or switch to PHP 5.2 and use mssql driver.2. I haven´t been able to run the doctrine:build task with existing tables.
3. Cannot use comment on the schema.yml
4. Problems using UTF-8 encoding on data. The data loaded from the fixtures.yml is encoded incorrectly when loaded to the database. This is because the fixtures.yml is encoded in UTF-8 and the database is expecting cp1252. The work around is switch the fixtures.yml encoding to cp1252.
The sfexample Project
I created a Symfony project to test the behavior of MS SQL Server 2008 with the ODBC driver and the mssql driver.I´m using MS SQL Server 2008 Express R2 and WAMP 2.0i
I created a simple clients table. My schema.yml:
client:
tableName: clients
columns:
id:
type: integer(4)
primary: true
autoincrement: true
firstname:
type: string(30)
lastname:
type: string(40)
country:
type: string(40)
My fixtures are:
client:
vj:
firstname: Víctor
lastname: Asunción
country: Panamá
bc:
firstname: Benjamón
lastname: Coclé
country: Panamá
Symnfony 1.4, PHP 5.3.0 and ODBC
To use the PDO ODBC driver you need to:1. Uncomment the extension on your php.ini. If you´re using WAMP be sure to edit the php.ini that Apache uses and the one that is in your php home directory.
extension=php_pdo_odbc.dll
2. If you´re using Netbeans make sure you´re using PHP 5.3.0. Go to Tool\Options, click on the PHP icon and verify in that PHP 5 Interpreter is pointing to the PHP 5.3 executable (in my case C:\wamp\bin\php\php5.3.0\php.exe)
3. Edit the Path environment variable to point to the PHP 5.3.0 home directory (in my case C:\wamp\bin\php\php5.3.0).
Change the databases.yml to look like this:
all:
doctrine:
class: sfDoctrineDatabase
param:
dsn: odbc:DRIVER={SQL Server};Server=MYMACHINE\SQLEXPRESS;Database=sfexample;dbname=sfexample;
username: sa
password: mypassword
Run
symfony doctrine:build --all --no-confirmation --and-load
The first time I ran it works. It creates the tables and loads the data. If I run it a second time I get this error:
SQLSTATE[25000]: Invalid transaction state: 3902 [Microsoft][ODBC SQL Server Driver][SQL Server]The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION. (SQLExecDirect[3902] at ext\pdo_odbc\odbc_driver.c:247)
The work around to it is manually delete the table. This is an inconvenience to say the least. If you have many tables with relations it can become a hassle.
I you take a look at my fixtures you´ll notice I use accented vowels. Since SQL Server doesn´t understand UTF-8 when I run a select from the clients table I get this
The collation for my database is Modern_Spanish_CI_AS. The work around I found was saving the fixtures.yml in cp1252 encoding. The problem with this, is that Netbeans assigns a single encoding for the whole project. So I decided to keep all my project in UTF-8 and edit the fixtures.yml externally (outside Netbeans).
I created a doctrine module for clients.
symfony doctrine:generate-module frontend clients Client
When I try t save a record I get a warning like this (with the whole call stack):
Deprecated: Function spliti() is deprecated in C:\symfony-1.4.8\lib\plugins\sfDoctrinePlugin\lib\vendor\doctrine\Doctrine\Connection\Mssql.php on line 192
It seems the spliti() function has been deprecated in PHP 5.3 and PHP decided to “share” it with us. To solve this go to the settings.yml and change the error reporting to:
.settings:
error_reporting: <?php echo E_ALL & ~E_DEPRECATED ."\n" ?>
To avoid the encoding problem from the client module change the character set to cp1252 on the setting.yml
all:
.settings:
charset: cp1252
You have to clear the cache to make the charset take.
Symnfony 1.4, PHP 5.2.11 and mssql
To use the PDO mssql driver you need to:
1. Uncomment the mssql and pdp mssql extensions on your php.ini. If you´re using WAMP be sure to edit the php.ini that Apache uses and the one that is in your php home directory.
extension=php_mssql.dll
extension=php_pdo_mssql.dll
2. If you´re using Netbeans make sure you´re using PHP 5.2.11. Go to Tool\Options, click on the PHP icon and verify in that PHP 5 Interpreter is pointing to the PHP 5.3 executable (in my case C:\wamp\bin\php\php5.2.11\php.exe)
3. Edit the Path environment variable to point to the PHP 5.2.11 home directory (in my case C:\wamp\bin\php\php5.2.11).
Change the databases.yml to look like this:
all:
doctrine:
class: sfDoctrineDatabase
param:
dsn: mssql:host=MYMACHINE\SQLEXPRESS;dbname=sfexample
username: sa
password: mypassword
Run
symfony doctrine:build --all --no-confirmation --and-load
The mssql driver doesn´t seem to have the problem with rerunning the task that the odbc driver has.
The client module was already created.
There was no problem with the spliti function since it´s still supported in PHP 5.2.x
To avoid the encoding problem from the client module change the character set to cp1252 on the setting.yml
all:
.settings:
charset: cp1252
You have to clear the cache to make the charset take.
References
http://forums.asp.net/t/1200021.aspx
