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

Installing Microsoft SQL Server 2008 Drivers for PHP

Installing the Microsoft Drivers for SQL Server 2008 is a not as simple as one would think. One of the things that had me confused was the fact that I had previously made a conection to SQL Server 2005 with no problem. Now, using SQL Server 2008 (Express) nothing worked.

After some research I realized the problem was tha in my previous connections I was using PHP 5.2.x which used the mssql driver. For some reason the mssql server is no longer supported for PHP 5.3 and we are supposed to use the Microsoft Driver.

Installation

Download the drivers from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=80e44913-24b4-4113-8807-caae6cf2ca05&displaylang=en .

When you install you´ll need to choose which dlls to use. Use the following table to decide:

PHP Version	Web Server	Dlls
5.2	IIS	php_pdo_sqlsrv_52_nts_vc6.dll php_sqlsrv_52_nts_vc6.dll
5.2	Apache	php_pdo_sqlsrv_52_ts_vc6.dll php_sqlsrv_52_ts_vc6.dll
5.3	IIS with FastCGI	php_pdo_sqlsrv_53_nts_vc9.dll php_sqlsrv_53_nts_vc9.dll
5.3	Apache as mod_php	php_pdo_sqlsrv_53_ts_vc6.dll php_sqlsrv_53_ts_vc6.dll
5.3	Apache as FastCGI	php_pdo_sqlsrv_53_nts_vc6.dll php_sqlsrv_53_nts_vc6.dll

Double click on SQLSRV20.EXE

Click yes.

Select your output directory and click Ok.

Click Ok.

Copy the file c:\temp\php_pdo_sqlsrv_53_ts_vc6.dll and c:\temp\php_sqlsrv_53_ts_vc6.dll to your PHP_HOME\ext folder (since I´m using WAMP my path is C:\wamp\bin\php\php5.3.0\ext)

Edit your php.ini (mine is at C:\wamp\bin\php\php5.3.0\) adding the following lines

extension=php_sqlsrv_53_ts_vc6.dll

extension=php_pdo_sqlsrv_53_ts_vc6.dll

If you´re using WAMP you have to edit the php.ini that Apache uses. Double click on php.ini add the extensions and restart Apache.

Testing Installation

Open command prompt a run

php –v

If you get an error like this one:

You´re using the wrong dlls for your php installation. Verify you´re using php_pdo_sqlsrv_53_ts_vc6.dll and php_sqlsrv_53_ts_vc6.dll

Create a directory c:\temp\sqlservertest.

In Apache create an alias for this directory with the name sqlservertest.

Create a file in c:\temp\sqlservertest with the name phpinfo.php and the following content:

<?php phpinfo(); ?>

We had already created a Table on SQL Server called users to test the driver.

Create and index.php file write the following code.

<?php
        /*
         * Specify the server and connection string attributes.
         */
        $serverName = "MYMACHINE\SQLEXPRESS";
        $uid = "username";
        $pwd = "password";
        $database = "sqlservertest";
        try {
        $conn = new PDO( "sqlsrv:server=$serverName;database=$database", $uid, $pwd);
        $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
        $conn->setAttribute(PDO::SQLSRV_ATTR_DIRECT_QUERY , true);

        echo "Connected to SQL Server<br /><br />\n";

        $sql = "select * from myschema.users";
        $results = $conn->query( $sql );
        outputRows($results);
       
// Free statement and connection resources.
        $stmt = null;
        $conn = null;
        } catch( PDOException $e ) {
        echo "<h1>Error connecting to SQL Server</h1><pre>";
        echo $e->getMEssage();
        echo "</pre>";
        exit();

        }
        function outputRows($results)
        {
        echo "<table border='1'>\n";
        while ( $row = $results->fetch( PDO::FETCH_ASSOC ) ){
        echo "<tr><td>{$row['id']}</td><td>{$row['firstname']}</td><td>{$row['lastname']}</td>\n";
        }
        echo "</table>\n";
        return;
        }

References

http://www.php.net/manual/en/ref.pdo-dblib.php

http://www.ditii.com/2010/11/16/sql-pdo-and-microsoft-sql-server-whitepaper-php-drivers-for-sql-server/

Writing to a Properties File with Apache ANT

I needed to create properties file every time I made a migration in order to know who, when and from where the migration was made.
The easiest way to do this is using Apache ANT.

To gain access to the environment variables you must declare the environment property. This can be done under the project tag.

<property environment="env" />

Inside your target use the following code:

When you run the make-properties target if will generate a file named build.properties with something like this:

<target name="make-properties " >
<propertyfile file="build.properties" comment="A comment to appear  in the properties file."> 
<entry key="program.BUILDDATE" type="date" value="now" pattern="yyyyMMdd-HHmmss" />
<entry key="program.BUILDHOST" value="${env.COMPUTERNAME}" />
<entry key="program.BUILDUSER" value="${user.name}" />
</propertyfile>
</target>

When you run the make-properties target if will generate a file named build.properties with something like this:

# A comment to appear  in the properties file.
#Wed, 01 Dec 2010 11:45:24 -0500
program.BUILDDATE=20101201-114524
program.BUILDHOST=GISHOST
program.BUILDUSER=LBerrocal

Using jQuery UI DatePicker in Symfony

The easiest way to use to use the jQuery UI datepicker is to install the sfFormExtraPlugin.

symfony install:plugin sfFormExtraPlugin

Getting the Libraries

You have to download the jquery library and the jquery-ui library, actually I found out that the jQuery UI zip includes the jQuery library. Since I'm developing in spanish I require the i18n javascript for jQuery UI. I couln't find it on the jQuery UI site (but I didn't look very hard). I found the script at http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.6/i18n/jquery-ui-i18n.min.js. My project doesn't have access to the Internet so I copied and pasted the code into /web/js/jquery-ui-i18n.min.js.

You need to copy your jquery-1.4.2.min.js, jquery-ui-1.8.6.custom.js and jquery-ui-i18n.js to the /web/js directory (the versions for the libraries will change depending on when you read this post). On your jQuery UI zip there is a css folder inside it you'll find folder with the theme you selected in my case its a folder named smoothness copy this folder to /web/css folder.

Declaring the Libraries and CSS in Symfony

In the /apps/myapplication/config/view.yml make change to declare the javascripts and css files.

  stylesheets:    [main.css, smoothness/jquery-ui-1.8.custom.css]

javascripts:    [jquery-1.4.2.min.js, jquery-ui-1.8.custom.min.js, jquery-ui-i18n.min.js]

Editing the the Form Class

Edit your form class /form/doctrine/myclassForm.class.php and change the date widget to sfWidgetFormJQueryDate it should look something like this:

public function configure() {
    $current_user = sfContext::getInstance()->getUser();
    $culture = substr($current_user->getCulture(), 0,2);
    $this->widgetSchema['requestDate'] = new sfWidgetFormJQueryDate(array(
              'image'=>'/images/calendar.gif',
              'config' => '{}',
              'culture' => $culture
            ));
}

The image is to have custom button, config is javascripts configuration data (I don't know what this does), culture is to have the date picker in your language. I originally tried to to pass the users culture to the culture option. My culture is es_PA (spanish Panama) and when I passed it to the culture option it showed the days and months in what I believe is Mandarin. I realized that what the widget recognizes as culture is actually just the language so I used the substr function to extract the first two letters from the culture.

Now publish the assets running the following command:

symfony plugin:publish-assets

Now you're ready to display a a datepicker besides you're date variable in the form.

You still get the comboboxes for days, months and years and beside it you get a button that will display a calendar and let you select date. I'm working on getting a read only input textbox besides the calendar button instead of the comboboxes.

Enjoy!!!