PostgreSQL FUNCTION return problem

------=_Part_19499_710162.1207578922637
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello, i have the following problem and i was wondering if you coulde help
me. I'll try to describe it as in depth as i can.

   I have a FUNCTION in a postgresql database which subtracts two DOUBLE
PRECISION FIELDS and returns the result. The problem is that when the
resul=
t
should be zero, the number i
get is a number ridiculously close to zero in scientific notation (for
example 2.4697823124E-14) but not zero. I know (or at least i think i
know)
the problem lies within the way postgresql and Java communicate because if
=
a
connect through a console to the db and run the function i get the result
right (zero) but if i debug the java code i get ther wrong
non-zero-but-very-close result

this is the function:

CREATE OR REPLACE FUNCTION getSaldoParticipante(BIGINT) RETURNS DOUBLE
PRECISION AS '
   DECLARE
    idParticipante ALIAS FOR $1;
    result DOUBLE PRECISION;
   BEGIN

   result :=3D 0;

   Select (Select CASE WHEN saldo ISNULL then 0 else saldo end FROM
(Select
sum(PG.saldo) as saldo FROM pagos.pagos PG WHERE PG.saldo > 0 AND
PG.participante=3DidParticipante AND PG.deleted =3D false) tmp)-(Select
CAS=
E
WHEN saldo ISNULL then 0 else saldo end FROM (Select sum(DE.saldo) as
saldo
FROM pagos.deudas DE WHERE DE.deleted =3D false AND
DE.participante=3DidParticipante) tmp) INTO result;

   RETURN result;

END;'
LANGUAGE 'plpgsql' CALLED ON NULL INPUT;

this is the Java code where i get the result that should be zero but
instea=
d
is just very close to zero

        String query =3D "select getSaldoParticipante(?)";
        params.add(new ParameterValue(idParticipante, Types.BIGINT));

        try {
            conn =3D tx.getConnection();

            //preparo y ejecuto el statement
            pstmt =3D prepareStatement (conn, query, params);
            rs =3D pstmt.executeQuery();

            //itero los registros
            while (rs.next()) {
                //the problems appears at the following line
                result =3D rs.getDouble("getSaldoParticipante");
            }
        }
        catch(Exception e){
                 ...


the only thing that works here is to use rs.getInt instead of
rs.getDouble,
but that is not good enough since i dont want to truncate the decimal part
of the number.

So, a lot of thanks in advance, any help will be greatly appreciated.

=A1Saludos!

Rafael

------=_Part_19499_710162.1207578922637
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello, i have the following problem and i was wondering if you coulde help
=
me. I&#39;ll try to describe it as in depth as i can.<br><br>&nbsp;&nbsp;
I=
 have a FUNCTION in a postgresql database which subtracts two DOUBLE
PRECIS=
ION FIELDS and returns the result. The problem is that when the result
shou=
ld be zero, the number i<br>
get is a number ridiculously close to zero in scientific notation (for
exam=
ple 2.4697823124E-14) but not zero. I know (or at least i think i know)
the=
 problem lies within the way postgresql and Java communicate because if a
c=
onnect through a console to the db and run the function i get the result
ri=
ght (zero) but if i debug the java code i get ther wrong
non-zero-but-very-=
close result<br>
<br>this is the function:<br><br>CREATE OR REPLACE FUNCTION
getSaldoPartici=
pante(BIGINT) RETURNS DOUBLE PRECISION AS &#39;<br>&nbsp;&nbsp;
DECLARE<br>=
&nbsp;&nbsp;&nbsp; idParticipante ALIAS FOR $1;<br>&nbsp;&nbsp;&nbsp;
resul=
t DOUBLE PRECISION;<br>&nbsp;&nbsp; BEGIN<br>&nbsp;&nbsp; <br>
&nbsp;&nbsp; result :=3D 0;<br><br>&nbsp;&nbsp; Select (Select CASE WHEN
sa=
ldo ISNULL then 0 else saldo end FROM (Select sum(PG.saldo) as saldo FROM
p=
agos.pagos PG WHERE PG.saldo &gt; 0 AND PG.participante=3DidParticipante
AN=
D PG.deleted =3D false) tmp)-(Select CASE WHEN saldo ISNULL then 0 else
sal=
do end FROM (Select sum(DE.saldo) as saldo FROM pagos.deudas DE WHERE
DE.de=
leted =3D false AND DE.participante=3DidParticipante) tmp) INTO
result;<br>
&nbsp;&nbsp; <br>&nbsp;&nbsp; RETURN result;<br><br>END;&#39;<br>LANGUAGE
&=
#39;plpgsql&#39; CALLED ON NULL INPUT;<br><br>this is the Java code where
i=
 get the result that should be zero but instead is just very close to
zero<=
br><br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; String query =3D &quot;select
=
getSaldoParticipante(?)&quot;;<br>
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;params.add(new
ParameterValue(idParti=
cipante, Types.BIGINT));<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
&nbsp;<br>&nbsp=
;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;try {<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
&=
nbsp;&nbsp;&nbsp;&nbsp; conn =3D tx.getConnection();<br>&nbsp;&nbsp;
&nbsp;=
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
&=
nbsp;&nbsp;&nbsp; &nbsp;//preparo y ejecuto el statement<br>&nbsp;&nbsp;
&n=
bsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;pstmt =3D prepareStatement
(conn,=
 query, params);<br>
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;rs =3D
pstmt.execu=
teQuery();<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
&nbsp;<br>=
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;//itero los
regist=
ros<br>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;while
(rs.n=
ext())
{<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp; //the problems appears at the following
line<br=
>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp=
; result =3D rs.getDouble(&quot;getSaldoParticipante&quot;);<br>
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;
=
&nbsp;&nbsp;&nbsp;&nbsp; }<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
ca=
tch(Exception
e){<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ...<br><br><br>the only thing
th=
at works here is to use rs.getInt instead of rs.getDouble, but that is not
=
good enough since i dont want to truncate the decimal part of the
number.<b=
r>
<br>So, a lot of thanks in advance, any help will be greatly
appreciated.<b=
r><br>=A1Saludos!<br><br>Rafael<br>

------=_Part_19499_710162.1207578922637--