SQL Exception when trying to view RSS feeds

Ranch Hand

Posts: 17424

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Hi,

When I try to view an RSS feed in jforum 2.1.8, I get a sequel exception. I installed version 2.1.8 manually twice in a new oracle 9 schema. Both times I had the same problem. Everything else is working perfectly... Does anyone know what could be wrong?

Thanks for any help!
Alex

The stacktrace is as follows:


[originally posted on jforum.net by buijs]

Migrated From Jforum.net

Ranch Hand

Posts: 17424

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

I meet the same problem some days ago. just change some script:

in /WEB-INF/config/database/oracle/oracle.sql, you need to find out "PostModel.selectLatestByForumForRSS" and change it:

reference to my post : https://coderanch.com/t/577641

hope help.
[originally posted on jforum.net by Patrick]

Migrated From Jforum.net

Ranch Hand

Posts: 17424

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

Thank you so much! That was it: just a small change in oracle.sql. RSS now works like a charm!
[originally posted on jforum.net by buijs]

Migrated From Jforum.net

Ranch Hand

Posts: 17424

posted 16 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

One addition: the recent topics feed also throws an SQL error: java.sql.SQLException: ORA-00904: "LINENUM": invalid identifier

I fixed this by changing the PostModel.selectHotForRSS query in oracle.sql. I also changed some of the logic so that the feed now shows the same items (limited to 15) as the page itself.

To sum it up, the necessary changes for all RSS feeds for an jforum Oracle installation should be made in oracle.sql from line 56:

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 <= ?
	
PostModel.selectHotForRSS = SELECT * FROM ( \
		SELECT t.topic_id, t.topic_title AS subject, p.post_id, t.forum_id, pt.post_text, p.post_time, p.user_id, u.username, \
		ROW_NUMBER() OVER(ORDER BY topic_type DESC, topic_last_post_id DESC) - 1 LINENUM \
		FROM jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u \
		WHERE p.post_id = t.topic_last_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 \
	) \
	WHERE LINENUM < ?

Whereas the original is as follows:

PostModel.selectLatestByForumForRSS = SELECT * FROM ( \
		SELECT p.topic_id, 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 <= ?
	
PostModel.selectHotForRSS = SELECT * FROM ( \
		SELECT t.topic_id, t.topic_title AS subject, p.post_id, t.forum_id, pt.post_text, p.post_time, p.user_id, u.username, \
		ROW_NUMBER() OVER(ORDER BY topic_first_post_id DESC) \
		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 \
		ORDER BY topic_first_post_id DESC \
	) \
	WHERE LINENUM <= ?

Regards,
Alex
[originally posted on jforum.net by buijs]

Migrated From Jforum.net

Ranch Hand

Posts: 17424

posted 15 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

In case anyone is interested I also go this to work with SQLServer:

PostModel.selectLatestByForumForRSS = SELECT * FROM ( \
SELECT ROW_NUMBER() OVER (ORDER BY t.topic_views DESC) AS rownumber, \
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 \
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 = ? ) AS tmp \
WHERE rownumber <= ?

PostModel.selectHotForRSS = SELECT * FROM ( \
SELECT ROW_NUMBER() OVER (ORDER BY t.topic_views DESC) AS rownumber, \
t.topic_id, t.topic_title AS subject, p.post_id, t.forum_id, pt.post_text, p.post_time, p.user_id, u.username \
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 \
) AS tmp \
WHERE rownumber <= ?

Cheers
[originally posted on jforum.net by prb75]

Won't you please? Please won't you be my neighbor? - Fred Rogers. Tiny ad:

We need your help - Coderanch server fundraiser

https://coderanch.com/wiki/782867/Coderanch-server-fundraiser