This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

Bug 187457 - Error code 0, SQL state S1009 ... can not be represented as java.sql.Date
Summary: Error code 0, SQL state S1009 ... can not be represented as java.sql.Date
Status: RESOLVED WONTFIX
Alias: None
Product: db
Classification: Unclassified
Component: MySQL (show other bugs)
Version: 6.x
Hardware: PC Windows XP
: P3 normal (vote)
Assignee: Jiri Rechtacek
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-06-10 22:03 UTC by rip_pit
Modified: 2012-03-03 20:28 UTC (History)
0 users

See Also:
Issue Type: DEFECT
Exception Reporter:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description rip_pit 2010-06-10 22:03:27 UTC 
i encoutered this error while using databases services in NB 6.9 RC2 :
when selecting "view datas" on some tables...I tested several tables, the one's containing "year" fields produce the error.

Step to reproduce:
create a db and import the following sql code.
Then in netbeans
services panel > databases > mysql > select the db > right click on the table > show data.

Error message :
[quote]
Error code 0, SQL state S1009: Value '0000' can not be represented as java.sql.Date
Line 1, column 1
Execution finished after 0 s, 1 error(s) occurred.
[/quote]

Here's a faulty table sample :
[MYSQL]
-- Serveur: localhost
-- Version du serveur: 5.1.34
-- Version de PHP: 5.2.9-1
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- DB : `test`
-- Structure de la table `datetest`
CREATE TABLE IF NOT EXISTS `datetest` (
  `myid` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `atext` text NOT NULL ,
  `ayear1` year(4) NOT NULL,
  PRIMARY KEY (`myid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
-- table `datetest` Content 
INSERT INTO `datetest` (`myid`, `atext`, `ayear1`) VALUES (1, 'sometext', 0000);
[/MYSQL]

Let me know if u need more informations
Comment 1 Jiri Rechtacek 2010-06-11 06:07:28 UTC 
Thanks for your test-case, I'll look on it.
Comment 2 Jiri Rechtacek 2010-06-21 13:11:21 UTC 
Also reproducible in JDeveloper with the same driver. Seems a bug in MySQL driver - mysql-connector-java-5.1.6-bin.jar
Comment 3 Jiri Rechtacek 2010-06-21 13:30:26 UTC 
Btw, neither the latest MySQL driver(http://dev.mysql.com/downloads/connector/j/) doesn't solve this problem yet.
Comment 4 rip_pit 2010-06-21 16:18:01 UTC 
hey! i found something
it seems to be related with an invalid date (0000 is not a valid year, 2010 is)

it can be forced using "ALLOW_INVALID_DATES" in a mysql query/server option to disable this full checking of dates
more info @ http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_allow_invalid_dates

in my scripts, i was forced to use : allowing to use not valid dates.
@mysql_query("SET SESSION SQL_MODE='ALLOW_INVALID_DATES';");

After some more testing i found that :
year->0000 produce the error
year->2010 doesn't produce the error ... BUT ... the year column is showed/converted into a "full date" value and not just Year value.
(Instead of showing 2010 in the columns of viewed tables, it shows 2010-01-01)
So if you have valid dates, all is fine.

It helps but doesn't fix coz tables containing 0000 in a YEAR column will still produce the error in NB.

hope it can help to see clear ;) 
thanx for your investigations, bye
Comment 5 matthias42 2012-03-03 20:28:42 UTC 
Please see "zeroDateTimeBehavior" option of the mysql-jdbc driver. Setting it to "convertToNull" should fix the symptoms of your problem - reporting dates of this type as null.