NetBeans Forums

FAQ

Memberlist

Profile

Internal Exception: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column XX to TIMESTAMP

NetBeans Forums -> Java EE Users

View previous topic :: View next topic

Author

Message

kohuk3

Joined: 18 Mar 2009
Posts: 6

Posted: Tue Apr 14, 2009 7:38 am Post subject: Internal Exception: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column XX to TIMESTAMP

Hi (again).

I'm following this tutorial here:http://www.netbeans.org/kb/docs/web/jsf-jpa-crud-wizard.html to create my own jsf crud application.

I use Mysql table which has triggers and the base code looks for the problematic table (and connected tables) looks like that:

Code:

CREATE TABLE property(
property integer(30) NOT NULL AUTO_INCREMENT PRIMARY KEY,
property_type integer(30) NOT NULL,
name varchar(100) DEFAULT 'Nimetu',
created TIMESTAMP NULL DEFAULT NULL,
created_by integer(30) NOT NULL,
updated timestamp(8) NULL DEFAULT NULL,
updated_by integer(30)
)ENGINE=InnoDB;

CREATE TRIGGER property_entries_created BEFORE INSERT ON `property` FOR EACH ROW SET
NEW.created = IFNULL(NEW.created, NOW()),
NEW.updated = IFNULL(NEW.updated, '0000-00-00 00:00:00');

CREATE TRIGGER property_entries_update BEFORE UPDATE ON `property` FOR EACH ROW SET
NEW.updated = CASE
WHEN NEW.updated IS NULL THEN OLD.updated
WHEN NEW.updated = OLD.updated THEN NOW()
ELSE NEW.updated
END,
NEW.created = IFNULL(NEW.created, OLD.created);

CREATE TABLE property_class(
property_class integer(30) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(100) NOT NULL,
description varchar(300) DEFAULT 'Puudub'
)ENGINE = InnoDB;

CREATE TABLE property_type(
property_type integer(30) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name varchar(100) NOT NULL,
description varchar(300) DEFAULT 'Puudub',
property_class integer(30)
)ENGINE=InnoDB;

ALTER TABLE property
ADD CONSTRAINT FK_property_type_1 FOREIGN KEY (property_type) REFERENCES property_type(property_type),
ADD CONSTRAINT FK_created_by_6 FOREIGN KEY (created_by) REFERENCES employee(employee),
ADD CONSTRAINT FK_updated_by_7 FOREIGN KEY (updated_by) REFERENCES employee(employee);

CREATE INDEX property_index ON property(name, property_type, created, created_by, updated, updated_by);

INSERT INTO property_class(name) VALUES ('sõidukid'), ('kinnisvara'), ('arvutustehnika'), ('koduelektroonika'), ('meditsiinitehnika'), ('tööstusseadmed');

ALTER TABLE property_type
ADD CONSTRAINT FK_property_type_2 FOREIGN KEY (property_class) REFERENCES property_class(property_class);

.

My problem is that i get an error like that:

Code:

HTTP Status 500 -

--------------------------------------------------------------------------------

type Exception report

message

descriptionThe server encountered an internal error () that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP.
Error Code: 0
Call: SELECT property, updated, created, name, property_type, created_by, updated_by FROM property
Query: ReportQuery(jpaProperty.Property)
root cause

javax.el.ELException: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP.
Error Code: 0
Call: SELECT property, updated, created, name, property_type, created_by, updated_by FROM property
Query: ReportQuery(jpaProperty.Property)
root cause

Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.1 (Build b60e-fcs (12/23/2008))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP.
Error Code: 0
Call: SELECT property, updated, created, name, property_type, created_by, updated_by FROM property
Query: ReportQuery(jpaProperty.Property)
root cause

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP.
root cause

java.sql.SQLException: Value '[B@c7f0d4' can not be represented as java.sql.Timestamp
note The full stack traces of the exception and its root causes are available in the Sun GlassFish Enterprise Server v2.1 logs.

--------------------------------------------------------------------------------

Sun GlassFish Enterprise Server v2.1

I have read from http://ondra.zizka.cz/stranky/programovani/java/index.texy and http://bugs.mysql.com/bug.php?id=18308 and http://www.bennadel.com/blog/1434-MySQL-Cannot-Convert-Value-0000-00-00-00-00-00-From-Column-XX-To-TIMESTAMP.htmsite that you need to add

Quote:

What helps is setting JDBC driver's zeroDateTimeBehavior property to convertToNull:

What should happen when the driver encounters DATETIME values that are composed entirely of zeroes (used by MySQL to represent invalid dates)? Valid values are "exception", "round" and "convertToNull".

The way to set it depends on the way you configure JDBC driver. The most common case is to use connection URL parameters. In my case it reads:

Code:

jdbc:mysql://localhost/test?zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=UTF-8&chara

something like that.

My question is: where do i add the seroDateTimeBehavior thing? I currently have only persiostance, but do i need to add a new server resources and specify it somehow under the properties or something like that?

Of course there are tons of other files when exploring the project folder...

My project layout it the same as the jsf crud application layout.

Image added.

Please help!

image.PNG

Description:

Filesize:

38.17 KB

Viewed:

3622 Time(s)

kohuk3

Joined: 18 Mar 2009
Posts: 6

Posted: Tue Apr 14, 2009 8:01 am Post subject:

I have added a sun-fesources.xml file under server resources:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE resources PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server 9.0 Resource Definitions //EN" "http://www.sun.com/software/appserver/dtds/sun-resources_1_3.dtd">
<resources>
<jdbc-resource enabled="true" jndi-name="jdbc/varad" object-type="user" pool-name="varadPool">
<description/>
</jdbc-resource>
<jdbc-connection-pool allow-non-component-callers="false" associate-with-thread="false" connection-creation-retry-attempts="0" connection-creation-retry-interval-in-seconds="10" connection-leak-reclaim="false" connection-leak-timeout-in-seconds="0" connection-validation-method="auto-commit" datasource-classname="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" fail-all-connections="false" idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="true" lazy-connection-association="false" lazy-connection-enlistment="false" match-connections="false" max-connection-usage-count="0" max-pool-size="32" max-wait-time-in-millis="60000" name="varadPool" non-transactional-connections="false" pool-resize-quantity="2" res-type="javax.sql.DataSource" statement-timeout-in-seconds="-1" steady-pool-size="8" validate-atmost-once-period-in-seconds="0" wrap-jdbc-objects="false">
<property name="URL" value="jdbc:mysql://localhost:3306/varad?zeroDateTimeBehavior=convertToNull"/>
<property name="User" value="XXXX"/>
<property name="Password" value="XXXXX"/>
</jdbc-connection-pool>
</resources>

And also in web.xml

Code:

<resource-ref>
<description>testing</description>
<res-ref-name>jdbc/varad</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

this part to my previous code.

And still get the same error? Help?!

kohuk3

Joined: 18 Mar 2009
Posts: 6

Posted: Wed Apr 15, 2009 4:38 am Post subject:

I found and temporary solution to the problem by filling the null field with data, that is not suppose to be there, but still i need a solution to that problem above!

Display posts from previous:

	NetBeans Forums -> Java EE Users	All times are GMT
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum