Skip site navigation (1) Skip section navigation (2)

rs.getBigDecimal returning Null on field that is not null

From: David Gagnon <dgagnon(at)siunik(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: rs.getBigDecimal returning Null on field that is not null
Date: 2007-09-28 10:21:29
Message-ID: 46FCD5A9.8020504@siunik.com (view raw or flat)
Thread:
Lists: 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';



Responses

pgsql-jdbc by date

Next:From: Mike C.Date: 2007-09-28 13:13:41
Subject: Re: [BUGS] BUG #2856: Jdbc 4 connector running on JDK 1.6 should
Previous:From: Kris JurkaDate: 2007-09-27 21:21:26
Subject: Re: ResultSet with more than 5 rows causes error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group