Error accessing RSS links using Oracle

[Migrated From Jforum.net]

When I click on the RSS feed link within our forum, I get the following error:

<!-- BEGIN ERROR STACK TRACE
net.jforum.exceptions.ForumException: java.lang.reflect.InvocationTargetException
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:324)
at net.jforum.Command.process(Command.java:114)
at net.jforum.view.forum.RSSAction.process(RSSAction.java:168)
at net.jforum.JForum.processCommand(JForum.java:217)
at net.jforum.JForum.service(JForum.java:200)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1072)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:28)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at net.jforum.util.legacy.clickstream.ClickstreamFilter.doFilter(ClickstreamFilter.java:59)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6987)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3892)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2766)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)
Caused by: java.lang.reflect.InvocationTargetException
... 22 more
Caused by: net.jforum.exceptions.DatabaseException: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4571)
at oracle.jdbc.driver.OraclePreparedStatement.setInt(OraclePreparedStatement.java:4563)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setInt(NewProxyPreparedStatement.java:677)
at net.jforum.dao.generic.GenericPostDAO.selectLatestByForumForRSS(GenericPostDAO.java:493)
at net.jforum.view.forum.RSSAction.forumTopics(RSSAction.java:88)
... 22 more
Caused by: java.sql.SQLException: Invalid column index
... 30 more

URL is: /forum/rss/forumTopics/6.page?null
END ERROR STACK TRACE-->

We are using jforum 2.1.8 deployed in Weblogic 8.1 with an Oracle 10g database. Any ideas on how to resolve this? Thanks!

[originally posted on jforum.net by clc1023]

[Migrated From Jforum.net]

This sounds vaguely familiar... there may be a specific solution already in the forums somewhere...

But the most likely cause is a problem with the PostModel.selectLatestByForumForRSS query in the oracle.sql file (WEB-INF/config/database/oracle dir) and/or the definition of the tables.
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

There is a bug in the query and I already fixed it (one of the columns is listed twice in the select). However, SQLException is thrown when the first variable is being bound in the PreparedStatement. This suggests that the query actually being used by the Java code is not the query listed in oracle.sql, because the query in oracle.sql has two bind variables in it as expected.
[originally posted on jforum.net by clc1023]

[Migrated From Jforum.net]

Have you restarted the web application? The queries are cached in memory.

FWIW, the Oracle DAO driver extends the Generic Driver and since the RSS call doesn't need special Oracle coding, the generic method is being used.

The queries actually come from two places. First, the generic.sql queries are loaded. Then the db specific queries are loaded and any matching generic queries are replaced with the db specific ones.

The DB specific file is found via the database.driver.name config parameter, so make sure this is set to oracle in your custom config file.

Also, make sure that the key for your modified query match exactly the query key being requested. If it doesn't, then the generic query will be used. The code is looking for:

PostModel.selectLatestByForumForRSS

I think case matters for this too!
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

Yes, the application has been restarted and we are still receiving the error. Also, the keys match.

Any other ideas?
[originally posted on jforum.net by clc1023]

[Migrated From Jforum.net]

Hmm, I noticed the URL in your error message looked strange: Ie:

URL is: /forum/rss/forumTopics/6.page?null

I wonder if the ?null part is causing the code that parses out the parameters to fail. Then Oracle is getting a non-number passed to a numeric field and choking on it.
[originally posted on jforum.net by monroe]

[Migrated From Jforum.net]

Hi I had the same problem and I fixed.

The query had two mistakes, this is the correct one:

PostModel.selectLatestByForumForRSS = SELECT * FROM ( \
		SELECT p.topic_id, p.post_id, p.forum_id, pt.post_subject AS subject, pt.post_text, p.post_time, p.user_id, u.username,\
		ROW_NUMBER() OVER(ORDER BY t.topic_id DESC) - 1 LINENUM \
		FROM jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u \
		WHERE p.post_id = t.topic_first_post_id \
		AND p.topic_id = t.topic_id \
		AND p.user_id = u.user_id \
		AND p.post_id = pt.post_id \
		AND p.need_moderate = 0 \
		AND t.forum_id = ? \
		ORDER BY t.topic_id DESC \
	) \
	WHERE LINENUM <= ?

Previously had p.topic_id twice and it was missing a backslash after

 u.username,\

.

Hope it helps.
[originally posted on jforum.net by Runnerdave]