You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@roller.apache.org by Dave Johnson <da...@rollerweblogger.org> on 2006/03/13 20:55:40 UTC
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
Re: Oracle users: verify that we should use "clob" instead of "text"
Posted by Ben Avery <be...@youthnet.org>.
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
Tom.Stroobants@persgroep.be 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(ErrorCodeConverter.java:92)
> at
> org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
> at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at
> org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:99)
> at
> org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.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.java: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" <Be...@youthnet.org>
> 14/03/2006 01:10
> Please respond to
> roller-user@incubator.apache.org
>
>
> To
> <ro...@incubator.apache.org>
> 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:dave.johnson@rollerweblogger.org]
> Sent: Mon 13/03/2006 19:55
> To: roller-dev@incubator.apache.org; roller-user@incubator.apache.org
> 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: ben.avery@youthnet.org
phone: 020 7288 7333
snail: 2-3 Upper Street
London N1 0PQ
Re: Oracle users: verify that we should use "clob" instead of "text"
Posted by Ben Avery <be...@youthnet.org>.
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
Tom.Stroobants@persgroep.be 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(ErrorCodeConverter.java:92)
> at
> org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
> at
> org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
> at
> org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:99)
> at
> org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.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.java: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" <Be...@youthnet.org>
> 14/03/2006 01:10
> Please respond to
> roller-user@incubator.apache.org
>
>
> To
> <ro...@incubator.apache.org>
> 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:dave.johnson@rollerweblogger.org]
> Sent: Mon 13/03/2006 19:55
> To: roller-dev@incubator.apache.org; roller-user@incubator.apache.org
> 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: ben.avery@youthnet.org
phone: 020 7288 7333
snail: 2-3 Upper Street
London N1 0PQ
RE: Oracle users: verify that we should use "clob" instead of "text"
Posted by To...@persgroep.be.
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(ErrorCodeConverter.java:92)
at
org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at
org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:99)
at
org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.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.java: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" <Be...@youthnet.org>
14/03/2006 01:10
Please respond to
roller-user@incubator.apache.org
To
<ro...@incubator.apache.org>
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:dave.johnson@rollerweblogger.org]
Sent: Mon 13/03/2006 19:55
To: roller-dev@incubator.apache.org; roller-user@incubator.apache.org
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
RE: Oracle users: verify that we should use "clob" instead of "text"
Posted by Ben Avery <Be...@youthnet.org>.
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:dave.johnson@rollerweblogger.org]
Sent: Mon 13/03/2006 19:55
To: roller-dev@incubator.apache.org; roller-user@incubator.apache.org
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