Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0036624Openbravo ERPA. Platformpublic2017-08-08 11:282017-08-09 14:37
ReporterJONHM 
Assigned Toplatform 
PriorityhighSeveritymajorReproducibilityalways
StatusclosedResolutionopen 
PlatformOS5OS Version
Product Versionpi 
Target VersionFixed in Version 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customerOBPS
Web browser
ModulesCore
Support ticket47762
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0036624: Error thrown in web services if the URL includes a where parameter that references a computed column
DescriptionThe 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}}
Steps To ReproduceUsing Postman:

- GET:
https://livebuilds.openbravo.com/erp_pi_pgsql/org.openbravo.service.json.jsonrest/FinancialMgmtPeriod?_where=status='P' [^]
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
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 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2017-08-08 11:28JONHMNew Issue
2017-08-08 11:28JONHMAssigned To => platform
2017-08-08 11:28JONHMOBNetwork customer => Yes
2017-08-08 11:28JONHMModules => Core
2017-08-08 11:28JONHMSupport ticket => 47762
2017-08-08 11:28JONHMResolution time => 1503957600
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 [^]