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;",

            $errorMessage = mssql_get_last_message();
            if ($sqlResult == false) {
                // something went wrong 
                $result = false;              
            } else {                    
                // stay on the safe side            
                if (is_resource($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.

No comments :