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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 21, 2008 2:45 AM > To: Derby Discussion > Subject: Re: INTEGER size limit? > > [EMAIL PROTECTED] 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
