db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Date Fri, 06 Jan 2006 15:11:27 GMT
On Friday 06 January 2006 7:10 am, Bernt M. Johnsen wrote:
> >>>>>>>>>>>> Craig L Russell wrote (2006-01-05 16:59:00):
> >
> > Hi,
> >
> > I asked Lance Anderson, spec lead for JDBC 4.0, about this issue and
> > he replied that he thinks that due to compatibility with existing
> > applications that rely on this behavior, it's unlikely to change.
> >
> > My opinion is that the behavior is surprising, and that most
> > applications that discover that the API with a  BigDecimal parameter
> > truncates all the decimals, simply change to the API call that allows
> > you to specify the scale.
> >
> > So my big unfounded claim is that there is no use for the API without
> > the scale parameter taking a BigDecimal parameter with the current
> > behavior. And that changing it has low risk of untoward results.
> Do you suggest that we should throw an exception if setObject without
> scale is called with a BigDecimal argument? Or do you suggest that we
> let setObject default to the scale of the BigDecimal parameter and
> thus violating the spec?
> Bernt


First, this is clearly not a Derby issue. (well except for Bernt's last 

Second, while this is a discussion on the JDBC spec, I fail to see what is 
wrong with the current design and implementation.

Maybe I'm a tad slow, but would someone please show me what I am missing?

The issue is with the PreparedStatement object class and its setObject() 

First the method setObject() is overloaded. One method does not require scale 
and assumes that there is a scale of 0. The second method does require scale 
and it is to be used for objects that have a scale component.

The method in question:
public void setObject(int parameterIndex,
                      Object x,
                      int targetSqlType,
                      int scale)

I've read this thread and in communications with an individual offline. I 
believe that this quote sums up his belief: 

"The JDBC spec is flawed here because the application has to know how
the scale is set up in the database, otherwise you'll probably run
into problems (as I did, because I was assuming that it takes the
scale that the database was set up with rather than the useless
default of 0)."

Now the first problem is that this programmer is under the assumption that its 
feasible to use the scale within the target database's column definition. 
This is incorrect since the scale value in question has nothing to do with 
the target database but with the object being inserted. Try inserting an 
object who's scale is 5 in to a database table/column where the scale of the 
column is 7. You can do this, however, if you set the scale in setObject() to 
7 and the object has a scale of 5, you're going to have problems.

Another issue is the "Types.NUMERIC". This actually should be Types.DECIMAL.
(But that's splitting hairs)

So, when using setObject() with a bigDecimal you should be using the 
	setObject(idx,bigDecimal, Types.DECIMAL, bigDecimal.scale());

In theory, it is possible for there to be only one setObject() method without 
the scale parameter being passed in. Because we know the sqlType of the 
object, if the object requires scale, we could just get the scale from the 

Of course this is not a good design for a couple of reasons. 
(Performance, and maintainability are the top two ...)

As pointed out, the setObject(int parameterIndex,
                      Object x,
                      int targetSqlType,
                      int scale)
is the primary method and the  setObject(int parameterIndex,
                      Object x,
                      int targetSqlType)
is the overloaded method.
( setObject(int parameterIndex, Object x, int targetSQLType)  can call 
setObject(parameterIndex, x, targetSQLType, 0) as its body and then return.)

Ok... So what am I missing? The current spec looks ok to me.

In response to Bernt's last question, Derby can't throw an exception.
Its theoretically possible for someone to use a data type decimal and set the 
scale to 0.     (The scale is the number of digits to the right of the 
decimal point.)

In theory you can ignore the scale input value and get the scale directly from 
the object. But as you say, you're violating the spec ...

So, where's the beef?
Michael Segel
Michael Segel Consulting Corp.
(312) 952-8175 [mobile]

View raw message