Change datatype of a sequence H2DB

Question

I need to change the returned value of a sequence stored into a H2DB, when i call nextVal through a direct SQL query H2 return a BigInt and i need a BigDecimal.

I can't cast or convert this value, I need H2 returning a BigDecimal.

How I can do that?

EDIT: I can't change the Java code beacuse I'm testing so cast or convert the request value from DB is not a option.

Answer 1

You could create you own patched version of H2, if you are allowed to replace the H2 jar file.

In org.h2.expression.Function change

    addFunctionNotDeterministic("NEXTVAL", NEXTVAL,
            VAR_ARGS, Value.LONG); 

to

    addFunctionNotDeterministic("NEXTVAL", NEXTVAL,
            VAR_ARGS, Value.DECIMAL);

and in org.h2.expression.SequenceValue change

@Override
public Value getValue(Session session) {
    long value = sequence.getNext(session);
    session.setLastIdentity(ValueLong.get(value));
    return ValueLong.get(value);
}

@Override
public int getType() {
    return Value.LONG;
} 

to

@Override
public Value getValue(Session session) {
    long lv = sequence.getNext(session);
    ValueDecimal value = ValueDecimal.get(BigDecimal.valueOf(lv)); 
    session.setLastIdentity(value);
    return value;
}

@Override
public int getType() {
    return Value.DECIMAL;
} 

Answer 2

I've tried to take the code from wero's answer and make this into a feature in H2 itself.

This GitHub fork: https://github.com/portofrotterdam/h2database returns BigDecimals instead of BigInteger/longs when using 'MODE=Oracle' in H2, making it more compatible with Oracle's database.

I've requested a pull to the master so perhaps this behaviour will be available in h2database.