Extrafield sql error after upgrade V20

erikvanberkum · December 22, 2024, 10:40pm

We upgraded from 19.0.3. to version 20.0.2 we have a extrafield in products: Select from table which had the following settings.

product:ref:rowid::ref LIKE ‘%pack_box_carton%’

This field was working in version 19 but now get an error in version 20:

Error in request SELECT rowid as rowid, ref FROM llx_product WHERE Filter error - Bad syntax of the search string ORDER BY ref You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘error - Bad syntax of the search string ORDER BY ref’ at line 1. Check setup of extra parameters.

Something has changed?

sonikf · December 22, 2024, 11:41pm

Hi @erikvanberkum

You should always check the ChangeLog and test before upgrading.

github.com

Dolibarr/dolibarr/blob/3cefab32d51e66cea31f97e60341a0bb58906573/ChangeLog#L597


      
          QUAL: the config_extended of phpstan will be kept on apstats only for the
          QUAL: The signature for all ->delete() method has been modified to match
          QUAL: Use phan to help detect new issues (#27706)
          QUAL: use real time when creating action from list
          QUAL: replace hard-coded dictionary IDs with constants (#29501)
          
          WARNING:
          --------
          
          The following changes may create regressions for some external modules, but were necessary to make Dolibarr better:
          * If a filter is defined into and extrafields "Select from a table", this filter MUST use the Universai Search Filter syntax (field:=:value) instead of field=value.
            See https://wiki.dolibarr.org/index.php?title=Universal_Search_Filter_Syntax
          * The parameter $filter of methods fetchAll() does not accept array of SQL commands but must be a string of an Universal Search Filter syntax.
            See https://wiki.dolibarr.org/index.php?title=Universal_Search_Filter_Syntax
          * The use of 'customurl' into $filter has been removed. Must use the USF syntax.
          * Some API HTTP return code were moved from 401 to 403 to better follow REST specification.
          * More class properties (with old name in french) are now deprecated in favor of the property name in english.
          * dolibarrtriggers class VERSION_XXX constants have been deprecated. Please use array dictionary VERSIONS['XXX'].
          * Properties ->date_update and ->date_modification were merged into date_modification.
          * All CLI tools (into /scripts) return a positive value to the shell if error (0 remains success) for a better
            cross platform compatibility. On linux the exit(-1) was caught as 255, it may be now exit(1) so will be caught as 1.

so from now on the correct syntax will be

product:ref:rowid::(ref:LIKE:'%pack_box_carton%')

erikvanberkum · December 23, 2024, 12:02am

Thanks missed that, it’s working now. I thought already something must have changed so didnt post to github.

963 · December 25, 2024, 8:32pm

Hi

My similar problem (version 20.0.2):
Old:
resource:ref:rowid::entity

New:
resource:ref:rowid::(entity:=:‘1’)
OK working
resource:ref:rowid::(entity:=:‘%entity%’)
Not working.

Thanks your answer

963 · December 25, 2024, 8:39pm

Solution:
resource:ref:rowid

Sorry