Forum OpenACS Development: Error in a psql function execution

Collapse
1: Error in a psql function execution
Posted by Pol Vilarmau on 06/03/03 12:19 AM
I'm developing a web project in OpenACS4.6.2 final release.

I've created some new tables and functions for a new package and i've got some problems with one of the functions, but after 2 days searching  the error... maybe it's so easy (surely), but i could'nt find it!

I'm using redhat 7.3 and the version of postgres that comes with this version of the O.S.

Well, this is the error:

/------------------error begin----------------------/
Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {
            select cads_category__new (
                :category_id,
                :short_name,
                :descrip..."
    ("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]} {
            ns_log Debug "PLPGSQL: bypassed anon function"
            set selection [db_exec ..."
    ("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_category {
        begin
            :1 := cads_category.new (
                category_id => :category_id,
                sho..."
    (procedure "cads_category_new" line 3)
    invoked from within
"cads_category_new -category_id $category_id -domain_id $domain_id -short_name $short_name
"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $transaction_code "
    (procedure "db_transaction" line 39)
    invoked from within
"db_transaction {
    cads_category_new -category_id $category_id -domain_id $domain_id -short_name $short_name
}"
    ("uplevel" body line 21)
    invoked from within
"uplevel {
          ad_page_contract {

    Create a new category in a domain.

    @author Pol Vilarmau <mailto:pvilarmau@hotmail.com>
@creation-date 2003-..."
    (procedure "code::tcl::/home/pvilarmau/web/projekt/packages/classificats..." 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 2)
    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..."Database operation "0or1row" failed (exception NSDB, "Query was not a statement returning rows.")
    while executing
"ns_pg_bind 0or1row nsdb0 {
            select cads_category__new (
                :category_id,
                :short_name,
                :descrip..."
    ("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]} {
            ns_log Debug "PLPGSQL: bypassed anon function"
            set selection [db_exec ..."
    ("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_category {
        begin
            :1 := cads_category.new (
                category_id => :category_id,
                sho..."
    (procedure "cads_category_new" line 3)
    invoked from within
"cads_category_new -category_id $category_id -domain_id $domain_id -short_name $short_name
"
    ("uplevel" body line 2)
    invoked from within
"uplevel 1 $transaction_code "
    (procedure "db_transaction" line 39)
    invoked from within
"db_transaction {
    cads_category_new -category_id $category_id -domain_id $domain_id -short_name $short_name
}"
    ("uplevel" body line 21)
    invoked from within
"uplevel {
          ad_page_contract {

    Create a new category in a domain.

    @author Pol Vilarmau <mailto:pvilarmau@hotmail.com>
@creation-date 2003-..."
    (procedure "code::tcl::/home/pvilarmau/web/projekt/packages/classificats..." 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 2)
    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..."

/------------------------error end ---------------------/

the functions, xql and code that i'm executing is:

/------------- code of the page requested begin -----------/

db_transaction {
  cads_category_new -category_id $category_id -domain_id $domain_id -short_name $short_name
}
/------------ code of the page requested end -------------/

/--------------the cads_category_new function begin ------/

cads_category_new (public)

cads_category_new [ -category_id category_id ] -short_name short_name \
    [ -description description ] -domain_id domain_id

Defined in packages/classificats/tcl/cads-procs.tcl

Create a new category.

Switches:
    -category_id (optional)
    -short_name (required)
    -description (optional)
    -domain_id (required)
Source code:

cads_category_new__arg_parser

    return [db_exec_plsql create_category {
        begin
            :1 := cads_category.new (
                category_id => :category_id,
                short_name => :short_name,
                description => :description,
                domain_id => :domain_id
            );
        end;
    }]

/ ---------------- cads_category_new end -----------------/

/ ---------------- the xql for postgres begin ------------/

<fullquery name="cads_category_new.create_category"> <querytext>
select cads_category__new (
                :category_id,
                :short_name,
                :description,
                :domain_id,
                null,
                now(),
                null,
                null,
                'cads_category'
          );
      </querytext>
</fullquery>

/ ------------------ the xql for postgres end ------------/

/-------- the function created in the database begin -----/

create function cads_category__new (integer, varchar, varchar, integer,integer, timestamp, integer, varchar,
varchar)
returns integer as '
declare
p_category_id alias for $1; -- default null
p_short_name alias for $2;
p_description alias for $3; -- default null
p_domain_id alias for $4;
p_context_id alias for $5; -- default null
p_creation_date alias for $6; -- default now()
p_creation_user alias for $7; -- default null
p_creation_ip alias for $8; -- default null
p_object_type alias for $9; -- default ''cads_category''
v_category_id integer;
v_context_id integer;
begin
v_context_id := coalesce(p_context_id, p_domain_id);
v_category_id := acs_object__new(
  p_category_id,
  p_object_type,
  p_creation_date,
  p_creation_user,
  p_creation_ip,
  v_context_id
);

insert into cads_categories
  (category_id, short_name, p_description, p_domain_id);
  return v_category_id;
end;
' language 'plpgsql';

/ ----------- the function created in the database end ----/

Any suggestions? Thanks in advance,

    Pol Vilarmau

Collapse
2: Re: Error in a psql function execution (response to 1)
Posted by Tilmann Singer on 06/03/03 01:15 AM
Pol, first of all you need to find the real database error. The stuff after the first lines of the error you posted is not informative, it's just a tcl stacktrace. To find the real error do a 'tail -f /path/to/your/logfile', reproduce the error and then scroll up - above of 'Database operation "0or1row" failed' you should find some more interesting message from postgresql, which will tell you about the real problem here.

I believe that a more recent nspostgresql driver would show the relevant error conveniently in the browser.

To find out your postgresql version you could do for example: 

$ psql yourdbname
yourdbname=# select version();
Looking at the plpgsql you posted I see an error with this non-legal sql: 
 insert into cads_categories
  (category_id, short_name, p_description, p_domain_id);
Are you sure you created the cads_category__new successfully and you can execute it manually from psql?
Collapse
3: Re: Error in a psql function execution (response to 2)
Posted by Pol Vilarmau on 06/03/03 02:20 PM
Thanks for all Tilmann!

The error was the simple insert query!

The cads_category__new function, was created successfully with the error instead! It's normal?

Anyway, i drop it and create it again.

Before i remove it, i tried to view the error again in the error.log file but the result was the same that i obtain through the browser.

Tanks again for all,

  Pol Vilarmau