You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by "Bernt M. Johnsen" <Be...@Sun.COM> on 2006/01/02 11:03:37 UTC
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
>>>>>>>>>>>> Thomas Dudziak wrote (2005-12-25 16:18:14):
> When executing this code snippet:
>
>
> Statement stmt = conn.createStatement();
>
> stmt.executeUpdate("CREATE TABLE test (\n"+
> " pk INTEGER NOT NULL,\n"+
> " value NUMERIC(15,7) NOT NULL,\n"+
> " PRIMARY KEY (pk)\n"+
> ")");
> stmt.close();
>
> PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test (pk,
> value) VALUEs (?, ?)");
>
> pstmt.setInt(1, 1);>
> pstmt.setObject(2, new BigDecimal("0.01"), Types.NUMERIC);
> pstmt.execute();
> pstmt.close();
>
> conn.close();
>
>
> the resulting value in the database is 0e-7, not 1e-2 as I would expect.
> When changing that to
>
>
> pstmt.setBigDecimal(2, new BigDecimal("0.01"));
>
>
> it works though.
> Did I make a mistake or is this a bug ?
I think you have found a bug. I experimented a bit and found that it
works for values >= 1.0 but the result will be 0 for values < 1.0.
pstmt.setObject(2, new BigDecimal("1.0"), Types.NUMERIC);
works perefectly ok while
pstmt.setObject(2, new BigDecimal("0.999999999"), Types.NUMERIC);
is errouneous.
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Michael Segel <de...@segel.com>.
On Wednesday 04 January 2006 6:11 am, Bernt M. Johnsen wrote:
Uhm,
Sorry to top post byt Bernt is right.
This is not a bug and IMHO there shouldn't be a JIRA issue opened.
What you are seeing is correct.
Check out :
"http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html"
You have a couple of methods for PreparedStatement.setObject().
You chose the wrong one.
You do need to set the scale.
The method that you are using assumes the scale to be 0.
(This is per the Java Docs provided by Sun)
But hey, what do I know? I just happend to take the time to RTFM. ;-)
Cheers!
-G
> >>>>>>>>>>>> Thomas Dudziak wrote (2006-01-03 17:18:02):
> >
> > On 1/2/06, Bernt M. Johnsen <Be...@sun.com> wrote:
> > > >>>>>>>>>>>> Thomas Dudziak wrote (2005-12-25 16:18:14):
> > > >
> > > > When executing this code snippet:
> > > >
> > > >
> > > > Statement stmt = conn.createStatement();
> > > >
> > > > stmt.executeUpdate("CREATE TABLE test (\n"+
> > > > " pk INTEGER NOT NULL,\n"+
> > > > " value NUMERIC(15,7) NOT NULL,\n"+
> > > > " PRIMARY KEY (pk)\n"+
> > > > ")");
> > > > stmt.close();
> > > >
> > > > PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test
> > > > (pk, value) VALUEs (?, ?)");
> > > >
> > > > pstmt.setInt(1, 1);>
> > > > pstmt.setObject(2, new BigDecimal("0.01"), Types.NUMERIC);
> > > >
> > > > pstmt.execute();
> > > > pstmt.close();
> > > >
> > > > conn.close();
> > > >
> > > >
> > > > the resulting value in the database is 0e-7, not 1e-2 as I would
> > > > expect. When changing that to
> > > >
> > > >
> > > > pstmt.setBigDecimal(2, new BigDecimal("0.01"));
> > > >
> > > >
> > > > it works though.
> > > > Did I make a mistake or is this a bug ?
> > >
> > > I think you have found a bug. I experimented a bit and found that it
> > > works for values >= 1.0 but the result will be 0 for values < 1.0.
> > >
> > > pstmt.setObject(2, new BigDecimal("1.0"), Types.NUMERIC);
> > >
> > > works perefectly ok while
> > >
> > > pstmt.setObject(2, new BigDecimal("0.999999999"), Types.NUMERIC);
> > >
> > > is errouneous.
>
> BUT: pstmt.setObject(2, new BigDecimal("0.999999999"), Types.NUMERIC, 7);
>
> (giving the scale) does work.
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Thomas Dudziak wrote (2006-01-03 17:18:02):
> On 1/2/06, Bernt M. Johnsen <Be...@sun.com> wrote:
> > >>>>>>>>>>>> Thomas Dudziak wrote (2005-12-25 16:18:14):
> > > When executing this code snippet:
> > >
> > >
> > > Statement stmt = conn.createStatement();
> > >
> > > stmt.executeUpdate("CREATE TABLE test (\n"+
> > > " pk INTEGER NOT NULL,\n"+
> > > " value NUMERIC(15,7) NOT NULL,\n"+
> > > " PRIMARY KEY (pk)\n"+
> > > ")");
> > > stmt.close();
> > >
> > > PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test (pk,
> > > value) VALUEs (?, ?)");
> > >
> > > pstmt.setInt(1, 1);>
> > > pstmt.setObject(2, new BigDecimal("0.01"), Types.NUMERIC);
> >
> > > pstmt.execute();
> > > pstmt.close();
> > >
> > > conn.close();
> > >
> > >
> > > the resulting value in the database is 0e-7, not 1e-2 as I would expect.
> > > When changing that to
> > >
> > >
> > > pstmt.setBigDecimal(2, new BigDecimal("0.01"));
> > >
> > >
> > > it works though.
> > > Did I make a mistake or is this a bug ?
> >
> > I think you have found a bug. I experimented a bit and found that it
> > works for values >= 1.0 but the result will be 0 for values < 1.0.
> >
> > pstmt.setObject(2, new BigDecimal("1.0"), Types.NUMERIC);
> >
> > works perefectly ok while
> >
> > pstmt.setObject(2, new BigDecimal("0.999999999"), Types.NUMERIC);
> >
> > is errouneous.
BUT: pstmt.setObject(2, new BigDecimal("0.999999999"), Types.NUMERIC, 7);
(giving the scale) does work.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Michael Segel <de...@segel.com>.
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
All,
First, this is clearly not a Derby issue. (well except for Bernt's last
question...)
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()
methods.
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
following:
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
object.
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
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Daniel John Debrunner <dj...@apache.org>.
Lance J. Andersen wrote:
> The spec needs to be followed whether you agree or disagree with the
> semantics. The javadocs are very specific here and there must have been
> a reason for this decision at the time. As i indicated to Craig, I am
> trying to find out if any of the previous spec leads recall why this was
> done before i do anything else on this issue.
I think the spec is correct.
The reason that the scale defaults to zero is that it's the only choice.
There is no guarantee that the scale can be obtained from the passed in
object. I.e. what happens if the object is not a BigDecimal, what would
the scale be?
Here is a summary of the ways that a DECIMAL column could be set from an
Object.
setBigDecimal(int, BigDecimal) - DECIMAL value passed as-is to engine
setString(int, String) - VARCHAR or LONGVARCHAR value passed as-is to
engine
setObject(int, Object) - same as setBigDecimal if value is an instance
of BigDecimal, or same as setString is value is an instance of String.
Note type mapping (Java to SQL) is defined by the class of the value.
setObject(int, Object, int) - DECIMAL value passed to engine has scale
set to zero if type is Type.DECIMAL or Type.NUMERIC
Note type mapping (Java to SQL) is defined by the type (3rd) parameter.
setObject(int, Object, int, int) - DECIMAL value passed to engine has
passed in scale if type is Type.DECIMAL or Type.NUMERIC
Note type mapping (Java to SQL) is defined by the type (3rd) parameter.
Note the big difference is that the last two setObject methods define
the type of the value sent to the engine from the type parameter, not
from the class of the Object. Thus, in these methods, any object to SQL
type conversion has to happen in the jdbc driver, not the engine.
E.g. for setting on a DECIMAL column
// Type passed to engine as a VARCHAR
// converted to DECIMAL by *database engine*.
ps.setObject(1, "2.3");
// Type passed to engine as a DECIMAL
// converted to DECIMAL(n,0) by *JDBC driver*.
ps.setObject(1, "2.3", Types.DECIMAL);
// Type passed to engine as a DECIMAL
// converted to DECIMAL(n,3) by *JDBC driver*.
ps.setObject(1, "2.3", Types.DECIMAL, 3);
Of course, with Derby embedded the line between database engine and JDBC
driver does not exist, but that's the abnormal case. The typical JDBC
driver is a client driver.
Dan.
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Lance J. Andersen wrote (2006-01-06 09:14:46):
> The spec needs to be followed whether you agree or disagree with the
> semantics. The javadocs are very specific here and there must have been
> a reason for this decision at the time. As i indicated to Craig, I am
> trying to find out if any of the previous spec leads recall why this was
> done before i do anything else on this issue.
I agree that we need to follow the specs. Just wondered what Craig
thought his "big unfounded claim" would imply..... ;-)
> A change in this area could potentially break existing applications who
> are relying on the functionality as documented. This would have to be
> weighed by the JDBC EG prior to any changes in behavior.
>
> It is just not a simple change the javadocs at this time. This has to
> be researched and discussed further. It could well be that no one is
> impacted, i just do not know at this point in time.
>
> Regards
> Lance
> 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
> >
> >
> >
> >
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Thomas Dudziak <to...@gmail.com>.
On 1/6/06, Lance J. Andersen <La...@sun.com> wrote:
> The spec needs to be followed whether you agree or disagree with the
> semantics. The javadocs are very specific here and there must have been a
> reason for this decision at the time. As i indicated to Craig, I am trying
> to find out if any of the previous spec leads recall why this was done
> before i do anything else on this issue.
>
> A change in this area could potentially break existing applications who are
> relying on the functionality as documented. This would have to be weighed
> by the JDBC EG prior to any changes in behavior.
>
> It is just not a simple change the javadocs at this time. This has to be
> researched and discussed further. It could well be that no one is impacted,
> i just do not know at this point in time.
Agreed. While my personal opinion is that this is not intuitive and
problematic at times (e.g. what to do if the value to be inserted into
a NUMERIC/DECIMAL is a double, not a BigDecimal ? Should the driver
clip the double to scale 0 ?) the spec stands as it is.
regards,
Tom
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by "Lance J. Andersen" <La...@Sun.COM>.
The spec needs to be followed whether you agree or disagree with the
semantics. The javadocs are very specific here and there must have been
a reason for this decision at the time. As i indicated to Craig, I am
trying to find out if any of the previous spec leads recall why this was
done before i do anything else on this issue.
A change in this area could potentially break existing applications who
are relying on the functionality as documented. This would have to be
weighed by the JDBC EG prior to any changes in behavior.
It is just not a simple change the javadocs at this time. This has to
be researched and discussed further. It could well be that no one is
impacted, i just do not know at this point in time.
Regards
Lance
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
>
>
>
>
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> 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
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Craig L Russell <Cr...@Sun.COM>.
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.
Craig
On Jan 5, 2006, at 5:21 AM, Thomas Dudziak wrote:
> On 1/4/06, Bernt M. Johnsen <Be...@sun.com> wrote:
>
>> I was too fast in my conclusions. It is not a bug.
>>
>> setObject(idx, bigDecimal, Types.NUMERIC) is defined to be identical
>> with setObject(idx, bigDecimal, Types.NUMERIC, 0) and that's exactly
>> what Derby does.
>>
>> If you need decimals, you have to specify scale. E.g.:
>>
>> setObject(idx, new BigDecimal("0.9999"), Type.NUMERIC, 4);
>
> Indeed, you're right (though I think this is a design error in JDBC
> itself). I've changed my source accordingly (using setBigDecimal
> instead).
>
> Thanks,
> Tom
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Thomas Dudziak <to...@gmail.com>.
On 1/4/06, Bernt M. Johnsen <Be...@sun.com> wrote:
> I was too fast in my conclusions. It is not a bug.
>
> setObject(idx, bigDecimal, Types.NUMERIC) is defined to be identical
> with setObject(idx, bigDecimal, Types.NUMERIC, 0) and that's exactly
> what Derby does.
>
> If you need decimals, you have to specify scale. E.g.:
>
> setObject(idx, new BigDecimal("0.9999"), Type.NUMERIC, 4);
Indeed, you're right (though I think this is a design error in JDBC
itself). I've changed my source accordingly (using setBigDecimal
instead).
Thanks,
Tom
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-01-04 13:00:17):
> >>>>>>>>>>>> Thomas Dudziak wrote (2006-01-03 17:18:02):
> >
> > Shall I file a JIRA issue then ?
>
> I'll do it.
I was too fast in my conclusions. It is not a bug.
setObject(idx, bigDecimal, Types.NUMERIC) is defined to be identical
with setObject(idx, bigDecimal, Types.NUMERIC, 0) and that's exactly
what Derby does.
If you need decimals, you have to specify scale. E.g.:
setObject(idx, new BigDecimal("0.9999"), Type.NUMERIC, 4);
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Thomas Dudziak wrote (2006-01-03 17:18:02):
>
> Shall I file a JIRA issue then ?
I'll do it.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: setObject(idx, bigDecimal, Types.NUMERIC); doesn't work ?
Posted by Thomas Dudziak <to...@gmail.com>.
On 1/2/06, Bernt M. Johnsen <Be...@sun.com> wrote:
> >>>>>>>>>>>> Thomas Dudziak wrote (2005-12-25 16:18:14):
> > When executing this code snippet:
> >
> >
> > Statement stmt = conn.createStatement();
> >
> > stmt.executeUpdate("CREATE TABLE test (\n"+
> > " pk INTEGER NOT NULL,\n"+
> > " value NUMERIC(15,7) NOT NULL,\n"+
> > " PRIMARY KEY (pk)\n"+
> > ")");
> > stmt.close();
> >
> > PreparedStatement pstmt = conn.prepareStatement("INSERT INTO test (pk,
> > value) VALUEs (?, ?)");
> >
> > pstmt.setInt(1, 1);>
> > pstmt.setObject(2, new BigDecimal("0.01"), Types.NUMERIC);
>
> > pstmt.execute();
> > pstmt.close();
> >
> > conn.close();
> >
> >
> > the resulting value in the database is 0e-7, not 1e-2 as I would expect.
> > When changing that to
> >
> >
> > pstmt.setBigDecimal(2, new BigDecimal("0.01"));
> >
> >
> > it works though.
> > Did I make a mistake or is this a bug ?
>
> I think you have found a bug. I experimented a bit and found that it
> works for values >= 1.0 but the result will be 0 for values < 1.0.
>
> pstmt.setObject(2, new BigDecimal("1.0"), Types.NUMERIC);
>
> works perefectly ok while
>
> pstmt.setObject(2, new BigDecimal("0.999999999"), Types.NUMERIC);
>
> is errouneous.
Shall I file a JIRA issue then ?
Tom