jueves, 16 de diciembre de 2010

Running Symfony 1.4 with MS SQL Server 2008

I have used Symfony with Oracle and MySQL and was very happy that I could switch seamlessly from one RBDMS to another. When I had to switch to SQL Server 2008 I thought it would be pretty much the same thing. Well… I was wrong.
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

4 comentarios:

  1. Podria ponerme en coantacto contigo? tengo una situacion parecida pero con propel.

    Gracias

    I'm trying to use PHP5 with Symfony 1.4 but propel plugin 1.5 (sfPropel15Plugin). Perform the installation as explained here http://www.symfony-project.org/plugins/sfPropel15Plugin.

    But when you try to test I get the following error:

    SQLSTATE [HY010] [Microsoft] [ODBC Driver Manager] Function sequence error

    or this one:

    SQLSTATE [24000]: [Microsoft] [SQL Server Native Client 10.0] Invalid cursor state

    ResponderEliminar
  2. Hola Alejo. No tengo ninguna experiencia en Propel. De todas formas si algo que pueda hacer por ti contactame x Twitter mi cuenta es @luiscberrocal.

    ResponderEliminar
  3. When using the attribute ATTR_AUTO_ACCESSOR_OVERRIDE you cannot use the field
    name "table_name" because it is reserved by Doctrine. You must choose another fi
    eld name.

    ResponderEliminar
  4. it doesn't work for me i am in symfony 1.4 wamp 2.0 php 5.2.11 and i enabled the mssql extension pdo_mssl extention also ;(

    ResponderEliminar