• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

error when going to forum from forum list

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Everyone,
I found a problem, using the tomcat 6.0.16, postgresql 8.3.
I get the following exception when clicking on one of the forums in the forum categories list.
I resolved it changing one row [#496] in src/net/jforum/dao/generic/GenericForumDAO.java
, in the sourrinding method getModeratorList()
from:

to:


because the row set a parameter in the statement for jforum_role_values.role_value
that is of varchar type and not of int type.

I have to write it to jira?


Exception:
Method public java.util.List net.jforum.entities.Forum.getModeratorList() threw an exception when invoked on [Test Forum, id=1, order=1] The problematic instruction: ---------- ==> assignment: moderators=forum.getModeratorList() [on line 314, column 41 in default/forum_show.htm] ---------- Java backtrace for programmers: ---------- freemarker.template.TemplateModelException: Method public java.util.List net.jforum.entities.Forum.getModeratorList() threw an exception when invoked on [Test Forum, id=1, order=1] at freemarker.ext.beans.SimpleMethodModel.exec(SimpleMethodModel.java:136) at freemarker.core.MethodCall._getAsTemplateModel(MethodCall.java:93) at freemarker.core.Expression.getAsTemplateModel(Expression.java:89) at freemarker.core.Assignment.accept(Assignment.java:90) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.MixedContent.accept(MixedContent.java:92) at freemarker.core.Environment.visit(Environment.java:196) at freemarker.core.Environment.process(Environment.java:176) at freemarker.template.Template.process(Template.java:232) at net.jforum.JForum.processCommand(JForum.java:243) at net.jforum.JForum.service(JForum.java:208) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:62) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.agiletec.cas.jforum.filter.JForumCasSSOTicketFilter.doFilter(JForumCasSSOTicketFilter.java:127) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:619) Caused by: net.jforum.exceptions.DatabaseException: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: character varying = integer at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at net.jforum.dao.generic.GenericForumDAO.getModeratorList(GenericForumDAO.java:499) at net.jforum.repository.ForumRepository.getModeratorList(ForumRepository.java:593) at net.jforum.entities.Forum.getModeratorList(Forum.java:103) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at freemarker.ext.beans.BeansWrapper.invokeMethod(BeansWrapper.java:825) at freemarker.ext.beans.SimpleMethodModel.exec(SimpleMethodModel.java:113) ... 29 more Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLException: ERROR: operator does not exist: character varying = integer ... 45 more Caused by: java.sql.SQLException: ERROR: operator does not exist: character varying = integer ... 45 more
[originally posted on jforum.net by g_cocco80]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, a Jira issue will make sure this doesn't get lost in the forum noise... FWIW, I'd suggest using Integer.toString(..) but that's more style.

One thing I'm not sure if is if this is really a jForum error or a Postgres JDBC issue. I would think that the correct behavior is for the JDBC driver to use the meta information and convert the integer to String internally. Obviously other JDBC drivers do. Have you tried different JDBC drivers?
[originally posted on jforum.net by monroe]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This post made my evening. I didn't find it on first couple quick searches and that error was really bugging me. Thanks!
[originally posted on jforum.net by Tarpan]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

monroe wrote:One thing I'm not sure if is if this is really a jForum error or a Postgres JDBC issue. I would think that the correct behavior is for the JDBC driver to use the meta information and convert the integer to String internally. Obviously other JDBC drivers do. Have you tried different JDBC drivers?



I try with postgresql-8.3-603.jdbc4.jar that is in the tomcat classpath and removing postgresql-8.0-313.jdbc3.jar
from WEB-INF/lib of jforum. But I have the same error.
[originally posted on jforum.net by g_cocco80]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, it was worth the try... won't be the first time application code has to work around (IMHO) a driver specific bug.
[originally posted on jforum.net by monroe]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I fixed this problem by editing $CATALINA_HOME/webapps/jforum/WEB-INF/config/database/generic/generic_queries.sql

There are 7 lines you need to change.

Line 42:
from ConfigModel.update = UPDATE jforum_config SET config_value = ? WHERE config_name = ?
to ConfigModel.update = UPDATE jforum_config SET config_value = CAST(? AS VARCHAR(255)) WHERE config_name = ?

Line 248:
from ForumModel.statsFirstPostTime = SELECT MIN(post_time) FROM jforum_posts WHERE post_time > 0
to ForumModel.statsFirstPostTime = SELECT MIN(post_time) FROM jforum_posts WHERE post_time is not null and post_time > '2000-01-01'


Line 249:
from ForumModel.statsFirstRegisteredUserTime = SELECT MIN(user_regdate) FROM jforum_users WHERE user_regdate > 0
to ForumModel.statsFirstRegisteredUserTime = SELECT MIN(user_regdate) FROM jforum_users WHERE user_regdate is not null and user_regdate > '2000-01-01'

Line 279:
from AND rv.role_value = ? \
to AND rv.role_value = CAST(? AS VARCHAR(255)) \

Line 449:
from AND rv.role_value = ?
to AND rv.role_value = CAST(? AS VARCHAR(255))

Line 451:
from PermissionControl.deleteRoleValues = DELETE FROM jforum_role_values WHERE role_value = ? AND role_id IN (#IDS#)
to PermissionControl.deleteRoleValues = DELETE FROM jforum_role_values WHERE role_value = CAST(? AS VARCHAR(255)) AND role_id IN (#IDS#)

Line 521:
from UserSessionModel.update = UPDATE jforum_sessions SET session_start = ?, session_time = ?, session_id = ? WHERE session_user_id = ?
to UserSessionModel.update = UPDATE jforum_sessions SET session_start = ?, session_time = ?, session_id = CAST(? AS VARCHAR(150)) WHERE session_user_id = ?

I'm actually not sure about lines 248 & 249. I haven't found a case yet where it doesn't work, but it seems pointless to specify a date > '2001-01-01' because that's the minimum value for that data type in PostgreSQL. Maybe the "not null" is sufficient.

I haven't tested this with other databases yet, but it should work. I've avoided any postgres-specific syntax. I think a more satisfactory long-term fix would be to change the data types of the columns that are being treated like integers to integer.

These all involve the same kind of error, i.e. comparing data of different types with =, < or >. At some point PostgreSQL got a whole lot less tolerant of this sort of thing.

I wasn't able to check Jira to see if this had been reported because it's been down for about two days now. They don't say where we're supposed to report that.
[originally posted on jforum.net by Isadore Nabi]
 
reply
    Bookmark Topic Watch Topic
  • New Topic