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,

from news/www/item-create-3.tcl:
set news_id [db_exec_plsql create_news_item {
  begin
  :1 := news.new(
  title           => :publish_title,
  publish_date    => :publish_date_ansi, 
  archive_date    => :archive_date_ansi,
  mime_type       => :mime_type,        
  package_id      => :package_id,       
  approval_user   => :approval_user,     
  approval_date   => :approval_date,      
  approval_ip     => :approval_ip,   
  creation_ip     => :creation_ip,    
  creation_user   => :user_id,     
  is_live_p       => :live_revision_p    
  );
  end;
}]
I replaced the quotation marks with curly braces { }, seems safer to me. Note the :1 = ... at the beginning of the statement.
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.

To add to what Jun said, you can also "watch" your .xql files so that you do not have to restart aolserver to have your subsequent changes reloaded (usually .xql files are loaded only on startup). Go to "manage file information" in APM.

Removing queries from the tcl code, I think, is good practice.

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.
Wou Thanks a lot guys these advices helped me a lot. Now it works :). Thanks a lot again
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.
OK. A partial answer to my own question is that the postgres query
in the .xql file "full query name" should be named without the "."s
And it seems .xql files in the www directory only get loaded after
restarting the server.  Also the queries get stored somewhere in
memory when they are valid; so if I make a deliberate error in the
.xql query after the good query has been loaded once, the good query
is already in memory somewhere so the erroneous query is ignored.

So in summary to change an .xql query in the www directory change
the query then start or restart nsd.  Hope thats good info - if not
please correct me so others that come along and read this won't be
misled.  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?

Zack, you don't need to restart the server everytime you change a
cached .tcl or .xql file.  You should use the very slick "watch"
feature of the APM, which will tell the server to source the file
every time it's accessed. To try it: alter an .xql or .tcl file,
just add a space or something, then go to /acs-admin/apm/?&supertype=all and there wil be a "reload" link. The watch will stay in effect until the server is restarted. Note that watching files slows everything down therefore should not be used on production servers.
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.

My bad, Zack. I assumed that you were in package-land. I don't know of any way to watch a file w/o it being in a package. :(
Zach,

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

Thanks guys.

Dave are you saying the solution is to put a tcl proc which contains the query in a package and then call it from the home page code which isn't currently in any package?  Then I could "watch" the file by selecting to watch it from the APM - do I understand correctly?

Zack.