Re: Using BigInteger as argument to AbstractJdbc2Statement.setObject

On Thu, 6 Aug 2009, Sylvain Leroux wrote:

>> Selecting a target type based on the magnitude of the parameter value
>> passed may work better; then at least you get obvious behavior for cases
>> where the value can fit in an integer.
>
> That's what I have done now: setBigInteger will choose the narrowest integer
> representation. NUMERIC will still be used when no integer type is wide
> enough to hold the value.
>

I don't think this is a great idea.  Changing behavior based on the value
of the parameter will just lead to unpredictable behavior as the size
boundaries are crossed.  It also seems like this design is based upon the
fact that Jython uses BigInteger for all integer sizes, but that seems
like the exception rather than the rule.  Most users of BigIntegers will
really have large values.

> As a side note, MySQL JDBC Driver accepts BigInteger in a setObject call too.
> But it takes less care of them, since they are simply mapped to strings:
>>         public void setObject(int parameterIndex, Object parameterObj)
>>                         throws SQLException {
>>     ...
>>     ...
>>                         } else if (parameterObj instanceof BigInteger) {
>>                                 setString(parameterIndex,
>> parameterObj.toString());
>

That seems like a reasonable behavior if it's equivalent to what we could
do indicating that the value was of unknown type and letting the server
figure it out.

In my mind there are a couple of possible options:

1) Always map BigInteger -> numeric.  Maybe not the most obvious mapping,
but it's predictable for every single value and is a number type.  The
downside of this mapping is that numeric doesn't fit into the integer
number comparison family of operators for indexes, so something like the
following wouldn't use an index.

SELECT * FROM tab WHERE int4col = '3'::numeric.

2) Map BigInteger -> int8 for everything that fits in int8 and numeric
otherwise.  While this is behavior dependent on size, it avoids the
downside of #1 and doesn't change multiple times.

3) Map BigInteger -> unknown.  Will work most of the time for any type
comparison.  The downside is that when the server can't figure out the
type, the user can't hint it via the BigInteger class.

Personally I would lean towards #3.

Kris Jurka