Re: JDBC CallableStatement bug on functions with return parameter

  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
>