Thread: Using BigInteger as argument to AbstractJdbc2Statement.setObject
Hi,
I am using Jython to access a PostgreSQL database throught the JDBC driver.
I have a problem with a code fragment like that:
> stmt = self.conn.prepareStatement("INSERT INTO campagne(id) VALUES(?)")
> stmt.setObject(1, 123456899999)
Of course, in the real code, the value 123456899999 is not hard wired.
Anyway, Jython ``autobox'' such values into a java.math.BigInteger object.
But AbstractJdbc2Statement.setObject does not handle that kind of
object. Which leads to the error:
> stmt.setObject(1, 123456899999)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1735)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:597)
>
> org.postgresql.util.PSQLException: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance
of
> java.math.BigInteger. Use setObject() with an explicit Types value to specify the type to use.
Since the column campagne(id) is a NUMERIC, as the error message
suggests, I could write something like that instead:
> stmt.setObject(1, 123456899999, Types.NUMERIC)
But this defeats the purpose of using a scripting language both for
simplicity and dynamic typing.
Moreover other scripting languages for the Java platform may exhibit the
same behavior. So, it would be great if we have support for passing
BigInteger as argument to the setObject method in prepared statements.
Even if it's not required by the JDBC API.
Best of all, the patch is trivial as you will see: I send the result of
a ``cvs diff -u8pN'' as an attachment.
Best regards,
Sylvain Leroux
--
Website: http://www.chicoree.fr
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.114
diff -u -8 -p -r1.114 AbstractJdbc2Statement.java
--- org/postgresql/jdbc2/AbstractJdbc2Statement.java 27 May 2009 23:55:19 -0000 1.114
+++ org/postgresql/jdbc2/AbstractJdbc2Statement.java 5 Aug 2009 09:30:00 -0000
@@ -1224,16 +1224,36 @@ public abstract class AbstractJdbc2State
*/
public void setDouble(int parameterIndex, double x) throws SQLException
{
checkClosed();
bindLiteral(parameterIndex, Double.toString(x), Oid.FLOAT8);
}
/*
+ * Set a parameter to a java.lang.BigInteger value. The driver
+ * converts this to a SQL NUMERIC value when it sends it to the
+ * database.
+ *
+ * <b>This is an extension to the JDBC API!</b>
+ *
+ * @param parameterIndex the first parameter is 1...
+ * @param x the parameter value
+ * @exception SQLException if a database access error occurs
+ */
+ public void setBigInteger(int parameterIndex, BigInteger x) throws SQLException
+ {
+ checkClosed();
+ if (x == null)
+ setNull(parameterIndex, Types.DECIMAL);
+ else
+ bindLiteral(parameterIndex, x.toString(), Oid.NUMERIC);
+ }
+
+ /*
* Set a parameter to a java.lang.BigDecimal value. The driver
* converts this to a SQL NUMERIC value when it sends it to the
* database.
*
* @param parameterIndex the first parameter is 1...
* @param x the parameter value
* @exception SQLException if a database access error occurs
*/
@@ -1731,16 +1751,18 @@ public abstract class AbstractJdbc2State
{
checkClosed();
if (x == null)
setNull(parameterIndex, Types.OTHER);
else if (x instanceof String)
setString(parameterIndex, (String)x);
else if (x instanceof BigDecimal)
setBigDecimal(parameterIndex, (BigDecimal)x);
+ else if (x instanceof BigInteger)
+ setBigInteger(parameterIndex, (BigInteger)x);
else if (x instanceof Short)
setShort(parameterIndex, ((Short)x).shortValue());
else if (x instanceof Integer)
setInt(parameterIndex, ((Integer)x).intValue());
else if (x instanceof Long)
setLong(parameterIndex, ((Long)x).longValue());
else if (x instanceof Float)
setFloat(parameterIndex, ((Float)x).floatValue());
Sylvain Leroux wrote: > Moreover other scripting languages for the Java platform may exhibit the > same behavior. So, it would be great if we have support for passing > BigInteger as argument to the setObject method in prepared statements. > Even if it's not required by the JDBC API. > > Best of all, the patch is trivial as you will see: I send the result of > a ``cvs diff -u8pN'' as an attachment. Why NUMERIC instead of an integer type? Might as well make setBigInteger() private if you're not also going to expose it on PGStatement (I don't think it needs to be exposed there) Incidentally, the JDBC spec does provide a standard mapping from BigDecimal (not BigInteger) to NUMERIC. -O
Hi, and first of all, thanks for your answer. Oliver Jowett a écrit : > Why NUMERIC instead of an integer type? > > Might as well make setBigInteger() private if you're not also going to > expose it on PGStatement (I don't think it needs to be exposed there) According to the JDK doc, java.math.BigInteger provides "arbitrary-precision integers". The closest match will be NUMERIC since it allows up to 1000 digits. As far as I know, the integer types have much narrower range. *Or* could it be required to inspect the BigInteger in order to use the most appropriate type? But, I don't see any benefits here: it appears not to be a problem to send a NUMERIC value for an INTEGER column - as far as the value is in the supported range. Concerning the visibility of setBigInteger(), you're perfectly right: it should be private [I must admit it was a (too) quick-and-dirty patch - mostly a copy of setBigDecimal - sorry ;) ] > > Incidentally, the JDBC spec does provide a standard mapping from > BigDecimal (not BigInteger) to NUMERIC. You are right here too: I've double checked the JDBC documentation. There's no mention of the BigInteger type - whereas the BigDecimal type is supported. I could have lived with that, but Jython maps values out of the Python integer range to BigInteger. Not BigDecimal. Which is perfectly legitimate. Moreover, I have done some tests with Groovy. It has somewhat the same behavior and wraps literal integers that exceed the ''long'' range in a BigInteger. So this could be definitively a concern for using prepared statements with scripting languages. As an attachment is a modified version of the patch - with setBigInteger set private. Sylvain -- Website: http://www.chicoree.fr Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java =================================================================== RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v retrieving revision 1.114 diff -u -8 -p -r1.114 AbstractJdbc2Statement.java --- org/postgresql/jdbc2/AbstractJdbc2Statement.java 27 May 2009 23:55:19 -0000 1.114 +++ org/postgresql/jdbc2/AbstractJdbc2Statement.java 5 Aug 2009 12:29:20 -0000 @@ -1224,16 +1224,36 @@ public abstract class AbstractJdbc2State */ public void setDouble(int parameterIndex, double x) throws SQLException { checkClosed(); bindLiteral(parameterIndex, Double.toString(x), Oid.FLOAT8); } /* + * Set a parameter to a java.lang.BigInteger value. The driver + * converts this to a SQL NUMERIC value when it sends it to the + * database. + * + * <b>This is an extension to the JDBC API!</b> + * + * @param parameterIndex the first parameter is 1... + * @param x the parameter value + * @exception SQLException if a database access error occurs + */ + private void setBigInteger(int parameterIndex, BigInteger x) throws SQLException + { + checkClosed(); + if (x == null) + setNull(parameterIndex, Types.DECIMAL); + else + bindLiteral(parameterIndex, x.toString(), Oid.NUMERIC); + } + + /* * Set a parameter to a java.lang.BigDecimal value. The driver * converts this to a SQL NUMERIC value when it sends it to the * database. * * @param parameterIndex the first parameter is 1... * @param x the parameter value * @exception SQLException if a database access error occurs */ @@ -1731,16 +1751,18 @@ public abstract class AbstractJdbc2State { checkClosed(); if (x == null) setNull(parameterIndex, Types.OTHER); else if (x instanceof String) setString(parameterIndex, (String)x); else if (x instanceof BigDecimal) setBigDecimal(parameterIndex, (BigDecimal)x); + else if (x instanceof BigInteger) + setBigInteger(parameterIndex, (BigInteger)x); else if (x instanceof Short) setShort(parameterIndex, ((Short)x).shortValue()); else if (x instanceof Integer) setInt(parameterIndex, ((Integer)x).intValue()); else if (x instanceof Long) setLong(parameterIndex, ((Long)x).longValue()); else if (x instanceof Float) setFloat(parameterIndex, ((Float)x).floatValue());
Sylvain Leroux wrote: > Hi, > > and first of all, thanks for your answer. > > Oliver Jowett a écrit : >> Why NUMERIC instead of an integer type? >> >> Might as well make setBigInteger() private if you're not also going to >> expose it on PGStatement (I don't think it needs to be exposed there) > According to the JDK doc, java.math.BigInteger provides > "arbitrary-precision integers". > The closest match will be NUMERIC since it allows up to 1000 digits. As > far as I know, the integer types have much narrower range. > > *Or* could it be required to inspect the BigInteger in order to use the > most appropriate type? But, I don't see any benefits here: it appears > not to be a problem to send a NUMERIC value for an INTEGER column - as > far as the value is in the supported range. But there's no implicit cast from NUMERIC to integer types, is there? (It'd lose precision) > oliver=# CREATE OR REPLACE FUNCTION test_numeric(int4) RETURNS int4 AS 'SELECT $1' LANGUAGE SQL; > CREATE FUNCTION > oliver=# PREPARE s1(int4) AS SELECT test_numeric($1); > PREPARE > oliver=# PREPARE s2(numeric) AS SELECT test_numeric($1); > ERROR: function test_numeric(numeric) does not exist > LINE 1: PREPARE s2(numeric) AS SELECT test_numeric($1); > ^ > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > So my concern with mapping Jython integer value -> BigInteger -> NUMERIC is that you end up with a statement parameter that's not actually an integer, and so "stmt.setObject(1,1234567899999)" will fail in cases where you would expect an integer value to work. 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. -O
> So my concern with mapping Jython integer value -> BigInteger -> NUMERIC
> is that you end up with a statement parameter that's not actually an
> integer, and so "stmt.setObject(1,1234567899999)" will fail in cases
> where you would expect an integer value to work.
Ok, I understand.
>
> 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.
With this, I think we have coherent behavior between Jython and psql.
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());
Sylvain
--
Website: http://www.chicoree.fr
Index: org/postgresql/jdbc2/AbstractJdbc2Statement.java
===================================================================
RCS file: /cvsroot/jdbc/pgjdbc/org/postgresql/jdbc2/AbstractJdbc2Statement.java,v
retrieving revision 1.114
diff -u -r1.114 AbstractJdbc2Statement.java
--- org/postgresql/jdbc2/AbstractJdbc2Statement.java 27 May 2009 23:55:19 -0000 1.114
+++ org/postgresql/jdbc2/AbstractJdbc2Statement.java 6 Aug 2009 13:11:03 -0000
@@ -1229,6 +1229,35 @@
}
/*
+ * Set a parameter to a java.lang.BigInteger value. The driver
+ * converts this to the narrowest integer representation available
+ * when it sends it to the database.
+ *
+ * <b>This is an extension to the JDBC API!</b>
+ *
+ * @param parameterIndex the first parameter is 1...
+ * @param x the parameter value
+ * @exception SQLException if a database access error occurs
+ */
+ private void setBigInteger(int parameterIndex, BigInteger x) throws SQLException
+ {
+ checkClosed();
+ if (x == null)
+ setNull(parameterIndex, Types.DECIMAL);
+ else {
+ int bl = x.bitLength();
+ if (bl < 16)
+ bindLiteral(parameterIndex, x.toString(), Oid.INT2);
+ else if (bl < 32)
+ bindLiteral(parameterIndex, x.toString(), Oid.INT4);
+ else if (bl < 64)
+ bindLiteral(parameterIndex, x.toString(), Oid.INT8);
+ else
+ bindLiteral(parameterIndex, x.toString(), Oid.NUMERIC);
+ }
+ }
+
+ /*
* Set a parameter to a java.lang.BigDecimal value. The driver
* converts this to a SQL NUMERIC value when it sends it to the
* database.
@@ -1736,6 +1765,8 @@
setString(parameterIndex, (String)x);
else if (x instanceof BigDecimal)
setBigDecimal(parameterIndex, (BigDecimal)x);
+ else if (x instanceof BigInteger)
+ setBigInteger(parameterIndex, (BigInteger)x);
else if (x instanceof Short)
setShort(parameterIndex, ((Short)x).shortValue());
else if (x instanceof Integer)
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
Kris Jurka <books@ejurka.com> writes:
> On Thu, 6 Aug 2009, Sylvain Leroux wrote:
>> 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.
For what it's worth, if you type a bare integer constant in a SQL
command, the backend will take it to be int4, int8, or numeric depending
on size. It's been that way for a long time and we've had few
complaints, in part because the set of implicit casts is designed
expecting that behavior. So I'm not sure that mirroring that behavior
on the JDBC side is a bad thing.
(However, if the proposed patch is also mixing int2 into the picture,
that *would* be a bad thing. Stick to int4 or larger.)
> 1) Always map BigInteger -> numeric.
This would break a lot of things. The backend's implicit casts are
designed to upcast int to numeric when context demands, but not the
reverse.
regards, tom lane