Forum OpenACS Q&A: More Ecommerce fun!

If you want to spam visitors that looked at a certain product, it records the product_id in ec_spam_log. If there is no product_id, it tries to insert and empty string like this ''

The integrity contrainst for ec_spam_log.product_id is integer references ec_products.

Should we be inserting a NULL here instead of empty string?

Here is the offending code and error from the log:

from spam-3.tcl:

...
if { ![info exists product_id] } {
    set product_id ""
...

ns_db dml $db "insert into ec_spam_log
(spam_id, spam_text, mailing_list_category_id, 
mailing_list_subcategory_id, mailing_list_subsubcategory_id, 
user_class_id, product_id, last_visit_start_date, last_visit_end_date)
values
($spam_id, '$QQmessage', '$mailing_list_category_id', '$mailing_list_s
ubcategory_id', '$mailing_list_subsubcategory_id', '$user_class_id', '
$product_id', to_date('$start_date','YYYY-MM-DD HH24:MI:SS'), to_date
('$end_date','YYYY-MM-DD HH24:MI:SS'))
"

and from the log:

[19/Feb/2001:16:00:03][2611.4101][-conn0-] Error: Ns_PgExec: result 
status: 7 message: ERROR:   referential integrity violation -
 key referenced from ec_spam_log not found in ec_products

[19/Feb/2001:16:00:03][2611.4101][-conn0-] Error: dbinit: error
(localhost::nh,ERROR:   referential integrity violation - 
key referenced from ec_spam_log not found in ec_products
): 'insert into ec_spam_log
(spam_id, spam_text, mailing_list_category_id, 
mailing_list_subcategory_id, mailing_list_subsubcategory_id, 
user_class_id, product_id, last_visit_start_date, last_visit_end_date)
values
(4, 'Mailing list spam test', '4', '', '', '', '', to_date('','YYYY-
MM-DD HH24:MI:SS'), to_date('','YYYY-MM-DD HH24:MI:SS'))
'
[19/Feb/2001:16:00:03][2611.4101][-conn0-] Error: Database 
operation "dml" failed
Database operation "dml" failed
    while executing
"ns_db dml $db "insert into ec_spam_log
(spam_id, spam_text, mailing_list_category_id, 
mailing_list_subcategory_id, mailing_list_subsubcategory_id, use..."
    (file "/web/nh/www/admin/ecommerce/customer-service/spam-3.tcl" 
line 158)
    invoked from within
"source $script"
    (procedure "ns_sourceproc" line 6)
    invoked from within
"ns_sourceproc cns3 {}"

I have been working with the ecommerce module for some weeks, and i think i have cleaned the bugs you are pointing, along with some more. I have been using it for some time without errors (but i'm afraid i haven't tested it deeply yet, so maybe there are still some uncaught bugs).

Let me work a couple of days in my local cvs (mostly to get rid of some junk i may have included in the code), and i will post a .diff.

I have a question, though. Should i make my diff against the newest cvs version, or against official 3.2.4 version? I think the last option is better, because you if you have the 3.2.4 version you'll be completely sure than the patch will work without complaints.

What does people think about it?