From: GOLD@ILP.MIT.EDU (Mark D. Gold)
Subject: Help - ABF/4GL questions.
Organization: National Radio Astronomy Observatory
Date: Thu, 31 Dec 1992 18:35:16 GMT

Hi!

I'm having several problems writing 4GL procedures.  I was hoping 
that an INGRES expert could point me in the right direction.

1.  One 4GL procedure does the following work:

	a.  create temporary table x

	b.  3 large, complex inserts (with selects) into table x

	c.  3 large, complex select loops which each have
            REPEATED inserts into table x within the loops
            (the select loops also call C procedures)

        d.  create array z, in which is stored a large list of id#s

	e.  unload array z statement with 7 REPEATED insert
            statements into table x within the unload statement

        f.  modify table x to heapsort on....

	g.  call report passing temporary table.


    If I use REPEATED INSERTS throughout the 4GL procedure, the 
procedure runs approximately 4 times faster than when I just use 
regular INSERTS (i.e., 15 minutes : 1 hour).  However, I need to be 
able to define the name of the temporary table ("table x" above) at 
the start of the 4GL procedure based on the username and sessionid 
using dbmsinfo.  This means (I assume) that I would have to store the 
temporary table name in a variable at the start of the 4GL procedure. 
Unfortunately, I can't use a REPEATED INSERT into a tablename that is 
stored in a variable, even though the variable will not be changing 
thoughout the 4GL procedure.

This limitation means that we have to either (1) live with an 
extremely slow 4GL procedure, or (2) hard-code the temporary table 
name into the 4GL procedure, which means that each username will have a 
copy of that temporary table AND only one person at a time logged 
onto the same account will be able to use the temporary table.
Option #1 is unacceptable for us.  

We were planning to create one username for each company that might 
use our database.  Several employees of the company can be logged on 
the same username (account) at once.  If we hard-code the temporary 
table name into the 4GL procedure (Option #2), we will have to make 
certain that two users logged on to the same account cannot use the 
4GL procedure at the same time.

Does this mean that I have to take out an exclusive table lock on the 
temporary table (set lockmode on tablename where readlock = exclusive)
to keep another user on the same account from running the 4GL 
procedure?  How can I see if the temporary table is locked 
by another user at the start of the 4GL procedure so that I can tell 
the second user to try again later?

One developer suggested that I DON'T use INGRES locking to prevent 
two users in the same account from using the 4GL procedure, but 
that I create my own locking table for the database that inserts a 
row for a username when the 4GL procedure is being used and deletes 
the row when the procedure finishes.  She didn't think that relying 
on INGRES locking for what I wanted to do was a good idea.  Any 
thoughts?  However, how would I delete a row in this lock table if 
the user CTRL-Y's or CTRL-C's out of the application?

2.  I need to be able to run a clean_up procedure specific to each 
4GL procedure if a user CTRL-Y's, CTRL-C's, or F6's (VMS) interrupts 
out of my application.  This local clean_up procedure would include 
deleting rows from various temporary tables and setting flags on 
other tables.

I already do this for INGRES errors at the end of each transactions:

             INQUIRE_INGRES (h_error=errorno,
                             row_count=rowcount);
                IF :h_error != 0 THEN
                    CALLPROC err_message
                       (error_num =e$fatal_error,
                        form_name = :cur_frame);
                    ROLLBACK;
=================>> ret_code = CALLPROC clean_up;
                    RETURN r$error;
                ELSE
                    COMMIT;
                ENDIF;

but I don't know how to capture any of the possible interrupts 
(CTRL-Y, CTRL-C, F6) and the rollback and then run my clean_up 
procedure.  Any ideas would be greatly appreciated.
			- Mark
--------------------------------------------------------------
Mark D. Gold             Massachusetts Institute of Technology
gold@ilp.mit.edu         Industrial Liaison Program
(617) 253-0430           Cambridge, Massachusetts  02139

Newsgroups: comp.databases.ingres