You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Nathan C Hampton <of...@nhampton.net> on 2008/03/30 21:42:05 UTC
Odd SQL Exception
Hi everyone!
I'm having a little problem in the catalog manager. I have some
feature categories that contain a significant number of features
(read: 100+). When I go to edit those categories in the catalog
manager, the first page comes up fine, but when I click 'Next' to get
items 51-100, I get this error:
Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
Exception while getting value : lastUpdatedStamp [LAST_UPDATED_STAMP]
(11) (Cannot convert value '2008-03-11 16:30:00' from column 11 to
TIMESTAMP.)
At first I assumed that I had made a mistake when I imported one of
those features into the database, so I ran an SQL query to change the
last updated stamp to a known good value (specifically, "2008-03-11
16:30:01.001"). Unfortunately, this produces the same error:
Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
Exception while getting value : lastUpdatedStamp [LAST_UPDATED_STAMP]
(11) (Cannot convert value '2008-03-11 16:30:01' from column 11 to
TIMESTAMP.)
I've tried clearing all the caches and restarting OFBiz, but nothing
seems to help. Any advice would be appreciated.
Thanks!
--Nathan C. Hampton
Re: Odd SQL Exception
Posted by Nathan C Hampton <of...@nhampton.net>.
I'm using MySQL. (I can't afford Oracle!) What confuses me most is
that this is happening with the second page, but not the first.
--NCH
On 30 Mar 2008, at 6:00 PM, David E Jones wrote:
>
> Nathan,
>
> Just a wild guess: are using Oracle for your database?
>
> If so this is an issue that was reported a few months ago with a new
> version of Oracle and the JDBC driver from Oracle. No one has
> submitted a patch to correct it yet though. This may not be too
> complicated, like making sure all Strings going to the JDBC driver
> for date/time fields are converted to Timestamp objects before being
> sent over so that we don't rely on the JDBC driver to pass the
> correct data to the database. I heard that this a known/reported bug
> in the Oracle JDBC drivers that others have complained about too (ie
> not just in the OFBiz world), but I have not had a chance to
> personally look into this.
>
> BTW, in terms of databases a large or significant number is usually
> in the tens or hundreds of thousands before it has a significant
> impact on query performance or anything else, and in those cases
> it's just slow (because of table scans etc, if indexing is done to
> improve things the problems can usually be fixed).
>
> -David
>
>
> On Mar 30, 2008, at 1:42 PM, Nathan C Hampton wrote:
>> Hi everyone!
>>
>> I'm having a little problem in the catalog manager. I have some
>> feature categories that contain a significant number of features
>> (read: 100+). When I go to edit those categories in the catalog
>> manager, the first page comes up fine, but when I click 'Next' to
>> get items 51-100, I get this error:
>>
>> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
>> Exception while getting value : lastUpdatedStamp
>> [LAST_UPDATED_STAMP] (11) (Cannot convert value '2008-03-11
>> 16:30:00' from column 11 to TIMESTAMP.)
>>
>> At first I assumed that I had made a mistake when I imported one of
>> those features into the database, so I ran an SQL query to change
>> the last updated stamp to a known good value (specifically,
>> "2008-03-11 16:30:01.001"). Unfortunately, this produces the same
>> error:
>>
>> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
>> Exception while getting value : lastUpdatedStamp
>> [LAST_UPDATED_STAMP] (11) (Cannot convert value '2008-03-11
>> 16:30:01' from column 11 to TIMESTAMP.)
>>
>> I've tried clearing all the caches and restarting OFBiz, but
>> nothing seems to help. Any advice would be appreciated.
>>
>> Thanks!
>>
>> --Nathan C. Hampton
>
>
Re: Odd SQL Exception
Posted by David E Jones <jo...@hotwaxmedia.com>.
Nathan,
Just a wild guess: are using Oracle for your database?
If so this is an issue that was reported a few months ago with a new
version of Oracle and the JDBC driver from Oracle. No one has
submitted a patch to correct it yet though. This may not be too
complicated, like making sure all Strings going to the JDBC driver for
date/time fields are converted to Timestamp objects before being sent
over so that we don't rely on the JDBC driver to pass the correct data
to the database. I heard that this a known/reported bug in the Oracle
JDBC drivers that others have complained about too (ie not just in the
OFBiz world), but I have not had a chance to personally look into this.
BTW, in terms of databases a large or significant number is usually in
the tens or hundreds of thousands before it has a significant impact
on query performance or anything else, and in those cases it's just
slow (because of table scans etc, if indexing is done to improve
things the problems can usually be fixed).
-David
On Mar 30, 2008, at 1:42 PM, Nathan C Hampton wrote:
> Hi everyone!
>
> I'm having a little problem in the catalog manager. I have some
> feature categories that contain a significant number of features
> (read: 100+). When I go to edit those categories in the catalog
> manager, the first page comes up fine, but when I click 'Next' to
> get items 51-100, I get this error:
>
> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
> Exception while getting value : lastUpdatedStamp
> [LAST_UPDATED_STAMP] (11) (Cannot convert value '2008-03-11
> 16:30:00' from column 11 to TIMESTAMP.)
>
> At first I assumed that I had made a mistake when I imported one of
> those features into the database, so I ran an SQL query to change
> the last updated stamp to a known good value (specifically,
> "2008-03-11 16:30:01.001"). Unfortunately, this produces the same
> error:
>
> Target exception: org.ofbiz.entity.GenericDataSourceException: SQL
> Exception while getting value : lastUpdatedStamp
> [LAST_UPDATED_STAMP] (11) (Cannot convert value '2008-03-11
> 16:30:01' from column 11 to TIMESTAMP.)
>
> I've tried clearing all the caches and restarting OFBiz, but nothing
> seems to help. Any advice would be appreciated.
>
> Thanks!
>
> --Nathan C. Hampton