?
Solved

Bug in MS SQL Server 2005 JDBC 1.1 Drive when ResultSet.getDate() used on datetime/smalldatetime?

Posted on 2007-08-03
4
Medium Priority
?
571 Views
Last Modified: 2010-05-18
Hello, I have encountered what I suspect is a serious, data corrupting bug in MS SQL Server JDBC Driver 1.1 (downloaded from Microsoft 2-August-2007)? However, I find it hard to believe that I have found a bug, using a commonly used method, ResultSet.getDate on a datetime or smalldatetime column. The bug is that getDate changes the date by making the month = 0 and day = 1 (in Java terms ... in human terms it makes the month 1 and day 1). Below is my code. My question / request is to run this code using the MS SQL Server 2005 JDBC Driver 1.1 sqljdbc.jar http://www.microsoft.com/downloads/details.aspx?familyid=6d483869-816a-44cb-9787-a866235efc7c&displaylang=en
and paste in the result and whether or not you get the error. Thank you.

// START OF PROGRAM
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

//import java.sql.*;

/**
 * @author pd 7/31/07
 *
 * Demonstrate bug in MS SQL Server Driver 1.1 where ResultSet getDate
 * returns January 1 no matter what the date actual is.
 *
 */
public class TestSqlServerDates {

      public static void main(String[] args) {

      Connection connection=null;
      Statement statement=null;
      ResultSet rs=null;
     
            // Load the JDBC driver
            try {
                  Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         connection = DriverManager.getConnection("jdbc:sqlserver://localhost;database=yourdatabase", "user", "password");
            } catch (ClassNotFoundException e) {
                  System.out.println("Error loading JDBC driver: " + e.getMessage());
                  e.printStackTrace();
                  System.exit(0);
            } catch (SQLException e) {
         System.out.println("Error getting connection: " + e.getMessage());
         e.printStackTrace();
         System.exit(0);
      }

      // Create and load temporary table
      try {
         statement = connection.createStatement();
         statement.execute("CREATE TABLE #TestDates (seq INT PRIMARY KEY, date1 DATETIME, date2 SMALLDATETIME)");
         statement.executeUpdate("INSERT INTO #TestDates VALUES (1, '2007-09-30', '2007-10-31')");
      } catch (Exception e) {
         System.err.println("Exception: " + e.getMessage());
         e.printStackTrace();
      } finally {
      }
     
      // execute query
      try {
         statement = connection.createStatement();
         rs = statement.executeQuery("SELECT seq, date1, date2 FROM #TestDates");
         if (rs.next()) {
            int seq = rs.getInt("seq");
            String date1String = rs.getString("date1");
            Date date1GetDate = rs.getDate("date1");
            Date date1GetTimestamp = rs.getTimestamp("date1");
            Date date1GetObject = (Date) rs.getObject("date1");
            System.out.println("date1 datetime     " + "  getString: " + date1String + "  getDate: " + date1GetDate + "  getTimestamp: " + date1GetTimestamp + "  getObject: " + date1GetObject);
            Date checkDate1 = new Date(2007-1900, 9-1, 30);
            if (date1GetDate.compareTo(checkDate1)!=0) System.out.println("**** Method getDate returned invalid result *****");
            String date2String = rs.getString("date2");
            Date date2GetDate = rs.getDate("date2");
            Date date2GetTimestamp = rs.getTimestamp("date2");
            Date date2GetObject = (Date) rs.getObject("date2");
            System.out.println("date2 smalldatetime" + "  getString: " + date2String + "  getDate: " + date2GetDate + "  getTimestamp: " + date2GetTimestamp + "  getObject: " + date2GetObject);
            Date checkDate2 = new Date(2007-1900, 10-1, 31);
            if (date2GetDate.compareTo(checkDate2)!=0) System.out.println("**** Method getDate returned invalid result *****");
         } else {
            throw new RuntimeException("No rows returned");
         }
            } catch (Exception e) {
                  System.err.println("Exception: " + e.getMessage());
                  e.printStackTrace();
            } finally {
         // drop temporary table
         try {
            statement = connection.createStatement();
            statement.execute("DROP TABLE #TestDates");
         } catch (Exception e) {}
            }

      }
}
// END OF PROGRAM
0
Comment
Question by:pdegregorio
  • 2
3 Comments
 

Author Comment

by:pdegregorio
ID: 19629115
PS here's what I get
date1 datetime       getString: 2007-09-30 00:00:00.0  getDate: 2007-01-01  getTimestamp: 2007-09-30 00:00:00.0  getObject: 2007-09-30 00:00:00.0
**** Method getDate returned invalid result *****
date2 smalldatetime  getString: 2007-10-31 00:00:00.0  getDate: 2007-01-01  getTimestamp: 2007-10-31 00:00:00.0  getObject: 2007-10-31 00:00:00.0
**** Method getDate returned invalid result *****
0
 

Author Comment

by:pdegregorio
ID: 19632452
I found this on Microsoft forum ... http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=993838&SiteID=17
which explains that MS does not officially support the IBM JVM with version 1.1 but was expecting to issue a hotfix and also expects to support it with 1.2 version currently in test.

>Answer  Re: SQLServer 2005 JDBC Driver - Date rendered correctly on local environment(windows), >but not on Websphere server (unix)
>Hi all,
>Thank you for your feedback and patience on this issue.  We will be issuing a hotfix to the Microsoft >SQL Server 2005 JDBC Driver v1.1 to address this issue by working around certain behavior in the IBM >JVM that ships with Websphere.  Current planned availability of that hotfix is end of April.  Also, the >v1.2 CTP 1 planned for April will also include the fix.  Please note that Microsoft does not officially >support the IBM JVM for use with the v1.1 driver.  Current plan is to support the IBM JVM with the v1.2 >driver.  Until a fix is avaliable, possible workarounds are: Request the value as a String or use another >vendor's JVM.  For example, the Sun JVM does not exhibit this problem.
>Hope this helps.
>Thanks again for your continued interest in the Microsoft SQL Server 2005 JDBC driver.
>--David Olix [MSFT]
0
 
LVL 1

Accepted Solution

by:
Vee_Mod earned 0 total points
ID: 19677697
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video teaches viewers about errors in exception handling.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question