porting Apache Cayenne from oracle to postgreSQL

Question

I'm porting an existing Java project from Oracle 10 to PostgreSQL 9.1.8 (is this version actually supported from Cayenne?)

Something is wrong in OUT parameters when a cursor is returned. Return type should be of

types.OTHER

but it looks like as a null object. No problem with OUT parameters when different from cursors.

This is a piece of my datamap, there is a call to a postgreSQL stored function ShowValidSource:

<procedure name="ShowValidSource" schema="voting_x">
    <procedure-parameter name="i_n_sourceid" type="BIGINT" direction="in" />    
    <procedure-parameter name="i_s_description" type="VARCHAR" direction="in" />
    <procedure-parameter name="i_n_active" type="BIGINT" direction="in" />
    <procedure-parameter name="cur_ref" type="OTHER" direction="out" />
    <procedure-parameter name="o_n_exitflag" type="BIGINT" direction="out" />
    <procedure-parameter name="o_s_exitmsg" type="VARCHAR" direction="out" />
</procedure>
<db-entity name="cur_ref">
    <db-attribute name="SOURCE_ID" type="DOUBLE" isPrimaryKey="true"  />
    <db-attribute name="DESCRIPTION" type="VARCHAR" />
    <db-attribute name="ACTIVE" type="DOUBLE" />
</db-entity>
<obj-entity name="SourceByDescription" readOnly="true" className="it.mxx.vote.dao.types.SourceByDescriptionImpl" dbEntityName="cur_ref">
    <obj-attribute name="sourceId" type="java.lang.Double" db-attribute-path="SOURCE_ID" />
    <obj-attribute name="description" type="java.lang.String" db-attribute-path="DESCRIPTION" />
    <obj-attribute name="active" type="java.lang.Double" db-attribute-path="ACTIVE" />
</obj-entity> 

Here is my procedure declare:

 CREATE OR REPLACE FUNCTION showvalidsource(IN i_n_sourceid bigint, IN i_s_description character varying, IN i_n_active bigint, OUT o_rc_source refcursor, OUT o_n_exitflag bigint, OUT o_s_exitmsg character varying)
  RETURNS record AS
$BODY$
DECLARE
  --
  V_S_Description varchar(255);
  V_N_Active      bigint;
  V_N_ExitFlag    bigint:= NULL;
  v_rc_source     refcursor := 'cur_ref';
  -- 
BEGIN
  --
  IF I_N_Active = -1 THEN
    V_N_Active := null;
  ELSE
    V_N_Active :=I_N_Active;
  END IF;
  --
  V_S_Description := trim(lower(I_S_Description));
  --
  BEGIN
    --
    IF coalesce(V_N_Active,0) IN (0,1) THEN
      NULL;
    ELSE
      --
      V_N_ExitFlag := -10;
      O_S_exitMsg  := 'WARNING! I_N_Active accepted values : 0, 1, -1, null';
      RAISE EXCEPTION USING MESSAGE =  O_S_exitMsg;    
      --
    END IF;
  END;
  --
  --    IF  V_N_SourceId IS NOT NULL THEN
  IF coalesce(I_N_SourceId, -1) <> -1 THEN
    --
          OPEN v_rc_source FOR
           SELECT  vsd.source_id,
                   vsd.description,
                   vsd.active
              FROM voting_system_detail vsd
            WHERE vsd.source_id   = I_N_SourceId
              AND vsd.Description = coalesce(V_S_Description, vsd.Description)
              AND vsd.active      = coalesce(V_N_Active, vsd.active)
            ORDER BY vsd.source_id;
    --
    o_rc_source    := v_rc_source;
    O_N_exitFlag   := 0;
    O_S_exitMsg    := 'Ok';
    --
  ELSE
    --
          OPEN v_rc_source FOR
            SELECT vsd.source_id,
                   vsd.description,
                   vsd.active
              FROM voting_system_detail vsd
            WHERE vsd.Description = coalesce(V_S_Description, vsd.Description)
              AND vsd.active      = coalesce(V_N_Active, vsd.active)
            ORDER BY vsd.source_id;
    --
    o_rc_source    := v_rc_source;    
    O_N_exitFlag   := 0;
    O_S_exitMsg    := 'Ok';
  --
  END IF;   --IF  V_N_SourceId IS NOT NULL THEN
  --
  EXCEPTION
   WHEN OTHERS THEN
     O_N_exitFlag := coalesce(V_N_ExitFlag,-1);
     O_S_exitMsg  := SQLERRM;
     --
     OPEN v_rc_source FOR
            SELECT -1  source_id,
                   'x' description,
                   -1  active
             WHERE 1=2;
     o_rc_source    := v_rc_source;

  END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION showvalidsource(bigint, character varying, bigint)
  OWNER TO dbvoting;

NOTE: this cursor by pg client returns more records in 3 column type (SOURCE_ID double, DESCRIPTION varchar, ACTIVE double)

What could be wrong? Thanks

Answer 1

Ok, I simplified a function a bit to run it with Cayenne unit tests. The result of a select on 9.1 was the following DataRow:

{o_rc_source=org.postgresql.jdbc4.Jdbc4ResultSet@4bd27069, 
 o_s_exitmsg=Ok, 
 o_n_exitflag=11} 

which seems like exactly what you'd expect, refcursor is there, accessible as a ResultSet. This was a trunk build of Cayenne (should be the same as 3.1 in respect to PostgreSQL interaction) and postgresql-9.1-901-1.jdbc4.jar driver. So how are you checking that it is NULL in your case?

Function:

CREATE OR REPLACE FUNCTION cayenne_tst_out_proc(IN i_n_sourceid int4, 
  OUT o_rc_source refcursor,
  OUT o_n_exitflag int4, 
  OUT o_s_exitmsg character varying
)
  RETURNS RECORD AS
$BODY$
DECLARE
   v_rc_source  refcursor := 'cur_ref';
BEGIN

   OPEN v_rc_source FOR
     SELECT * FROM artist;

    o_rc_source    := v_rc_source;    
    O_N_exitFlag   := 11;
    O_S_exitMsg    := 'Ok';
  END;

$BODY$
  LANGUAGE plpgsql VOLATILE COST 100;

Cayenne mapping:

<procedure name="cayenne_tst_out_proc">
    <procedure-parameter name="i_n_sourceid" type="INTEGER" direction="in"/>
    <procedure-parameter name="o_rc_source" type="OTHER" direction="out"/>
    <procedure-parameter name="o_n_exitflag" type="INTEGER" direction="out"/>
    <procedure-parameter name="o_s_exitmsg" type="VARCHAR" direction="out"/>
</procedure>