db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Derby Discussion" <derby-u...@db.apache.org>
Subject RE: INTEGER size limit?
Date Tue, 21 Oct 2008 14:50:21 GMT
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