Microsoft KB Archive/818504

= FIX: JDBC Driver Overwrites Timestamp Parameter Values with the Last Value Set =

Article ID: 818504

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Driver for JDBC

-



SYMPTOMS
When the Microsoft SQL Server 2000 Driver for JDBC is used to set values for multiple Timestamp parameters that are bound to the same Timestamp object, the driver overwrites all such values with the value set for the last Timestamp parameter on execution of the query.



CAUSE
A bug in the driver causes this problem. Because the same bound object is used for all the parameters, the driver internally overwrites the previous value. This bug also affects Time parameters.



RESOLUTION
A supported fix is now available from Microsoft, but it is only intended to correct the problem described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing to further ensure product quality. Therefore, if you are not severely affected by this problem, Microsoft recommends that you wait for the next service pack that contains this hotfix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Note In special cases, charges that are ordinarily incurred for support calls may be canceled if a Microsoft Support Professional determines that a specific update will resolve your problem. The usual support costs will apply to additional support questions and issues that do not qualify for the specific update in question. The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel. The Windows version of this fix has the following properties:

Date        Time   Version            Size    File name --  02-Apr-2003  17:44  2.2.0033          286,787  Msbase.jar 02-Apr-2003 17:44  2.2.0033           67,040  Mssqlserver.jar 02-Apr-2003 17:44  2.2.0033           58,903  Msutil.jar

The Unix-based version of this fix has the following properties:

Date        Time   Version            Size    File name --  08-Apr-2003  18:53  2.2.0033          286,787  Msbase.jar 08-Apr-2003 18:53  2.2.0033           67,040  Mssqlserver.jar 08-Apr-2003 18:53  2.2.0033           58,903  Msutil.jar Note Although the file dates are different for each operating system that is listed, internally the files are exactly the same. The best way to determine the version of the driver you are using is to use the getDriverVersion method of the DatabaseMetaData interface.



WORKAROUND
Bind each parameter to a new object instead of using the same object for all the parameters. In some cases ths may not work around the bug, specifically if you are using any wrapper classes for the JDBC API. Internally those classes re-use the same object for setting the values for different parameters.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Note Do not use this fix with the Microsoft virtual machine (Microsoft VM). If you experience this problem, and you must have a fix that works with virtual machine, contact Microsoft Product Support Services as described in the &quot;Resolution&quot; section of this article.



MORE INFORMATION
Though the following steps show how to reproduce the problem with Timestamp parameters by using the setTimeStamp method of the PreparedStatement, this bug can also surface with the Time parameters where the values are set with the setTime method of the PreparedStatement. 

Steps to Reproduce the Problem
  From SQL Query Analyzer, create the following table, and insert different values for each column: CREATE TABLE Test (Date1 DATETIME, Date2 DATETIME, Date3 DATETIME) INSERT INTO Test VALUES ('2001-JAN-01', '2002-FEB-02', '2003-MAR-03 11:45') Note the values that you just inserted: SELECT * FROM Test   Compile, and then run the following Java code.

Note You must replace the server name, user id, and password in the connection string as appropriate for your environment. import java.util.*; import java.sql.*;

public class UpdateDate {   public static void main (String args []) {

try {

Class.forName(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot; ).newInstance;

Connection connection1 = DriverManager.getConnection(&quot;jdbc:sqlserver://myServer:1433;databaseName=myDatabase&quot;,&quot;userid&quot;,&quot;password&quot;); //Set parameter index int parmIndex = 1;

//Initialize a Timestamp object Timestamp ts = new Timestamp(new java.util.Date.getTime); PreparedStatement updStmt = null; String UPDSTMT = &quot;UPDATE Test SET Date1 = ?, Date2 = ?, Date3 = ?&quot;; try {               updStmt = connection1.prepareStatement(UPDSTMT); //setTime for the first parameter by using the ts timestamp object ts.setTime(100); updStmt.setTimestamp(parmIndex++, ts); //set a different time for the second parameter by using the same ts timestamp object ts.setTime(2000); updStmt.setTimestamp(parmIndex++, ts); //set a different time for the third parameter by using the same ts timestamp object ts.setTime(30000); updStmt.setTimestamp(parmIndex++, ts); updStmt.execute; }           catch (SQLException e)              { System.out.println(e.getMessage); }           connection1.close; }       catch(Exception e)        { System.out.println(&quot; Exception = &quot; + e ); }

return; }

}   Query the table from Query Analyzer: SELECT * FROM Test Notice that all the 3 datetime columns have the same value as the last datetime column. 

To work around the problem, set the value for each parameter by using a new Timestamp object as follows: updStmt.setTimestamp(parmIndex++, new Timestamp(100)); updStmt.setTimestamp(parmIndex++, new Timestamp(2000)); updStmt.setTimestamp(parmIndex++, new Timestamp(30000));

Additional query words: setDate java type4

Keywords: kbhotfixserver kbqfe kbqfe kbfix kbbug KB818504

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.