Forum OpenACS Development: Basic question. How to run a pg stored procedure from tcl

I have been trying to figure out how to run a pg store procedure
from a tcl file with no success. I have tried to copy the way the
news pakcage does it but it did not work neither.

I have this stored procedure  which works fine when called from
postgres' shell

create function news__unido_document_object ( timestamp, integer,
timestamp, integer,varchar, varchar, bool, integer, timestamp,
integer, timestamp)
returns integer as '

declare

new__publish_date          alias for $1;  -- default now()
new__document_id            alias for $2;  -- default null
new__creation_date          alias for $3;  -- default now()
new__creation_user          alias for $4; -- default null

.....

begin


  v_document_id := acs_object__new(
      new__document_id,
      ''unido_document'',
      new__creation_date,
      new__creation_user,
      new__creation_ip,
      new__contex_id
  );
  insert into unido_documents (
    document_id, parent_document_id, author, expiry_date,
relevant_from_date, relevant_to_date, cleared_for_public_p,
document_type
  ) values (
    v_document_id, new__parent_id, new__creation_user,
new__expiry_date, new__publish_date, new__relevant_to_date,
new__cleared_public_p, new__document_type
  );



  return v_document_id;

end;' language 'plpgsql';

--------------------------

I have the -postgres.xql file that looks like this

<?xml version="1.0"?>

<queryset>
  <rdbms><type>postgresql</type><version>7.1</version></rdbms>

<fullquery name="doc_insert">
      <querytext>
        select news__unido_document_object (
                :publish_date_ansi,
                null,
                :publish_date_ansi,
                :approval_user,
                :approval_ip,
                'tm',
                'Yes',
                null,
                :archive_date_ansi,
                :package_id, null
        );
      </querytext>
</fullquery>

</queryset>

---------------------------------------

and then I have tryed calling from tcl this procedure in this ways
but none works

db_exec_plsql doc_insert (
begin
news.unido_document_object (
new__publish_date => :publish_date_ansi,
new__document_id =>  :null,
new__creation_date => :publish_date_ansi,
new__creation_user => :approval_user,
new__creation_ip => :approval_ip,
new__document_type => :'tm',
new__cleared_public_p => :'Yes',
new__parent_id => :null ,
new__expiry_date => :archive_date_ansi ,
new__contex_id => :package_id ,
new__relevant_to_date => :null

);
end;
)

and

set new_id [db_exec_plsql doc_insert "
begin
:news.unido_document_object (
new__publish_date => :publish_date_ansi,
new__document_id =>  :null,
new__creation_date => :publish_date_ansi,
new__creation_user => :approval_user,
new__creation_ip => :approval_ip,
new__document_type => :'tm',
new__cleared_public_p => :'Yes',
new__parent_id => :null ,
new__expiry_date => :archive_date_ansi ,
new__contex_id => :package_id ,
new__relevant_to_date => :null

);
end;
"]

Could anybody tell me what am I doing wrong? What am I missing? or
Where can I learn How to run a postgres store procedure
from a tcl file?

Thanks in advance,

Hi Tomas,

Since you are using xql files you can just ommit the body part and just go db_exec_plsql doc_insert "" on your tcl file.  The precedense of the Query dispatcher is foo-[dbname].xql, foo.xql then finally foo.tcl.

You may want to look at the APM.  go to http://foohost/acs-admin/apm.  Then click on the package where you have your xql.  Somewhere below the package there is "Manage files... blah".  After clicking here you will see a list of files for that package.  Scroll down and click on "scan for new files... blah".  Then add your xql.

Take the above with a big grain of salt since its been a while I have done OACS coding.  But I know this might help.

In case you still want to run the query from the tcl file directly instead of the xql file, for example because this is custom code that will certainly never need to be ported to another database, then you would have to enter the double underscore postgres syntax in the inline query, e.g. by doing

db_exec_plsql doc_insert {
  begin
  news__unido_document_object (
  ...
  );
  end;
}

not news.unido_document_object, which is the syntax for oracle. The reason why all inline queries in the toolkit are written in this syntax is because they are simply left in there from before the porting effort and they were originally written for oracle, but the query dispatcher chooses those from the xql file if present.

Greetings, I have a related postgres/OpenACS question. The index.tcl file in my www directory runs this query:

db_multirow nodies site_nodes {}

which is in index-postgresql.xql as:
fullquery name="dbqd.www.index.site_nodes"
  querytext
select site_node__url(node_id) as url, acs_object__name(object_id) 
as name
etc.

It seems that no matter what I do to change the query in the .xql or .tcl file for that matter nothing changes (index.xql doesn't have the query in it). The query seems to be stored somewhere permanently. I even put deliberate errors in the .xql file and restart the server and still no change. Does anybody have an idea why? Where else could this query be stored? Thanks, Zack.

That also raises another intersting *feature*.

I don't know if anyone's already done/fixed this, but the query extractor is very poor at reporting potential problems. Errors in .xql file etc (as you mentioned) just get silently ignored.... thats not really ideal and can be v.v irritating....

Is this due to be fixed for the 4.6 release?

Thanks Arjun,

I have used the watch before but only on files that are part of a package.  This file was part of the home page in the www directory and I couldn't figure out how to set the system to watch it.  Any suggestion?

Thanks again, Zack.

Zach,

Create a tcl wrapper inside your package to hold the query.
Call the tcl proc from the www/foo.tcl file.