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
>