Forum OpenACS Development: acs_message__new

Collapse
Posted by Iuri Sampaio on
Is there anyone aware of this error when adding a new comment?

I ran a fresh install of oacs core 5.5 and 5.6. Then i installed xowiki and general comments as well.

So far so good untill i try to add a comment within a xowiki page.

The error is in the API acs_message__new

api-doc/plsql-subprogram-one?type=FUNCTION&name=acs_message__new

cheers,

[20/Oct/2010:17:58:20][17462.3052403600][-default:15-] Notice: 1408 | 1506 | test xowiki | text/plain | 1408 | 653 | 192.168.6.157 | t | | xowiki
[20/Oct/2010:17:58:20][17462.3052403600][-default:15-] Error: Ns_PgExec: result status: 7 message: ERRO: coluna "get_value__parameter_id" não existe
LINE 1: ...es v where v.package_id = $1 and parameter_id = get_value_...
^
QUERY: select attr_value from apm_parameter_values v where v.package_id = $1 and parameter_id = get_value__parameter_id
CONTEXT: PL/pgSQL function "apm__get_value" line 9 at SQL statement
comando SQL "select apm__get_value(package_id, 'SystemURL') from apm_packages where package_key='acs-kernel'"
PL/pgSQL function "acs_message__new" line 39 at SQL statement
PL/pgSQL function "acs_message__new" line 20 at RETURN

[20/Oct/2010:17:58:20][17462.3052403600][-default:15-] Warning: /comments/comment-add-3 has no doc(title) set.
[20/Oct/2010:17:58:20][17462.3052403600][-default:15-] Error: POST http://teste2.softwarepublico.gov.br:80/comments/comment-add-3?
referred by "http://teste2.softwarepublico.gov.br/comments/comment-add-2";
Database operation "0or1row" failed (exception ERROR, "ERRO: coluna "get_value__parameter_id" não existe
LINE 1: ...es v where v.package_id = $1 and parameter_id = get_value_...
^
QUERY: select attr_value from apm_parameter_values v where v.package_id = $1 and parameter_id = get_value__parameter_id
CONTEXT: PL/pgSQL function "apm__get_value" line 9 at SQL statement
comando SQL "select apm__get_value(package_id, 'SystemURL') from apm_packages where package_key='acs-kernel'"
PL/pgSQL function "acs_message__new" line 39 at SQL statement
PL/pgSQL function "acs_message__new" line 20 at RETURN
")

ERRO: coluna "get_value__parameter_id" não existe
LINE 1: ...es v where v.package_id = $1 and parameter_id = get_value_...
^
QUERY: select attr_value from apm_parameter_values v where v.package_id = $1 and parameter_id = get_value__parameter_id
CONTEXT: PL/pgSQL function "apm__get_value" line 9 at SQL statement
comando SQL "select apm__get_value(package_id, 'SystemURL') from apm_packages where package_key='acs-kernel'"
PL/pgSQL function "acs_message__new" line 39 at SQL statement
PL/pgSQL function "acs_message__new" line 20 at RETURN

SQL:
select acs_message__new (
'1506', -- 1 p_message_id
NULL, -- 2 p_reply_to
current_timestamp, -- 3 p_sent_date
NULL, -- 4 p_sender
NULL, -- 5 p_rfc822_id
'test xowiki', -- 6 p_title
NULL, -- 7 p_description
'text/plain', -- 8 p_mime_type
NULL, -- 9 p_text
NULL, -- empty_blob(), -- 10 p_data
-4, -- 11 p_parent_id
'1408', -- 12 p_context_id
'653', -- 13 p_creation_user
'192.168.6.157', -- 14 p_creation_ip
'acs_message', -- 15 p_object_type
't' -- 16 p_is_live
)

while executing
"ns_pg_bind 0or1row nsdb0 {
select acs_message__new (
:comment_id, -- 1 p_message_id
NULL, -- 2 p_reply_to
current_timestamp, -- 3 p..."
("uplevel" body line 1)
invoked from within
"uplevel $ulevel [list ns_pg_bind $type $db $sql]"
invoked from within
"db_exec 0or1row $db $full_statement_name $sql"
("uplevel" body line 8)
invoked from within
"uplevel 1 $code_block "
invoked from within
"db_with_handle -dbn $dbn db {
# plsql calls that are simple selects bypass the plpgsql
# mechanism for creating anonymous fun..."
(procedure "db_exec_plsql" line 57)
invoked from within
"db_exec_plsql insert_comment { }"
("uplevel" body line 3)
invoked from within
"uplevel 1 $transaction_code "
(procedure "db_transaction" line 39)
invoked from within
"db_transaction {

db_exec_plsql insert_comment { }

db_dml add_entry { }

db_1row get_revision {}

db_dml set_content {} -blobs [list $content]
..."
(procedure "general_comment_new" line 4)
invoked from within
"general_comment_new -object_id $object_id -comment_id $comment_id -title $title -comment_mime_type $comment_mime_type -context_id $context_id -u..."
("uplevel" body line 42)
invoked from within
"uplevel {
# /packages/general-comments/www/comment-add-3.tcl

ad_page_contract {
Inserts a comment for object_id into the database

@author Ph..."
(procedure "code::tcl::/var/lib/aolserver/openacs/packages/general-comme..." line 2)
invoked from within
"code::tcl::$__adp_stub"
("uplevel" body line 12)
invoked from within
"uplevel {

if { [file exists $__adp_stub.tcl] } {

# ensure that data source preparation procedure exists and is up-to-date
adp_init t..."
(procedure "adp_prepare" line 2)
invoked from within
"adp_prepare"
invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
(procedure "adp_parse_ad_conn_file" line 6)
invoked from within
"$handler"
("uplevel" body line 2)
invoked from within
"uplevel $code"
invoked from within
"ad_try {
$handler
} ad_script_abort val {
# do nothing
}"
invoked from within
"rp_serve_concrete_file [ad_conn file]"
(procedure "rp_serve_abstract_file" line 60)
invoked from within
"rp_serve_abstract_file "$root/$extra_url""
("uplevel" body line 2)
invoked from within
"uplevel $code"
invoked from within
"ad_try {

Collapse
2: Re: acs_message__new (response to 1)
Posted by Iuri Sampaio on
So far, i tracked down the error and fixed on my source code.

The bug is in the source code of acs-kernel version 5.5 and 5.6 (if not also on HEAD) within the file /acs-kernel/sql/postgresql/apm-create.sql line 1537

The error is in the select query in the line 1549:

"and parameter_id = get_value__parameter_id"

when it must be "and parameter_id = v_parameter_id" instead

You can take a look at the psql function from /api-doc

/api-doc/plsql-subprogram-one?type=FUNCTION&name=apm__get_value

create or replace function apm__get_value(int4,varchar) returns varchar as '
1:declare
2: get_value__package_id alias for $1;
3: get_value__parameter_name alias for $2;
4: v_parameter_id apm_parameter_values.parameter_id%TYPE;
5: value apm_parameter_values.attr_value%TYPE;
6:begin
7: v_parameter_id := apm__id_for_name (get_value__package_id, get_value__parameter_name);
8:
9: select attr_value into value from apm_parameter_values v
10: where v.package_id = get_value__package_id
11: and parameter_id = get_value__parameter_id;
12:
13: return value;
14:
15:end;' language 'plpgsql';

cheers,