db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: INTEGER size limit?
Date Tue, 21 Oct 2008 15:24:44 GMT
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
>>     
>
>
>   


Mime
View raw message