This discussion is archived
8 Replies Latest reply: Dec 20, 2013 1:10 PM by jschellSomeoneStoleMyAlias RSS

ResultSet.getString(Date) differs based on driver

b237d10d-d16c-4345-840a-44c3debcb632 Newbie
Currently Being Moderated

I am using "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options"

 

I have a table whose schema is

COLUMN_NAMEDATA_TYPEDATA_TYPE_MODDATA_TYPE_OWNERDATA_LENGTH
CITYVARCHAR2(null)(null)30
COUNTRYVARCHAR2(null)(null)30
DATE_TYPEDATE(null)(null)7
PARTNONUMBER(null)(null)22
STATEVARCHAR2(null)(null)30
ZIPVARCHAR2(null)(null)30

I have written a simple java client to fetch the DATE_TYPE .

public class DateIssue {
  private void testDateOutput() {
       Connection con = null;
       Statement psmt = null;
       try {
            con = getConnection();
            con.setAutoCommit(true);   
            psmt = con.createStatement();
            String sql = "SELECT DATE_TYPE FROM EMP";
            ResultSet rs = psmt.executeQuery(sql);
            while (rs.next()) {
                 String dateString = rs.getString(1);
                 System.out.println("As String :"+ dateString);
            }
            con.close();
       }
       catch (SQLException e) {
            e.printStackTrace();
       }
  }
  private static Connection getConnection() {
       Connection connection = null;
       try {
            Class.forName("oracle.jdbc.pool.OracleDataSource");
            java.util.Properties info = new java.util.Properties();
            info.put("user", "myuser");
            info.put("password", "mypass");
            info.put("oracle.jdbc.mapDateToTimestamp", "false");
       connection = DriverManager.getConnection("jdbc:oracle:thin:@myserver:1521:myservicename", info);
       }
       catch (ClassNotFoundException e) {
            e.printStackTrace();
       } catch (SQLException e) {
            e.printStackTrace();
       }
       return connection;
  }
   public static void main(String rgs[]) throws Exception {
       DateIssue di = new DateIssue();
       di.testDateOutput();
       System.out.println("----------------------------------------------------");
  }

 

 

With ojdbc6.jar(12.1.0.1.0) the output is   :  As String :2013-11-12

With ojdbc6.jar(11.2.0.2.0) the output is   :  As String :2013-11-12 11:10:09

 

Java version  : 1.7

 

Why the behavior changes in  ojdbc6.jar(12.1.0.1.0) ?

If I need the output in the format 2013-11-12 11:10:09  using  ojdbc6.jar(12.1.0.1.0) what should i do?

  • 1. Re: ResultSet.getString(Date) differs based on driver
    rp0428 Guru
    Currently Being Moderated
    If I need the output in the format 2013-11-12 11:10:09  using  ojdbc6.jar(12.1.0.1.0) what should i do?

    Just query the data in the format you need.

     

    Since all you need is a string use TO_CHAR to format the value the way you want it.

    SELECT TO_CHAR(DATE_TYPE, 'YYYY-MM-DD hh24:mi:ss') FROM EMP;
  • 2. Re: ResultSet.getString(Date) differs based on driver
    b237d10d-d16c-4345-840a-44c3debcb632 Newbie
    Currently Being Moderated

    Hi rp0428,

    Thank you very much for your response.

     

    I can use the query as given by you.

    But why there is discrepancy between drivers ? Is there any documentation for this change ?

  • 3. Re: ResultSet.getString(Date) differs based on driver
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    > But why there is discrepancy between drivers ?

     

    As a guess - because you are getting it as a string.  You shouldn't be doing that.

     

    Note that in the following that it specifically says that the default representation for a date, in text, is just the date part and no time part.  And the JDBC spec says nothing about how the string representation should occur.

     

    Native Datatypes

     

     

    > Is there any documentation for this change ?

    I don't see anything in the release notes.

     

    Oracle Database 11g Release 2 JDBC Driver Downloads</title><meta name="Title" content="Oracle Da…

  • 4. Re: ResultSet.getString(Date) differs based on driver
    dsurber Explorer
    Currently Being Moderated

    The getXXX code was heavily revised in 12.1. In previous versions there were multiple code paths that were executed to do getXXX depending on exactly the circumstances of the call. Because of this, different ways of calling getXXX would produce different results and there was a lot of extra code to maintain. In 12.1 we revised all the getXXX code so it all goes through the same code path. We eliminated a lot of redundant code and we made the results consistent. There are lots of little behavioral changes is 12.1 as a result. Calling getXXX on a CallableStatement, forward read-only ResultSet, scrollable ResultSet, and updateable ResultSet frequently gave different values. In 12.1 they are all the same.

     

               Class.forName("oracle.jdbc.pool.OracleDataSource"); 

     

    The above line of code does nothing. Delete it and never ever write anything like it again. You probably meant to write

     

      Class.forName("oracle.jdbc.OracleDriver");

     

    This idiom is obsolete. Don't use it. In JDK 1.6 and later the JRE loads the JDBC driver automatically as needed. Same for

     

    new oracle.jdbc.OracleDriver();

     

    and

     

      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

     

    Don't use either of those idioms. Just call DriverManager.getConnection or create a DataSource. Don't worry about loading the driver.

  • 5. Re: ResultSet.getString(Date) differs based on driver
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    > This idiom is obsolete

     

    I doubt that.  Did a specification change to state that that idiom is no longer supported?  If so please cite.

    > In JDK 1.6 and later the JRE loads the JDBC driver automatically as needed.

     

    HOWEVER....

     

    The problem with relying on that is that if the driver is not found then it is not loaded.  And there is no indication that it failed to find the driver.  Errors if the driver failed to load are similar to errors if the connection string is incorrect.

     

    Using the Class.forName() idiom specifically allows for verification that the driver did in fact load.

  • 6. Re: ResultSet.getString(Date) differs based on driver
    dsurber Explorer
    Currently Being Moderated

    The exception you get if the driver doesn't load seems reasonable clear to me.

     

    Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@localhost:1521:main

        at java.sql.DriverManager.getConnection(DriverManager.java:604)

        at java.sql.DriverManager.getConnection(DriverManager.java:243)

        at GetConnection.main(GetConnection.java:4)

     

     

    I see no reason to use this idiom and it certainly isn't needed.

  • 7. Re: ResultSet.getString(Date) differs based on driver
    user793560 Newbie
    Currently Being Moderated

    It's also related to using mapDateToTimestamp=false. 

     

    That purely exists to perpetuate behaviour that was introduced in 10g and then removed in 11i whereby oracle.sql.DATE mapped to java.sql.Date. 

     

    Unless you explicitly define the column otherwise, with that property set as false the correct string output will have no time component since java.sql.Date doesn't have one.

     

    Set the property to true (which is the default) and the String will contain a time component since it is mapped to Timestamp.

     

    The 11g behaviour is probably a bug.  The 12c behaviour is now correct.

  • 8. Re: ResultSet.getString(Date) differs based on driver
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated

    > The exception you get if the driver doesn't load seems reasonable clear to me.

     

    And what exception do you get when you use the wrong jdbc name in your connection url?

     

    > I see no reason to use this idiom and it certainly isn't needed.

     

    Which of course says nothing.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points