Database Independence: Need for SQLJ Alias Definition for PostgreSQL

Type conversion problems with SQLJ functions

Resolved

PostgreSQL

erbox_253b-*

sqlj/postgresql/sqlj.ddr
sqlj/postgresql/alias.sql

Some of the SQLJ functions have signatures with (Java) int parameters. The SQLJ implementation of PL/Java mandates that the signature declaration of the externalised function in sqlj.ddr is of type (SQL) INTEGER in these cases.

A problem arises when a SQLJ function with an (SQL) INTEGER parameters is called from Java via JDBC with a BigDecimal value; this happens in many places in Compiere. It does not present a problem with Oracle because of Oracle's NUMBER datatype which can hold integers and floating point numbers alike.

For each SQLJ function which contains an INTEGER parameter in sqlj.ddr a corresponding "typecast alias" has to be created, which externalises the same function, but with a NUMERIC parameter instead of the INTEGER parameter.

Example

SQLJ function from sqlj.ddr:

CREATE OR REPLACE FUNCTION productAttribute(M_AttributeSetInstance_ID INTEGER)
RETURNS VARCHAR
AS 'org.compiere.sqlj.Product.attributeName(int)'
LANGUAGE java;

"Typecast alias" from alias.sql

CREATE OR REPLACE FUNCTION productAttribute(M_AttributeSetInstance_ID NUMERIC)
RETURNS VARCHAR AS $$
BEGIN
RETURN productAttribute(CAST(M_AttributeSetInstance_ID AS INTEGER));
END;
$$ LANGUAGE plpgsql;