Forum .LRN Q&A: problem when posting to a forum

It seems like the posting_date variable in forums/www/message-post.tcl is only being used by message-preview-chuck then discarded.

Problem arises when forums::message::new is called and no posting_date was passed resulting in an error because posting_date in forums_messages table is not nullable.

Temporary fix: I just replicated the "select sysdate" call right before calling forums::message::new and passed the resulting variable. Although the "preview" data is not anymore in sync with the actual data.

One error I found is in the message-post-oracle.xql, one query's name is message_select, it should be select_date.

Other than that, code looks good. But I'm stumped on why I'm still getting this error...

ora8.c:3568:ora_tcl_command: error in `OCIStmtExecute ()': ORA-01400: cannot insert NULL into ("ROBS"."FORUMS_MESSAGES"."POSTING_DATE") ORA-06512: at "ROBS.FORUMS_MESSAGE", line 50 ORA-06512: at line 4

... when my forums_messages packages clearly defines the default of posting_date to be sysdate. I can't quite catch it yet but it's less likely that this is an Oracle issue as I'm testing on two different machines with two different Oracle installs. I think I need more coffee... =)

Arjun,

I now see why I still can't post. It's because when forum::message::new is called, posting_date defaults to an empty string which gets passed to the pl/sql proc which sees that posting date is not null because it contains an empty string and tries to insert that.

My temporary fix is to change the following line in forums/tcl/message-procs.tcl

 
oacs_util::vars_to_ns_set -ns_set $extra_vars -var_list {forum_id 
message_id parent_id subject content html_p user_id posting_date}  

to...

if {[empty_string_p $posting_date]} { 
    oacs_util::vars_to_ns_set -ns_set $extra_vars -var_list {forum_id 
message_id parent_id subject content html_p user_id}
} else { 
    oacs_util::vars_to_ns_set -ns_set $extra_vars -var_list {forum_id 
message_id parent_id subject content html_p user_id posting_date}
} 

Arjun,

It happens on my side whenever I post anything to a forum. The proc forums::message::new has a 'posting_date ""' parameter which passes an empty string to Oracle. I'm not an Oracle guru but I presume it sees that it has a parameter, albeit empty, thus not assigning the default sysdate to it. Then when the insert happens, it fails because posting_date is not nullable. That's why I came up with the above temporary fix. I'm using Oracle 8.1.7 and the 2.6 oracle driver from the old arsdigita aolserver distribution if in case it's an Oracle issue. And I get this on two different servers connecting to two different Oracle dbs.

Is there an oracle driver newer than 2.6?  Aolserver seems to have
dropped their support for the oracle driver as they don't have it
anymore on their download page.  The newest I can find on the
OpenACS software page is the one included in aolserver3.3.ad13 which
is what I'm using now.  new-file-storage also yield no results. I'd
appreciate it if you can point out it's location.  Thanks.

I'm having exactly the same problem with forum postings.
I have installed dotLRN using the latest CVS (for OpenACS + dotLRN packages, as per instructions) with AOLServer 3.3ad13 and Oracle 8.1.7 on RH Linux 7.2 (although Java & InterMedia were added to Oracle after the initial Oracle install).

I also noticed that the demo Collaboraid dotLRN site (http://dotlrn.collaboraid.net/register/guest-user-login) has a problem with posting into a forum (although I can't confirm it's the same one, as no debug info is shown). I then tried the SloanSpace dotLRN guest account, and could post a new message, but when I tried deleting it, the systen produced a Request Error (I have shown only the first 10 or so lines)

Error Message: ------------------
ora8.c:3930:ora_tcl_command: error in `OCIStmtExecute ()': ORA-02292: integrity constraint (SSV2.NOTIF_REPONSE_ID_FK) violated - child record found
ORA-06512: at "SSV2.ACS_OBJECT", line 104
ORA-06512: at "SSV2.FORUMS_MESSAGE", line 131
ORA-06512: at "SSV2.FORUMS_MESSAGE", line 166
ORA-06512: at line 3

SQL:
            declare begin
                forums_message.delete_thread(:message_id);
            end;

    while executing
"ns_ora dml nsdb0 {
End: ------------------

While I can implement the fix described by Deds, is the problem a dotLRN one, or is something else (I noticed the questions about OpenACS Oracle driver, but there has not yet been a reply)?

http://dotlrn.openforce.net/bugs/bug?bug%5fnumber=11