[OFBIZ-2192] GenericDataSourceException in ViewFacilityInventoryByProduct.bsh on pgSQL 8.3

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: Release Branch 4.0
• Fix Version/s: Release Branch 4.0
• Component/s: product

Environment

SQL: PostgreSQL 8.3.5
JDBC Driver: postgresql-8.3-603.jdbc4.jar

• Assignee: Jacques Le Roux
• Reporter: Jan Valkovic
• Created: 16/Feb/09 16:34
• Updated: 16/Feb/09 16:49
• Resolved: 16/Feb/09 16:49

Description

Script: [OFBIZ_HOME]/product/webapp/facility/WEB-INF/actions/facility/ViewFacilityInventoryByProduct.bsh throws an exception:

Target exception: org.ofbiz.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT DISTINCT PROD.INTERNAL_NAME, PROD.IS_VIRTUAL, PROD.SALES_DISCONTINUATION_DATE, PROD.PRODUCT_TYPE_ID, PRFA.PRODUCT_ID, PRFA.FACILITY_ID, PRFA.MINIMUM_STOCK, PRFA.REORDER_QUANTITY, PRFA.DAYS_TO_SHIP FROM public.PRODUCT PROD INNER JOIN public.PRODUCT_FACILITY PRFA ON PROD.PRODUCT_ID = PRFA.PRODUCT_ID WHERE ((PRFA.FACILITY_ID = ? AND PROD.PRODUCT_TYPE_ID = ?) AND (PROD.IS_VIRTUAL IS NULL OR PROD.IS_VIRTUAL <> ?) AND (PROD.SALES_DISCONTINUATION_DATE IS NULL OR PROD.SALES_DISCONTINUATION_DATE > ?)) ORDER BY PRFA.PRODUCT_ID ASC (ERROR: operator does not exist: timestamp with time zone > character varying)

I fixed this error by add explicit conversion to Timestamp in line 112:
new EntityExpr("salesDiscontinuationDate", EntityOperator.GREATER_THAN, Timestamp.valueOf(productsSoldThruTimestamp)))

I tested this only on Derby and pgSQL.