Forum .LRN Q&A: spam mail in dotLRN class gives the following error

Database operation "select" failed (exception NSDB, "Query was not a
statement returning rows.")
    while executing
"ns_db select $db $query"
    invoked from within
"set selection [ns_db select $db $query]"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
            set selection [ns_db select $db $query]
            ns_db flush $db
        }"
    (procedure "bulk_mail::new" line 5)
    invoked from within
"bulk_mail::new  -package_id
[site_node_apm_integration::get_child_package_id -package_key
[bulk_mail::package_key]]  -send_date [template::util::date:..."
    invoked from within
"if {[ns_queryexists "form:confirm"]} {
    form get_values spam_message  community_id from rel_type subject
message send_date referer

    set segment..."
    ("uplevel" body line 61)
    invoked from within
"uplevel {
          #
Heya Ken...
<p>
The thing to do, is watch the log while you generate the error. Almost universally, "Query was not a statement returning rows" can be the result of many different, and more specific, problems. Normally, the server's error log will display the more specific problem.
<p>
Overall, the process involves running a shell on the server machine in question and running "tail -f servername-error.log". Then, you try to cause the problem again, and watch what happens in the log. So if you have a server running on port 1234 whose name is "foo", then you'd cd to the place on your server where the aolserver logs are kept, and run "tail -f foo-error.log".
Or even better

Open emacs and do M-x shell

Then run tail -f error.log from within emacs.

Then you can search and page up and down in the error log.

Alternately use less. F will behave like tail -f. Hit C-c to stop it then use pgup/pgdn or f and b to page back and forth.

[02/Sep/2002:15:12:39][45353.146017280][-conn1-] Error: Ns_PgExec: result status: 7 message: ERROR: Function 'decode(varchar, unknown, varchar, unknown, varchar, unknown, varchar, unknown)' does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts notice: RP (159.813 ms): error in rp_handler: serving POST /dotlrn/classes/center-for-elaw_at_leiden/cpu-werkgeheugen-en-randapparatuur/technische-rechtsinformatica/spam ad_url "/dotlrn/classes/center-for-elaw_at_leiden/cpu-werkgeheugen-en-randapparatuur/technische-rechtsinformatica/spam" maps to file "/web/openacs-4/packages/dotlrn/www/spam.adp" errmsg is Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.") Debug: db_qd_get_fullname: following query in file: packages.acs-tcl.tcl.acs-permissions-procs proc: permission::permission_p Error: POST /dotlrn/classes/center-for-elaw_at_leiden/cpu-werkgeheugen-en-randapparatuur/technische-rechtsinformatica/spam Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.") while executing "ns_db select $db $query" invoked from within "set selection [ns_db select $db $query]" ("uplevel" body line 2) invoked from within "uplevel 1 $code_block " invoked from within "db_with_handle db { set selection [ns_db select $db $query] ns_db flush $db }"

Having the same trouble as Kenneth here. Tried to tail it, but I must admit it's a little bit out of my league. Someone? I tried to mark the relevant text as bold, yet I'm not sure it IS the relevant text, so I added the whole lot.
Known problem ... Janine Sisk of furfly ran into this recently, too.  I'm going to try to find time to port it to PG later today.
Collapse
Posted by Hamilton Chua on
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()

      }"

Hi Ham,

Its seems that your problem is that bulk_mail__new() is not on the db.  So that plsql might not exists.  If it only exists in oracle (maybe.. bulk_mail.new), then you will have to port it.  Or it maybe that the plsql exists but accepts a different argument, either you look at pg_procs table or look at the sql files of bulk mail package.  Do we need this for PT&T?

Sorry not much help now, I need to concentrate on PGDC.

Thanks for taking time to post Jun, but I have already double checked the pg database with pgacess and I found a bulk_mail__new function.

My guess is that the parameters are not being passed properly or are not being passed at all.

i haven't looked at the source yet (working on other stuff) but i'd be willing to bet that the call is failing because there is no "define_function_args" call or it is out of sync with the actual parameters of the function. check the postgres sql file.

note: define_function_args is needed by the package_instantiate_object tcl api

You wre right, it is missing a define arg for bulk mail new. But it was more than that, apparent the function had a few problems with data types so I had to do some type casting with local vars.

The modified statements from bulk-mail-package-create.sql are found below Please see if I did it right.

Now spam.tcl works but when it reaches spam-2 it redirects to the community/control-panel and returns a page not found. I checked my installation and I can't find bulk mail or bullk mail applet or portlest anywhere. It is however, installed in the apm. Am not sure if this is a bug or a messed up installation. Will keep you posted but if you have an idea why please do comment. Thanks.

select define_function_args('bulk_mail__new','bulk_mail_id,package_id,send_date,date_format;YYYY MM DD HH24 MI SS,sent_p:f,from_addr,subject,reply_to,extra_headers,message,query,creation_date,creation_user,creation_ip,context_id');

create function bulk_mail__new (integer, integer, varchar, varchar, varchar, varchar, varchar, varchar, varchar, text, varchar, timestamp, integer, varchar, integer)
returns integer as '
declare
    bulk_mail__new__bulk_mail_id alias for $1; -- default to null
    bulk_mail__new__package_id alias for $2;
    bulk_mail__new__send_date alias for $3; -- default to null
    bulk_mail__new__date_format alias for $4; -- default to "YYYY MM DD HH24 MI SS"
    bulk_mail__new__sent_p alias for $5; -- default to "f"
    bulk_mail__new__from_addr alias for $6;
    bulk_mail__new__subject alias for $7; -- default to null
    bulk_mail__new__reply_to alias for $8; -- default to null
    bulk_mail__new__extra_headers alias for $9; -- default to null
    bulk_mail__new__message alias for $10;
    bulk_mail__new__query alias for $11;
    bulk_mail__new__creation_date alias for $12; -- default to now()
    bulk_mail__new__creation_user alias for $13; -- default to null
    bulk_mail__new__creation_ip alias for $14; -- default to null
    bulk_mail__new__context_id alias for $15; -- default to null
    v_bulk_mail_id integer;
    v_send_date varchar(4000);
    v_sent_p boolean;
    v_date_format char(100);
begin

    v_bulk_mail_id := acs_object__new(
        bulk_mail__new__bulk_mail_id,
        ''bulk_mail_message'',
        bulk_mail__new__creation_date,
        bulk_mail__new__creation_user,
        bulk_mail__new__creation_ip,
        bulk_mail__new__context_id
    );

    if bulk_mail__new__date_format is null then
        -- bulk_mail__new__date_format := ''YYYY MM DD HH24 MI SS'';
	v_date_format := ''YYYY MM DD HH24 MI SS'';
    else
       v_date_format := bulk_mail__new__date_format;
    end if;

    v_send_date := bulk_mail__new__send_date;
    if v_send_date is null then
        select to_char(now(), v_date_format)
        into v_send_date;
    end if;

    if bulk_mail__new__sent_p is null then
        -- bulk_mail__new__sent_p := ''f'';
	     v_sent_p := ''f'';
    else
        v_sent_p := bulk_mail__new__sent_p;
    end if;

    insert
    into bulk_mail_messages
    (bulk_mail_id, package_id,
     send_date, sent_p,
     from_addr, subject, reply_to,
     extra_headers, message, query)
    values
    (v_bulk_mail_id, bulk_mail__new__package_id,
     to_date(v_send_date,v_date_format), v_sent_p,
     bulk_mail__new__from_addr, bulk_mail__new__subject, bulk_mail__new__reply_to,
     bulk_mail__new__extra_headers, bulk_mail__new__message, bulk_mail__new__query);

    return v_bulk_mail_id;

end;
' language 'plpgsql';