Re: PreparedStatement parameters and mutable objects

Dave Cramer wrote:
> Well there is some hint that this is incorrect:
>
>
> pstmnt = con.prepareStatment("insert into foo values(?,?)");
>
> pstmnt.setString(1,"abcd");
>
> for( i=0; i< 5; i++)
> {
> Integer intx = new Integer(i);
> pstmnt.setInt(2,intx)
> pstmnt.executeUpdate();
> }
>
> The above code should insert ("abcd", 0 .. 4 ) into five rows
>
> The point being that the value for setXXX does not change until setXXX
> is called.

Well, this is a different case -- Integer is immutable, so when the
driver transforms it to a DB representation is irrelevant. (I assume you
mean setObject(), above, since setInt() expects a primitive int..).

Even if the implication of the above code is that a parameter doesn't
change from what you last set it to on execution, that doesn't really
help us answer the question .. so you don't set it, and have the same
mutable object reference, but that's no different to the case where you
set it to a new mutable object reference .. it could still mutate before
execution.

If we were using e.g. setBytes() the analogous case is something like this:

byte[] data = new byte[10];
// (1)
for (int i = 0; i < 5; ++i) {
data[0] = i;
pstmt.setBytes(2, data); // (2)
pstmt.executeUpdate();
}

If binding was guaranteed to happen at execution you could get away with
a setBytes() at (1) and remove (2). But I think the above code is the
only way to be safe under all drivers.

The above case is relatively boring as 'data' does not change between
setBytes() and executeUpdate() anyway. More interesting is this one..

byte[] data = new byte[10];
for (int i = 0; i < 5; ++i) {
data[0] = i;
pstmt.setBytes(2, data);
pstmt.addBatch();
}
pstmt.executeBatch();

I think this is the case that is more likely to bite us. "set
parameters, modify mutable objects, execute" seems like an unlikely
pattern to use in real code. "reuse mutable objects, set parameters, add
to batch, loop" seems more likely.

I'm still in favour of an "undefined behaviour" interpretation here.
There's not much benefit to application code in nailing down one
behaviour or the other, and leaving it undefined gives the driver the
flexibility to do whichever is a better implementation for the DB in
question.

I might send a query to the JDBC expert group about this. Anyone have a
better contact address than the spec feedback address? (the Sun
JDBC-INTEREST list seems to be pretty useless, it seems to be the "where
can I find an oracle driver?" mailing list)

-O