Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0036624Openbravo ERPA. Platformpublic2017-08-08 11:282017-08-09 14:37
JONHM 
platform 
highmajoralways
closedopen 
5
pi 
 
caristu
Core
No
0036624: Error thrown in web services if the URL includes a where parameter that references a computed column
The following error is thrown when trying to get data from an entity filtering from a computed column:

{"response":{"data":[{"response":{"status":-1,"error":{"message":"could not execute query using scroll","messageType":"Error","title":""},"totalRows":0}}
Using Postman:

- GET:
https://livebuilds.openbravo.com/erp_pi_pgsql/org.openbravo.service.json.jsonrest/FinancialMgmtPeriod?_where=status='P' [^]
No tags attached.
related to defect 0025773 closed alostale Error thrown in web services if the request includes a field that references a computed column 
related to design defect 00241043.0MP27 closed alostale Unnecessary evaluation of computed columns 
Issue History
2017-08-08 11:28JONHMNew Issue
2017-08-08 11:28JONHMAssigned To => platform
2017-08-08 11:28JONHMModules => Core
2017-08-08 11:28JONHMTriggers an Emergency Pack => No
2017-08-08 11:28JONHMRelationship addedrelated to 0025773
2017-08-09 14:04caristuRelationship addedrelated to 0024104
2017-08-09 14:06caristuSummaryError thrown in web services if the request includes a field that references a computed column => Error thrown in web services if the URL includes a where clause that references a computed column
2017-08-09 14:12caristuReview Assigned To => caristu
2017-08-09 14:12caristuNote Added: 0098452
2017-08-09 14:12caristuStatusnew => closed
2017-08-09 14:13caristuSummaryError thrown in web services if the URL includes a where clause that references a computed column => Error thrown in JSON web services if the URL includes a where clause that references a computed column
2017-08-09 14:13caristuSummaryError thrown in JSON web services if the URL includes a where clause that references a computed column => Error thrown in JSON web services if the URL includes a where parameter that references a computed column
2017-08-09 14:14caristuNote Edited: 0098452bug_revision_view_page.php?bugnote_id=0098452#r15633
2017-08-09 14:23caristuNote Added: 0098456
2017-08-09 14:37caristuSummaryError thrown in JSON web services if the URL includes a where parameter that references a computed column => Error thrown in web services if the URL includes a where parameter that references a computed column
2017-08-09 14:38caristuNote Edited: 0098452bug_revision_view_page.php?bugnote_id=0098452#r15634
2017-08-09 14:38caristuNote Edited: 0098452bug_revision_view_page.php?bugnote_id=0098452#r15635
2017-08-09 14:44caristuNote Edited: 0098456bug_revision_view_page.php?bugnote_id=0098456#r15637

Notes
(0098452)
caristu   
2017-08-09 14:12   
(edited on: 2017-08-09 14:38)
Since MP27, when working with HQL (i.e. OBQuery) computed columns cannot be directly accessed as they are not properties of the entity[1].

Web services are internally making use of an OBQuery to return the results. Besides as stated here[2], the _where parameter should contain a valid HQL where-clause.

Therefore to filter the result of a Web Service based on a compute column, using the _where parameter, then the "_computedColumns" property must be used.

So, in this case the correct URL for the GET request will be:

https://livebuilds.openbravo.com/erp_pi_pgsql/org.openbravo.service.json.jsonrest/FinancialMgmtPeriod?_where=_computedColumns.status='P' [^]

[1] http://wiki.openbravo.com/wiki/How_to_create_a_Computed_Column#Limitations [^]
[2] http://wiki.openbravo.com/wiki/JSON_REST_Web_Services#GET [^]

(0098456)
caristu   
2017-08-09 14:23   
(edited on: 2017-08-09 14:44)
Updated wiki pages:

 http://wiki.openbravo.com/wiki/JSON_REST_Web_Services#GET [^]
 http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/Concepts/XML_REST_Web_Services#Query_Request_and_Response [^]