Hi ken,:

    You can check the catalog iiprocedure.

    Try select dbp_name from iiprocedure where dbp_name=...
and dbp_owner=...

    The terminator is still a ';' but to execute the staement within
the terminal monitor use '\g'

    You don't have to return a value explicitly with the return command.
But if you choose to do so it must be an integer. But there alternatives to
return.

It also appears that I cannot create a variable in the declare

    Not so.

    You may certainly have nullable and non nullable items in the
declare section.

drop procedure test_p;
create procedure test_p (
   a integer with null,
       b varchar(100) with null
   )
as
declare
       dummy   integer with null;
   msg varchar(100) not null;
begin
       if (a is null) then
               msg = 'Hi There: a is null';
       else
       msg = 'Hi There: a = ' + varchar(:a);
       endif;
   message msg;
       dummy=2;
       b='return string';
end;
execute procedure test_p;
execute procedure test_p(a=1);

he only way to return a

No. You have 3 options all of which may process variables of all types,
sizes etc.
The simple one is to pass the variable by reference from the
application via the execute procedure statement with a command like:
    execute procedure test_p(b=byref(:whatever));

In this case the value of return_string is passed to the procedure when
it is executed and the procedure will set the applications host language
variable :return_string. In the above example after concluding execution
of test_p, the application would have 'return string' stored in the variable
'whatever'.

Note that this can only be done from an application and not from the
terminal monitor interface.

As you've been away for the last ten years you probably havent heard
of session temporary tabels and row producing procedures...

The 2nd method is to use the 'set of' parameter to pass the procedure
a session temporary table. The procedure may insert as many rows as
it desires into the table and your calling application may then process
that table (or not) as it wills.

The last option is to have a row producing procedure (as distinct to the
'set of' option. This explicitly produces result rows, which must be
explicitly processed by the application using a begin/end block attached
to the procedure call.
eg execute row_prod_proc ()
    begin
        your code;
    end;

Martin Bowes

1. I dont think you can alter a procedure like MSSQL. The simplest method is
to drop and recreate procedures.  store your permissions in the procedure
scripts.

copydb -with_proc will give you a script with procedures and perms only.

I do this:

\continue    /* continue anyway if the procedure is missing */
drop procedure pw;\g
\nocontinue
create procedure pw as .... \g
grant execute on procedure pw .... \g

But Ingres QUEL has some powerful scripting functionality. I've only dabbled
with it but here goes ...

--- q0.txt ---
\sql
\include q1.txt

--- q1.txt ---
\quel
\macro

retrieve (proc_name=iiprocedures.procedure_name,
proc_owner=iiprocedures.procedure_owner)
where lowercase(iiprocedures.procedure_owner) = "ingres"
    and lowercase(iiprocedures.procedure_name) = "p1"
\p\g
\branch ?{ifgt;@{tuplecount};0;1;0}=0 NoProcedure
{type Procedure exists need to drop it.}\v

\sql
drop procedure p1
\p\g

\quel
\mark NoProcedure
\nomacro

\sql
create procedure p1 as begin return 1 end;
\p\g

------------------
Now, wasnt the first example easier?

In answer to 2.
\g is equivalent to mssql GO

The ; is a mandatory statement separator (unlike mssql)
but is not required before \g

for example

create table pw (v1 varchar(10));
insert into pw values ('a');
insert into pw values ('b');
insert into pw values ('c');
insert into pw values ('d');
commit\g

-----Original Message-----
From: info-ingres-admin@cariboulake.com
[mailto:info-ingres-admin@cariboulake.com]On Behalf Of kenp
Sent: Monday, 26 June 2006 7:45 PM
To: info-ingres@cariboulake.com
Subject: [Info-ingres] Check if Proc exists

I am back working with Ingres after a 10 year gap so I have newbie
questions which I have not been able to easily identify on a FAQ.

1. How do I test for the existence of a Procedure? I want to create
script for my Procedures. In MS SQL I use something like:-

IF NOT EXISTS (SELECT * FROM SYSOBJECTS
WHERE ID = OBJECT_ID(N'Rpt_Batch_Script_Summary')
AND OBJECTPROPERTY(id,N'isprocedure') = 1 AND uid=user_id())
BEGIN
  EXEC('CREATE PROC Rpt_Batch_Script_Summary AS --')
  PRINT "*** DBA PLEASE GRANT PERMISSIONS ***"
END
GO

ALTER PROCEDURE dbo.Rpt_Batch_Script_Summary ...

Which checks for the existence of the Proc, if it does not exist it
creates a blank one, since it now exists it then alters the Proc (this
is better than drop/create because you do not loose any permissions
granted to the Proc).

2. The statement terminator in Ingres is ";". What is the batch
terminator is it "/" or "/g"?

3. It seems the return variable in a procedure must be integer and not
null. It also appears that I cannot create a variable in the declare
section of the Proc which is not null. The only way to return a
variable value from the Proc appears to be to create the variable as a
parameter to the Proc and define it as int not null. Is this correct?

Thanks in advance.

_______________________________________________
Info-ingres mailing list
Info-ingres@cariboulake.com
http://mailman.cariboulake.com/mailman/listinfo.py/info-ingres