Bug 49994

Changing to ENHANCEMENT, as this is not really bug. You are right, it
is possible to detect when only time is set. But I'm not sure how to
handle timezones. It is not mandatory to save the date in GMT and
then, when you are in GMT-1, getTime() call on column '1070-01-01
0:00:00.0' returns -360000, doesn't it?

Hmmm, see your point.

I thought I might have a look at the code to maybe create a patch that at least
works for my specific dataset, but I am unsure where to look first. I would have
thought that the SQL would return timestamps, but in my exploration of the code
so far I cannot see why they would get treated as dates.

Could you please advise me as to which are the main classes to look at for
database querying and SQL results displaying?

Sure. The code is in db/core in the
org.netbeans.modules.db.sql.execute.ResultSetTableModelSupport class. It is a
helper class which creates a ResultSetTableModel, which is a TableModel for the
results JTable. Try to implement your own ColumnTypeDef for dates and return
whatever you think should be displayed for dates from your impl of the
getColumnValue() method.

This is a valid defect, we are not doing the right thing for Oracle. If I understand correctly there are two aspects:

1. displaying the time part (if one is present) along with the date part.
2. not displaying the date part if only the time part is present.

#1 looks doable *if* the Oracle driver returns the time information, which I still need to check (java.sql.Date seems to
claim it doesn't care about time information.). I only plan to display the time part if there is one (i.e., '00:00:00'
will never be displayed. This is in order to avoid displaying a time part on databases for which the DATE type only
holds a date part.

#2 is not trivial, since for a time-only date value the date part is the first day of the current month of the current
year. So for such values I will just display both the date and time part. Not a big inconvenience I think, because if
you use time-only date values you know about it, so you will know to ignore the date part.

Makes sense?

What I am finding is that I only ever see the date part.  However, when the time is only important then you find that
this is on 1970-01-01 and so if possible it is best if timestamps on that day are converted to be just times.

The key thing to remember about Oracle is that its Date is, or should be, mapped to java.sql.Timestamp as it has both
date and time parts.  It has not date-only type or time-only type.  As far as I can make out the Oracle driver does give
the correct Java type, so I'm not sure why NetBeans only displays the date.

It seems that the Oracle driver in Oracle v8 returned timestamp for dates whereas for later versions it returns date. 
This is unfortunate.  It seems that setting -Doracle.jdbc.V8Compatible=true for NetBeans reverts this behaviour to that
of the old version.  Apparently, this has also been hit with glassfish
http://wiki.netbeans.org/wiki/view/VwpFAQOracleSqlDate .

Could NetBeans get the Oracle driver to have oracle.jdbc.V8Compatible set without requiring the user to have such
detailed knowledge?

> Could NetBeans get the Oracle driver to have oracle.jdbc.V8Compatible set
> without requiring the user to have such detailed knowledge?

That seems too risky. I would rather prefer calling getTimestamp() instead of getObject(). That would, even for DATE
columns, return a java.sql.Timestamp object, which NetBeans display correctly (both the date and time part).

Out of my curiosity, how do you work with the time part of DATE columns in Java applications? Do you use the
V8Compatible flag?

I agree with Andrei, we should just use getTimestamp() rather than getObject(), since we're changing the code anyway. 
The v8compatible flag is for applications where making the change over to getTimestamp() is not possible, as I
understand it.

Working on this

Fixed this issue.  I had to pass down DatabaseMetaData to ResultTableModelSupport so it could determine whether we were
talking to Oracle.  All unit tests pass in core (I had to fix a properties file).

Checking in src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java,v  <--  ResultSetTableModel.java
new revision: 1.8; previous revision: 1.7
done
Checking in src/org/netbeans/modules/db/sql/execute/ui/SQLResultPanelModel.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ui/SQLResultPanelModel.java,v  <--  SQLResultPanelModel.java
new revision: 1.6; previous revision: 1.5
done
Checking in src/org/netbeans/modules/db/sql/execute/SQLExecutionResult.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/SQLExecutionResult.java,v  <--  SQLExecutionResult.java
new revision: 1.7; previous revision: 1.6
done
Checking in src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java,v  <--  ResultSetTableModelSupport.java
new revision: 1.11; previous revision: 1.10
done
Checking in test/unit/data/mysql5.0/dbprop.properties;
/cvs/db/core/test/unit/data/mysql5.0/dbprop.properties,v  <--  dbprop.properties
new revision: 1.4; previous revision: 1.3
done

Adjustments based on Andrei's feedback.  All core unit tests pass...

Checking in ResultSetTableModelSupport.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java,v  <--  ResultSetTableModelSupport.java
new revision: 1.12; previous revision: 1.11
done

Thanks for your efforts, but alas the fix doesn't work.  What I get is the time always being displayed as 00:00:00.  It
seems that only a date has been retrieved from the server and then we request rendering it with a time part, which is
then just midnight.

I am sorry to hear it does not work for me.  In the famous words of Joe Debugger: "It Works For Me" :)

There appears to be some dynamics here that I am not yet sure of.  Either my testing environment is broken somehow, or
there are conditions I am unaware of that determine whether the time part is printed out correctly.

I will try to track this down.

Ah, we've all been there...

Perhaps it is worth verifying that you haven't left -Doracle.jdbc.V8Compatible=true set anywhere.

If it helps, my current driver is for Oracle's 10.2.0 and I was connecting to a 10.2.0 server.

Second attempt.

Checking in src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ResultSetTableModelSupport.java,v  <--  ResultSetTableModelSupport.java
new revision: 1.13; previous revision: 1.12
done
Checking in src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java;
/cvs/db/core/src/org/netbeans/modules/db/sql/execute/ui/ResultSetTableModel.java,v  <--  ResultSetTableModel.java
new revision: 1.9; previous revision: 1.8
done

OK, I see what the problem is.  I have two connections, with two different driver versions.  With the Oracle 9.2.0.5
driver, getTimestamp() returns the full date and time .  For the Oracle 10.2.0.3.0 driver, getTimestamp() does not
deliver the time part.  I also tried it with the 10.1 driver.  These are *all* running against the same Oracle server,
version 10.2.0.1.0.

I even tried "select to_timestamp(mydate) from TRAVEL.TESTDATE" and got the same results -- actually it made things
worse, because the time part was lost even with the Oracle 9 driver.

All the existing documentation that I found through Google, including the one you attached
(http://wiki.netbeans.org/wiki/view/VwpFAQOracleSqlDate), seems to indicate that getTimestamp() should work, but
apparently it does not with newer versions.

When I started NetBeans with -Doracle.jdbc.V8Compatible=true it worked great with all versions of the driver.

Unless anyone has some ideas about how we can get the full and time out of Oracle with the newer drivers, this seems
like a bug/"feature" with Oracle, and not something we can really fix.  I think Oracle is trying to "gently" encourage
users to move to the SQL ANSI standard and use TIMESTAMP to store date and time.

I suggest setting -Doracle.jdbc.V8Compatible=true or altering your table to be a timestamp type rather than a date type?
 But at least with this fix it is *possible* to get the timestamp out if you set the V8Compatible flag.

I tried it with Andrei's changes, same behavior.  The source of the confusion is that with the Oracle 9.2.0.5 driver,
getTimestamp() returns the full date and time .  For the Oracle 10.2.0.3.0 driver, getTimestamp() does not deliver the
time part.  I also tried it with the 10.1 driver.  These are *all* running against the same Oracle server, version
10.2.0.1.0.

I even tried "select to_timestamp(mydate) from TRAVEL.TESTDATE" and got the same results -- actually it made things
worse, because the time part was lost even with the Oracle 9 driver.

All the existing documentation that I found through Google, including the one you attached
(http://wiki.netbeans.org/wiki/view/VwpFAQOracleSqlDate), seems to indicate that getTimestamp() should work, but
apparently it does not with newer versions.

When I started NetBeans with -Doracle.jdbc.V8Compatible=true it worked great with all versions of the driver.

Unless anyone has some ideas about how we can get the full and time out of Oracle with the newer drivers, this seems
like a bug/"feature" with Oracle, and not something we can really fix.  I think Oracle is trying to "gently" encourage
users to move to the SQL ANSI standard and use TIMESTAMP to store date and time.

I suggest setting -Doracle.jdbc.V8Compatible=true or altering your table to be a timestamp type rather than a date type?
 But at least with this fix it is *possible* to get the timestamp out if you set the V8Compatible flag, so we definitely
shouldn't back it out.

Sorry for the duplicate entries, issuezilla told me that my change was lost due to conflict with Andrei's comment, but
apparently not the case.  

Summary:

- Andrei's change apparently doesn't change things for newer drivers, same problem
- Setting the V8Compatible flag makes things work
- Recommend users alter their columns to TIMESTAMP if they can, as that is what Oracle appears to be encouraging.

My change works fine for me with Oracle 10.2.0.1.0 and driver 10.2.0.1.0. If it doesn't work with some drivers, well,
there's not much we can do here. But I expect users won't use such drivers much, as their applications won't work with
those drivers either (unless using the V8Compatible property, that is).

It turns out that the reason Andrei's change wasn't working for me was because I had assumed a clean build under the
'db' directory included the 'core' module, but it doesn't.

When I did a fresh build of the 'core' module separately, it works for me too with the latest drivers.  

Thanks to for getting this to work in time for 6.0. Code freeze happens tonight, so this went in under the wire.

Thank you for all your efforts.  However I do have the driver 10.2.0.1.0 driver working against an Oracle 10.2.0.1.0
database and it still isn't working for me.  Is there something else I need to do?  Re-register the driver in NB?  Clear
some caching?

Are you testing with a download of the nightly build or building NetBeans yourself?  If the former, when did you get the
build?  If the latter, how are you rebuilding?

Ah, that's it.  I downloaded the latest build this morning, but I now see an even later one is there which when tested
works!

Thanks for all your efforts

Dan