BigDecimal to SQL NUMBER: check for value larger than precision

Question

In my app, I handle numbers as BigDecimal and store them as NUMBER(15,5). Now I'd need to properly check on Java if the BigDecimal values would fit the column, so that I can generate proper error messages without executing the SQL, catching exceptions and verifying the vendor error code. My database is Oracle 10.3, and such errors cause error 1438.

After some googling, I found no such code for that, so I came up with my own. But I'm really unsatisfied with this code... simple, but at the same time simple enough to doubt its correctness. I tested it with many values, random ones and boundaries, and it seems to work. But as I'm really bad with numbers, I'd like some more robust and well-tested code.

//no constants for easier reading
public boolean testBigDecimal(BigDecimal value) {
    if (value.scale() > 5)
        return false;
    else if (value.precision() - value.scale() > 15 - 5)
        return false;
    else
        return true;
}

Edit: Recent tests did not got an exception for numbers out of scale, just got silently rounded, and I'm not sure what is different between not and when I made these first tests. Such rounding is unacceptable because the application is financial, and any rounding/truncation must be explicit (through BigDecimal methods). Exception-is-gone aside, this test method must assure that the number is not too large for the desired precision, even if by non-significant digits. Sorry about the late clarification.

Thanks for your time.

---

I'm still curious about this question. My code is still running, and I haven't got some "proof" of correctness or fail situation, or some standard code for this kind of test.

So, I'm putting a bounty on it, hopefully getting any of these.

Answer 1

The following regexp would do the trick too:

public class Big {
    private static final Pattern p = Pattern.compile("[0-9]{0,10}(\\.[0-9]{0,5}){0,1}");

    public static void main(String[] args) {
        BigDecimal b = new BigDecimal("123123.12321");
        Matcher m = p.matcher(b.toString());
        System.out.println(b.toString() + " is valid = " + m.matches());
    }
}

This could be another way to test your code or it could be the code. The regexp requires between 0 and 10 digits optionally followed by a decimal point and 0 to 5 more digits. I didn't know if a sign was needed or not, as I think about it. Tacking something like [+-]{0,1} to the front will do.

Here is a better class, maybe, and a test class with a partial set of tests.

public class Big {
    private static final Pattern p = Pattern.compile("[0-9]{0,10}(\\.[0-9]{0,5}){0,1}");

public static boolean isValid(String s) {
    BigDecimal b = new BigDecimal(s);
    Matcher m = p.matcher(b.toPlainString());
    return m.matches();
    }
}

---

package thop;

import junit.framework.TestCase;

/**
 * Created by IntelliJ IDEA.
 * User: tonyennis
 * Date: Sep 22, 2010
 * Time: 6:01:15 PM
 * To change this template use File | Settings | File Templates.
 */
public class BigTest extends TestCase {

    public void testZero1() {
        assertTrue(Big.isValid("0"));
    }

    public void testZero2() {
        assertTrue(Big.isValid("0."));
    }

    public void testZero3() {
        assertTrue(Big.isValid("0.0"));
    }

    public void testZero4() {
        assertTrue(Big.isValid(".0"));
    }

    public void testTooMuchLeftSide() {
        assertFalse(Big.isValid("12345678901.0"));
    }

    public void testMaxLeftSide() {
        assertTrue(Big.isValid("1234567890.0"));
    }

    public void testMaxLeftSide2() {
        assertTrue(Big.isValid("000001234567890.0"));
    }

    public void testTooMuchScale() {
        assertFalse(Big.isValid("0.123456"));
    }

    public void testScientificNotation1() {
        assertTrue(Big.isValid("123.45e-1"));
    }

    public void testScientificNotation2() {
        assertTrue(Big.isValid("12e4"));
    }
}

Answer 2

one of the problems with your function is that in some cases it may be too restrictive, consider:

BigDecimal a = new BigDecimal("0.000005"); /* scale 6 */
a = a.multiply(new BigDecimal("2")); /* 0.000010 */
return testBigDecimal(a); /* returns false */

As you can see, the scale is not adjusted down. I can't test right now if something similar happens with high-end precision (1e11/2).

I would suggest a more direct route:

public boolean testBigDecimal(BigDecimal value) {
    BigDecimal sqlScale = new BigDecimal(100000);
    BigDecimal sqlPrecision = new BigDecimal("10000000000");
    /* check that value * 1e5 is an integer */
    if (value.multiply(sqlScale)
          .compareTo(value.multiply(sqlScale)
              .setScale(0,BigDecimal.ROUND_UP)) != 0)
        return false;
    /* check that |value| < 1e10 */
    else if (value.abs().compareTo(sqlPrecision) >= 0)
        return false;
    else
        return true;
}

---

Update

You've asked in a comment if the database would throw an error if we try to insert 0.000010. In fact the database will never throw an error if you try to insert a value with too much precision, it will silently round the inserted value.

The first check is therefore not needed to avoid an Oracle error, I was assuming that you were performing this test to make sure that the value you want to insert is equal to the value you actually inserted. Since 0.000010 and 0.00001 are equal (with BigDecimal.compareTo) shouldn't they both return the same result?

Answer 3

Instead if looping over thousands of random numbers, you could write test cases that stress the 'edges' - the maximum value +.00001, the maximum value, the maximum value - .00001, 0, null, the minimum value -.00001, the minimum value, the minimum value + .00001, and values with 4, 5, and 6 values to the right of the decimal point. There are probably many more.

If you have those in junit, you're good.

Answer 4

Well, since nobody came up with another solution, I'm leaving the code as it is.

I couldn't make this precision/scale test fail, and it always matched the regex solution, so maybe both are correct (I tested the boundaries and with over 5M randomly generated values). I'll use the precision/scale solution, as it is over 85% faster, and may it fail I replace it.

Thanks for your replies Tony.

---

My previous "answer", still here for history purposes, but I'm looking for a real answer =)