rs.getBigDecimal returning Null on field that is not null - Mailing list pgsql-jdbc

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';