This exact same problem happens on SQL server (TEXT fields). I
submitted patches a while back but nobody integrated it to the trunk.
What you need to do is to modify the clearReferrers and the
clearReferrers(WebsiteData)
To something like
Dialect currentDialect = ((SessionFactoryImplementor)
session.getSessionFactory()).getDialect();
if ( currentDialect instanceof SQLServerDialect ||
currentDialect instanceof OracleDialect ){
delete = "delete RefererData where excerpt is null or
excerpt like ''";
} else {
delete = "delete RefererData where excerpt is null or
excerpt=''";
}
LIKE should work on CLOB fields.
George Daswani
-----Original Message-----
From: Ben Avery [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 3:55 AM
To: [email protected]
Cc: [email protected]
Subject: Re: Oracle users: verify that we should use "clob" instead of
"text"
hi Tom,
I've tracked this down in the source code to a query created from this
line in org.roller.business.hibernate.HibernateRefererManagerImpl
delete RefererData where excerpt is null or excerpt=''
and the problem with CLOBs is that you can't do comparisons like that on
them. They're not held in the table data file, but referred to with a
pointer.
In Oracle an empty character field *is* considered to be null. so if
Roller was just written for Oracle, you would fix this by changing the
line to
delete RefererData where excerpt is null
Other database users (and roller-dev group): would this be a problem for
other databases, are empty text fields not considered null?
I've just had a search through the code, it looks like this is the only
case of a query doing this on a text/clob field. I've suggested a few
other changes to the Oracle database scripts, so I'll include this one
too.
if we can't fix it this way, we'll have to change the table structure to
a type which can handle this. I prefer to use large VARCHAR fields, but
they have a limit of 4000 characters, which might be exceeded by some of
the CLOB fields in the Roller database, so we should just change this
one.
the table definition should now be
-- Referer tracks URLs that refer to websites and entries
create table referer (
id varchar(48) not null primary key,
websiteid varchar(48) not null,
entryid varchar(48),
datestr varchar(10),
refurl varchar(255) not null,
refpermalink varchar(255),
reftime timestamp,
requrl varchar(255),
title varchar(255),
excerpt varchar(4000),
dayhits integer default 0 not null,
totalhits integer default 0 not null,
visible number(1) default 0 not null,
duplicate number(1) default 0 not null
);
unfortunately, there's no easy way to change the datatype from clob to
varchar, so we'll have to go jump through these hoops:
create table referer_tmp as select * from referer;
drop table referer;
create table referer (
id varchar(48) not null primary key,
websiteid varchar(48) not null,
entryid varchar(48),
datestr varchar(10),
refurl varchar(255) not null,
refpermalink varchar(255),
reftime timestamp,
requrl varchar(255),
title varchar(255),
excerpt varchar(4000),
dayhits integer default 0 not null,
totalhits integer default 0 not null,
visible number(1) default 0 not null,
duplicate number(1) default 0 not null
);
insert into referer (select * from referer_tmp);
drop table referer_tmp;
let me know if this works for you, and solves your problem,
cheers
Ben
[EMAIL PROTECTED] wrote:
> Ben
>
> At night I get the following errors in my log file ...
>
> INFO 2006-03-16 00:00:00,002 TurnoverReferersTask:run - task started
> WARN 2006-03-16 00:00:00,699 JDBCExceptionReporter:logExceptions -
SQL
> Error: 932, SQLState: 42000
> ERROR 2006-03-16 00:00:00,700 JDBCExceptionReporter:logExceptions -
> ORA-00932: inconsistent datatypes: expected - got CLOB
>
> ERROR 2006-03-16 00:00:00,708
HibernateRefererManagerImpl:clearReferrers -
> EXCEPTION resetting referers
> org.hibernate.exception.GenericJDBCException: could not execute update
> query
> at
>
org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(E
rrorCodeConverter.java:92)
> at
>
org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.ja
va:80)
> at
>
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.
java:43)
> at
>
org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExe
cutor.java:99)
> at
>
org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorI
mpl.java:297)
> at
> org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:871)
> at
org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)
> at
>
org.roller.business.hibernate.HibernateRefererManagerImpl.clearReferrers
(HibernateRefererManagerImpl.java:485)
> at
>
org.roller.presentation.TurnoverReferersTask.run(TurnoverReferersTask.ja
va:44)
> at java.util.TimerThread.mainLoop(Timer.java:432)
> at java.util.TimerThread.run(Timer.java:382)
> Caused by: java.sql.SQLException: ORA-00932: inconsistent datatypes:
> expected - got CLOB
>
>
>
>
>
>
> "Ben Avery" <[EMAIL PROTECTED]>
> 14/03/2006 01:10
> Please respond to
> [email protected]
>
>
> To
> <[email protected]>
> cc
>
> Subject
> RE: Oracle users: verify that we should use "clob" instead of "text"
>
>
>
>
>
>
> that's right, I've just upgraded from 2.0 to 2.1 and had to fix this.
>
> I sent a revised version of the database creation script to the Roller
dev
> list a few days ago - should be in the archives. The script for 2.0
was,
> if I
> recall correctly, fine for Oracle, and the error is just in the 2.1
> version,
> which looks like a copy of the MySql version.
>
> The other mistyped field is BIT, which Oracle (9) doesn't have either.
> This
> needs to be replaced with NUMBER(1) as a single digit number, as
Oracle
> also
> doesn't have boolean fields.
>
> I found another problem with the Oracle database creation script, a
not
> null
> field for the template table, I think. which means an error is thrown
when
> you try to create a new template through the web interface, as empty
> varchar
> fields are null. I'll confirm this from work tomorrow.
>
>
>
>
> -----Original Message-----
> From: Dave Johnson [mailto:[EMAIL PROTECTED]
> Sent: Mon 13/03/2006 19:55
> To: [email protected]; [email protected]
> Subject: Oracle users: verify that we should use "clob" instead of
"text"
>
> According to issue ROL-1077, we should use the CLOB instead of
> TEXT data type in the create database script for Oracle.
> Can any Oracle users verify that this change is needed?
>
> http://opensource2.atlassian.com/projects/roller/browse/ROL-1077
>
> -Dave
>
>
>
>
>
--
Ben Avery
Software Developer
Youthnet UK
email: [EMAIL PROTECTED]
phone: 020 7288 7333
snail: 2-3 Upper Street
London N1 0PQ
