Thread: Re: JDBC CallableStatement bug on functions with return parameter

John LH  wrote:

> callStatement.registerOutParameter(1, java.sql.Types.FLOAT);

> callStatement.setObject(2, new Float(25.25));

PostgreSQL doesn't support using approximate data types (like Float)
for money.  Try compiling and running this to see why:

import java.math.BigDecimal;
public class ExactnessTest
{
    public static void main(String[] args)
    {
        double approximate;
        BigDecimal exact;

        // Set an approximation of a penny.
        approximate = 0.01;
        // Put the actual value into an exact class.
        exact = new BigDecimal(approximate);
        // Show the actual value assigned to the double.
        System.out.println(exact);
        // Create and show an exact decimal penny.
        exact = new BigDecimal("0.01");
        System.out.println(exact);
    }
}

Try using BigDecimal, which *is* capable of storing exact
representations of all decimal fractions.  Note that feeding an
approximate value, including an unquoted literal, to the BigDecimal
constructor will cause the BigDecimal value to be less precise than
you might expect.

-Kevin

  Hi,

 >> Try using BigDecimal, which *is* capable of storing exact
representations of all decimal fractions.

In my previous email I mentioned that I also tried BigDecimal and it did
not work.

 >> I've also tried the combination and results of ...
 >> ===
 >> java.sql.Types.DOUBLE/Double
 >> org.postgresql.util.PSQLException: ERROR: function
public.sales_tax(double precision) does not exist
 >> ===
 >> java.sql.Types.NUMERIC/BigDecimal
 >> org.postgresql.util.PSQLException: ERROR: function
public.sales_tax(numeric) does not exist

I am aware of the approximate datatype issue with Float and Double.  The
reason I tried Float and Double is because if I call
statement.execute("select * from tableWithMoney"), and then call
ResultSet.getObject("money_column"), it will actually return a Double
datatype.  Which is a possible bug in the JDBC driver.

I tried Float, Double and BigDecimal.  I can't find any documentation
indicating what datatype to use.  Looking at the JDBC driver code it
looks like it is incorrectly converting the SQL.

thanks
-John


On 2/5/2011 9:43 AM, Kevin Grittner wrote:
> John LH  wrote:
>
>> callStatement.registerOutParameter(1, java.sql.Types.FLOAT);
>
>> callStatement.setObject(2, new Float(25.25));
>
> PostgreSQL doesn't support using approximate data types (like Float)
> for money.  Try compiling and running this to see why:
>
> import java.math.BigDecimal;
> public class ExactnessTest
> {
>      public static void main(String[] args)
>      {
>          double approximate;
>          BigDecimal exact;
>
>          // Set an approximation of a penny.
>          approximate = 0.01;
>          // Put the actual value into an exact class.
>          exact = new BigDecimal(approximate);
>          // Show the actual value assigned to the double.
>          System.out.println(exact);
>          // Create and show an exact decimal penny.
>          exact = new BigDecimal("0.01");
>          System.out.println(exact);
>      }
> }
>
> Try using BigDecimal, which *is* capable of storing exact
> representations of all decimal fractions.  Note that feeding an
> approximate value, including an unquoted literal, to the BigDecimal
> constructor will cause the BigDecimal value to be less precise than
> you might expect.
>
> -Kevin
>