Re: [Dolibarr-dev] About PgSQL Support and SQL standard

Thanks for the heads up Florian !

If you don't want to switch to PostgreSQL as your development database target, at least set MySQL in strict mode on your development and test platform as indicated on the wiki : http://wiki.dolibarr.org/index.php/Language_and_development_rules#Mysql_specificities

MySQL should then catch these errors and help you debug your SQL.

Cheers,

2013/4/25 Florian Henry <address@hidden>

Dear developpers,

        I'm currently experiement lot's of problem with PostgreSQL only due to non SQL respect standard.

        As reminder with an agregate SQL function you must put in GROUP BY all field that are not in the agreate

        For example :   SELECT t.name,t.id, SUM(t.cost) FROM llx_table GROUP BY t.id
            That's WRONG ! MySQL let it go, but PgSQL no !

        Correct version is SELECT t.name,t.id, SUM(t.cost) FROM llx_table GROUP BY t.name,t.id

        This kind of problem cannot be detected by UnitTest because most of the time the are on list, not in object method, but in list SQL.

        Other common problem is into insert or update method
        If your field is an integer, you must have this to build the request :
             $sql.= " ".(! empty($this->fk_fourn)?"'".$this->fk_fourn."'":"null");
        and NOT
            $sql.= " '".(! empty($this->fk_fourn)?$this->fk_fourn:"null")."'";

        Try to sent update or insert request UPDATE llx_table SET fk_fourn='' WHERE ... in PgSQL it will say '' is not a valid inter, and is right.
        Once again MySQL let it go but insert 0 instead of NULL....

Regards

-- Florian HENRY address@hidden +33 6 03 76 48 07 http://www.open-concept.pro Twitter : @_Open_Concept_
_______________________________________________
Dolibarr-dev mailing list
address@hidden
https://lists.nongnu.org/mailman/listinfo/dolibarr-dev

Raphaël Doursenaud

05 35 53 97 13 - 06 68 48 20 10

address@hidden

From:	Doursenaud , Raphaël
Subject:	Re: [Dolibarr-dev] About PgSQL Support and SQL standard
Date:	Thu, 25 Apr 2013 15:02:23 +0200