java.sql.SQLException: java.sql.Time

Thread view:

Enable EMail Alerts

Start New Thread

mehatespam1085@yahoo.com - 30 Jul 2007 14:44 GMT

I get the following error message...
java.sql.SQLException: Value '00:00:00 can not be represented as
java.sql.Time

>From the following SQL call...

SELECT COUNT(pageurl) AS PageCount, pageurl, MAX(datetime) AS
LastAccess, SUM(VisitLength) as TotalVisitLength, VisitLength,
RecordNum, SUM(CASE WHEN VisitLength IS NULL THEN 1 ELSE 0 END) AS
NullVisits FROM clubvisits WHERE groupid = #clubid# GROUP BY pageurl
ORDER BY #OrderKey#

I am using ColdFusion 7 on MySQL 4.1.12a. I'm not a Java programmer -
I don't know any Java, but ColdFusion acts essentially as a script
layer to J2EE, so for some reason it's throwing a Java error. I'm
especially stuck, because I need to resolve this without programming
any Java, and the Adobe forums for ColdFusion and MySQL groups are no
help.

The error refers to the "VisitLength" field which is a "time" data
type in the database. In the MySQL documentation, it says that
00:00:00 is allowable for time (http://dev.mysql.com/doc/refman/4.1/
en/ time.html).

So my question is, why is this error coming up if it's an allowable
value? And is there anything I can do?

Reply to this Message

Roedy Green - 30 Jul 2007 16:36 GMT

>So my question is, why is this error coming up if it's an allowable
>value? And is there anything I can do?

one way is to store the values as raw ints or longs as far as SQL is
concerned.

Signature

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

Reply to this Message

Bjorn Abelli - 30 Jul 2007 18:47 GMT

<mehatespam1085@yahoo.com> wrote...

>I get the following error message...
> java.sql.SQLException: Value '00:00:00 can not be represented as
> java.sql.Time

[snip]

> The error refers to the "VisitLength" field which is a "time" data
> type in the database. In the MySQL documentation, it says that
> 00:00:00 is allowable for time (http://dev.mysql.com/doc/refman/4.1/
> en/ time.html).

Note that TIME in MySQL is not intended to be used in the same way as
java.sql.Time.

While an instance of java.sql.Time denotes a time of day, TIME in MySQL is
primarily used for denoting a time length. As such TIME can exceed valid
values of a java.sql.Time.

The documentation also says that "illegal" values always will come up as
00:00:00.

> So my question is, why is this error coming up if it's
> an allowable value?

I can't really answer to your question, as I have shut down my MySQL for
now... ;-)

> And is there anything I can do?

As the use of TIME and java.sql.Time are different, I'd suggest that you
retrieve it as a String instead, parse it into a type of your own (e.g. a
"Timespan" or similar).

/// Bjorn A

Reply to this Message

java.sql.SQLException: java.sql.Time

Free Magazines