Tuesday 17 May 2011

CakePHP and FreeTDS on CentOS 5

We are almost finished with a project using CakePHP 1.3.8 running on a CentOS 5 box that accesses a Microsoft SQL Server 2008 database, using PHP 5.3.6-4 and the freetds driver, all available from Remi's repository.

This is a report of all the little problems we faced and the solutions we came up with :

  1. The first thing that hit us, was he encoding of the saved data. Although we used NVARCHAR columns for all character fields, we ended up seeing gibberish inside the SQL Server Management Studio, whenever we had to enter anything that was not Latin. Both the server and the application are using UTF-8 locales and data entered via Cake would be returned correctly, however there was no way to get the MSSQL Management Studio to display UTF-8 data.
    After some dispute about whether or not to leave things as they are we decided to utilize a behavior that converted data to and from different character sets before storing and retrieving them from the database. The relevant post from back 2009 is accessible via the following link.
    This approach kept almost everyone happy.
  2. Second issue was the length of the Model's visual fields. I have blogged about this a few days ago and it turns out that the maximum number of characters allowed to a virtual field name is 14.
  3. And finally the stored procedure issue. Up until now, we have been unable to properly call a T-SQL stored procedure using mssql_init() and mssql-bind(). The only workarround for doing the job is to use the mssql_query() function and execute an "EXEC ... " query.
    The code we use -- as a Model method -- is more or less like this :
        /**
         * Execute the T-SQL stored procedure named "dbo.storedProcName"
         * and return execution status.
         *
         * NOTE: if the stored proc uses the T-SQL RAISERROR statement
         *       to trigger some sort of error condition, then
         *       mssql_query still returns true but the 
         *       mssql_get_last_message() will contain the actual error
         *       text that will be returned to the caller.
         */
        function callStoredProc($procParam, &$errorMessage = '')
        {
            // get the low level database connection from the model data
            $conn = $this->getDataSource()->connection;

            // execute a query that calls the stored proc
            $sqlResult = mssql_query(
                          " EXEC dbo.storedProcName
                            @procParam = $procParam;",
                          $conn
                        );

            $errorMessage = mssql_get_last_message();
                
            if ($sqlResult == false) {
                // something went wrong 
                $result = false;              
            } else {                    
                // stay on the safe side            
                if (is_resource($sqlResult))
                    mssql_free_result($sqlResult);
                // if the stored procedure error message is empty then all
                // is well
                $result = $errorMessage == '' ? true : false;
            }

            // return success
            return $result;
        }

As always, I try to update the list if anything new comes up.

Tuesday 3 May 2011

CakePHP: Dynamicaly disabling links

Sometimes we get buried so deeply into ... nuclear science that we forget that after all CakePHP is just PHP, the resulting pages are just HTML and you don't need JQuery to disable a link. Setting it's onlick event to return false, usually does it.

The problem was simple: Given a list of records, (arrivals) selectively enable an action link for each record, (create a purchase order in our case). based on a record attribute (that is whether the purchase order has already been created).

Now, that is now so hard. Well, if you already have something like :

<?php echo $this->Html->link(
                    $this->Html->image('actions/upload.png'),
                    array(
                          'action' => 'createPurchasOorder',
                          $arrival['Arrival']['id']
                    ),
                    array(
                      'escape' => false,
                      'title' => 'Create purchase order for the arrival.'
                    )
                ); ?> 

inside a PHP foreach loop, then it's not that difficult to transform it into :

<?php 
         $canCreatePO = empty($arrival['Arrival']['purchase_order_code']);
         echo $this->Html->link(
                    $this->Html->image('actions/upload.png'),
                    array(
                          'action' => 'createPurchasOorder',
                          $arrival['Arrival']['id']
                    ),
                    array(
                      'escape' => false,
                      'onclick' => $canCreatePO 
                              ? 'return true;'
                              : 'return false',
                      'title' => $canCreatePO 
                              ? 'Create purchase order for the arrival.'
                              : 'Purchase order has already been created.'
                    )
                ); ?> 

So, why the hell did it take me an entire morning to figure this out....