Forum .LRN Q&A: Fixed this bug but encountered a new one . . .

Hello Everyone,

Am usgin PG7.2. I checked out the latest copy of bulkmail and I also encountered the same problem above.

I found that it was this query that was the problem

    set query "
        select '$from' as from_addr,
               '$sender_first_names' as sender_first_names,
               '$sender_last_name' as sender_last_name,
               parties.email,
              'decode(acs_objects.object_type,
                      'user',
                      (select first_names
                       from persons
                       where person_id = parties.party_id),
                      'group',
                      (select group_name
                       from groups
                       where group_id = parties.party_id),
                      'rel_segment',
                      (select segment_name
                       from rel_segments
                       where segment_id = parties.party_id),
                      '') as first_names,
               decode(acs_objects.object_type,
                      'user',
                      (select last_name
                       from persons
                       where person_id = parties.party_id),
                      '') as last_name,
               '$community_name' as community_name,
               '$community_url' as community_url
            from party_approved_member_map,
                 parties,
                 acs_objects
            where party_approved_member_map.party_id = $segment_id
            and party_approved_member_map.member_id <> $segment_id
            and party_approved_member_map.member_id = parties.party_id
            and parties.party_id = acs_objects.object_id
    "

It is found in /dotlrn/www/spam.tcl.

I corrected it for postgres by replacing decode with case when - here is what it looks now

	set query "
        select '$from' as from_addr,
               '$sender_first_names' as sender_first_names,
               '$sender_last_name' as sender_last_name,
               parties.email,
			   CASE
			   		WHEN acs_objects.object_type = 'user' THEN (select first_names from persons where person_id = parties.party_id)
					WHEN acs_objects.object_type = 'group' THEN  (select group_name from groups where group_id = parties.party_id)
					WHEN acs_objects.object_type = 'rel_segment' THEN (select segment_name from rel_segments where segment_id = parties.party_id)
					ELSE ''
 				END as first_names,
				CASE
					WHEN acs_objects.object_type = 'user' THEN (select last_name from persons where person_id = parties.party_id)
					ELSE ''
				END as last_name,
               '$community_name' as community_name,
               '$community_url' as community_url
            from party_approved_member_map,
                 parties,
                 acs_objects
            where party_approved_member_map.party_id = $segment_id
            and party_approved_member_map.member_id <> $segment_id
            and party_approved_member_map.member_id = parties.party_id
            and parties.party_id = acs_objects.object_id
	"

After the above fix, I found a new error which seems very rooted into the ACS Objects procs that I am having difficulty fixing. Can you guys take a look ?

[26/Sep/2002:13:32:15][5858.449541][-conn15-] Notice: Querying '

        select bulk_mail__new();'
[26/Sep/2002:13:32:15][5858.449541][-conn15-] Error: Ns_PgExec: result status: 7 message: ERROR:  Function 'bulk_mail__new()' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts

[26/Sep/2002:13:32:15][5858.449541][-conn15-] Error: dbinit: error(localhost::robs,ERROR:  Function 'bulk_mail__new()' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts
): '

        select bulk_mail__new()

      '
[26/Sep/2002:13:32:15][5858.449541][-conn15-] Notice: RP (3396.696 ms): error in rp_handler: serving POST /dotlrn/clubs/academic-pentathlon/spam
        ad_url "/dotlrn/clubs/academic-pentathlon/spam" maps to file "/web/robs/packages/dotlrn/www/spam.adp"
errmsg is Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
[26/Sep/2002:13:32:15][5858.449541][-conn15-] Notice: Querying '
            select 1
            where 't' = acs_permission__permission_p('50951', '2577', 'admin');'
[26/Sep/2002:13:32:16][5858.449541][-conn15-] Notice: dbinit: sql(localhost::robs): '
            select 1
            where 't' = acs_permission__permission_p('50951', '2577', 'admin')
        '
[26/Sep/2002:13:32:16][5858.449541][-conn15-] Error: POST http://robs.ham.ip.inet/dotlrn/clubs/academic-pentathlon/spam?
referred by "http://robs.ham.ip.inet/dotlrn/clubs/academic-pentathlon/spam"
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {

        select bulk_mail__new()

      }"