0028909: can't create orders in Oracle - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Retail Modules

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0028909

Retail Modules

Web POS

public

2015-02-10 17:17

2016-03-08 20:36

Reporter

alostale

Assigned To

marvintm

Priority

high

Severity

major

Reproducibility

always

Status

closed

Resolution

duplicate

Platform

OS Version

Community Appliance

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

Orekaria

OBNetwork customer

Support ticket

Regression level

Automated tests

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0028909: can't create orders in Oracle

Description

OrderLoader fails in Oracle because of incorrect invocation to M_GET_STOCK_PARAM procedure.

Current code invokes this procedure as a function.

In Oracle CallStoredProcedure can be used to invoke functions ("select function() from dual") and procedures ("call procedure()"), based on its returnResults parameter. Note this only affects Oracle because PostgreSQL doesn't differentiate functions and procedures.

Currently M_GET_STOCK_PARAM, which is a procedure with output parameters is being invoked as a function, that's the reason the exception is thrown.

Steps To Reproduce

. in oracle and Web POS
. in OrderLoader.java the called to M_GET_STOCK_PARAM procedure:
error is returned:

Caused by: java.lang.IllegalStateException: java.sql.SQLSyntaxErrorException: ORA-00904: "M_GET_STOCK_PARAM": invalid identifier
    [junit]
    [junit] at org.openbravo.service.db.CallStoredProcedure.call(CallStoredProcedure.java:142)
    [junit] at org.openbravo.retail.posterminal.OrderLoader.callProcessGetStock(OrderLoader.java:1795)
    [junit] at org.openbravo.retail.posterminal.OrderLoader.createShipmentLines(OrderLoader.java:860)
    [junit] at org.openbravo.retail.posterminal.OrderLoader.saveRecord(OrderLoader.java:243)

Proposed Solution

Possible solutions:
-Invoke it as a procedure. In this case, output parameters will not be retrieved. Looking at the current code they are not used anyway. Note in this case output paramters are required to be passed as parameters in Oracle but not in PG.
-Use a CallableStatement. In this way it is possible to have the same code both for Oracle and PG. To do in this way it is necessary to indicate which are the output parameters.

See attached test cases implementing different approaches both for PG and ORA.

Additional Information