Forum OpenACS Q&A: Error in general-comments/upload-attachment.tcl

I tried to fix an error in general-comments where it's impossible to add an attachment to the comment due to some unported Oracle blob dml operations.
I compared with how it was done in pvt/portrait/upload-2.tcl and fooled myself into believing it was a piece of cake (maybe it is for some people).
What I did was adding a line in the data model (general_comments):
lob			integer references lobs,

...and did this in general-comments/upload-attachement.tcl:
#ns_ora blob_dml_file $db "update general_comments 
#set attachment = empty_blob(),
#    client_file_name = '[DoubleApos $client_filename]',
#    file_type = '[DoubleApos $guessed_file_type]',
#    file_extension = '[DoubleApos $file_extension]',
#    caption = '$QQcaption',
#    original_width = [ns_dbquotevalue $original_width number],
#    original_height = [ns_dbquotevalue $original_height number]
#where comment_id = $comment_id
#returning attachment into :1" $tmp_filename


set attachment [database_to_tcl_string $db "select empty_lob()"]

ns_db dml $db "begin"
ns_db dml $db "update general_comments
set lob = $attachment,
    client_file_name = '[DoubleApos $client_filename]',
    file_type = '[DoubleApos $guessed_file_type]',
    file_extension = '[DoubleApos $file_extension]',
    caption = '$QQcaption',
    original_width = [ns_dbquotevalue $original_width number],
    original_height = [ns_dbquotevalue $original_height number]
where comment_id = $comment_id"

ns_pg blob_dml_file $db $attachment $tmp_filename
ns_db dml $db "end"
All I got was the following error:
[19/Feb/2001:14:38:17]
    Error: Ns_PgExec: result status: 7 message: ERROR:  
referential integrity violation - key referenced from general_comments
not found in lobs
    

[19/Feb/2001:14:38:17]
    Error: dbinit: error(localhost::infogettable,ERROR:  
referential integrity violation - key referenced from general_comments
not found in lobs
    ): 'update general_comments
    set lob = 12,
        client_file_name = 'error.jpg',
        file_type = 'image/jpeg',
        file_extension = 'jpg',
        caption = 'sdjkigsd',
        original_width = '70',
        original_height = '50'
    where comment_id = 7'
Would someone care to help? Thanks,
Collapse
Posted by Don Baccus on
Check out lob.sql, there's a trigger you need to define on the table that contains lobs.  There's sketchy but complete documentation on how to use the facility there.

Coincidently, I fixed this myself in general comments about four-five days ago but haven't committed the fixes yet.

Collapse
Posted by Don Baccus on
I committed my changes to general-comments.  They've been tested in
the context of a customized site I'm working on and work fine there.
Thank you!