Forum OpenACS Q&A: Re: spam module charset encoding issue when inserting to blob

Hi again,

There is a bug half related with this topic #822 Add text storage support to cr_import_content.

Maybe adding a CLOB storage type would be the perfect solution, but since I am not very comfortable with this kind of datatype I have done a workaround with varchar2(4000).

So if you want to send emails in iso-8859-2 but reducing the capacity of a 'text' type in your content repository to 4000 characters, here are the changes:

*** Attention! I have not test all the side effects outside the acs-mail and spam packages. UAYOR ***

  • add the 'text' column in your 'cr_revisions' table.
    >alter table CR_REVISIONS add text varchar2(4000)
  • Comment the 'text to lob' action at the beggining of your '' pl/sql function.
    --  if storage_type = 'text' then
    --     v_storage_type := 'lob';
    --  else
        v_storage_type := storage_type;
    --  end if;
  • Modify the '' pl/sql function. This funtion is overloaded, change only te one with the 'text' parameter.
    function new (
      title         in cr_revisions.title%TYPE,
      description   in cr_revisions.description%TYPE default null,
      publish_date  in cr_revisions.publish_date%TYPE default sysdate,
      mime_type   	in cr_revisions.mime_type%TYPE default 'text/plain',
      nls_language 	in cr_revisions.nls_language%TYPE default null,
      text		    in varchar2 default null,
      item_id       in cr_items.item_id%TYPE,
      revision_id   in cr_revisions.revision_id%TYPE default null,
      creation_date	in acs_objects.creation_date%TYPE default sysdate,
      creation_user	in acs_objects.creation_user%TYPE default null,
      creation_ip	in acs_objects.creation_ip%TYPE default null
    ) return cr_revisions.revision_id%TYPE is
      v_revision_id integer;
      v_content_type acs_object_types.object_type%TYPE;
      v_content_type := content_item.get_content_type(item_id);
      v_revision_id :=
          object_id     => revision_id,
          object_type   => v_content_type,
          creation_date => creation_date,
          creation_user => creation_user,
          creation_ip   => creation_ip,
          context_id    => item_id
      insert into cr_revisions (
        revision_id, title, description, mime_type, publish_date,
        nls_language, text, item_id
      ) values (
        v_revision_id, title, description, mime_type, publish_date,
        nls_language, text, item_id
      return v_revision_id;
    end new;
  • Change the 'acs_mail_body_to_mime_get_content_simple' query in the 'packages/acs-mail/tcl/acs-mail-procs.xql' file.
    select content, text, mime_type as v_content_type
            from cr_revisions
            where revision_id = :revision_id
  • Change the 'acs_mail_encode_content' function in the 'packages/acs-mail/tcl/acs-mail-procs.tcl' file.
                    if [db_0or1row acs_mail_body_to_mime_get_content_simple {
                            select content, text, mime_type as v_content_type
                            from cr_revisions
                            where revision_id = :revision_id
                    }] {
                            if [string equal $storage_type text] {
                                    ns_log "Notice" "acs-mail: encode: one part hit $content_item_id"
                                    # vinodk: no need for this, since we're checking
                                    #         storage_type
                                    # We win!  Hopefully.  Check if there are 8bit characters/data.
                                    # HT NL CR SP-~  The full range of ASCII with spaces but no
                                    # control characters.
                                    if ![regexp "\[^\u0009\u000A\u000D\u0020-\u007E\]" $content] {
                                            ns_log "Notice" "acs-mail: encode: good code $content_item_id"
                                            # We're still okay.  Use it!
                                            return [list $v_content_type $text]
                                    ns_log "Notice" "acs-mail: encode: bad code $content_item_id"
                            } else {

I think I don't leave anything, hope it helps,