Thursday, 21 April 2011

CakePHP: Length of a virtual field's name

Perhaps the fact that I am accessing an MS SQL 2008 server from Linux using the freetds driver could be the cause of my troubles.

What I did find out today, is that when using the $virualFields property of a model the size of the virtual field's name, cannot exceed 14 characters.

My setup here is: CakePHP 1.3.8, running on CentOS 5.6 x86_64 using PHP 5.3.6-3 from Remi's repository.

And here is my case: My Model looks pretty much like this

class ArrivalPackage extends AppModel {
 var $name = 'ArrivalPackage';
 var $belongsTo = array('Arrival', 'Product', 'PackageType');
 var $hasMany = 'PackageLine';

 var $virtualFields = array(
   'reserved_date_gr' => 'CONVERT(NVARCHAR(10), ArrivalPackage.reserved_date, 105)',  //  not good 
   'loaded_date_gr'   => 'CONVERT(NVARCHAR(10), ArrivalPackage.loaded_date, 105)',
   'sold_date_gr'     => 'CONVERT(NVARCHAR(10), ArrivalPackage.sold_date, 105)'
 );
}

After I baked a view and controller for PackageLines I added a pr($packageLine); on the top of my view.ctp view file. The result was :

Array
(
    [ArrivalPackage] => Array
        (
            [id] => 3
            [arrival_id] => 3
            [product_id] => C2ΕLΙLΤ075000000075DULW4
            [supplier_package_number] => 345345345
            [shelman_package_number] => 1099228
            [SSCC] => 
            [min_length] => 2600
            [max_length] => 2600
            [package_type_id] => 1
            [expected_m3] => 0
            [total_pcs] => 20
            [total_m3] => 0.015
            [total_m] => 2
            [loaded] => 0
            [sold] => 1
            [reserved] => 1
            [loaded_date] => 
            [sold_date] => 2011-04-21
            [reserved_date] => 2011-04-18
            [reserved_user] =>  
            [loaded_date_gr] => 
            [sold_date_gr] => 21-04-2011
        )
    
    [0] => Array
        (
            [ArrivalPackage__reserved_date_] => 18-04-2011
        )
    
)

Only after I shortened the name of the reserved_date_gr field to rsvd_date_gr, did I get my expected

Array
(
    [ArrivalPackage] => Array
        (
            [id] => 3
            [arrival_id] => 3
            [product_id] => C2ΕLΙLΤ075000000075DULW4
            [supplier_package_number] => 345345345
            [shelman_package_number] => 1099228
            [SSCC] => 
            [min_length] => 2600
            [max_length] => 2600
            [package_type_id] => 1
            [expected_m3] => 0
            [total_pcs] => 20
            [total_m3] => 0.015
            [total_m] => 2
            [loaded] => 0
            [sold] => 1
            [reserved] => 1
            [loaded_date] => 
            [sold_date] => 2011-04-21
            [reserved_date] => 2011-04-18
            [reserved_user] =>  
            [resvd_date_gr] => 18-04-2011
            [loaded_date_gr] => 
            [sold_date_gr] => 21-04-2011
        )
)

Funny thing is that the actual sql statement produced by Cake, no matter what you name the field is valid for SQL Server, still unless the field name becomes smaller than 14 characters things don't work as you would expect them.

2 comments :

Stefano Manfredini said...

I'll have to check.
But - iirc- i do have longer virtual field names, using a standard lamp or wamp stack - it seems it works with mysql.
Maybe your first guess is correct :)

Athanassios Bakalidis said...

Hi Stefano
I tried to recreate the problem using my laptop that runs the Fedora 15 beta, but in this case, I failed to get a connection to MSSQL completely.

You know what, I 'l just wait until I have a more stable system and then try again.