Forum OpenACS Development: psql error in a function

Collapse
Posted by Pol Vilarmau on
Hi! I've got some problems with a new package

Well, first... i'm working with redhat 7.3 , OpenACS 4.5 and postgres 7.1.3.

I'm triyin to insert some values in a table executing the next code in a web page after catching the values of a form:

db_transaction {
cads_domain_new -domain_id $domain_id -short_name
$short_name\
-description $description -active_p $active_p \
-cads_id $package_id -creation_user
[ad_verify_and_get_user_id] \
-creation_ip [ad_conn peeraddr] -context_id $package_id
}

in the tcl directory of the package i've got de next functions:

function in cads-procs.tcl:

ad_proc -public cads_domain_new {
    {-domain_id ""}
    {-short_name:required}
    {-description ""}
    {-active_p t}
    {-cads_id:required}
    {-context_id ""}
    {-creation_user ""}
    {-creation_ip ""}
} {
    Create a new domain.
} {
    return [db_exec_plsql create_domain {
        begin
            :1 := cads_domain.new (
                domain_id => :domain_id,
                short_name => :short_name,
                description => :description,
                active_p => :active_p,
                cads_id => :cads_id,
                context_id => :context_id,
                creation_user => :creation_user,
                creation_ip => :creation_ip
            );
        end;
    }]
}

function in cads-procs-postgresql.xql:

<fullquery name="cads_domain_new.create_domain">
      <querytext>
            select cads_domain__new (
                :domain_id,
                :short_name,
                :description,
                :active_p,
                :cads_id,
                :context_id,
                now(),
                :creation_user,
                :creation_ip,
                'cads_domain'
            );
      </querytext>
</fullquery>

finally, i've got de next function loaded in the database:

create function cads_domain__new (integer, varchar, varchar, char, integer,
                integer, timestamp, integer, varchar, varchar)
returns integer as '
declare
    p_domain_id    alias for $1;    -- default null
    p_short_name    alias for $2;
    p_description    alias for $3;    -- default null
    p_active_p    alias for $4;    -- default ''f''
    p_cads_id    alias for $5;
    p_context_id    alias for $6;    -- default null
    p_creation_date    alias for $7;    -- default now()
    p_creation_user    alias for $8;    -- default null
    p_creation_ip    alias for $9;    -- default null
    p_object_type    alias for $10;    -- default ''cads_domain''
    v_context_id    integer;
    v_domain_id    integer;
begin
    v_context_id := coalesce(p_context_id, p_cads_id);
    v_domain_id := acs_object_new (
    p_domain_id,
    p_object_type,
    p_creation_date,
    p_creation_user,
    p_creation_ip,
    v_context_id
    );

    insert into cads_domains
            (domain_id, short_name, description, active_p, cads_id)
        values (v_domain_id, p_short_name, p_description, p_active_p, p_cads_id);
    return v_domain_id;
end;
' language 'plpgsql';

/*****************/

After the execution this is the error served:

Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")

ERROR:  parser: parse error at or near "for"

SQL:
            select cads_domain__new (
                '7337',
                'Compartir pis',
                'la teva',
                't',
                '7276',
                '7276',
                now(),
                '2537',
                '192.168.1.2',
                'cads_domain'
            );

    while executing
"ns_pg_bind 0or1row nsdb0 {
            select cads_domain__new (
                :domain_id,
                :short_name,
                :description..."
    ("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"
    invoked from within
"if {[regexp -nocase -- {^\s*select} $test_sql match]} {
            db_qd_log Debug "PLPGSQL: bypassed anon function"
            set selection [db_ex..."
    ("uplevel" body line 6)
    invoked from within
"uplevel 1 $code_block "
    invoked from within
"db_with_handle db {
        # plsql calls that are simple selects bypass the plpgsql
        # mechanism for creating anonymous functions (OpenACS - ..."
    (procedure "db_exec_plsql" line 13)
    invoked from within
"db_exec_plsql create_domain {
        begin
            :1 := cads_domain.new (
                domain_id => :domain_id,
                short_name =>..."
    (procedure "cads_domain_new" line 3)
    invoked from within
"cads_domain_new -domain_id $domain_id -short_name $short_name  -description $description -active_p $active_p  -cads_id $package_id -creation_user [ad_..."
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $transaction_code "
    (procedure "db_transaction" line 1)
    invoked from within
"db_transaction {
    cads_domain_new -domain_id $domain_id -short_name $short_name  -description $description -active_p $active_p  -cads_id $package_i..."
    ("uplevel" body line 23)
    invoked from within
"uplevel {
          ad_page_contract {

    Create a new domain.

    @author Pol Vilarmau <mailto:pvilarmau@hotmail.com>
@creation-date 2003-04-08
    @cvs..."
    (procedure "code::tcl::/web/projekt/packages/classified/www/domain-new-2" line 2)
    invoked from within
"code::tcl::$__adp_stub"
    invoked from within
"if { [file exists $__adp_stub.tcl] } {

      # ensure that data source preparation procedure exists and is up-to-date
      adp_init tcl $__adp_stub
..."
    ("uplevel" body line 3)
    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 3)
    invoked from within
"adp_prepare "
    (procedure "template::adp_parse" line 30)
    invoked from within
"template::adp_parse [file root [ad_conn file]] {}"
    (procedure "adp_parse_ad_conn_file" line 7)
    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/$path""
    ("uplevel" body line 2)
    invoked from within
"uplevel $code"
    invoked from within
"ad_try {
    rp_serve_abstract_file "$root/$path"
    set tcl_url2file([ad_conn url]) [ad_conn file]
    set tcl_url2path_info([ad_conn url]) [ad_conn path_inf..."

Anybody could help me? Thank's in advance,

    Pol Vilarmau

Collapse
Posted by Bart Teeuwisse on
Pol,

it appears that the plpgsql function chokes on your parameter declaration. Just a hunch, could it be that you have used a TAB character in between the name parameter and the word for?

Plpgsql doesn't like that at all. If so add at least one space before the word for.

/Bart

Collapse
Posted by Pol Vilarmau on
Thanks Bart! That's the solution!!!! :)