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.

Firefox4 on CentOS 5

Yes it runs! Much faster than 3.6. Check out this CentOS wiki post for all the ... scary details.

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....