Odd SQL Exception

[Nathan C Hampton]

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

[David E Jones]

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

[Nathan C Hampton]

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
>
>

[rrhati2010]

Hi,

I got a solution for this.

Please use the right version of database driver connector(connector version should match with the DB Sever instance version.). The issue is resolved in case of MySQL DB. For other DB I have not tested. This worked for me.