You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Øyvind Harboe <oy...@zylin.com> on 2008/08/08 10:20:45 UTC
NUMERIC default scale behaves differently on Derby & SQL Server
If I set scale to blank (default) or -1, then Derby will round off down
to nearest whole number whereas SQL Server will not.
Is this intentional?
--
Øyvind Harboe
http://www.zylin.com/zy1000.html
ARM7 ARM9 XScale Cortex
JTAG debugger and flash programmer
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Øyvind Harboe <oy...@zylin.com>.
On Fri, Aug 8, 2008 at 10:20 AM, Øyvind Harboe <oy...@zylin.com> wrote:
> If I set scale to blank (default) or -1, then Derby will round off down
> to nearest whole number whereas SQL Server will not.
Relevant?
http://mail-archives.apache.org/mod_mbox/db-derby-commits/200505.mbox/%3C20050502235830.18113.qmail@minotaur.apache.org%3E
--
Øyvind Harboe
http://www.zylin.com/zy1000.html
ARM7 ARM9 XScale Cortex
JTAG debugger and flash programmer
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Øyvind Harboe <oy...@zylin.com>.
On Fri, Aug 8, 2008 at 8:28 PM, Andrus Adamchik <an...@objectstyle.org> wrote:
> Here is a potential hole. By default (unless ExtendedType is explicitly
> defined for a given Java class), Cayenne does the following JDBC calls:
>
> PreparedStatement st = ..
> if (scale != -1) {
> st.setObject(pos, val, type, scale);
> }
> else {
> st.setObject(pos, val, type);
> }
>
> Looks correct from the JDBC standpoint, but from my experience some drivers
> may have it all wrong, and inconsistent with a corresponding
> 'setSpecificType(..)' method for a given object class. So what is the class
> of the object parameter? BigDecimal? Something else?
BigDecimal.
--
Øyvind Harboe
http://www.zylin.com/zy1000.html
ARM7 ARM9 XScale Cortex
JTAG debugger and flash programmer
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Øyvind Harboe <oy...@zylin.com>.
On Tue, Aug 12, 2008 at 12:26 AM, Andrus Adamchik
<an...@objectstyle.org> wrote:
>
> On Aug 9, 2008, at 12:18 PM, Øyvind Harboe wrote:
>
>> I guess the problem I see is that I test only against one or two databases
>> so I'd rather see the *same* behaviour across databases regardless of what
>> JDBC is defined to do...
>
> I understand.
>
>> We flipped scale="2" for all our NUMERIC types
>> since that is what our application assumes.
>
> I think this is the "correct solution" as it explicitly tells the driver
> what to do leaving no room for ambiguity. I am still unsure about what the
> Cayenne default behavior should be (when there's no scale set).
Good question indeed. Perhaps this is just a bug in Derby? Shouldn't
scale -1 mean infinite precision for BigDecimal?
I *could* add a BigDecimal extended type to handle this in a way
that follows the *applications* rules, right? (whatever those rules
might be).
Make scale=0 default? Forces user to choose?
Perhaps add a file to cayenne which lists known ideosynchrazies
across databases?
Keep it under Subversion such that it is in sync w/source code
from a historical point of view, a javadoc comment would do...
That would only document it, not necessarily help the developers
catch the problem up front(as that would require reading documentation
and everybody knows how well that works...)
I can't think of anything that is a 30% improvement over the current
state of affairs, so perhaps doing nothing is just as well...
--
Øyvind Harboe
http://www.zylin.com/zy1000.html
ARM7 ARM9 XScale Cortex
JTAG debugger and flash programmer
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 9, 2008, at 12:18 PM, Øyvind Harboe wrote:
> I guess the problem I see is that I test only against one or two
> databases
> so I'd rather see the *same* behaviour across databases regardless
> of what
> JDBC is defined to do...
I understand.
> We flipped scale="2" for all our NUMERIC types
> since that is what our application assumes.
I think this is the "correct solution" as it explicitly tells the
driver what to do leaving no room for ambiguity. I am still unsure
about what the Cayenne default behavior should be (when there's no
scale set).
Andrus
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Øyvind Harboe <oy...@zylin.com>.
> So truncating a value to scale = 0 is actually appropriate if the scale is
> not mapped. Of course something like 'setBigDecimal' would probably preserve
> it, so maybe we still need a special ExtendedType to call this method.
I guess the problem I see is that I test only against one or two databases
so I'd rather see the *same* behaviour across databases regardless of what
JDBC is defined to do... We flipped scale="2" for all our NUMERIC types
since that is what our application assumes.
--
Øyvind Harboe
http://www.zylin.com/zy1000.html
ARM7 ARM9 XScale Cortex
JTAG debugger and flash programmer
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Andrus Adamchik <an...@objectstyle.org>.
On the other hand, maybe those drivers are following the spec. From
PreparedStatement javadocs:
"void setObject(int parameterIndex, Object x, int targetSqlType)
throws SQLException
Sets the value of the designated parameter with the given object. This
method is like the method setObject above, except that it assumes a
scale of zero."
So truncating a value to scale = 0 is actually appropriate if the
scale is not mapped. Of course something like 'setBigDecimal' would
probably preserve it, so maybe we still need a special ExtendedType to
call this method.
Andrus
On Aug 8, 2008, at 2:28 PM, Andrus Adamchik wrote:
> Here is a potential hole. By default (unless ExtendedType is
> explicitly defined for a given Java class), Cayenne does the
> following JDBC calls:
>
> PreparedStatement st = ..
> if (scale != -1) {
> st.setObject(pos, val, type, scale);
> }
> else {
> st.setObject(pos, val, type);
> }
>
> Looks correct from the JDBC standpoint, but from my experience some
> drivers may have it all wrong, and inconsistent with a corresponding
> 'setSpecificType(..)' method for a given object class. So what is
> the class of the object parameter? BigDecimal? Something else?
>
> Andrus
>
>
> On Aug 8, 2008, at 2:16 PM, Øyvind Harboe wrote:
>
>> On Fri, Aug 8, 2008 at 7:53 PM, Andrus Adamchik <andrus@objectstyle.org
>> > wrote:
>>> Cayenne doesn't do anything special on Derby vs. SQLServer in this
>>> respect.
>>
>> My thinking was that databases behaved differently here and that
>> Cayenne then either defines that the default behaviour is undefined
>> when scale is not specified or that Cayenne defines it to be the
>> same as whatever the database does.
>>
>>> Is this behavior happening for selected objects or when binding a
>>> query
>>> parameter?
>>
>> First I run an update w/a prepared statement(or rather Cayenne uses
>> prepared
>> statements for updates) and afterwards the queries return e.g. "1"
>> instead of "1.5".
>>
>> If I run a select statement from *outside* Cayenne, it reveals that
>> the data in
>> the database are actually truncated.
>>
>> Is there a testcase for this in Cayenne?
>>
>> --
>> Øyvind Harboe
>> http://www.zylin.com/zy1000.html
>> ARM7 ARM9 XScale Cortex
>> JTAG debugger and flash programmer
>>
>
>
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Andrus Adamchik <an...@objectstyle.org>.
Here is a potential hole. By default (unless ExtendedType is
explicitly defined for a given Java class), Cayenne does the following
JDBC calls:
PreparedStatement st = ..
if (scale != -1) {
st.setObject(pos, val, type, scale);
}
else {
st.setObject(pos, val, type);
}
Looks correct from the JDBC standpoint, but from my experience some
drivers may have it all wrong, and inconsistent with a corresponding
'setSpecificType(..)' method for a given object class. So what is the
class of the object parameter? BigDecimal? Something else?
Andrus
On Aug 8, 2008, at 2:16 PM, Øyvind Harboe wrote:
> On Fri, Aug 8, 2008 at 7:53 PM, Andrus Adamchik <andrus@objectstyle.org
> > wrote:
>> Cayenne doesn't do anything special on Derby vs. SQLServer in this
>> respect.
>
> My thinking was that databases behaved differently here and that
> Cayenne then either defines that the default behaviour is undefined
> when scale is not specified or that Cayenne defines it to be the
> same as whatever the database does.
>
>> Is this behavior happening for selected objects or when binding a
>> query
>> parameter?
>
> First I run an update w/a prepared statement(or rather Cayenne uses
> prepared
> statements for updates) and afterwards the queries return e.g. "1"
> instead of "1.5".
>
> If I run a select statement from *outside* Cayenne, it reveals that
> the data in
> the database are actually truncated.
>
> Is there a testcase for this in Cayenne?
>
> --
> Øyvind Harboe
> http://www.zylin.com/zy1000.html
> ARM7 ARM9 XScale Cortex
> JTAG debugger and flash programmer
>
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Øyvind Harboe <oy...@zylin.com>.
On Fri, Aug 8, 2008 at 7:53 PM, Andrus Adamchik <an...@objectstyle.org> wrote:
> Cayenne doesn't do anything special on Derby vs. SQLServer in this respect.
My thinking was that databases behaved differently here and that
Cayenne then either defines that the default behaviour is undefined
when scale is not specified or that Cayenne defines it to be the
same as whatever the database does.
> Is this behavior happening for selected objects or when binding a query
> parameter?
First I run an update w/a prepared statement(or rather Cayenne uses prepared
statements for updates) and afterwards the queries return e.g. "1"
instead of "1.5".
If I run a select statement from *outside* Cayenne, it reveals that the data in
the database are actually truncated.
Is there a testcase for this in Cayenne?
--
Øyvind Harboe
http://www.zylin.com/zy1000.html
ARM7 ARM9 XScale Cortex
JTAG debugger and flash programmer
Re: NUMERIC default scale behaves differently on Derby & SQL Server
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cayenne doesn't do anything special on Derby vs. SQLServer in this
respect. Is this behavior happening for selected objects or when
binding a query parameter?
Andrus
On Aug 8, 2008, at 4:20 AM, Øyvind Harboe wrote:
> If I set scale to blank (default) or -1, then Derby will round off
> down
> to nearest whole number whereas SQL Server will not.
>
> Is this intentional?
>
> --
> Øyvind Harboe
> http://www.zylin.com/zy1000.html
> ARM7 ARM9 XScale Cortex
> JTAG debugger and flash programmer
>