[Info-ingres] Ingres Terminal Monitor - Conditional Execution

[Roy Hann]

Roy Hann wrote:

> Shelia White wrote:

[...]
>> So I only want to run the update part of my script if the input 
>> parameter is set to UPDATE.  I've tried various forms of this  but can't 
>> get it work.  Is it even possible?
>>
>> I suppose I can generate the script on the fly but that's a bit messy.
>
> I can think of a couple of ways to do what you want.  Probably the least
> freaky way of doing it would be to use the terminal monitor (sql) macro
> facility.
>
> It takes some careful study to use it but it allows conditional
> branching in sql scripts.
>
> Take a look at the SQL Reference Guide chapter on Terminal Monitor where
> you'll find a section on Terminal Monitor Macros.

Here's a script I used to conditionally create some users on a system I
was commissioning.  The users might already exist so it checks and
skips parts of the script as necessary.

Roy

-- create NEWAPP users

\nocontinue
\macro

set autocommit on; \p\g
select dbmsinfo('ima_vnode') as host; \p\g
select dbmsinfo('database') as database; \p\g
select dbmsinfo('username') as run_by; \p\g
select date('now') as rundatetime; \p\g

-- create the NEWAPPdba user if necessary
select user_name 
from iiusers 
where user_name = 'NEWAPPdba'; \p\g
\branch ?{ifgt;@{tuplecount};0;1;0}=1 NEWAPPdbaExists
-- the user doesn't exist, so create it
create user NEWAPPdba
  with NOGROUP, 
       NOEXPIRE_DATE,
       privileges =  ( CREATEDB ),
       NOPROFILE, 
	   NOPASSWORD; \p\g
\mark NEWAPPdbaExists

-- check that the (possibly preexisting) NEWAPPdba user has CREATEDB privilege
select 'HAS CREATEDB' 
from iiusers
where user_name = 'NEWAPPdba'
  and createdb = 'Y'; \p\g
\branch ?{ifgt;@{tuplecount};0;1;0}=1 CREATEDBgranted
{type >>>ERROR>>> The existing NEWAPPdba user lacks the CREATEDB privilege.} \v
-- drop dead
\q
\mark CREATEDBgranted

-- create the NEWAPPjru user if necessary
select user_name 
from iiusers 
where user_name = 'NEWAPPjru'; \p\g
\branch ?{ifgt;@{tuplecount};0;1;0}=1 NEWAPPjruExists
-- the user doesn't exist, so create it
create user NEWAPPjru
  with NOGROUP, 
       NOEXPIRE_DATE,
       NOPROFILE, 
	   NOPASSWORD; \p\g
\mark NEWAPPjruExists

-- report users and privileges
select * from iiusers 
where user_name in ( 'NEWAPPdba', 'NEWAPPjru' ); \p\g