rs.getBigDecimal returning Null on field that is not null

[David Gagnon <dgagnon(at)siunik(dot)com>]

Hi all,

 I ran into this problem that look like a bug if someone can confirm 
and hopefully provide a workaround.

I have a storedProcedure that use a refcursor + temp table to create a 
report.  The problem is that the NUMERIC field 13 I get in my 
application is always NULL.  When I run the stored procedure from 
PGADMINIII I can see that field 13 has value 5.5400000000000000.

I don't know it that may help look into the debugger  and the array 
this_row[12] is NULL.  So it seem that he has problem fetching the value 
from postgresql...

I'm running 8.2.4 with jdbc postgresql-8.2-506.jdbc3.jar

Thanks for your help and for any clue!
Best regards
David

P.S.:I include several debug info:

OUTPUT PGADMINIII
-------------------------
icnum;icdesca;icdescf;iccoutda;icfprix;icprixu;bxmontu;cus;unite_sec;unite_pri;iqqstock;iqqcomm;cms;icstatut;iciknum;ikdesc_pri;ikdesc_sec;icimnum;imdesc_pri;imdesc_sec
"ZEB71150";"SURLIGNEUR ZAZZLE BRIGHTS (5)";"SURLIGNEUR ZAZZLE BRIGHTS 
(5)";"5.80";"1.540000";"8.93";"5.801600";"5.8016000000000000000000";"Unité";"Unit";"3";"0";"5.5400000000000000";"0";"8";"Écriture 
et Dessin";"Écriture et Dessin";"ZEB";"Zebra";"Zebra"

LOG TO CREATE THE DATA FROM PGADMINIII
------------------------------------------------------


-- Executing query:
CREATE  TABLE TMP_IC (

           ICNUM VARCHAR(20) primary key

       )
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"tmp_ic_pkey" for table "tmp_ic"

Query returned successfully with no result in 94 ms.

-- Executing query:
CREATE TABLE TMP_IQ (

           IQICNUM VARCHAR(20) primary key,

           IQQSTOCK INT,

           IQQCOMM INT

       )
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"tmp_iq_pkey" for table "tmp_iq"

Query returned successfully with no result in 78 ms.

-- Executing query:
CREATE  TABLE TMP_CMS (

           ICNUM VARCHAR(20) primary key,

           CMS NUMERIC

       )
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"tmp_cms_pkey" for table "tmp_cms"

Query returned successfully with no result in 125 ms.

-- Executing query:
INSERT INTO TMP_IC

         SELECT DISTINCT ICNUM

         FROM IC LEFT OUTER JOIN IK ON IC.ICIKNUM = IK.IKNUM

         WHERE (1 = 1)  AND ICNUM ~* '***=ZEB71150' AND (( 1 = 0) OR 
(ICSTATUT = 0)  ) 

Query returned successfully: 1 rows affected, 63 ms execution time.

-- Executing query:
INSERT INTO TMP_IQ

               SELECT IQICNUM, SUM(IQQSTOCK), SUM(IQQCOMM)

               FROM IQ INNER JOIN TMP_IC AS TIC ON IQ.IQICNUM = TIC.ICNUM

               GROUP BY IQICNUM

Query returned successfully: 1 rows affected, 31 ms execution time.

-- Executing query:
INSERT INTO TMP_CMS

                 SELECT IRICNUM, SUM(COALESCE(ITQFFO, 0) * 
COALESCE(IRCOUT, 0)) / SUM(COALESCE(ITQFFO, 0)) as CMS

                 FROM IR INNER JOIN TMP_IC AS TIC ON IR.IRICNUM = TIC.ICNUM

                     INNER JOIN IT ON IR.IRNUM = IT.ITIRNUM

               WHERE ITQFFO > 0


ERROR:  column "ir.iricnum" must appear in the GROUP BY clause or be 
used in an aggregate function


-- Executing query:
INSERT INTO TMP_CMS

                 SELECT IRICNUM, SUM(COALESCE(ITQFFO, 0) * 
COALESCE(IRCOUT, 0)) / SUM(COALESCE(ITQFFO, 0)) as CMS

                 FROM IR INNER JOIN TMP_IC AS TIC ON IR.IRICNUM = TIC.ICNUM

                     INNER JOIN IT ON IR.IRNUM = IT.ITIRNUM

               WHERE ITQFFO > 0

                   AND IRSENS = 'E'

                   AND IRSTATUT in (2,3)

               GROUP BY IRICNUM

Query returned successfully: 1 rows affected, 406 ms execution time.

-- Executing query:
select * from tmp_cms


Total query runtime: 0 ms.
Data retrieval runtime: 0 ms.
1 rows retrieved.

-- Executing query:
SELECT IC.ICNUM,

                    IC.ICDESCA, IC.ICDESCF,

                    IC.ICCOUTDA, IC.ICFPRIX,

                    
ROUND(COALESCE(udf_InvItem_PrixUnitairePourFournisseur_Obtenir('M', 
IC.ICNUM, BX.BXRRNUM, '3', '2', '0' , '', 1, FALSE, CURRENT_DATE), 0), 
2) AS ICPRIXU ,

                    udf_InvItem_CoutAchatPourFournisseur_Obtenir('M', 
IC.ICNUM, BX.BXRRNUM, '4', '2', '0' , '', 1, FALSE) AS BXMONTU,

                    
COALESCE(udf_InvItem_CoutUnitairePourFournisseur_Obtenir('M' , IC.ICNUM, 
BX.BXRRNUM, '4' , '2', '0' , '' , 1, FALSE), 0) AS CUS,

                    AUDCTIFR AS UNITE_SEC, AUDCTIAN AS UNITE_PRI,

                    IQQSTOCK, IQQCOMM,

                    CMS, ICSTATUT, ICIKNUM, IKDESC_PRI, IKDESC_SEC, 
ICIMNUM, IMDESC_PRI, IMDESC_SEC

                    FROM TMP_IC AS TIC INNER JOIN IC ON TIC.ICNUM = 
IC.ICNUM

                                     LEFT OUTER JOIN BX ON IC.ICNUM = 
BX.BXICNUM AND BXTYPE = 1 AND BXREFER = true

                                     LEFT OUTER JOIN RR ON BX.BXRRNUM = 
RR.RRNUM

                                     LEFT OUTER JOIN AD ON RR.RRADNUM = 
AD.ADNUM

                                     LEFT OUTER JOIN AU ON IC.ICAUNUM = 
AU.AUNUM

                                     LEFT OUTER JOIN TMP_IQ AS TIQ ON 
TIC.ICNUM = TIQ.IQICNUM

                                     LEFT OUTER JOIN TMP_CMS AS TCMS ON 
TIC.ICNUM = TCMS.ICNUM

                                     LEFT OUTER JOIN IK ON IC.ICIKNUM = 
IK.IKNUM

                                     LEFT OUTER JOIN IM ON IC.ICIMNUM = 
IM.IMNUM  ORDER BY  IC.ICNUM


Total query runtime: 62 ms.
Data retrieval runtime: 47 ms.
1 rows retrieved.


THE STORED PROCEDURE
--------------------------------

CREATE OR REPLACE FUNCTION usp_Inventaire_Catalogue(VARCHAR, BOOLEAN, 
BOOLEAN, BOOLEAN, BOOLEAN, INTEGER, VARCHAR, VARCHAR, BOOLEAN, BOOLEAN, 
BOOLEAN, VARCHAR, INTEGER, BOOLEAN) RETURNS refcursor  AS '
DECLARE

    itemId ALIAS FOR $1;
    statusActive ALIAS FOR $2;
    statusInactive ALIAS FOR $3;
    statusDiscontinued ALIAS FOR $4;
    statusVirtual ALIAS FOR $5;
    orderBy ALIAS FOR $6;
    itemCategoryId ALIAS FOR $7;
    manufacturerId ALIAS FOR $8;
    displayQtyOrderedToSupplier ALIAS FOR $9;
    displayPrice ALIAS FOR $10;
    displayCost ALIAS FOR $11;
    companyId ALIAS FOR $12;
    priority ALIAS FOR $13;
    statusSpecified ALIAS FOR $14;
    ref refcursor;
    statement varchar(4000);
    FacteurCoutAjoute float;
    DecimalesAchat INTEGER;
    DecimalesVente INTEGER;
    DecimalesProd INTEGER;
    PARAM_FOURNPROD VARCHAR(10);

    temp RECORD;

BEGIN



   -- Create temp table

   --  Table des produits à inclure
   EXECUTE ''
   CREATE TEMP TABLE TMP_IC (
       ICNUM VARCHAR(20) primary key
   ) ON COMMIT DROP'';

   --  Table des stock
   EXECUTE ''
   CREATE TEMP TABLE TMP_IQ (
       IQICNUM VARCHAR(20) primary key,
       IQQSTOCK INT,
       IQQCOMM INT
   ) ON COMMIT DROP'';

   --  Table des coûts moyens en stock
   EXECUTE ''
   CREATE TEMP TABLE TMP_CMS (
       ICNUM VARCHAR(20) primary key,
       CMS NUMERIC
   ) ON COMMIT DROP'';


   select udf_Parametre_get(''Cie'', ''Item.FacteurCoutAjoute'', 
companyId) as v into temp;
   FacteurCoutAjoute := to_number(temp.v, ''999.9999'');
   select udf_Parametre_get(''Cie'', ''General.DecimalesAchat'', 
companyId) as v into temp;
   DecimalesAchat := to_number(temp.v, ''9'');
   select udf_Parametre_get(''Cie'', ''General.DecimalesVente'', 
companyId) as v into temp;
   DecimalesVente := to_number(temp.v, ''9'');
   select udf_Parametre_get(''Cie'', ''General.DecimalesProduction'', 
companyId) as v into temp;
   DecimalesProd := to_number(temp.v, ''9'');
   select COALESCE(udf_Parametre_get(''License'', 
''Production.Fournisseur'', ''''))  as v into temp;
   PARAM_FOURNPROD := temp.v;





    --  Tous les produits qui correspondent aux critères
     statement := ''
     INSERT INTO TMP_IC
     SELECT DISTINCT ICNUM
     FROM IC LEFT OUTER JOIN IK ON IC.ICIKNUM = IK.IKNUM
     WHERE (1 = 1) '';

     IF ( itemId IS NOT NULL AND itemId <> '''' )
     THEN
       statement := statement || '' AND ICNUM ~* '' || 
quote_literal(''***='' || itemId) ;
     END IF;


        IF ( statusSpecified = true )
     THEN
         statement := statement || '' AND (( 1 = 0) '';

         IF ( statusActive = true )
         THEN
           statement := statement || ''OR (ICSTATUT = 0) '';
         END IF;

         IF ( statusInactive = true )
         THEN
           statement := statement || ''OR (ICSTATUT = 1) '';
         END IF;

         IF ( statusDiscontinued = true )
         THEN
           statement := statement || ''OR (ICSTATUT = 2) '';
         END IF;

         IF ( statusVirtual = true )
         THEN
           statement := statement || ''OR (ICSTATUT = 3) '';
         END IF;
         statement := statement || '' )  '';

     END IF;





     IF ( itemCategoryId IS NOT NULL AND itemCategoryId <> '''' )
     THEN
       statement := statement || '' AND ICIKNUM = '' || 
quote_literal(itemCategoryId) ;
     END IF;

     IF ( manufacturerId IS NOT NULL AND manufacturerId <> '''' )
     THEN
       statement := statement || '' AND ICIMNUM = '' || 
quote_literal(manufacturerId) ;
     END IF;

     IF ( priority IS NOT NULL )
     THEN
       statement := statement || '' AND IKPRIORITE <= '' || priority ;
     END IF;

     RAISE NOTICE ''Statement here is %'', statement;
     EXECUTE statement;


       --  Les stock pour ces produits
      statement := ''
         INSERT INTO TMP_IQ
           SELECT IQICNUM, SUM(IQQSTOCK), SUM(IQQCOMM)
           FROM IQ INNER JOIN TMP_IC AS TIC ON IQ.IQICNUM = TIC.ICNUM
           GROUP BY IQICNUM '';

     RAISE NOTICE ''Statement here is %'', statement;
     EXECUTE statement;


       --  Les coûts moyens en inventaire pour ces produits
      statement := ''
         INSERT INTO TMP_CMS
             SELECT IRICNUM, SUM(COALESCE(ITQFFO, 0) * COALESCE(IRCOUT, 
0)) / SUM(COALESCE(ITQFFO, 0)) as CMS
             FROM IR INNER JOIN TMP_IC AS TIC ON IR.IRICNUM = TIC.ICNUM
                 INNER JOIN IT ON IR.IRNUM = IT.ITIRNUM
           WHERE ITQFFO > 0
               AND IRSENS = '' || quote_literal(''E'') || ''
               AND IRSTATUT in (2,3)
           GROUP BY IRICNUM'';



     RAISE NOTICE ''Statement here is %'', statement;
     --EXECUTE statement;

       --  Informations à retourner
       statement := ''
               SELECT IC.ICNUM,
                IC.ICDESCA, IC.ICDESCF,
                IC.ICCOUTDA, IC.ICFPRIX,
                
ROUND(COALESCE(udf_InvItem_PrixUnitairePourFournisseur_Obtenir('' || 
quote_literal(companyId) || '', IC.ICNUM, BX.BXRRNUM, '' || 
quote_literal(DecimalesVente) || '', '' || quote_literal(DecimalesProd) 
|| '', '' || quote_literal(FacteurCoutAjoute) || '' , '' || 
quote_literal(PARAM_FOURNPROD) || '', 1, FALSE, CURRENT_DATE), 0), 2) AS 
ICPRIXU ,
                udf_InvItem_CoutAchatPourFournisseur_Obtenir('' || 
quote_literal(companyId) || '', IC.ICNUM, BX.BXRRNUM, '' || 
quote_literal(DecimalesAchat) || '', '' || quote_literal(DecimalesProd) 
|| '', '' || quote_literal(FacteurCoutAjoute) || '' , '' || 
quote_literal(PARAM_FOURNPROD) || '', 1, FALSE) AS BXMONTU,
                
COALESCE(udf_InvItem_CoutUnitairePourFournisseur_Obtenir('' || 
quote_literal(companyId) || '' , IC.ICNUM, BX.BXRRNUM, '' || 
quote_literal(DecimalesAchat) || '' , '' || quote_literal(DecimalesProd) 
|| '', '' || quote_literal(FacteurCoutAjoute) || '' , '' || 
quote_literal(PARAM_FOURNPROD) || '' , 1, FALSE), 0) AS CUS,
                AUDCTIFR AS UNITE_SEC, AUDCTIAN AS UNITE_PRI,
                IQQSTOCK, IQQCOMM,
                CMS, ICSTATUT, ICIKNUM, IKDESC_PRI, IKDESC_SEC, 
ICIMNUM, IMDESC_PRI, IMDESC_SEC
                FROM TMP_IC AS TIC INNER JOIN IC ON TIC.ICNUM = IC.ICNUM
                                 LEFT OUTER JOIN BX ON IC.ICNUM = 
BX.BXICNUM AND BXTYPE = 1 AND BXREFER = true
                                 LEFT OUTER JOIN RR ON BX.BXRRNUM = 
RR.RRNUM
                                 LEFT OUTER JOIN AD ON RR.RRADNUM = 
AD.ADNUM
                                 LEFT OUTER JOIN AU ON IC.ICAUNUM = 
AU.AUNUM
                                 LEFT OUTER JOIN TMP_IQ AS TIQ ON 
TIC.ICNUM = TIQ.IQICNUM
                                 LEFT OUTER JOIN TMP_CMS AS TCMS ON 
TIC.ICNUM = TCMS.ICNUM
                                 LEFT OUTER JOIN IK ON IC.ICIKNUM = 
IK.IKNUM
                                 LEFT OUTER JOIN IM ON IC.ICIMNUM = 
IM.IMNUM'';


     -- By id
     IF ( orderBy = 0 )
     THEN
        statement := statement || ''  ORDER BY  IC.ICNUM'';
     ELSIF ( orderBy = 1 )
     THEN
        statement := statement || ''  ORDER BY  IC.ICSTATUT'';
     ELSIF ( orderBy = 2 )
     THEN
        statement := statement || ''  ORDER BY  IC.ICIKNUM'';
     ELSIF ( orderBy = 3 )
     THEN
        statement := statement || ''  ORDER BY  IC.ICIMNUM'';
     END IF;

     RAISE NOTICE ''Statement here is %'', statement;

   OPEN ref FOR EXECUTE statement;
   RETURN ref;

END;
' LANGUAGE 'plpgsql';