Why are you doing this anyway. A prepared statement by it's nature suggests the types are fixed ?
This was noticed as part of one of database copying pieces of code, that dynamically changes what it does based on what databases and tables it is copying from and to (we primarily support Oracle / MySQL / PostgreSQL), basically it continually syncs data / changes / updates data between multiple database servers and multiple database technologies (trying to use each database to each of their strengths).
The majority of the code just uses setObject() when writing to the destination database using the field type picked up from the metadata of the source database (rather than the type from the destination database), we found this works best on "most" databases. However as we support timezones (if enabled) we always use setDate() or setTimestamp() if we are writing dates / timestamps respectively. This issue originated when copying from Oracle, as most Oracle JDBC drivers show a type of "Date" for their date field which of course can hold a time component: It is because of this that this scenario occurred, e.g. we were using setTimestamp() but for null fields these just use our standard setObject() code that uses the source database type for that field. Thats the background in case you were actually interested! :-)
We have a simple workaround in place to ensure we always use the correct type in this and similar scenarios, I was just reporting in case it affected anyone else in the future / or if the PostgreSQL community felt that this was worth protecting against in the future.
Anyway, thats why I did question in my first email whether you might just classify this as "misuse" of PreparedStatement re-use, rather than a bug, it was just an odd issue, but I agree it is a fairly contrived example!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DISCLAIMER: This email message and any attachments is for the sole
use of the intended recipient(s) and may contain confidential and
privileged information. Any unauthorised review, use, disclosure
or distribution is prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of
the original message.
The views expressed in this message may not necessarily reflect the
views of Bluestar Software Ltd.
Bluestar Software Ltd, Registered in England
Company Registration No. 03537860, VAT No. 709 2751 29
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
