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 Be...@gmail.com on 2008/10/18 18:25:36 UTC
INTEGER size limit?
From http://db.apache.org/derby/docs/10.4/ref/rrefnumericlimits.htm
The largest INTEGER is 2,147,483,647.
ij>describe t1;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |NULL |NULL |YES
NUM |INTEGER |0 |10 |10 |NULL |NULL |YES
NAME |VARCHAR |NULL|NULL|128 |NULL |256 |YES
3 rows selected
ij> UPDATE t1 SET num=CASE WHEN num*2<2147483647 THEN num*2 ELSE 2147483647
END WHERE id>=0 AND id<=10;
ERROR 22003: The resulting value is outside the range for the data type
INTEGER.
ij> UPDATE t1 SET num=CAST(num*2 AS INTEGER) WHERE id>=0 AND id<=10;
ERROR 22003: The resulting value is outside the range for the data type
INTEGER.
ij> UPDATE t1 SET num=CAST(CAST(num AS BIGINT)*2 AS INTEGER) WHERE id>=0
AND id<=10;
ERROR 22003: The resulting value is outside the range for the data type
INTEGER.
I expected these queries to assign a value within the range of an INTEGER.
Where did I go wrong?
Thank you,
Ben
Re: INTEGER size limit?
Posted by Rick Hillegas <Ri...@Sun.COM>.
As Michael points out, you could replace the case statement with
something like this:
update t1 set num = doubleAndTruncate( num )
where you hide the overflow logic in a Java routine (doubleAndTruncate),
which you previously registered using CREATE FUNCTION.
Hope this helps,
-Rick
Derby Discussion wrote:
> Gee,
>
> I hate to point out the obvious, but what about using a try/catch block
> instead?
>
> Ok, here's the reasoning.
>
> The current proposed solution is to store the calculation result in a
> BIGINT. What happens if you want to do this using BIGINTs? What's the next
> size larger? ;-)
>
> The point is that if you know or suspect that you're going to hit a limit,
> you may want to take a step back, be less fancy and use an UPDATE CURSOR
> (Updateable cursor) and loop through the data and update the row based on
> the value being calculated.
>
> So instead of writing a query and then executing it, you write a simple java
> routine that selects the data, and for each row, tries the calculation in a
> try/catch block, catching the right exception and setting the update value.
> Then using the prepared statement you update the value where current of the
> cursor being used in the fetch.
>
> I realize that this is being old fashioned, but if you consider your
> environment, its going to be fairly efficient. If you're working in a
> networked environment and this isn't a single use code, you could write this
> as a stored procedure (assuming that you can write Java Stored Procedures
> against JAVA DB/DERBY/Cloudscape ...)
>
> This would make the code more flexible and maintainable.
>
> I'm sorry I'm not provided a code example. I believe that there are enough
> SUN and IBM boffins on the list who can explain more about this.
>
> Thx
>
> -Mike
>
>
>
>> -----Original Message-----
>> From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM]
>> Sent: Tuesday, October 21, 2008 2:45 AM
>> To: Derby Discussion
>> Subject: Re: INTEGER size limit?
>>
>> BenCollver@gmail.com writes:
>>
>>
>>> From http://db.apache.org/derby/docs/10.4/ref/rrefnumericlimits.htm
>>> The largest INTEGER is 2,147,483,647.
>>>
>>> ij>describe t1;
>>> COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
>>> ------------------------------------------------------------------------
>>>
>> ------
>>
>>> ID |INTEGER |0 |10 |10 |NULL |NULL |YES
>>> NUM |INTEGER |0 |10 |10 |NULL |NULL |YES
>>> NAME |VARCHAR |NULL|NULL|128 |NULL |256 |YES
>>>
>>> 3 rows selected
>>>
>>> ij> UPDATE t1 SET num=CASE WHEN num*2<2147483647 THEN num*2 ELSE
>>>
>> 2147483647
>>
>>> END WHERE id>=0 AND id<=10;
>>> ERROR 22003: The resulting value is outside the range for the data type
>>> INTEGER.
>>>
>> Hi Ben,
>>
>> You get an integer overflow in num*2<2147483647. Try this instead to use
>> bigint arithmetic:
>>
>> UPDATE t1 SET num=CASE
>> WHEN CAST(num AS BIGINT)*2<2147483647
>> THEN num*2
>> ELSE
>> 2147483647
>> END
>> WHERE id>=0 AND id<=10;
>>
>> Hope this helps.
>>
>> --
>> Knut Anders
>>
>
>
>
RE: INTEGER size limit?
Posted by Derby Discussion <de...@db.apache.org>.
Gee,
I hate to point out the obvious, but what about using a try/catch block
instead?
Ok, here's the reasoning.
The current proposed solution is to store the calculation result in a
BIGINT. What happens if you want to do this using BIGINTs? What's the next
size larger? ;-)
The point is that if you know or suspect that you're going to hit a limit,
you may want to take a step back, be less fancy and use an UPDATE CURSOR
(Updateable cursor) and loop through the data and update the row based on
the value being calculated.
So instead of writing a query and then executing it, you write a simple java
routine that selects the data, and for each row, tries the calculation in a
try/catch block, catching the right exception and setting the update value.
Then using the prepared statement you update the value where current of the
cursor being used in the fetch.
I realize that this is being old fashioned, but if you consider your
environment, its going to be fairly efficient. If you're working in a
networked environment and this isn't a single use code, you could write this
as a stored procedure (assuming that you can write Java Stored Procedures
against JAVA DB/DERBY/Cloudscape ...)
This would make the code more flexible and maintainable.
I'm sorry I'm not provided a code example. I believe that there are enough
SUN and IBM boffins on the list who can explain more about this.
Thx
-Mike
> -----Original Message-----
> From: Knut.Hatlen@Sun.COM [mailto:Knut.Hatlen@Sun.COM]
> Sent: Tuesday, October 21, 2008 2:45 AM
> To: Derby Discussion
> Subject: Re: INTEGER size limit?
>
> BenCollver@gmail.com writes:
>
> > From http://db.apache.org/derby/docs/10.4/ref/rrefnumericlimits.htm
> > The largest INTEGER is 2,147,483,647.
> >
> > ij>describe t1;
> > COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> > ------------------------------------------------------------------------
> ------
> > ID |INTEGER |0 |10 |10 |NULL |NULL |YES
> > NUM |INTEGER |0 |10 |10 |NULL |NULL |YES
> > NAME |VARCHAR |NULL|NULL|128 |NULL |256 |YES
> >
> > 3 rows selected
> >
> > ij> UPDATE t1 SET num=CASE WHEN num*2<2147483647 THEN num*2 ELSE
> 2147483647
> > END WHERE id>=0 AND id<=10;
> > ERROR 22003: The resulting value is outside the range for the data type
> > INTEGER.
>
> Hi Ben,
>
> You get an integer overflow in num*2<2147483647. Try this instead to use
> bigint arithmetic:
>
> UPDATE t1 SET num=CASE
> WHEN CAST(num AS BIGINT)*2<2147483647
> THEN num*2
> ELSE
> 2147483647
> END
> WHERE id>=0 AND id<=10;
>
> Hope this helps.
>
> --
> Knut Anders
Re: INTEGER size limit?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
BenCollver@gmail.com writes:
> From http://db.apache.org/derby/docs/10.4/ref/rrefnumericlimits.htm
> The largest INTEGER is 2,147,483,647.
>
> ij>describe t1;
> COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
> ------------------------------------------------------------------------------
> ID |INTEGER |0 |10 |10 |NULL |NULL |YES
> NUM |INTEGER |0 |10 |10 |NULL |NULL |YES
> NAME |VARCHAR |NULL|NULL|128 |NULL |256 |YES
>
> 3 rows selected
>
> ij> UPDATE t1 SET num=CASE WHEN num*2<2147483647 THEN num*2 ELSE 2147483647
> END WHERE id>=0 AND id<=10;
> ERROR 22003: The resulting value is outside the range for the data type
> INTEGER.
Hi Ben,
You get an integer overflow in num*2<2147483647. Try this instead to use
bigint arithmetic:
UPDATE t1 SET num=CASE
WHEN CAST(num AS BIGINT)*2<2147483647
THEN num*2
ELSE
2147483647
END
WHERE id>=0 AND id<=10;
Hope this helps.
--
Knut Anders