| View previous topic :: View next topic |
| Author |
Message |
Thomas Wolf
Posted via mailing list.
|
 Posted: Mon Dec 13, 2010 2:06 pm Post subject: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
 |
|
NB just bit me.
I needed to temporarily make some column type changes on one of our tables. Looking at the columns that needed changing, Netbeans' DB tool told me that they were of type TIMESTAMP. So when it came time to revert my changes, that is what I changed the column back to. But the application stopped working when it tried to make updates on that table. When I looked at the same table on a system I hadn't modified using SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they were of type DATE!
Is this a known issue with NB's DB tool?
Tom
p.s. The database is an Oracle DB 11g. |
|
| Back to top |
|
 |
tkellerer
Joined: 15 Aug 2008
Posts: 475
|
| Posted: Mon Dec 13, 2010 3:00 pm Post subject: Re: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
| Thomas Wolf wrote: | NB just bit me.
I needed to temporarily make some column type changes on one of our tables. Looking at the columns that needed changing, Netbeans' DB tool told me that they were of type TIMESTAMP. So when it came time to revert my changes, that is what I changed the column back to. But the application stopped working when it tried to make updates on that table. When I looked at the same table on a system I hadn't modified using SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they were of type DATE!
p.s. The database is an Oracle DB 11g. |
Oracle's DATE type includes the time, so it is a timestamp
Newer Oracle JDBC drivers will report a DATE column as java.sql.Types.TIMESTAMP so this is more an Oracle problem and not a NetBeans problem.
I think there is a property that you can set with the JDBC driver to report Oracle DATE columns as JDBC DATE again (reverting to the old driver's behaviour) but that means that you will truncate the time information contained in those columns! |
|
| Back to top |
|
|
Thomas Wolf
Posted via mailing list.
|
| Posted: Mon Dec 13, 2010 3:26 pm Post subject: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
On Dec 13, 2010, at 10:01 AM, tkellerer wrote:
| Quote: |
Thomas Wolf wrote:
| Quote: | NB just bit me.
I needed to temporarily make some column type changes on one of our tables. Looking at the columns that needed changing, Netbeans' DB tool told me that they were of type TIMESTAMP. So when it came time to revert my changes, that is what I changed the column back to. But the application stopped working when it tried to make updates on that table. When I looked at the same table on a system I hadn't modified using SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they were of type DATE!
p.s. The database is an Oracle DB 11g.
|
Oracle's DATE type includes the time, so it is a timestamp
Newer Oracle JDBC drivers will report a DATE column as java.sql.Types.TIMESTAMP so this is more an Oracle problem and not a NetBeans problem.
I think there is a property that you can set with the JDBC driver to report Oracle DATE columns as JDBC DATE again (reverting to the old driver's behaviour) but that means that you will truncate the time information contained in those columns!
|
Thanks for the explanation.
When our application opens a connection to the db, we alter the session to set the date format (because all our code enters the date in that format):
alter session set NLS_DATE_FORMAT ='MM/DD/YYYY HH24:MI:SS'
When I "reverted" the column back to TIMESTAMP rather than DATE, inserts/updates started failing because Oracle didn't like the format of the insert/update - presumably because TIMESTAMP wasn't affected by the 'alter' statement and was still expecting the format YYYY-MM-DD HH:MM:SS.
Not very nice of Oracle then to mislead people on the exact type.
Tom |
|
| Back to top |
|
|
Bayless Kirtley
Posted via mailing list.
|
| Posted: Mon Dec 13, 2010 3:33 pm Post subject: [SPAM] Re: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
But Tom, Oracle has never been known to be particularly "nice."
Bayless
----- Original Message -----
From: "Thomas Wolf" <address-removed>
To: <address-removed>
Sent: Monday, December 13, 2010 9:12 AM
Subject: [nbusers] Re: Why does NB's DB tool identify DATE columns as
TIMESTAMP?
On Dec 13, 2010, at 10:01 AM, tkellerer wrote:
| Quote: |
Thomas Wolf wrote:
| Quote: | NB just bit me.
I needed to temporarily make some column type changes on one of our
tables. Looking at the columns that needed changing, Netbeans' DB tool
told me that they were of type TIMESTAMP. So when it came time to revert
my changes, that is what I changed the column back to. But the
application stopped working when it tried to make updates on that table.
When I looked at the same table on a system I hadn't modified using
SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they
were of type DATE!
p.s. The database is an Oracle DB 11g.
|
Oracle's DATE type includes the time, so it is a timestamp
Newer Oracle JDBC drivers will report a DATE column as
java.sql.Types.TIMESTAMP so this is more an Oracle problem and not a
NetBeans problem.
I think there is a property that you can set with the JDBC driver to
report Oracle DATE columns as JDBC DATE again (reverting to the old
driver's behaviour) but that means that you will truncate the time
information contained in those columns!
|
Thanks for the explanation.
When our application opens a connection to the db, we alter the session to
set the date format (because all our code enters the date in that format):
alter session set NLS_DATE_FORMAT ='MM/DD/YYYY HH24:MI:SS'
When I "reverted" the column back to TIMESTAMP rather than DATE,
inserts/updates started failing because Oracle didn't like the format of the
insert/update - presumably because TIMESTAMP wasn't affected by the 'alter'
statement and was still expecting the format YYYY-MM-DD HH:MM:SS.
Not very nice of Oracle then to mislead people on the exact type.
Tom |
|
| Back to top |
|
|
tkellerer
Joined: 15 Aug 2008
Posts: 475
|
| Posted: Mon Dec 13, 2010 3:49 pm Post subject: Re: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
| Thomas Wolf wrote: | When our application opens a connection to the db, we alter the session to set the date format (because all our code enters the date in that format):
alter session set NLS_DATE_FORMAT ='MM/DD/YYYY HH24:MI:SS'
When I "reverted" the column back to TIMESTAMP rather than DATE, inserts/updates started failing because Oracle didn't like the format of the insert/update - presumably because TIMESTAMP wasn't affected by the 'alter' statement and was still expecting the format YYYY-MM-DD HH:MM:SS.
Not very nice of Oracle then to mislead people on the exact type.
|
This is getting way off-topic now, but:
If your inserts fail due to a different NLS_DATE_FORMAT then your Java code is not correct (robust/stable/...).
You should never rely on any implict literal to date conversion. Either use a PreparedStatement and use setTimestamp() (do not use setDate() unless you want to use the time part) or make sure your format is correct by using to_date() to convert your character literal to a valid date.
Using a PreparedStatement will also prevent (some of) the security problems you might get from SQL injection. For SELECT statements it will also improve performance because Oracle won't need to hard-parse your statements every time. |
|
| Back to top |
|
|
Ian Clough
Posted via mailing list.
|
| Posted: Mon Dec 13, 2010 3:59 pm Post subject: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
I believe the Oracle DB DATE type holds both date and and time this more closely maps to java.sql.Timestamp than to java.sql.Date which is a pure date value. Why someone should have decided to change this behaviour I don't know but then Oracle was never quite a SQL standard DB.
On 13/12/2010 15:01, tkellerer wrote: | Quote: | | Quote: | Thomas Wolf wrote:
| Quote: | NB just bit me.
I needed to temporarily make some column type changes on one of our tables. Looking at the columns that needed changing, Netbeans' DB tool told me that they were of type TIMESTAMP. So when it came time to revert my changes, that is what I changed the column back to. But the application stopped working when it tried to make updates on that table. When I looked at the same table on a system I hadn't modified using SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they were of type DATE!
p.s. The database is an Oracle DB 11g.
|
Oracle's DATE type includes the time, so it is a timestamp
Newer Oracle JDBC drivers will report a DATE column as java.sql.Types.TIMESTAMP so this is more an Oracle problem and not a NetBeans problem.
I think there is a property that you can set with the JDBC driver to report Oracle DATE columns as JDBC DATE again (reverting to the old driver's behaviour) but that means that you will truncate the time information contained in those columns!
|
|
|
|
| Back to top |
|
|
Thomas Wolf
Posted via mailing list.
|
| Posted: Mon Dec 13, 2010 4:13 pm Post subject: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
On Dec 13, 2010, at 10:49 AM, tkellerer wrote:
| Quote: |
Thomas Wolf wrote:
| Quote: | When our application opens a connection to the db, we alter the session to set the date format (because all our code enters the date in that format):
alter session set NLS_DATE_FORMAT ='MM/DD/YYYY HH24:MI:SS'
When I "reverted" the column back to TIMESTAMP rather than DATE, inserts/updates started failing because Oracle didn't like the format of the insert/update - presumably because TIMESTAMP wasn't affected by the 'alter' statement and was still expecting the format YYYY-MM-DD HH:MM:SS.
Not very nice of Oracle then to mislead people on the exact type.
|
This is getting way off-topic now, but:
If your inserts fail due to a different NLS_DATE_FORMAT then your Java code is not correct (robust/stable/...).
You should never rely on any implict literal to date conversion. Either use a PreparedStatement and use setTimestamp() (do not use setDate() unless you want to use the time part) or make sure your format is correct by using to_date() to convert your character literal to a valid date.
Using a PreparedStatement will also prevent (some of) the security problems you might get from SQL injection. For SELECT statements it will also improve performance because Oracle won't need to hard-parse your statements every time.
|
Agreed - but as with any 10+ year old software product, cruft accumulates (bad developers come & go but leave their "marks", developers take shortcuts under time pressures, etc.) and management makes calls on when these things are fixed.
Tom |
|
| Back to top |
|
|
Thomas Wolf
Posted via mailing list.
|
| Posted: Mon Dec 13, 2010 8:47 pm Post subject: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
NB just bit me.
I needed to temporarily make some column type changes on one of our tables. Looking at the columns that needed changing, Netbeans' DB tool told me that they were of type TIMESTAMP. So when it came time to revert my changes, that is what I changed the column back to. But the application stopped working when it tried to make updates on that table. When I looked at the same table on a system I hadn't modified using SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they were of type DATE!
Is this a known issue with NB's DB tool?
Tom |
|
| Back to top |
|
|
tkellerer
Joined: 15 Aug 2008
Posts: 475
|
| Posted: Mon Dec 13, 2010 10:09 pm Post subject: Re: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
| Thomas Wolf wrote: | NB just bit me.
I needed to temporarily make some column type changes on one of our tables. Looking at the columns that needed changing, Netbeans' DB tool told me that they were of type TIMESTAMP. So when it came time to revert my changes, that is what I changed the column back to. But the application stopped working when it tried to make updates on that table. When I looked at the same table on a system I hadn't modified using SQLPLUS, it tells me that the columns weren't TIMESTAMP at all - they were of type DATE!
Is this a known issue with NB's DB tool?
Tom |
Again: this is not a NetBeans problem.
It is an Oracle JDBC driver problem!
And an Oracle DATE column is indeed more a TIMESTMAP column than a DATE column. |
|
| Back to top |
|
|
tkellerer
Joined: 15 Aug 2008
Posts: 475
|
| Posted: Mon Dec 13, 2010 10:16 pm Post subject: Re: Why does NB's DB tool identify DATE columns as TIMESTAMP? |
|
|
| tkellerer wrote: | Again: this is not a NetBeans problem.
It is an Oracle JDBC driver problem!
And an Oracle DATE column is indeed more a TIMESTMAP column than a DATE column. |
Just to complete this topic, this is even a FAQ item of the Oracle JDBC FAQ:
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01 |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
